25 Duplicating a Database

To duplicate a database:

This section describes the basic steps of database duplication. Follow the link in each step for further instructions.

Duplicating a whole multitenant container database (CDB) or one or more pluggable databases (PDBs) requires only minimal adjustments to the procedure for duplicating a non-CDB as described in "Basic Steps of Database Duplication".

When duplicating a whole CDB or one more PDBs:

• You must create the auxiliary instance as a CDB. To do so, start the instance with the following declaration in the initialization parameter file:

  enable_pluggable_database=TRUE
  

• When instructed to connect to the necessary instances, connect to the root as a user with SYSDBA or SYSBACKUP privilege. On both instances, the password for the user performing the duplication must be the same.

• When instructed to create an initialization parameter file for the auxiliary instance, you must copy the file from the source database. This ensures that the auxiliary instance is also a CDB. After copying, you modify DB_NAME and the various destination/location parameters.

When transferring backup files from the source to the destination host:

• In "Making Disk Backups Accessible to the Auxiliary Instance" and "Making Disk Backups Accessible Without Shared Disk", note the following adjustment:

  Use the PLUGGABLE DATABASE syntax of the BACKUP command to copy only the backups of a specific PDB.

This example transfers the backup files for the entire CDB:

BACKUP COPY OF DATABASE;

The next example transfers only the backup files for the PDB pdb3:

BACKUP COPY OF PLUGGABLE DATABASE pdb3;

RMAN enables you to duplicate PDBs with the DUPLICATE command. You can duplicate a single PDB, a set of PDBs, or a set of tablespaces within a PDB. You must be logged in to theroot of the CDB as a user who is granted the SYSDBA or SYSBACKUP privilege.

To duplicate PDBs, you must create the auxiliary instance as a CDB. To do so, start the instance with the declaration enable_pluggable_database=TRUE in the initialization parameter file. When you duplicate one or more PDBs, RMAN also duplicates the root (CDB$ROOT) and the seed database (PDB$SEED). The resulting duplicate database is a fully functional CDB that contains the root, the seed database, and the duplicated PDBs.Table 25-1 describes the options that you can use to duplicate PDBs or tablespaces within PDBs.

To duplicate PDBs:

1. Ensure that you have completed Steps 1 to 4 in "Basic Steps of Database Duplication".

2. Run the DUPLICATE command with either the PLUGGABLE DATABASE or SKIP PLUGGABLE DATABASE option described in Table 25-1.

   The following are some examples of duplicating PDBs:

   • To duplicate the PDB pdb1 to the CDB cdb1, use the following command:

     DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1;
     

   • To duplicate the PDBs pdb1, pdb3, and pdb4 to the database cdb1, use the following command:

     DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1,pdb3,pdb4;
     

   • To duplicate all the databases in the CDB, except the PDB pdb3, use the following command:

     DUPLICATE DATABASE TO cdb1 SKIP PLUGGABLE DATABASE pdb3;
     

To duplicate tablespaces within a PDB:

Your business requirements and the database environment determine which duplication technique is best for your situation. Consider the following questions:

• Are you familiar with the prerequisites for each duplication technique?

  Review the Prerequisites section of the DUPLICATE command description in Oracle Database Backup and Recovery Reference for a complete list.

  Some prerequisites are specific and depend on the duplication technique. For example, active duplication requires that the source and auxiliary instances use the same password as the source database, whereas backup-based duplication without connections to the target database and recovery catalog requires only that all backups and database copies reside in a single location.

• Do backups of the source database exist?

  The principal advantage of active database duplication is that it does not require source database backups. Active duplication copies mounted or online database files over a network to the auxiliary instance. One disadvantage of this technique is the negative performance effect on the network. Another disadvantage is that the source database is running processes required to transfer the files to the auxiliary host, thereby affecting the source database and production workload.

  If the source database backups exist, and if the effect on the network is unacceptable, then backup-based duplication may be a better option. You can copy backups to temporary storage and transfer them manually to the destination host. If duplication is made with a connection to the target or the recovery catalog, then the backup files on the destination host must have the same file specification as they had on the source host. Otherwise, this is not a requirement.

• Is a recovery catalog available?

  If a recovery catalog exists, then you can perform backup-based duplication without connecting RMAN as TARGET to the source database. This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication.

• How much disk space is available on the destination host?

  The disk space on the destination host can be an issue when you perform duplication using disk backups. For example, if the source database is 1 terabyte (TB), and if you duplicate the database from disk backups without using shared disk or network file system (NFS), then you must have at least 2 terabytes (TB) of space available on the destination host. In some environments, manual transfer of backups is necessary because NFS performance is a bottleneck.

• Are the source and destination hosts connected by a LAN or a WAN?

  Performance of active database duplication is probably slower on a wide area network (WAN) than on a local area network (LAN). If the performance degradation on a WAN is unacceptable, then backup-based duplication may be the only viable option.

• When do you plan to duplicate the database?

  If you must duplicate the database during a period of high user activity, then the loss of network throughput caused by active duplication may be a problem, making backup-based duplication a better choice. Also, in active database duplication, the RMAN channels required for copying files to the auxiliary host can affect performance.

When duplicating a database, RMAN generates names for the duplicate control files, data files, temp files, and online redo log files. Therefore, you must decide on a naming strategy for these files.

The simplest duplication strategy is to configure the duplicate database to use the same names as the source database. Using the same names means that your environment meets the following requirements:

• If the source database uses ASM disk groups, then the duplicate database must use ASM disk groups with the same names.

• If the source database files are Oracle Managed Files, then the auxiliary instance must set DB_FILE_CREATE_DEST to the same directory location as the source database. Although the directories are the same on the source and destination hosts, Oracle Database chooses the relative names for the duplicate files.

• If the names of the database files in the source database contain a path, then this path name must be the same in the duplicate database.

  Note:

  If the destination and source hosts are the same, then use one of several methods available to modify the database file paths in the duplicate database. See "Specifying Alternative Names for Duplicate Database Files".

When you configure your environment as suggested, no additional configuration is required to name the duplicate files.

It is recommended that you use different names for the ASM disk groups in the source and duplicate database. Use one of the following techniques to specify different names for database files in the duplicate database:

• Set the DB_CREATE_FILE_DEST and LOG_FILE_CREATE_DEST initialization parameters in the auxiliary instance. This is recommended when you use ASM and OMF.

• Use the SET NEWNAME command.

• Set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters in the auxiliary instance.

  If DB_FILE_NAME_CONVERT is applied for ASM names, only disk group name changes must be performed.

If the source and destination hosts are different, then you must install the Oracle Database software on the destination host so that you can create the auxiliary instance. You can install the software in one of two ways:

• Perform a normal installation with Oracle Universal Installer (OUI).

  Install an Oracle Database that is the same release as the source database. Do not create a database; install the software only. Apply any required patches.

• Clone the source Oracle home.

  Use OUI to clone the source Oracle home. This ensures that all patches applied to the source database are present in the duplicate database.

When duplicating with a target and recovery catalog or just a target connection, RMAN uses metadata in the RMAN repository to locate backups and archived redo log files needed for duplication. If RMAN is connected to a recovery catalog, then RMAN obtains the backup metadata from the catalog. If RMAN is not connected to a catalog, as may be the case when performing backup-based duplication with a target connection, then RMAN obtains metadata from the control file.

Unless you are duplicating without a connection to the target and to the recovery catalog, the names of the backups must be available with the same names recorded in the RMAN repository. Ensure that auxiliary channels on the destination host can access all data file backups and archived redo log files (required to restore and recover the duplicate database to the desired point in time). If not, duplication fails. The archived redo log files can be available either as image copies or backup sets.

This section contains the following topics:

• Making SBT Backups Accessible to the Auxiliary Instance

• Making Disk Backups Accessible to the Auxiliary Instance

When a database must be accessed from another host, you must set up a password file and Oracle Net connectivity. Be aware of the potential security consequences of this type of setup.

This section contains the following topics:

• Establishing Database Connectivity

• Creating a Password File for the Auxiliary Instance

If transparent encryption is configured on the source database, then you must ensure that the Oracle software keystore from the source database is available to the auxiliary instance. Manually copy the source database keystore to the destination host.

The following are requirements for the keystore at the duplicate database:

• The keystore must be in the default location, or in the location indicated by the sqlnet.ora file.

• Permissions on the Oracle keystore file must be set so that the database can access the file.

• The keystore must be an auto login keystore.

• If the source database uses a password-based software keystore (not an auto-login software keystore), then you must provide the keystore password before you begin the database duplication.

  Use the SET command with the DECRYPTION WALLET OPEN IDENTIFIED BY clause to specify the password that must be used to open the keystore.

  The following command sets specifies that the password used to open the keystore is my_pswd:

  SET DECRYPTION WALLET OPEN IDENTIFIED BY my_pswd;
  

The location and content of the initialization parameter file depend on your choice in "Step 2: Choosing a Strategy for Naming Duplicate Files". This section makes the following assumptions:

• You choose the recommended technique of using the same naming strategy for the source and destination hosts. For Oracle Real Application Clusters environments, you therefore use the same ORACLE_SID for source and destination hosts.

• You create a text-based initialization parameter file (PFILE) for the auxiliary instance. See Table 26-5.

• The initialization parameter file is located in the operating system-specific default location of the host on which SQL*Plus runs.

  For example, on Linux and UNIX the default initialization parameter file name is ORACLE_HOME/dbs/initORACLE_SID.ora and on Windows the file name is ORACLE_HOME\database\initORACLE_SID.ora.

• You plan to specify the SPFILE clause on the DUPLICATE command.

  The DUPLICATE ... SPFILE technique is easiest because during duplication RMAN automatically copies the server parameter file from the source database to the auxiliary instance or restores it from backup.

If you cannot meet the preceding requirements, then see "Duplicating a Database When No Server Parameter File Exists".

To create an initialization parameter file and start the auxiliary instance:

1. Do one of the following:

   • Copy the initialization parameter file from the source host to the destination host, placing it in the operating system-specific default location, and then modify DB_NAME, DB_DOMAIN, and the various destination/location parameters such as CONTROL_FILES and DB_RECOVERY_FILE_DEST.

     If you are duplicating a CDB, ensure that the ENABLE_PLUGGABLE_DATABASE parameter is present and set to TRUE.

   • Complete these steps:

     1. Using a text editor, create an empty file for use as a text-based initialization parameter file, and save it in the operating system-specific default location.

     2. In the parameter file, set DB_NAME and DB_DOMAIN. These are the only required parameters.

        Setting DB_DOMAIN enables you to connect to the default database service when connecting with a net service name.

     3. If the auxiliary instance is to be a CDB, then set the following parameter:

        ENABLE_PLUGGABLE_DATABASE=TRUE
        

2. If necessary, set other initialization parameters like those needed for Oracle Real Application Clusters.

3. Set the required environment variables, such as ORACLE_HOME and ORACLE_SID.

4. Start SQL*Plus and connect to the auxiliary instance as a user with SYSDBA or SYSBACKUP privileges. Start the auxiliary instance in NOMOUNT mode. No PFILE parameter on the STARTUP command is necessary if the file is in the default location.

   SQL> STARTUP NOMOUNT;
   

If RMAN is connected to the source database as TARGET, then the source database must be in the proper state for the duplication.

To ensure that the source database is in the proper state:

The channel on the auxiliary instance, not the source database instance, restores RMAN backups in backup-based duplication. The channel configuration depends on your duplication technique.

RMAN can use the same channel configurations on the source database for duplication on the destination host. RMAN can use these configurations even if the source database channels do not specify the AUXILIARY option.

Note the following additional considerations:

• The channel type (DISK or sbt) of the auxiliary channel must match the backup media. In general, the more channels you allocate for disk backups, the faster the duplication. You cannot increase the speed of duplication after the disks reach their maximum read/write rate. For tape backups, limit the number of channels to the number of devices available.

• If the auxiliary channels need special parameters (for example, to point to a different media manager), then you can configure an automatic channel with the AUXILIARY option of the CONFIGURE command.

• When you perform duplication without a target connection and without a recovery catalog, only disk channels can be used. If no user-allocated channels are used, then only one channel initially restores the control file. After the control file is mounted, the number of allocated channels depends on the configuration in the restored control file.

• If you omit the USING BACKUPSET clause from the DUPLICATE command and the number of allocated auxiliary channels is greater than or equal to the number of target channels, then RMAN still uses active database duplication with backup sets.

In active database duplication, you do not have to change your source database channel configuration or configure AUXILIARY channels.

To perform active database duplication using backup sets, you must allocate additional auxiliary channels. The number of auxiliary channels must be greater than or equal to the number of target channels. Using backup sets for active duplication also enables parallelism which can improve the speed of the duplication process.

In this variation of Example 25-1, RMAN does not use a TARGET connection to the source database. Example 25-3 creates a duplicate of the source database prod as it appeared in 2013 in a previous database incarnation. RMAN is not connected to the source database but must be connected to a recovery catalog because no BACKUP LOCATION clause is provided.

Assume a backup-based variation of Example 25-1 in which RMAN is connected as TARGET to the source database. Example 25-5 recovers the duplicate database to 1 week ago to view the data in the source database as it appeared then.

In the variation of Example 25-1 shown in Example 25-6, RMAN does not use a TARGET connection to the source database or a CATALOG connection to a recovery catalog. All backup and copies necessary for duplication until November 1 of 2013 at 2:00 PM, including a control file backup or copy, have been placed under /prod_backups.