Ivan Kartik - Oracle and Linux Blog

Be(a)ware of Licensing policy changes regarding Infrastructure Repository Databases

Oracle Database 12c comes with another significant change regarding using of Infrastructure Repository Databases and which could eventually make things more complicated. Before upgrade of Oracle Database, Database Administrator or a person responsible for Software Asset Management (and Licensing) should be familiar with changes in Licensing policy which come with new release and their implications. At first let me start with little comparison of probably the most frequently used versions in production 10gR2, 11gR2 and new 12cR1 to see an "evolution".

 

Here is an excerpt from the Licensing Guide and Enterprise Manager Licensing Information Guide for 10gR2:

"Infrastructure Repository Databases

A separate Oracle Database can be installed and used as a Recovery Manager (RMAN) repository without additional license requirements, provided that all the Oracle databases managed in this repository are correctly licensed. This repository database may also be used for the Oracle Enterprise Grid Control repository. It may not be used or deployed for other uses.

A separate Oracle Database can be installed and used as an Oracle Enterprise Manager Grid Control (OEM Grid Control) repository without additional license requirements, provided that all the targets (databases, applications, and so forth) managed in this repository are correctly licensed. This database may also be used for the RMAN repository. It may not be used or deployed for other uses.

Enterprise Manager Restricted-use License

Enterprise Manager includes a restricted-use license of the Oracle Database for use as the Oracle Management Repository only."

 

Here is the excerpt from the same guides for 11gR2:

"Infrastructure Repository Databases

A separate Oracle Database can be installed and used as a Recovery Manager (RMAN) repository without additional license requirements, provided that all the Oracle databases managed in this repository are correctly licensed. This repository database may also be used for the Oracle Enterprise Grid Control repository. It may not be used or deployed for other uses.

A separate Oracle Database can be installed and used as an Oracle Enterprise Manager Grid Control (OEM Grid Control) repository without additional license requirements, provided that all the targets (databases, applications, and so forth) managed in this repository are correctly licensed. This database may also be used for the RMAN repository. It may not be used or deployed for other uses.

 Enterprise Manager Restricted-use License

Enterprise Manager includes a restricted-use license of the Oracle Database for use as the Oracle Management Repository only.

Additional database options or additional servers for disaster recovery require separate licensing. Customers receive one single-instance database with the Grid Control, or RMAN, repository. To protect the repository with Data Guard, customers need to purchase a license for the standby site. To protect the repository with Oracle Real Application Clusters, customers must license the second node for the database, and both nodes require an Oracle Real Application Clusters license."

 

And here is the excerpt from the Licensing Information Guide guide for 12cR1:

"Infrastructure Repository Databases

A separate single instance Oracle Database can be installed and used as an infrastructure repository for RMAN, Oracle Enterprise Manager Cloud Control, Automatic Workload Repository (AWR) Warehouse, Global Data Services Catalog, and Grid Infrastructure Management Repository without additional license requirements, provided that all the targets are correctly licensed. It may not be used or deployed for other uses.

The infrastructure repositories for RMAN, Oracle Enterprise Manager Cloud Control, AWR Warehouse, and Global Data Services Catalog can be shared in one database, or deployed in separate databases. Enterprise Edition must be used for the infrastructure repository database(s).

If Oracle RAC is used for the infrastructure database, then Oracle RAC licenses for all servers, and Enterprise Edition licenses for all but the first server, are required.

If Data Guard is used for the infrastructure database, then an Enterprise Edition license for the standby server is required."

 

By comparison of above excerpts where you can see differences and changes regarding Infrastructure Repository Databases (later just IRD) for particular release. Since 12c (12cR1) there is a significant change regarding edition of the Oracle Database. Current 12c Guide simply says:

  1. If Standard Edition is used as IRD and you want to upgrade the repository database to 12c, then you have to migrate it to Enterprise Edition (later just EE)
  2. If current repository database is running in RAC mode, second or any additional node node is extra licensed (EE), moreover all servers must have RAC option licensed
  3. If current repository database is using Data Guard, a standby server has to be licensed extra licensed

 Well honestly in days of "cost cutting" where many companies are more and more looking at/for alternative solutions or even they are already in process of migration from Oracle to alternative solutions, claiming that "Oracle is expensive", then this is definitely backward step from the Oracle Corp. Oracle Database 12c comes with quite interesting and very useful features regarding Recovery Manager (RMAN) which is teasing us to use new version (of course for manipulation witch PDB's 12c version of RMAN is a must). But still there is a trade-off. To stay with the 11gR2 Recovery Manager thus use old 11gR2 Infrastructure Repository Database and as long as possible (forget about 12c RMAN features including multitenant) or even trying some other workarounds (e.g. relocate RMAN repository to some fully licensed servers or divide centralized repository (catalog) to several (repositories) catalogs and spread it across licensed HA databases) in order to prevent pay additional fees for high available Infrastructure Database. Honestly I would expect a little favor here.

But enough of blaming Oracle and let's see an example why Recovery Manager requires Enterprise Edition and what would happen when you try to use Standard Edition as a RMAN repository.

I have prepared fresh 12.1.0.1 Standard edition (12.1.0.2 isn't still available) using DB_NAME=RMANDB as a repository for RMAN catalog(s) in order to see whether EE is explicitly requested.

12.2.0.2 Enterprise Edition (EE) is used as "target" database using DBNAME=ORA12CR1

 

RMANDB:SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production


RMANDB:SQL> create user rman12 identified by rman default tablespace data;

User created.

RMANDB:SQL> grant recovery_catalog_owner, unlimited tablespace to rman12;

Grant succeeded.

 

Used rman command on future "target" DB host (12.1.0.2 EE):

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database
ORACLE error from recovery catalog database: ORA-06550: line 1, column 41:
PLS-00201: identifier 'DBMS_RCVCAT.GETPACKAGEVERSION' must be declared
ORA-06550: line 1, column 34:
PL/SQL: Statement ignored

If you got this error then you have to execute @?/rdbms/admin/dbmsrmansys.sql in order to fix this issue (I had to use this script from 12.1.0.2 home as my SE is 12.1.0.1).

 

Let's continue with creating a catalog:

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database

RMAN> create catalog;

error creating create_deleted_object_seq
ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog

If you've got error as above, it means you aren't using Enteprise Edition as Partitioning requires EE or Partitioning option is not enabled.

 

RMAN> connect catalog rman12/rman@rmandb

connected to recovery catalog database

RMAN> create catalog

recovery catalog created
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Fine-grained access control

And finally if you've got this one, it means that you aren't using Enteprise Edition (as Fine-grained access control is available in EE) and moreover you did very nasty thing in order to overcome previous error.

 New RMAN features rely on Partitioning, Virtual Private Database and Fine-grained access control (which is a part of VPD) which definitely require Enterprise Edition. If you are using centralized repository for RMAN, you should be aware of this important change which might cause additional costs. While VPD (Fine-grained access control) is not extra licensed , question whether Partitioning Option needs to be licensed remain unanswered. As always, you should contact your Oracle Sales representative in order to consult all question regarding licensing and to learn all implications according the changes.

 

Fun part: Creating 12c RMAN catalog Oracle Database 12c Standard Edition (just illustration for fun, don't use it in production!):

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> create user rmanbckp identified by rmanbckp;

User created.

SYS@ORA12CR1:SQL> grant sysbackup to rmanbckp;

Grant succeeded.

SYS@ORA12CR1:SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@lab00 ~]$ sqlplus "sys@rmandb / as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 29 20:46:54 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SYS@RMANDB:SQL> select edition from v$instance;

EDITION
-------
SE

SYS@RMANDB:SQL> create user rman12 identified by rman default tablespace data;

User created.

SYS@RMANDB:SQL> grant recovery_catalog_owner, unlimited tablespace to rman12;

Grant succeeded.

SYS@RMANDB:SQL> exit
Disconnected from Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
[oracle@lab00 ~]$ rman catalog rman12/rman@rmandb

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 29 20:50:49 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

RMAN> connect target "rmanbckp/rmanbckp@ora12cr1 as sysbackup"

connected to target database: ORA12CR1 (DBID=1346475582)

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> backup database;

Starting backup at 29-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/storage/oradata00/ORA12CR1/data01.dbf
input datafile file number=00001 name=/storage/oradata00/ORA12CR1/system01.dbf
input datafile file number=00002 name=/storage/oradata00/ORA12CR1/sysaux01.dbf
input datafile file number=00003 name=/storage/oradata00/ORA12CR1/undo01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JUL-15
channel ORA_DISK_1: finished piece 1 at 29-JUL-15
piece handle=/storage/oradata01/ORA12CR1/ARC/ORA12CR1/backupset/2015_07_29/o1_mf_nnndf_TAG20150729T205310_bvl88pd1_.bkp tag=TAG20150729T205310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-15
channel ORA_DISK_1: finished piece 1 at 29-JUL-15
piece handle=/storage/oradata01/ORA12CR1/ARC/ORA12CR1/backupset/2015_07_29/o1_mf_ncnnf_TAG20150729T205310_bvl896n3_.bkp tag=TAG20150729T205310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-15

RMAN>

 

As you can see using some (secret) tweaks I was able to create Oracle 12c Standard Edition for RMAN catalog. Of course this is not valid solution and it is not possible to use it for production as SE is not supported and it in fact violates the license. So no SE officially. But technically with couple of really nasty tweaks it is possible use SE as RMAN repository. ;-)

 

VirtualBox 5.0 released!

New stable major release 5.0 of Virtual Box has been announced today and it's available for download. New features/capabilities include:

  • Paravirtualization Support for Windows and Linux Guests: Significantly improves guest OS performance by leveraging built-in virtualization support on operating systems such as Oracle Linux 7 and Microsoft Windows 7 and newer.
  • Improved CPU Utilization: Exposes a broader set of CPU instructions to the guest OS, enabling applications to make use of the latest hardware instruction sets for maximum performance.
  • Support of USB 3.0 Devices: Guest operating systems can directly recognize USB 3.0 devices and operate at full 3.0 speeds. The guest OS can be configured to support USB 1.1, 2.0, and 3.0.
  • Bi-Directional Drag and Drop Support for Windows: On all host platforms, Windows, Linux and Oracle Solaris guests now support “drag and drop” of content between the host and the guest. The drag and drop feature transparently allows copying or opening of files, directories, and more.
  • Disk Image Encryption: Data can be encrypted on virtual hard disk images transparently during runtime, using the industry standard AES algorithm with up to 256 bit data encryption keys (DEK). This helps ensure data is secure and encrypted at all times, whether the VM is sitting unused on a developer's machine or server, or actively in use.

Complete Press Release: https://www.oracle.com/corporate/pressrelease/oracle-vm-virtualbox-5-070915.html

Project site: http://www.virtualbox.org

 

undefined

 

Change (or bug) regarding parsing of tnsnames.ora in 12c

We have experienced problem with invalid database links after upgrade of one our databases to 12cR1. Certain applications which used to work properly for many years and many releases of Oracle Database begun throwing an well known error: ORA-12154: TNS:could not resolve the connect identifier specified. We were sure that neither tnsnames.ora or sqlnet.ora had been modified. Let see the typical settings of tnsnames.ora used in our case:

[oracle@lab00 ~]$ cat tnsnames.ora
ORA12CR1.test, ORA12CR1, OTHERALIAS.test =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)

