Cloning a Pluggable Database from an RMAN Container Database Backup (Doc ID 2042607.1)
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.
Expectation is that the reader is familiar with the concepts of Oracle Multitenant and Oracle Recovery Manager (RMAN) and basic terminology and functionality.
GOAL
The following steps will show you how to perform a backup of a container database (CDB), including only the pieces required to migrate a subset of pluggable databases (PDB) with no disruption of application activity to the source PDBs. The backup and all datafiles contained in it will be consistent to a single point in time. Other than during the database backup, this operation requires no connection to the source CDB. The process makes use of Oracle Recovery Manager (RMAN) functionality to create the backup and create a clone of the CDB and specific PDBs within that container database. Once the CDB clone is created, the PDB(s) in the clone can be unplugged and plugged into a different CDB.
The RMAN backup can be created while the source CDB and PDB are active. Archive logs are included in the backupset to ensure that the cloned CDB can be recovered to a consistent point in time. Upon completion, the backup can be made accessible to some other heterogeneous hardware to be used for the restore process.
The RMAN DUPLICATE functionality is used to create the clone of the CDB. It uses data from the backupset to determine the consistency point for recovery and which datafiles need to be restored and which datafiles should be skipped and removed from the clone. This process is viable for Oracle RDBMS 12.1.0.2 and later.
SOLUTION
Considerations
- The following steps assume the source and destination environments are separate hardware and not sharing storage (e.g. two separate Exadata Database Machines). However, the same environment can be used as both source and destination.
- The source and destination environments must be the same platform.
- The source CDB can be either an Oracle Real Application Cluster (RAC) database or a single instance database.
- The backup can be a full backup of the source CDB. The process outlined here assumes a one-off operation that only requires the individual PDB(s) in the backupset.
- The process requires no connection to either the source database or RMAN catalog. There is no requirement to have an RMAN catalog.
- The RMAN DUPLICATE process requires an auxiliary instance to be created on the destination environment. This auxiliary instance will always be only a single instance database.
- The RMAN backups can be written to either filesystem or ASM. The RMAN backups when used for the DUPLICATE process can reside on either ASM or filesystem, irrespective of where they were originally written by the backup process.
- The following example will show the process for including a single PDB in the backupset. Additional PDBs can be added and duplicated. It is also possible to use the same backupset and perform separate duplication processes with each cloned CDB having different PDBs being restored from the backupset.
- The destination environment must have an ORACLE_HOME patched to the same level and with the same one-offs as the source environment.
Process
There are two distinct processes that occur as part of the duplicate:
- Backup which occurs on the source environment
- Duplication which occurs on the destination environment
In general the impact to these systems will be from I/O, process and memory utilization. The backup process will connect as a client to the source database. The duplication process will create a single instance auxiliary database instance at the destination site and can have the resource utilization configured through initialization parameter settings for this instance.
Source Backup
- The minimum backup requires what are called Auxiliary and Recovery sets. The Auxiliary set consists of the controlfile of the source database, CDB$ROOT (the root's system datafiles, sysaux datafiles, undo datafiles) and the PDB$SEED and any archive logs to make the datafiles consistent after restore. These are required to create a running container database after restore. The Recovery set consists of all of the datafiles belonging to the PDB(s) to be restored.
The following is an example of a backup command that can be used. The commands should be run in the order specified here to ensure all files will be able to be successfully recovered to a consistent point. The first statement should include any and all PDBs you wish to include in the cloned environment. In this example, PDB100 is the only PDB included for cloning.
The FORMAT and TAG clauses must be placed before the items to be backed up to ensure that they apply to each backed up item. The FORMAT can specify either a filesystem or ASM for location of the backups being created but should be the same for all backup commands as all of the backup files must reside in the same directory for the RMAN DUPLICATE process. The TAG is used to help identify the files that need to be copied to the destination environment. The archivelogs must be included to ensure that all files can be successfully recovered to a consistent point.
The following command will create a backupset that is all inclusive of the Auxiliary and Recovery sets, including only enough archivelogs to allow the datafiles to be restored/recovered to a consistent point in time. For more information on the impact and usage of the KEEP clause please see the Oracle Database Backup and Recovery User's Guide -> Backing up the Database -> Making Database Backups for Long-Term Storage. Note that the KEEP UNTIL TIME clause does not impact how long the backups are usable, it is just telling the source database controlfile how long to maintain information about the backup and to configure the backup for use with the REPORT/DELETE OBSOLETE command. The backup can be copied to another environment and used for cloning well after the defined KEEP UNTIL date.
RMAN> backup as backupset keep until time 'sysdate+1' format '+RE1/cdb100/backups/%U' tag 'my_bkup' (database ROOT) (pluggable database 'pdb$seed', pdb100);
NOTE: If you wish to perform multiple PDB migrations in parallel on the same destination environment, you can include all of the PDBs you need in the first backup command (e.g. …pdb100,pdb200,pdb300…). The backup can be copied to the destination site and either all restored in a single duplication or run in parallel with each duplicate restoring different PDBs. As long as the location where the backupsets are stored on the destination is shared, you only need to copy the files one time. Each duplicate execution can use the backupsets in parallel as it is read only.
- Connect to the source database as rman target / and run the backup command created in the previous step
$ rman target sys
RMAN>@bkup.cmd
- After performing the backup, connect to the source database and query V$BACKUP_PIECE and get the backup file name (HANDLE) where the TAG column is like ‘<tag in command>’. This will provide a list of files to be copied to the destination environment.
SQL> select handle from v$backup_piece where tag like 'MY_PDB%';
HANDLE
--------------------------------------------------------------------------------
+RE1/cdb100/backups/4lq8j2au
+RE1/cdb100/backups/4mq8j2c2
+RE1/cdb100/backups/4nq8j2cs
+RE1/cdb100/backups/4oq8j2dc
+RE1/cdb100/backups/4pq8j2g2
+RE1/cdb100/backups/4qq8j2gb
+RE1/cdb100/backups/4sq8j2gs
+RE1/cdb100/backups/4tq8j2hv
+RE1/cdb100/backups/4uq8j2iq
+RE1/cdb100/backups/4vq8j2ja
+RE1/cdb100/backups/50q8j2ju
+RE1/cdb100/backups/52q8j2k5
12 rows selected.
- Transfer the files above to the destination site. The files can be scp'd to filesystem or copied via ASMCMD.
NOTE: All files must reside in the same directory at the same directory level for the RMAN DUPLICATE process to find them. You cannot have sub-directories containing some of the files as RMAN will not traverse additional directory structures.
- Manually create a pfile from the spfile of the source database to be used for the auxiliary instance. The following is an example. Comments are denoted with an # and provide background information and direction for specific settings.
For the items marked "# THIS VALUE CAN BE ADJUSTED BASED ON RESOURCES AVAILABLE", note that increasing the values may speed up the duplication process and reducing the values may slow down the process. You should determine values based on your SLA requirements and available resources.
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/auxcdb/adump' # SET THIS TO A LOCAL FILESYSTEM LOCATION SPECIFIC TO THE DESTINATION SITE
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.cluster_database=false # EVEN THOUGH THE SOURCE IS RAC, CLUSTER_DATABASE MUST BE SET TO FALSE FOR THE AUXILIARY
*.compatible='12.1.0.2.0'
*.control_files='+DATA/AUXCDB/control01.dbf' # THIS SETTING ALLOWS FOR A NEW CONTROLFILE TO BE CREATED IN ASM
*.db_block_checking='false'
*.db_block_checksum='typical'
*.db_block_size=8192
*.db_create_file_dest='+DATA' # THIS MAY NEED TO BE CHANGED FROM SOURCE
*.db_create_online_log_dest_1='+DATA' # THIS MAY NEED TO BE CHANGED FROM SOURCE
*.db_domain=''
*.db_files=1024
*.db_lost_write_protect='typical'
*.db_name='auxcdb' # DB_NAME MUST BE SET TO A NAME DIFFERENT FROM THE SOURCE DATABASE. THE DUPLICATE PROCESS WILL THEN USE THIS NEW NAME AS THE DB_UNIQUE_NAME FOR THE AUXILIARY DATABASE
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=1024g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=auxcdbXDB)' #THIS VALUE SHOULD BE CHANGED TO MATCH THE AUXILIARY DATABASE
*.enable_pluggable_database=true # ENABLE_PLUGGABLE_DATABASE MUST BE SET TO TRUE TO ALLOW CREATION OF A CDB
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_buffer=134217728
*.open_cursors=300
*.os_authent_prefix=''
*.parallel_adaptive_multi_user=FALSE
*.parallel_execution_message_size=16384
*.parallel_max_servers=1280
*.parallel_min_servers=0
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=2048m # THIS VALUE CAN BE ADJUSTED BASED ON RESOURCES AVAILABLE
*.processes=300 # THIS VALUE CAN BE ADJUSTED BASED ON RESOURCES AVAILABLE
*.recyclebin='on'
*.remote_login_passwordfile='exclusive'
*.sga_target=6144m # THIS VALUE CAN BE ADJUSTED BASED ON RESOURCES AVAILABLE
*.sql92_security=TRUE
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1' #THIS MUST SPECIFY AT LEAST ONE VALID UNDO TABLESPACE NAME FROM THE SOURCE DATABASE
*.use_large_pages='ONLY'
Destination Restore
- Determine what you are going to call the auxiliary database. If you are running multiple duplicates in parallel, each should have a unique database name. Modify the pfile created above accordingly.
NOTE: For environments using Transparent Data Encryption (TDE), please see RMAN Duplicate Using TDE Encrypted Backups Document 1560327.1 for additional instructions for handling TDE keystores and keys.
- Ensure you have enough free space to handle the cloned database. This will be space for all of the datafiles in the root (system, sysaux, all undo for RAC sources, and any additional datafiles that may be part of root) AND all of the online redo logs for each thread in the source. The controlfile from the backup is restored and it must create all of the online redo logs that exist in the source controlfile and there is no way to modify the sizes of the online redo logs.
For example, if on your source you have 4 RAC instances with 2 groups each at 4g for each log and 1 member for each group, you will need 4 * 2 *4g = 32g space for online redo logs. The space for the root may only be temporary as the auxiliary database can be deleted after the duplicate operation is complete and the PDB is migrated to a new CDB. The space for the PDB(s) that are being cloned will continue to be used afterward. - Modify the sample pfile created above to use for the AUXILIARY instance.
- If it doesn't already exist, create the AUDIT_FILE_DEST directory structure
$ mkdir -p /u01/app/oracle/admin/auxcdb/pfile
- Create the duplicate command.
Create a file called dupe.cmd to store the commands to perform the database duplication. As you can see in the following command you can specify multiple channels to perform the restore if desired.- The DUPLICATE DATABASE TO clause should provide the name of the auxiliary database and match the DB_NAME setting in the pfile just created.
- Specify each PDB you wish to restore as part of this CDB with each PDB separated by a comma.
- You must specify the ROOT. Note that ROOT will cause the PDB$SEED to be restored as well.
- The LOCATION should provide the directory where the backups have been copied.
run {
allocate auxiliary channel dupe1 type disk;
allocate auxiliary channel dupe2 type disk;
allocate auxiliary channel dupe3 type disk;
allocate auxiliary channel dupe4 type disk;
duplicate database to auxcdb
pluggable database pdb100, root backup location '+RECO/auxcdb/backups';
}
- Set your environment to point to the auxiliary database ORACLE_HOME and ORACLE_SID
- Connect to SQL*PLUS as SYSDBA and startup the auxiliary instance in nomount being sure the init.ora created/modified in step 3 is used
$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/auxcdb/initauxcdb.ora'
- Connect to the auxiliary instance and execute the duplicate database command created in step 5.
NOTE: The connection here is to auxiliary, not target
$ rman auxiliary /
RMAN>@dupe.cmd
The RMAN DUPLICATE process will;- Read the backup files in the LOCATION directory to determine what files are available
- Create an spfile from memory of the auxiliary instance
- Restart the auxiliary in NOMOUNT to make small modifications to the spfile such as setting DB_NAME=<DB_NAME of the source database> and DB_UNIQUE_NAME=<auxiliary database name>
- Restart the auxiliary database in NOMOUNT with this updated spfile and restore the controlfile from the backup LOCATION
- Mount the auxiliary instance
- Based on information found in the controfile
- Determine a consistent recovery point SCN
- Determine which files and tablespaces should be restored and which should be skipped/removed
- Restores the datafiles
- Recovers the datafiles
- Restarts the auxiliary in NOMOUNT
- Creates a new controlfile for this new database
- Catalogs the new datafile names and uses as appropriate
- Drops any PDBs that existed in the source CDB that were not cloned
- Opens the new database resetlogs.
- After the RMAN DUPLICATE script completes, the auxiliary database will be in OPEN state, as will the PDB that was restored. Connect to the instance via sqlplus and unplug the PDB.
SQL> alter pluggable database <pdbname> close;
SQL> alter pluggable database <pdbname> unplug into ‘<some xml file>’;
SQL> drop pluggable database <pdbname> keep datafiles;
SQL> shutdown abort
- Set your environment to point to the destination CDB ORACLE_HOME and ORACLE_SID
- Connect to the destination CDB via SQL*Plus and perform the PDB plugin operation
NOTE: The PDB should get a new name to generate a new Global Unique Identifier (GUID) to ensure it doesn't conflict with the GUID of the source PDB.
The way this example is coded, the datafiles for the PDB created by the RMAN DUPLICATE process will be used directly for the new PDB. They will remain under the ASM directory structure of the auxiliary database until at some point in the future they are either moved or restored. If you wish to copy the datafiles to the ASM directory structure of the destination CDB, use the COPY clause instead of the NOCOPY clause.
SQL> create pluggable database <newpdbname> using ‘<xml file created from unplug>’ tempfile reuse nocopy;
- Drop the auxiliary database. The duplicate process also creates an spfile for the auxiliary instance in $ORACLE_HOME/dbs, you should delete this prior to re-running the RMAN DUPLICATE process with the same auxiliary database name.
If you used the NOCOPY clause when creating the new PDB in the destination CDB, remember to not delete the PDB's datafiles from the auxiliary CDB directory.