Oracle DBA Centre: Duplicate Active database using RMAN

Duplicate Active database using RMAN

This article will show you how you can Use RMAN to create a new database using the DUPLICATE…..FROM ACTIVE DATABASE command.

The steps are as follow:

Create the initialization Parameter file for the Target database
Establish network connectivity between the source and target (Auxiliary) database
Create the directories which are required for the Target database
Create the parameter file (initTargetDB.ora)
Start the Target(Auxiliary) instance
Test connectivity to target(auxiliary) and source instance from the Both Sides
Start the duplication process using RMAN

If can afford some downtime, you could also do this without RMAN:Clone the Oracle Database to new host, without RMAN

Video of duplication in Action:

Active database duplication copies the target database over the network to the destination and then creates the duplicate database. Only difference is that you don’t need to have the pre-existing RMAN backups and copies. The duplication work is performed by an auxiliary channel. This channel corresponds to a server session on the auxiliary instance on the auxiliary host.

During the duplicating operation, RMAN automates the following steps:

Creates a control file for the duplicate database.
Restarts the auxiliary instance and mounts the duplicate control file.
Creates the duplicate data files and recovers them with incremental backups and archived redo logs.
Opens the duplicate database with the resetlogs option.
For the active database duplication, RMAN will copy the target database data files over the network to the auxiliary instance.

The Only draw back is the increased resource utilization, CPU/IO and network bandwidth usage.

Setup Source Database

Before we start we need to verify that the database is in Archivelog Mode:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence           16

SQL>

If not: Enable it (Archivelog Guide)

Setup the Target Database

Before we start working in RMAN there is a few steps we need to perform on the Target side:

Create the initialization Parameter file

If you are using spfile then only parameter required for the duplicate database is DB_NAME. The Rest of the parameters can be set in the duplicate command itself.

If you are not using the spfile , then you need to set initialization parameters in the pfile.
Required parameters if running pfile on source database:

DB_NAME
CONTROL_FILES
DB_BLOCK_SIZE
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT

On the Target database in$ORACLE_HOME/dbs create the file spfileTargetDB.ora

If you’re Source database is started using a SPfile then you only have to add:

DB_NAME=TargetDB

If your source is started using pfile, then you have to add (as a minimum):

DB_NAME=TargetDB
DB_FILE_name_CONVERT=('oradata/prodb01','oradata/prodb02')
LOG_FILE_NAME_CONVERT=('oradata/prodb01','oradata/prodb02')
Memory_TARGET=2147483648
CONTROL_FILES='/oradata/prodb02/cntrl01.dbf'
COMPATIBLE= 11.2.0.0.0

Copy password file to Target

Password file is must for the Active database duplication where as it is not required for backup-based duplication. For Active database duplication it connects directly to the auxiliary instance using the password file with the same SYSDBA password as target database.

So copy the password from on SOURCE database to Target database:

oracle@prodb01:~$ cd $ORACLE_HOME/dbs

oracle@prodb02:/oracle/app/oracle/product/11.2.0/dbhome_1$ scp tnsnames.ora oracle@prodb02:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

Establish Oracle Net Connectivity to the Auxiliary Instance

Auxiliary instance must be available through Oracle Net if you are duplicating from an ACTIVE database.

Add following entries into listener.ora file.

For the Target database you need to have a static listener, like:

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=Target)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (SID_NAME = TargetDB)

 (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

 (GLOBAL_DBNAME = TargetDB)

 )

 )

Add the Following entry in the tnsnames.ora file ons BOTH the TargetDB AND the SourceDB

sourceDB =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = Source)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = sourceDB)

 )

 )

targetDB =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = Target)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = TargetDB)

 )

 )

Create the directories which are required for the duplicate database.

Create your folder structure accoding to your setup:

mkdir -p /oradata/TargetDB
mkdir -p /oradata/fast_recovery_area/TargetDB
mkdir -p /oracle/app/oracle/admin/TargetDB/pfile
mkdir -p /oracle/app/oracle/admin/TargetDB/adump

Start the Auxiliary instance in nomount.

Use SQL*Plus to connect to the auxiliary instance using the above created pfile and start it in NOMOUNT mode.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  229683200 bytes

Fixed Size                  2249560 bytes

Variable Size             171969704 bytes

Database Buffers           50331648 bytes

Redo Buffers                5132288 bytes

Test connectivity from both sides

tnsping TargetDB

tnsping SourceDB

sqlplus sys/oracle@TargetDB

sqlplus sys/oracle@SourceDB

Start the duplication process

First connect to the target database and the auxiliary database(if you are using a RMAN catalog, connect to that as well)

rman target sys/oracle@SourceDB auxiliary sys/oracle@TargetDB

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 23 21:08:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SourceDB (DBID=4040758735)

connected to auxiliary database: TargetDB (DBID=4168488355)

RMAN>

When we are connected to both the SourceDB and TargetDB(Auxiliary) database, we can set the new location folders using the following:

duplicate target database to TARGET from active database SPFILE 

parameter_value_convert 'SourceDB','TargetDB'

set db_file_name_convert='oradata/SourceDB','oradata/TargetDB'

set log_file_name_convert='oradata/SourceDB','oradata/TargetDB'

set control_files='/oradata/TargetDB/cntrl01.dbf'

NOFILENAMECHECK;

Depending on your goal, you might have to change the parameters or add more(info from docs.oracle.com

When running the DUPLICATE command in this configuration, you must specify the NOFILENAMECHECK option on the DUPLICATE command, otherwise you might run in to ORA-05001 Error. If you duplicate a database on the same host as the source database, then verify that NOFILENAMECHECK is not specified

When this commands finishes your Target database is already up and running

Done!

Oracle DBA Centre

Main Menu

Duplicate Active database using RMAN

Duplicate Active database using RMAN

Setup Source Database

Setup the Target Database

Create the initialization Parameter file

Copy password file to Target

Establish Oracle Net Connectivity to the Auxiliary Instance

Create the directories which are required for the duplicate database.

Start the Auxiliary instance in nomount.

Test connectivity from both sides

Start the duplication process

No comments: