Cloning an 11g Database with RMAN

You can create a duplicate database using the RMAN duplicate command. The duplicate database will have a different DBID from the source database.
Starting with Oracle 11g there are now two ways to clone a database:
1. Active Database Duplication
2. Backup-based Duplication

Database duplication involves copying the live running database transparently using RMAN over the network to the auxiliary destination and creating the duplicate database.
The backup-based duplication requires a manual copy of the backuped files to the destination database.
The only difference between the two is you do not need pre-existing RMAN backups and copies (archivelogs) for the 1st Case. The duplication work is performed by the auxiliary channel and this channel corresponds to a server session on the auxiliary instance on the auxiliary (destination) host.
The active database duplication is slower because it is using the network to transport the data blocks instead of accessing existing RMAN backupsets.
RMAN carries out the following steps as part of the duplication process:
    a. Creates a control file for the duplicate database
    b. Restarts the auxiliary instance and mounts the duplicate control file
    c. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs
    d. Opens the duplicate database with the RESETLOGS option

RMAN Options:
NOFILENAMECHECK
Prevents RMAN from checking whether the source database datafiles and online redo logs files share the same names as the duplicated files. This option is necessary when you are creating a duplicate database in a different host that has the same disk configuration, directory structure, and filenames as the host of the source database. If duplicating a database on the same host as the source database, then make sure that NOFILENAMECHECK is not set.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK

SPFILE ... SET 'string_pattern'
Sets the specified initialization parameters to the specified values. You can use SET to set the LOG_FILE_NAME_CONVERT parameter for the online redo logs.
It copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.
RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.
If you execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When you specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.
If FROM ACTIVE DATABASE is specified on DUPLICATE, then a server parameter file must be in use by the source database instance. If FROM ACTIVE DATABASE is not specified on DUPLICATE, then RMAN restores a backup of the server parameter file to the standby database.

DORECOVER:
Specifies that RMAN should recover the standby database after creating it. If you specify an untilClause, then RMAN recovers to the specified SCN or time and leaves the database mounted.
RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, you must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-only mode.

PARAMETER_VALUE_CONVERT:
Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.
You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,'+BETA’).

DB_FILE_NAME_CONVERT 'string_pattern'
Specifies a rule for creating the filenames for duplicate datafiles and tempfiles. Note that DB_FILE_NAME_CONVERT specified on the DUPLICATE command overrides the initialization parameter DB_FILE_NAME_CONVERT if it is set in the initialization parameter file.

Example:
DUPLICATE TARGET DATABASE TO dup1
FROM ACTIVE DATABASE
DB_FILE_NAME_CONVERT '/disk1','/disk2'
SPFILE
    PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
    SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
    SET SGA_MAX_SIZE '200M'
    SET SGA_TARGET '125M';

The PARAMETER_VALUE_CONVERT option substitutes /disk2 for /disk1 in all initialization parameters that specify filenames (with the exception of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT). The SET LOG_FILE_NAME_CONVERT clause substitutes /disk2 for /disk1 in the filenames of the online redo logs of the duplicate database. The DB_FILE_NAME_CONVERT option replaces /disk1 with /disk2 in the names of the duplicate datafiles and tempfiles.

Another Example with Path Changes:
run {
    allocate channel prmy1 type disk;
    allocate channel prmy2 type disk;
    allocate auxiliary channel stby type disk;
    duplicate target database for standby from active database
    dorecover
    spfile
        parameter_value_convert 'FGUARD','STB_QAFGUARD'
        SET DB_UNIQUE_NAME='STB_QAFGUARD'
        set db_file_name_convert='/opt/oracle/oradata/FGUARD/','/opt/oracle/oradata/STB_QAFGUARD/'
        set log_file_name_convert='/opt/oracle/oradata/FGUARD/','/opt/oracle/oradata/STB_QAFGUARD/'
        set control_files='/u01/app/oracle/oradata/STB_QAFGUARD/control01.ctl','/u01/app/oracle/oradata/STB_QAFGUARD/control02.ctl'
        set log_archive_max_processes='5'
        set fal_client='STB_QAFGUARD'
        set fal_server='FGUARD'
        set standby_file_management='AUTO'
        set log_archive_config='dg_config=(FGUARD,STB_QAFGUARD)'
        set log_archive_dest_2='service=FGUARD ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=FGUARD'
     ;
     }

Active Database Duplication
If you are using SPFILE, then the only parameter required for the duplicate database is the DB_NAME parameter. The rest of the parameters can be set in the DUPLICATE command.
You can also copy your own init.ora file to the Destination (Auxiliary) DB and use that one for the new DB.
On this example we will assume that the Destination DB Name will be FGUARD

Note:
We are assuming that the Primary DB is already on archive log mode.
If that is not the case, you need to perform the following steps on that Primary box:
archive log list;
shutdown immediate
startup mount;
alter database archivelog;
alter database open;

Steps
1. Determine if FORCE LOGGING is enabled on Primary. If it is not, then enable it by using the FORCE LOGGING mode.
This statement may take some time to complete, because it waits for all unlogged direct write I/O to finish. Use SQL*Plus to execute the following commands:
SELECT force_logging FROM v$database;
ALTER DATABASE FORCE LOGGING;
SELECT force_logging FROM v$database;

2. Create the Oracle Password file for the Auxiliary Instance
This is a requirement for RMAN in order to duplicate a database you need to connect directly to the auxiliary instance using the password file with the same SYSDBA password as the target database’s password. You can specify the PASSWORD FILE option on the DUPLICATE command in which case, RMAN will copy the source database password file to the destination host and overwrite any existing password file with the same name as the auxiliary instance’s name.
So basically, copy the password file from the $ORACLE_HOME/dbs directory primary database to $ORACLE_HOME/dbs on the destination database.

3. On the auxiliary DB, create a directory with a name that matches your SID:
mkdir -p $ORACLE_BASE/oradata/FGUARD
mkdir -p $ORACLE_BASE/admin/FGUARD/adump
mkdir -p $ORACLE_BASE/diag/rdbms/fguard/FGUARD/trace
mkdir -p $ORACLE_BASE/diag/rdbms/fguard/FGUARD/cdump
mkdir -p $ORACLE_BASE/flash_recovery_area/FGUARD

4. Start the Auxiliary instance from SQL*Plus – start the database in nomount mode but first take care of the following steps:
cd $ORACLE_HOME/dbs
echo DB_NAME='FGUARD' > initFGUARD.ora

export ORACLE_SID=FGUARD
sqlplus "sys as sysdba"
startup nomount ;

If this is a Windows Machine, then create the Database Service
% set ORACLE_SID=FGUARD
% set ORACLE_HOME=E:\opt\app\oracle\product\11.1.0\db_1
% oradim -NEW -SID FGUARD

5. Update the listener.ora and the tnsnames.ora on the auxiliary DB (and the tnsnames.ora file for your Primary DB) to add the new Auxiliary SID information

Example:
Update listener.ora on auxiliary machine by adding the following to the SID_LIST_LISTENER section. So the file it will look like this:
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = FGUARD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = FGUARD)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
)

LOGGING_LISTENER = OFF

Also, update tnsnames.ora on both machines:
PROD =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_PRIMARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
)

FGUARD =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_SECONDARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FGUARD)
    )
)

Stop and restart the Listener on the auxiliary DB
lsnrctl stop
lsnrctl start

Confirm the connectivity to the target and the auxiliary DB's.

6. Start RMAN and connect to the source database as the target connection. The duplicate database instance will be specified in the AUXILIARY connection.
You can invoke the RMAN client on any host as long as that host has connectivity and you can connect to all of the required database instances. If the auxiliary instance requires a text-based initialization parameter file (pfile) then this file must exist and it must reside on the same host that runs the RMAN client application.

% rman
RMAN> connect target sys/passwd@prod;
RMAN> connect auxiliary sys/passwd@fguard;
or
% rman target sys/oracle@prod auxiliary sys/oracle@fguard

7. Next you will be issuing the DUPLICATE database command in order to start the duplicate process. In its simplest form, the following command will create the duplicate DB with the SAME structure that the Primary DB:
RMAN> DUPLICATE TARGET DATABASE TO FGUARD FROM ACTIVE DATABASE;

The following example will perform a conversion of the DB Files:
RMAN> DUPLICATE TARGET DATABASE TO FGUARD FROM ACTIVE DATABASE DB_FILE_NAME_CONVERT '/opt/oradata/PROD','/opt/oradata/FGUARD';

The following example will perform a conversion of the parameter values, DB Files and Log Files:
RMAN> DUPLICATE TARGET DATABASE TO FGUARD FROM ACTIVE DATABASE
SPFILE
    PARAMETER_VALUE_CONVERT 'PROD', 'FGUARD'
    SET DB_FILE_NAME_CONVERT 'PROD','FGUARD'
    SET LOG_FILE_NAME_CONVERT 'PROD','FGUARD';