What we've got here. One (apparently valid) entry which has three aliases.

Now we'll create database links for each of aliases and we perform simple test of validity. We'll use the same file for 11gR2 and 12cR1 database.

12cR1 first:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';

Database link created.

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';

Database link created.

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';

Database link created.

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_c;

SYSDATE
---------
06-JUN-15

Now 11gR2:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';

Database link created.

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';

Database link created.

SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';

Database link created.

SYS@ORA11GR2:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA11GR2:SQL> select sysdate from dual@link_b;

SYSDATE
---------
06-JUN-15

SYS@ORA11GR2:SQL> select sysdate from dual@link_c;

SYSDATE
---------
06-JUN-15

 When you compare above results all three links work on 11gR2 while link_b on 12cR1 doesn't. 12cR1 simply is unable to recognize ORA12CR1.TEST alias in tnsnames.ora entry. Ok, that was about server software, what about client software? I expected the same results but as always I prefer test before making a conclusion.

12cR1 client - same problem:

[oracle@lab00 ~]$ tnsping ora12cr1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:30

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (10 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:36

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

TNS-03505: Failed to resolve name

11gR2 client - works well:

[oracle@lab00 ~]$ tnsping ora12cr1

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:18

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:25

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

11gR1 client - works well (skipped output from ora12cr1)

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 06-JUN-2015 14:49:54

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

10gR2 client - works well (skipped output from ora12cr1

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 06-JUN-2015 14:50:34

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

9iR2 client - works well (skipped output from ora12cr1)

[oracle@lab00 ~]$ tnsping ora12cr1.test

TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 06-JUN-2015 14:51:20

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)

As I said earlier this thing used to work for many years and releases. Question is whether it is a bug in parsing or planned change since 12c. In my opinion it's a bug in probably in parsing because the OTHERALIAS.test has worked fine. Obviously you now can't have the short name alias along with alias with domain in one entry because in that case the alias with domain name is being "ignored" while short name alias works. Let's do another test as a proof of this statement. We put another alias OTHERALIAS (without domain name) for existing entry and we create another database link (named link_d) using that alias. Expected result of test is link_b and link_c will get an ORA-12154 error (note that link_c has worked before).

[oracle@lab00 ~]$ cat tnsnames.ora 
ORA12CR1.test, ORA12CR1, OTHERALIAS.test, OTHERALIAS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)
[oracle@lab00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:11:10 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_d CONNECT TO test IDENTIFIED BY test USING 'otheralias';

Database link created.

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_c;
select sysdate from dual@link_c
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SYS@ORA12CR1:SQL> select sysdate from dual@link_d;

SYSDATE
---------
06-JUN-15

Solution is quite simple. You can't mix shortname (e.g. ORA12CR1) aliases with the same aliases with domain suffix (ORA12CR1.TEST). So in this case we need to create another separate entry for conflicting aliases.

[oracle@lab00 ~]$ cat tnsnames.ora 
ORA12CR1.test, OTHERALIAS.test =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)

