RMAN DUPLICATE command creates duplicate database from backups of the target database while retaining the original target database.

The duplicate database can be identical to the target database or contain only a subset of the table-spaces in the target database. The target site and the duplicate site can be on separate hosts or on the same host.

The Duplicate DB can run independently and used for several reasons for example:

• Test backup and recovery procedures
• Export data such as a table that was inadvertently dropped from the production database, and then import it back into the production database

1. Duplicate and Standby databases

• A duplicate database is distinct from a standby database, both are created with the DUPLICATE command.
• A standby DB is a copy of the primary database that continually update with archived logs from the primary DB.
• If the primary database is damaged or destroyed, then you can perform fail-over to the standby database and transform it into the new primary database.
• While duplicate database is not intended for fail-over scenarios and does not support the various standby recovery and fail-over options.

2. Process of Duplication

• Creates a control file for the duplicate database
• Restores the target data-files into the duplicate database and performs incomplete recovery using all available archived log and incremental backups.
• Shuts down and starts the auxiliary instance
• Opens the duplicate DB with the RESETLOGS option after incomplete recovery to create the online redo logs.
• Generates a new, unique database identifier for the duplicate database

During duplication, RMAN must perform incomplete recovery because the online redo logs in the target are not backed up and cannot be applied to the duplicate database. The farthest that RMAN can go in recovery of the duplicate database is the most recent redo log archived by the target database.

3. Creating a Duplicate Database with RMAN:

• You must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode
• Allocate at least one auxiliary channel on the auxiliary instance.
• This starts a server session on the duplicate host.

  This channel then restores the necessary backups of the primary database

• and initiates recovery

4. Backup and archive log required for Duplicate:

• Last full backup and archive log are required for duplication.
• Parameters file for starting new instance.
• RMAN client can run on any host. All backups and archived logs, must be accessible on duplicate host
• If the duplicate host is not the same as the target host, then copy backups and archive log to duplicate host with the same full path as in the primary database. Two options can be used
  1. Manually transfer files from the primary host to the remote host to an identical path.
  2. Manually transfer the backups from the primary host to the duplicate host at a new location. Run the CATALOG command to add these copies to the RMAN repository at the duplicate host.

Decide the Duplicate Database name for example (LHDUP)

1
2

GB IN BYTE    DIVIDE         MB 
2147483648  1024 x 1024      2048 

STEP – 0          Installation of Oracle Database software on duplicate host

Install oracle database software on duplicate host if required where you want to create duplicate database. For example

1
2
3
4
5

Screen 1:          Type of installation  =   Enterprise Edition
Screen 2:          HOME = C:\oracle\product\10.2.0\db_1
Screen 3:          No Action
Screen 4:          Install DATABASE software only
Screen 5:          Click Install

STEP – 1          Create an Oracle Password File for the Auxiliary Instance

Create a password file on duplicate host using password file utility. This password file will be used in creation of new auxiliary instance.

Orapwd file=C:\oracle\product\10.2.0\db_1\DATABASE\PWDLHDUP.ora password=DUP

STEP – 2          Create an Initialization Parameter File for the Auxiliary Instance

Use following list of commands to create parameters file using target DB. This file will be used in creation of new auxiliary instance.

Login to Target DB

1
2

