Creating Standby database using Active Duplication – Oracle Database 11g

Introduction:

Oracle database 11g introduced a new feature called Active database duplication. Using this feature you can create a new database (primary/standby) from your current running database. This feature does not needs any backup to be taken, nor we have to do any restore.

Creating active duplication is a RMAN feature and command for creating active duplication comes with various options.

Following is the command used for active duplication

duplicate target database to <DB_NAME> from active database;

Following are the various arguments that we can pass to this command

FROM ACTIVE DATABASE: (This is supplied if we want to do active database duplication)

Specifies that the files for the standby database should be provided directly from the source database and not from a backup of the source database

NOFILENAMECHECK:

Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.

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:

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.

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’).

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.

For more details on arguments to be supplied check – http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta020.htm

Following is one example where I created a standby database using active database duplication.

Pre-requisite:

We should have a primary database up and running
pfile, spfile and listener.ora file should be available for both primary and standby
All the relevent directories for datafiles and diagnostic dest should be present
Password file for standby should be present
Entry should exist in /etc/oratab for both primary and standby

Steps for active database duplication

Step 1) nomount the standby database

sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 4 06:53:47 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 2321612800 bytes
Fixed Size                  2174128 bytes
Variable Size            1198015312 bytes
Database Buffers         1073741824 bytes
Redo Buffers               47681536 bytes
SQL>


Step 2) Check the unique name for standby database

SQL> show parameters uniq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcl_a

Step 3) Start active duplication

rman target sys/<password>@orcl1 auxiliary=sys/<password>@orcl1_a

Recovery Manager: Release 11.1.0.7.0 - Production on Wed May 4 07:01:22 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL1 (DBID=998984585)
connected to auxiliary database: ORCL1 (not mounted)

RMAN> duplicate target database to orcl1 from active database nofilenamecheck; --> This will create a new primary database again
RMAN> duplicate target database for standby from active  database nofilenamecheck; --> This will create a new standby database

Starting Duplicate Db at 04-MAY-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1122 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  '/opt/app/oracle/product/11.1.0.7/A24db/dbs/orapworcl1' auxiliary format
 '/opt/app/oracle/product/11.1.0.7/A24db/dbs/orapworcl1'   ;
}
executing Memory Script

Starting backup at 04-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1109 device type=DISK
Finished backup at 04-MAY-11

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/ctl-01/databases/orcl1/control.ctl';
   restore clone controlfile to  '/ctl-02/databases/orcl1/control.ctl' from
 '/ctl-01/databases/orcl1/control.ctl';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 04-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.1.0.7/A24db/dbs/snapcf_orcl1.f tag=TAG20110504T072433 RECID=2 STAMP=750237875
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-MAY-11

Starting restore at 04-MAY-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-MAY-11

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/fs-b01-a/databases/orcl1/temp-01.dbf";
   set newname for tempfile  2 to
 "/fs-a01-a/databases/orcl1/dba_temp-01.dbf";
   set newname for tempfile  3 to
 "/fs-a01-a/databases/orcl1/temp-02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/fs-b01-a/databases/orcl1/system-01.dbf";
   set newname for datafile  2 to
 "/fs-a01-a/databases/orcl1/undo_t1-01.dbf";
   set newname for datafile  3 to
 "/fs-a01-a/databases/orcl1/sysaux-01.dbf";
   set newname for datafile  4 to
 "/fs-a01-a/databases/orcl1/administrator-01.dbf";
   set newname for datafile  5 to
 "/fs-b01-a/databases/orcl1/administrator_idx-01.dbf";
   set newname for datafile  6 to
 "/fs-a01-a/databases/orcl1/replication-01.dbf";
   set newname for datafile  7 to
 "/fs-b01-a/databases/orcl1/replication_idx-01.dbf";
   set newname for datafile  8 to
 "/fs-a01-a/databases/orcl1/rcvcat-001.dbf";
   set newname for datafile  9 to
 "/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf";
   set newname for datafile  10 to
 "/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf";
   set newname for datafile  11 to
 "/fs-d01-a/databases/orcl1/system-20090421171504.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/fs-b01-a/databases/orcl1/system-01.dbf"   datafile
 2 auxiliary format
 "/fs-a01-a/databases/orcl1/undo_t1-01.dbf"   datafile
 3 auxiliary format
 "/fs-a01-a/databases/orcl1/sysaux-01.dbf"   datafile
 4 auxiliary format
 "/fs-a01-a/databases/orcl1/administrator-01.dbf"   datafile
 5 auxiliary format
 "/fs-b01-a/databases/orcl1/administrator_idx-01.dbf"   datafile
 6 auxiliary format
 "/fs-a01-a/databases/orcl1/replication-01.dbf"   datafile
 7 auxiliary format
 "/fs-b01-a/databases/orcl1/replication_idx-01.dbf"   datafile
 8 auxiliary format
 "/fs-a01-a/databases/orcl1/rcvcat-001.dbf"   datafile
 9 auxiliary format
 "/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf"   datafile
 10 auxiliary format
 "/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf"   datafile
 11 auxiliary format
 "/fs-d01-a/databases/orcl1/system-20090421171504.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /fs-b01-a/databases/orcl1/temp-01.dbf in control file
renamed tempfile 2 to /fs-a01-a/databases/orcl1/dba_temp-01.dbf in control file
renamed tempfile 3 to /fs-a01-a/databases/orcl1/temp-02.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 04-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf
output file name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf
output file name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf
output file name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf
output file name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf
output file name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/fs-a01-a/databases/orcl1/administrator-01.dbf
output file name=/fs-a01-a/databases/orcl1/administrator-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf
output file name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/fs-a01-a/databases/orcl1/replication-01.dbf
output file name=/fs-a01-a/databases/orcl1/replication-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf
output file name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/fs-b01-a/databases/orcl1/system-01.dbf
output file name=/fs-b01-a/databases/orcl1/system-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/fs-a01-a/databases/orcl1/sysaux-01.dbf
output file name=/fs-a01-a/databases/orcl1/sysaux-01.dbf tag=TAG20110504T072447 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 04-MAY-11

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=750238434 file name=/fs-a01-a/databases/orcl1/rcvcat-001.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=750238434 file name=/fs-b01-a/databases/orcl1/rcvcat_idx-001.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=750238434 file name=/fs-d01-a/databases/orcl1/sysaux-20090310204613.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=750238434 file name=/fs-d01-a/databases/orcl1/system-20090421171504.dbf
Finished Duplicate Db at 04-MAY-11

RMAN>

This completes active duplication. Your standby is ready now.
Hope this helps !!
Advertisements