ORA12CR1, OTHERALIAS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12CR1.test)
 )
)
[oracle@lab00 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:39:49 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12CR1:SQL> select sysdate from dual@link_a;

SYSDATE
---------
06-JUN-15

SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
                                                                                                                                                                                            
SYSDATE                                                                                                                                                                                     
---------                                                                                                                                                                                   
06-JUN-15                                                                                                                                                                                   
                                                                                                                                                                                            
SYS@ORA12CR1:SQL> select sysdate from dual@link_c;                                                                                                                                          
                                                                                                                                                                                            
SYSDATE                                                                                                                                                                                     
---------                                                                                                                                                                                   
06-JUN-15                                                                                                                                                                                   
                                                                                                                                                                                            
SYS@ORA12CR1:SQL> select sysdate from dual@link_d;

SYSDATE
---------
06-JUN-15

 As you can see this solution works...

About 12c new feature: Online Datafile move

I consider as best of the new features those which allow to perform a maintenance or structure changes with no requirement to close the database or putting any part of it offline and especially when this feature is not bound to an extra paid option.
One of those comes with Oracle Database 12cR1 version and it can help reduce downtime for operation during which until 11gR2 needed downtime of database or tablespace needed to be offline. Simply said now you can move datafile online while the database/tablespace is open/online.

So we've got new command which we can use either to rename or relocate datafile:

ALTER DATABASE MOVE DATAFILE 'oldfilename' TO 'newfilename';

- Before moving or renaming of datafile ensure that you have sufficient free disk space that at minimum equals to size of moved datafile.

- Above command works for datafiles and it doesn't work for control files, temporary files or redo logs

- Command works irrespective of ARCHIVELOG or NOARCHIVELOG mode

- FLASHBACK DATABASE doesn't have any impact to this operation

- As always, do full backup of database after operation  (and do backup before as well)

Anyway, the idea behind this post is not to replace documentation (for detailed information about command and it's options take a look here: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837) but to test how much time this operation needs to complete (or what is the expected overhead) and comparing to existing solution(s) used until now. I've skipped the rename of datafile on OS level as it doesn't have sense because operation using "mv" command is being done within a second while using MOVE DATAFILE method performs a full copy of datafile first. So this test consist of moving datafile only.

At first, the baseline using dd and cp commands which are common methods in existing solutions prior 12c (skipped shutdown/startup mount, tablespace offline/online operations and move/rename commands):

[oracle@lab00 ORA12CR1]$ time dd if=data01.dbf of=/storage/oradata01/ORA12CR1/newdata01.dbf bs=16384
1966081+0 records in
1966081+0 records out
32212271104 bytes (32 GB) copied, 29.0502 s, 1.1 GB/s

real    0m29.051s
user    0m0.286s
sys     0m28.719s

[oracle@lab00 ORA12CR1]$ time cp data02.dbf /storage/oradata01/ORA12CR1/newdata02.dbf

real    0m27.204s
user    0m0.074s
sys     0m27.094s

 Second test using DBMS_FILE_TRANSFER package:

SYS@ORA12CR1:SQL> create directory SRCDIR as '/storage/oradata00/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> create directory TGTDIR as '/storage/oradata01/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER TABLESPACE TEST offline;

Tablespace altered.

Elapsed: 00:00:00.11
SYS@ORA12CR1:SQL> BEGIN DBMS_FILE_TRANSFER.COPY_FILE('SRCDIR','data01.dbf', 'TGTDIR', 'newdata01.dbf'); END;  
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:15.76
SYS@ORA12CR1:SQL> ALTER TABLESPACE test RENAME DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Tablespace altered.

Elapsed: 00:00:00.06
SYS@:SQL> ALTER TABLESPACE data ONLINE;

Tablespace altered.

Elapsed: 00:00:00.08

Now with new feature, online move operation:

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER DATABASE MOVE DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Database altered.

Elapsed: 00:07:42.79

 We can see significant difference between old method (offline tablespace) and new feature (online datafile move). I would say it's a tax or penalty for online operation.

According to times as above I would personally choose the "offline" method in case of huge or massive migration of datafiles and especially when time matters. But in case of mission critical system where of course the uptime is the main target and in case serveral changes have to be made or mean time for the operation is not important then this option becomes very handy.

 

SecureFiles - using, licensing and changes in 12c

SecureFiles were introduced in 11gR1 as a replacement  for (or better enhancement of) existing LOB storage architecture (now known as BasicFiles) which used to be the default storage until 12cR1. You can of course use both types together in the same table.

Idea behind this post is to discuss changes in12c and also to eliminate confusion which is still around this feature and this confusion is very often created due to misinterpretation of Licensing terms.

The irony is that there are some companies specialized for Software Asset Management are very often providing partial or vague information which creates misinterpretations and confusion on customer's side or even worse they are presenting these misinterpretations to customers.

First of all there is no licensing implication in use of SecureFiles (you don't have to pay additional fees) unless you are using any of option such as Compression, Deduplication or Encryption.

According to Oracle Database documentation and Oracle Database Licensing information you need license only for:

  • Compression - Advanced Compression option
  • Deduplication - Advanced Compression option
  • Encryption - Advanced Security option

 Let's create a tables using standard command without additional options.

SYS@ORA11GR2:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB) LOB(a, b) STORE AS SECUREFILE;

Table created.

SYS@ORA11GR2:SQL> col table_name format a15
SYS@ORA11GR2:SQL> col segment_name format a32
SYS@ORA11GR2:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024831C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024831C00002$$        YES NO   NO     NO

If you see similar output everything is fine and you're not required to pay any additional licensed option. There is a quotation of Kevin Jernigan, Senior Director Product Management at Oracle which confirm this conclusion:

"The change we made in Oracle Database 12c wrt SecureFiles and LOBs is that SecureFiles is now the default. So, when you create a LOB column in a table, if you don't specify BasicFiles or SecureFiles, it will default to SecureFiles. There is no change to licensing requirements - SecureFiles is available in all editions of Oracle Database 12c with no licensing requirements beyond the database license, and using the compression and deduplication features of SecureFiles LOBs requires licensing the Advanced Compression Option.

-KJ"

 Above statement talks about another important change which comes with 12cR1. SecureFiles is now default LOB storage. Until now you had to specify using of SecureFiles explicitly, now it's implicit. Let's check it.

SYS@ORA12CR1:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB);

Table created.

SYS@ORA12CR1:SQL> col table_name format a15
SYS@ORA12CR1:SQL> col segment_name format a32
SYS@ORA12CR1:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024836C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024836C00002$$        YES NO   NO     NO

 This behaviour is caused by change of default value of DB_SECUREFILE instance parameter from PERMITTED to PREFERRED.

 Note that this parameter affects results of upgrade to 12c. Consider to remove (if explicitly set) this parameter before upgrade database.

Results of upgrade when DB_SECUREFILE is set to PERMITTED (default value for 11gR1 and 11gR2), PREFERRED (default value for 12cR1) and ALWAYS:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

OWNER                  COUNT(*)
-------------------- ----------
GSMADMIN_INTERNAL             1
XDB                         291
SYS                          19

Results of upgrade when DB_SECUREFILE is set to NEVER or IGNORE:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

no rows selected

 

Another change which comes with Oracle Database 12c is support for parallel DML operations for LOB columns stored as SecureFiles LOBs in non-partitioned tables and Direct load support for SecureFiles LOB columns that have context index defined on them. This extends Parallel DML functionality introduced in Oracle Database 11gR2. Note Parallel DML support applies also to BasicFiles in case of partitioned tables.

To be continued...

Archive for obsolete Installation papers

It's been quite long time since I made last post on my blog. Last couple of years were really busy full of very interesting but also very time consuming projects I had been working on. As my blog by the time became outdated, many paper obsolete and I hadn't been satisfied with blog engine itself I decided to make couple of changes.

I've changed the Blog engine, which is better to customize and easy to manage as well. Also I have removed all installation papers from main page, so this post is in fact archive of all those papers, just in case anybody would have been looking for, for any of reason.

 

Installation papers for Oracle Database Software

9i
Installation of Oracle 9i(R2) on Fedora 2,3,4,5,6
Installation of Oracle 9i(R2) on Enteprise Linux 4
Installation of Oracle 9i(R2) on SuSE 9.x,10.1

10g
Installation of Oracle 10g(R2) on EL and RH EL 3,4,5
Installation of Oracle 10g(R2) on Fedora 2,3,4
Installation of Oracle 10g(R2) on SuSE 9.x
Installation of Oracle 10g(R2) on Solaris 10 x86

11g
Installation of Oracle 11g(R1) on EL and RH EL 4,5
Installation of Oracle 11g(R1) on SLES10 and OpenSuSE
Installation of Oracle 11g(R2) on Solaris x86(64)
Installation of Oracle 11g(R2) on Solaris 11 Express

 

 

Home ← Older posts