SQL > SYS/****@LHR AS SYSDB
SQL > Create pfile=’C:\initLHDUP.ora’ from spfile;

The file will be created on target host copy or move newly created file to duplicate host following location.

1

’C:\oracle\product\10.2.0\db_1\DATABASE\initLHDUP.ora’

Modify parameters. All path parameter should be accessible on duplicate host.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

db_name='LHDUP' 
 
db_file_name_convert=
('d:\oracle_data_files\oradata\LHDB',
'c:\oracle_data_files\LHDUP\oradata')
 
log_file_name_convert=
('d:\oracle_data_files\oradata\LHDB',
'c:\oracle_data_files\LHDUP\oradata')
 
control_files=
'C:\oracle_data_files\LHDUP\oradata\control01.ctl', 
'C:\oracle_data_files\LHDUP\oradata\control02.ctl', 
'C:\oracle_data_files\LHDUP\oradata\control03.ctl'
 
compatible='10.2.0.1.0' 
db_block_size=8192 # The block size for the auxiliary database must 
match that of the target DB 
 
db_cache_size=50331648
java_pool_size=25165824
large_pool_size=4194304
shared_pool_size=33554432
streams_pool_size=0
pga_aggregate_target=16777216
sga_max_size=113246208
sga_target=167772160
 
audit_file_dest='C:\oracle_data_files\LHDUP\admin\adump' 
background_dump_dest='C:\oracle_data_files\LHDUP\admin\bdump' 
core_dump_dest='C:\oracle_data_files\LHDUP\admin\cdump' 
user_dump_dest='C:\oracle_data_files\LHDUP\admin\udump' 

Create directory structure on duplicate host

Directory structure for target host (will be used to keep backups and archive logs for duplication)

1
2

MKDIR F:\flash_recovery_area\LHDB\BACKUPSET
MKDIR F:\flash_recovery_area\LHDB\ARCHIVELOG

New Directory structure for duplicate host (or use batch file provided)

1
2
3
4
5
6
7
8
9
10
11

MKDIR C:\oracle_data_files\LHDUP\oradata
MKDIR C:\oracle_data_files\LHDUP\admin
MKDIR C:\oracle_data_files\LHDUP\flash_recovery_area\ARCHIVELOG
 
MKDIR C:\oracle_data_files\LHDUP\admin\adump
MKDIR C:\oracle_data_files\LHDUP\admin\bdump
MKDIR C:\oracle_data_files\LHDUP\admin\cdump
MKDIR C:\oracle_data_files\LHDUP\admin\udump
MKDIR C:\oracle_data_files\LHDUP\admin\dpdump
MKDIR C:\oracle_data_files\LHDUP\admin\pfile
MKDIR C:\oracle_data_files\LHDUP\admin\scripts

STEP – 3          Create Auxiliary Instance

1
2
3
4
5
6
7
8
9
10
11
12
13

Cmd
Cd\
 
ORADIM -NEW -SID LHDUP -intpwd change_on_install -startmode auto 
-pfile 'c:\oracle\product\10.2.0\db_1\database\initLHDUP.ora'
 
ORADIM -NEW -SID LHDUP -startmode auto -pfile 
'c:\oracle\product\10.2.0\db_1\database\initLHDUP.ora'
            ORADIM -DELETE -SID LHDUP   # to delete existing instance
 
SET ORACLE_SID=LHDUP
 
 

STEP – 4          Establish Oracle Net Connectivity to the Auxiliary Instance ( on duplicate host)

• Add listener on duplicate host if not already added using “Oracle net configuration assistant” wizard
• Modify Listener.ora file using “Net manager utility“ to add auxiliary instance.

1
2
3
4
5

LISTENER > DATABASE services > ADD DATABASE
 
Global DATABASE Name  = LHDUP
SID                   = LHDUP
Oracle home directoy  = C:\oracle\product\10.2.0\db_1

• Save configuration and exit ( restart listener service)
• Modify tnsnames.ora file to add following services

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

LHDUP =                # Net connection for Auxiliary instance (required)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=SRDC-IT-04)(Port=1521))
)
(CONNECT_DATA =
(SID = LHDUP)  )   )
 
LHR =                   #Net connection for target database (required)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=150.154.0.2)(Port=1521))
)
(CONNECT_DATA =
(SID = LHDB)   )   )
 
DEVDB =                 # Net connection for recovery catalog (optional) 
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=150.154.0.31)(Port=1521))
)
(CONNECT_DATA =
(SID = DEVDB) )   )

STEP – 5          Start Auxiliary Instance

1
2

C:\> sqlplus "sys/change_on_install@lhdup as sysdba" 
SQL> startup nomount;  

STEP – 6          Mount or Open the Target Database  (optional if target Db is not opened)

1
2

C:\> sqlplus "sys/*****@lhr as sysdba" 
SQL> startup open; 

STEP – 7          Available Necessary backups and archive logs

Ensure that you have current level 0 backup and level 1 backup and archive logs after level 0 backup.

STEP – 8          Login to Target and Auxiliary instance

1
2
3
4

RMAN 
RMAN > CONNECT TARGET SYS@LHR 
RMAN > CONNECT CATALOG RMANLH/CAT@DEVDB     (this IS optional don’t USE) 
RMAN > CONNECT AUXILIARY SYS/DUP@LHDUP 

STEP – 9          RUN RMAN DUPLICATE command

1
2
3
4
5
6

run {
allocate auxiliary channel ch1 type disk;
duplicate target DATABASE TO lhdup
pfile='c:\oracle\product\10.2.0\db_1\database\initLHDUP.ora';