Thursday, 22 December 2016

Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c

This article discusses the new functionality added to the DUPLICATE command in Oracle 12c.
  • Active Database Duplication using Backup Sets
In previous releases, active duplicates were performed using implicit image copy backups, transferred directly to the destination server. From 12.1 it is also possible to perform active duplicates using backup sets by including the USING BACKUPSET clause. Compared to image copy backups, the unused block compression associated with a backup set can greatly reduce the amount of data pulled across the network for databases containing lots of unused blocks. The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using backup sets rather than image copy backups.

DUPLICATE DATABASE TO cdb2
  FROM ACTIVE DATABASE
  USING BACKUPSET
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
  • Active Database Duplication using Compressed Backup Sets
In addition to conventional backup sets, active duplicates can also be performed using compressed backup sets by adding the USING COMPRESSED BACKUPSET clause, which further reduces the amount of data passing over the network. The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using compressed backup sets.

DUPLICATE DATABASE TO cdb2
  FROM ACTIVE DATABASE
  USING COMPRESSED BACKUPSET
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
  • Active Database Duplication and Encryption
Oracle allows backup sets to be encrypted. Transparent encryption uses a wallet to hold the encryption key and is seamless to the DBA, since backup sets are encrypted and decrypted as required using the wallet. Password encryption requires the DBA to enter a password for each backup and restore operation.

Since Oracle 12c now supports active duplicates using backup sets, it also supports encryption of those backup sets using both methods.

If the source database uses transparent encryption of backups, the wallet containing the encryption key must be made available on the destination database.
If password encryption is used on the source database, the SET ENCRYPTION ON IDENTIFIED BY <password> command can be used to define an encryption password for the active duplication process. If you are running in mixed mode, you can use SET ENCRYPTION ON IDENTIFIED BY <password> ONLY to override transparent encryption.
The encryption algorithm used by the active duplication can be set using the SET ENCRYPTION ALGORITHM command, where the possible algorithms can be displayed using the V$RMAN_ENCRYPTION_ALGORITHMS view. If the encryption algorithm is not set, the default (AES128) is used.

The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using password encrypted backup sets.

SET ENCRYPTION ALGORITHM 'AES128';
SET ENCRYPTION ON IDENTIFIED BY MyPassword1 ONLY;

DUPLICATE DATABASE TO cdb2
  FROM ACTIVE DATABASE
  USING BACKUPSET
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
  • Active Database Duplication and Parallelism (Multisection)
Active database duplications can take advantage of the multisection backup functionality introduced in Oracle 12c, whether using image copies or backup sets. Including the SECTION SIZE clause indicates multisection backups should be used.

There must be multiple channels available for multisection backups to work, so you will either need to configure persistent channel parallelism using CONFIGURE DEVICE TYPE ... PARALLELISM or use set the parallelism for the current operation by performing multiple ALLOCATE CHANNEL commands.

The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using multisection backups.

CONFIGURE DEVICE TYPE disk PARALLELISM 4;

DUPLICATE DATABASE TO cdb2
  FROM ACTIVE DATABASE
  USING BACKUPSET
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK
  SECTION SIZE 400M;
  • Multitenant Considerations
All the examples shown previously involve multitenant databases, but there are some extra considerations when you are using the multitenant architecture.

If you are building an "initSID.ora" file from scratch, you must remember to include the following parameter.

enable_pluggable_database=TRUE
The previous examples didn't have to do this as the SPFILE was created as a copy of the source SPFILE, which already contained this parameter setting.

The DUPLICATE command includes some additional clauses related to the multitenant option.

Adding the PLUGGABLE DATABASE clause allows you to specify which pluggable databases should be included in the duplication. The following example creates a new container database (cdb2), but it only contains two pluggable databases (pdb1 and pdb2). The third pluggable database (pdb3) is not included in the clone.

DUPLICATE DATABASE TO cdb2 PLUGGABLE DATABASE pdb1, pdb2
  FROM ACTIVE DATABASE
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
The resulting clone contains the following PDBs.

SELECT name FROM v$pdbs;

NAME
------------------------------
PDB$SEED
PDB1
PDB2

SQL>

Using the SKIP PLUGGABLE DATABASE clause will create a duplicate CDB will all the PDBs except those in the list. The following example creates a container database (cdb2) with a single pluggable database (pdb3). The other two pluggable databases (pdb1 and pdb2) are excluded from the clone.

DUPLICATE DATABASE TO cdb2 SKIP PLUGGABLE DATABASE pdb1, pdb2
  FROM ACTIVE DATABASE
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
The resulting clone contains the following PDBs.

SELECT name FROM v$pdbs;

NAME
------------------------------
PDB$SEED
PDB3