On this example, we are renaming 1 by 1 the datafile names (we previously query them with select name, FILE# from v$datafile), we also change some init.ora parameters on the auxiliary DB:
run {
set newname for datafile '/u01/app/oracle/oradata/PROD/users01.dbf' to '/u01/app/oracle/oradata/FGUARD/users01.dbf';
set newname for datafile '/u01/app/oracle/oradata/PROD/undotbs01.dbf' to '/u01/app/oracle/oradata/FGUARD/undotbs01.dbf';
set newname for datafile '/u01/app/oracle/oradata/PROD/sysaux01.dbf' to '/u01/app/oracle/oradata/FGUARD/sysaux01.dbf';
set newname for datafile '/u01/app/oracle/oradata/PROD/system01.dbf' to '/u01/app/oracle/oradata/FGUARD/system01.dbf';
duplicate target database to FGUARD from active database
db_file_name_convert '/u01/app/oracle/oradata/PROD' , '/u01/app/oracle/oradata/FGUARD'
spfile parameter_value_convert = '/u01/app/oracle/admin/PROD' , '/u01/app/oracle/admin/FGUARD'
set log_file_name_convert = '/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/FGUARD'
set audit_file_dest='/u01/app/oracle/admin/FGUARD/adump'
set log_archive_dest_1=''
set memory_target='183001600'
set control_files='/u01/app/oracle/oradata/FGUARD/control01.ctl','/u01/app/oracle/oradata/FGUARD/control02.ctl','/u01/app/oracle/oradata/FGUARD/control03.ctl'
set db_recovery_file_dest_size = '2294967296';
}

On the following example, we will create the auxiliary database to a past point in time. The only difference comes at the end, during the execution of the duplicate command. You must use the until time clause to create an auxiliary database to a past period in time.
RMAN> duplicate target database to FGUARD
spfile
nofilenamecheck
until time 'sysdate-1';

8. Check the Auxiliary DB
export ORACLE_SID=FGUARD
sqlplus "sys as sysdba"
archive log list;
select name from v$datafile;
select name,open_mode from v$database;
show sga

Backup-based Duplication

This is very simple to perform:

1- Create a backup of the source DB (if you do not have one already):
$ rman target=/
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

2- Create a password file for the duplicate instance on the Duplicated Server.
$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwFGUARD password=my_password entries=10

3- Add the appropriate entries into the "tnsnames.ora" file in the "$ORACLE_HOME/network/admin" directory to allow connections to the target database from the duplicate server on the Duplicated Server.
PROD =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_PRIMARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
)

FGUARD =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MY_SECONDARY_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FGUARD)
    )
)

4- Create a PFILE for the duplicate database. Since we are duplicating the database onto a separate server with the same filesystem as the original, we don't need to convert the file names.
In this case, the PFILE is called "initFGUARD.ora" and is placed in the "$ORACLE_HOME/dbs" directory.

# Minimum Requirement.
DB_NAME=FGUARD
# Convert file names to allow for different directory structure if necessary
#DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/FGUARD/,/u01/app/oracle/oradata/FGUARD/)
#LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/FGUARD/,/u02/app/oracle/oradata/FGUARD/)

5- On the auxiliary DB, create a directory with a name that matches your SID:
mkdir -p $ORACLE_BASE/oradata/FGUARD
mkdir -p $ORACLE_BASE/admin/FGUARD/adump
mkdir -p $ORACLE_BASE/diag/rdbms/fguard/FGUARD/trace
mkdir -p $ORACLE_BASE/diag/rdbms/fguard/FGUARD/cdump
mkdir -p $ORACLE_BASE/flash_recovery_area/FGUARD

6- Copy the backup files from the source database to the destination server. That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use something like:
scp -r oracle@destination_server:/u01/app/oracle/fast_recovery_area/FGUARD/archivelog /u01/app/oracle/fast_recovery_area/FGUARD

7- Connect to the duplicate instance and Start the database in NOMOUNT mode.
export ORACLE_SID=FGUARD
sqlplus "sys as sysdba"
startup nomount ;

If this is a Windows Machine, then create the Database Service
% set ORACLE_SID=FGUARD
% set ORACLE_HOME=E:\opt\app\oracle\product\11.1.0\db_1
% oradim -NEW -SID FGUARD

8- For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).
$ export ORACLE_SID=FGUARD
$ rman AUXILIARY /
$ rman TARGET sys/password@PROD AUXILIARY /
$ rman CATALOG rman/password@CATALOG AUXILIARY /
$ rman TARGET sys/password@PROD CATALOG rman/password@CATALOG AUXILIARY /

9- We can now duplicate the database using one of the following commands.

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO FGUARD SPFILE NOFILENAMECHECK;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO FGUARD UNTIL TIME 'SYSDATE-4' SPFILE NOFILENAMECHECK;

# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO FGUARD SPFILE BACKUP LOCATION '/source/app/oracle/fast_recovery_area/FGUARD' NOFILENAMECHECK;