Oracle 11g, Active duplication.

Oracle Active Duplication, database duplication and cloning.
Oracle Enterprise Edition 11.2.0.2
Linux x86_64, RH 5

I have a previous post describing how I used duplication in 10g to clone my production database to a development instance on a development server. Now in 11g we have the option of using Active Duplication to achieve the same results. Active Duplication is more streamline and so much more simple than previous methods of duplication. This is my example of using Oracle 11gR2 Active Duplication to clone my production database instance to a development instance on the development database server.

Active Duplication does not require a backup of your database. This is a big plus! From the production database server we will login and push the production instance across the network to the development server using RMAN. The downside is that there is some utilization of the production database as well as increased network traffic. A small price to pay to alleviate the headaches the former method of duplication gave me.

There are two methods of Active duplication. Pfile and Spfile. I much prefer the Pfile method (and you will see why), but I will describe the differences and give examples of both methods.

In this example the production instance is named prod311 and our cloned copy will be called dev311.

Terms:
Target – The production database or database you are going to clone. I am using prod311. 
Auxiliary – The duplicated database. I am using dev311.

On the DEVELOPMENT server:
The auxiliary database server must have a matching directory structure to that of the target database server. If your auxiliary environment already exists then the duplication process is much simpler. If not then you must create the environment and directory structure and the simplest way to do that is by creating a new database on the auxiliary server, in this case it would be called dev311. Just be sure all the datafile directories exist (irregardless of the tablespace or datafile names) as they do on the target with the exception of the SID.

For example the prod311 database will have a paths of
/u01/oracle/oradata/prod311
/app/oracle/diag/rdbms/prod311/prod311/trace

and dev311 instance will have
/u01/oracle/oradata/dev311
/app/oracle/diag/rdbms/dev311/dev311/trace

If this is a repeat of the duplication process and your dev311 instance already exists then you are good to go. 

spfile method
Move the current pfile and create a new pfile with only the db_name parameter.
% more $ORACLE_HOME/dbs/initdev311.ora
db_name='dev311' 

pfile method
Before shutting down the auxiliary database, obtain a current pfile if you do not have one already.

SQL> create pfile from spfile;
File created. 

For the pfile method use your current pfile and add the following parameters: 

*.db_file_name_convert='prod311','dev311' 

*.log_file_name_convert='prod311','dev311' 

Both pfile and spfile methods:
Everything else applies to both the pfile and spfile methods with the exception of the RMAN script which I describe further down.

Oracle Net Connectivity:
Establish Oracle Net Connectivity to the Auxiliary instance (dev311) from the Target Host (production, prod311, server). Edit the tnsnames.ora on the Target (prod311) server and add the dev311 (Auxiliary) database.

Now test from the Target -
% tnsping dev311
TNS Ping Utility for Linux: Version 11.2.0.2.0
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Auxiliary-Server.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev311.Auxiliary-Server.com)))
OK (320 msec)

Password file on the Auxiliary server for dev311:
Password file must exist for the Auxiliary database and it MUST MATCH THE TARGET sys user:

Insure there is a password file for the auxiliary instance on the remote server. If not then create a password file on the auxiliary server for the auxiliary database. Be sure the password matches the sys password on your production instance since this is the database we will be cloning.
   
On the Auxiliary server
% cd $ORACLE_HOME/dbs
% orapwd file=orapwdev password=sys_same_on_prod311

Shutdown the auxiliary database.

Delete archive logs from the dev311 instance:
There will likely be existing archive log files on disk for the dev311 instance. Having any existing archive logs for your dev311 environment will cause problems during the duplication.
Delete all archive log folders and/or archive logs for the dev311 instance on the auxiliary server.

% cd $ORACLE_BASE/flash_recovery_area/dev311/archivelog/
% rm -rf *

Move the existing spfile to a new name:
# mv spfiledev311.ora spfiledev311.old 

Restart in nomount with the updated pfile:
SQL> shutdown immediate;
SQL> quit 

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 09:21:34 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.

Start the auxiliary database in nomount using the pfile:

SQL> startup nomount pfile='/app/oracle/product/11.2.0.2/dbs/initdev311.ora'
ORACLE instance started.

On the target prod311 database server start RMAN. Connect to the target and auxiliary. 
The RMAN catalog is not required for Active Duplication.

# rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 02:31:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Note: Do not use the syntax: connect target /
Specify it as: connect target sys/password@SID

Connect to the target (the database you are cloning) and include the alias.
RMAN> connect target sys/xxxxx@prod311;
connected to target database: prod311 (DBID=132331749)

Connect to the auxiliary, dev311, and include the alias.
RMAN> connect auxiliary sys/xxxxx@dev311;
connected to auxiliary database: dev311 (not mounted)

Notice the auxiliary database in not mounted.

Increase the parallelism to 4 (or more).
RMAN> configure device type disk parallelism 4;

Executing the RMAN duplication command:

The first 2 RMAN examples are using the spfile method. The 3rd example, and simplest, is using the pfile method.

This is the basic spfile example:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> nofilenamecheck;
This spfile method example demonstrates how to modify parameters from your target to your auxiliary database:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> set memory_max_target '10536091648'
8> set memory_target '9462349824'
9> set db_flashback_retention_target '1440'
10> set db_recovery_file_dest_size '104857600'
11> nofilenamecheck;

Finally, saving the best for last, using the pfile method
Pfile Method.

RMAN> duplicate target database to dev
2> from active database
3> nofilenamecheck;

RMAN> exit

Recovery Manager complete.

On the newly duplicated database -
I like to shutdown the new cloned database and start it again.


If you used the pfile method make a new spfile.

SQL> create spfile from pfile;
File created. 

If you used the spfile method then create a new pfile.

SQL> create pfile from spfile;
File created. 

SQL> shutdown immediate;
Database closed.Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.0489E+10 bytes
Fixed Size 2235656 bytes
Variable Size 6811550456 bytes
Database Buffers 3623878656 bytes
Redo Buffers 51380224 bytes
Database mounted.
Database opened.

SQL> select name, created, version_time, open_mode from v$database;

NAME CREATED VERSION_T OPEN_MODE
--------- --------- --------- --------------------
DEV311 12-APR-12 12-APR-12 READ WRITE

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If you were using the RMAN catalog for the dev311 instance before duplication you will need to reincarnate it. The newly created dev311 instance (from prod311) now has a new DBID.
Log onto the development server and dev311 database.

RMAN unregister and register after duplication.

Now backup your database.

If you were using OEM you will need to drop and recreate it for the newly duplicated database.
The following post describes how to recreate OEM after duplication,
How to recreate OEM.