SQL>
You can also limit the tablespaces that are included in a PDB using the TABLESPACE clause. If we connect to the source container database (cdb1) and check the tablespaces in the pdb1 pluggable database we see the following.

CONN sys/Password1@cdb1 AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
TEST_TS

SQL>
Next, we perform a duplicate for the whole of the pdb2 pluggable database, but just the TEST_TS tablespace in the the pdb1 pluggable database.

DUPLICATE DATABASE TO cdb2 PLUGGABLE DATABASE pdb2 TABLESPACE pdb1:test_ts
  FROM ACTIVE DATABASE
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
Checking the completed clone reveals both the pdb1 and pdb2 pluggable databases are present, but the pdb1 pluggable database does not include the USERS tablespace.

CONN sys/Password1@cdb2 AS SYSDBA

SELECT name FROM v$pdbs;

NAME
------------------------------
PDB$SEED
PDB1
PDB2

SQL>

ALTER SESSION SET CONTAINER = pdb1;

SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
TEST_TS

SQL>
Clones always contains a fully functional CDB and functional PDBs. Even when we just ask for the TEST_TS tablespace in pdb1, we also get the SYSTEM, SYSAUX and TEMP tablespaces in the PDB. The TABLESPACE clause can be used on it's own without the PLUGGABLE DATABASE clause, if no full PDBs are to be duplicated.

The SKIP TABLESPACE clause allows you to exclude specific tablespaces, rather than use the inclusion approach. The following example clones all the pluggable databases, but excludes the TEST_TS tablespace from pdb1 during the duplicate.

DUPLICATE DATABASE TO cdb2 SKIP TABLESPACE pdb1:test_ts
  FROM ACTIVE DATABASE
  SPFILE
    parameter_value_convert ('cdb1','cdb2')
    set db_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set log_file_name_convert='/u01/app/oracle/oradata/cdb1/','/u01/app/oracle/oradata/cdb2/'
    set audit_file_dest='/u01/app/oracle/admin/cdb2/adump'
    set core_dump_dest='/u01/app/oracle/admin/cdb2/cdump'
    set control_files='/u01/app/oracle/oradata/cdb2/control01.ctl','/u01/app/oracle/oradata/cdb2/control02.ctl','/u01/app/oracle/oradata/cdb2/control03.ctl'
    set db_name='cdb2'
  NOFILENAMECHECK;
Not surprisingly, the resulting clone contains all the pluggable databases, but the pdb1 pluggable database is missing the TEST_TS tablespace.

CONN sys/Password1@cdb2 AS SYSDBA

SELECT name FROM v$pdbs;

NAME
------------------------------
PDB$SEED
PDB1
PDB2
PDB3

SQL>

ALTER SESSION SET CONTAINER = pdb1;

SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS

SQL>
  • Appendix
The examples in this article are based on the following assumptions.
  1. The source database is a container database (cdb1), with three pluggable databases (pdb1, pdb2 and pdb3).
  2. The destination database is called cdb2.
  3. Both the source and destination databases use file system storage and do not use Oracle Managed Files (OMF), hence the need for the file name conversions.
  4. The basic setup for active duplicates was performed using the same process described for 11g.
Between every test the following clean-up was performed.

# Set the paths using the source DB.
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

# Set the SID for the new clone (destination).
export ORACLE_SID=cdb2

# Stop the clone if it already exists.
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Cleanup any previous clone attemps.
mkdir -p /u01/app/oracle/admin/cdb2/adump
mkdir -p /u01/app/oracle/admin/cdb2/cdump
mkdir -p /u01/app/oracle/oradata/cdb2/
rm -Rf /u01/app/oracle/oradata/cdb2/*
mkdir -p /u01/app/oracle/oradata/cdb2/pdbseed/
mkdir -p /u01/app/oracle/oradata/cdb2/pdb1/
mkdir -p /u01/app/oracle/oradata/cdb2/pdb2/
mkdir -p /u01/app/oracle/oradata/cdb2/pdb3/
rm $ORACLE_HOME/dbs/spfilecdb2.ora
rm $ORACLE_HOME/dbs/initcdb2.ora
rm $ORACLE_HOME/dbs/orapwcdb2

# Recreate an init.ora and password file.
echo "db_name=cdb2" > $ORACLE_HOME/dbs/initcdb2.ora
orapwd file=/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcdb2 password=Password1 entries=10

# Mount the clone (auxiliary).
sqlplus / as sysdba <<EOF
STARTUP NOMOUNT;
EXIT;
EOF

# Connect to the target and auxiliary RMAN using the tsnnames.ora entry.
rman target sys/Password1@cdb1 auxiliary sys/Password1@cdb2

At this point the relevant RMAN DUPLICATE command was run.