Duplicate Database Using RMAN backup location 11gR2

Introduction

RMAN has the ability to duplicate a database from a backup. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

The article assumes the duplicate database is being created on a separate server, using the same SID (TEST) and the same file structure as the source database. 

• Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD password=password entries=10

scp password file to TEST server ($ORACLE_HOME/dbs location) 

•  Make proper entries in tnsnames.ora in both PROD and TEST database

Sample entry as below mention:-

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD) (UR=A)

    )

  )

TEST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = test.localdomain.com)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = TEST)(UR=A)

 )

 )

 

Preparation of TEST database.

        

•  Create pfile (ORACLE_HOME/dbs/initTEST.ora) with below mention parameters.

*.DB_NAME=TEST
*.DB_UNIQUE_NAME=TEST
*.COMPATIBLE='11.2.0'
*.db_block_size=8192
*.db_files=500
*.diagnostic_dest='/u01/app/test/db/tech_st/11.2.0/admin/TEST_test'
*.db_file_name_convert='/d01/oracle/PROD/data/','/d01/oracle/TEST/data/','/d01/oracle/PROD/archive/','/d01/oracle/TEST/archive/'
*.log_file_name_convert=
'/d01/oracle/PROD/data/','/d01/oracle/TEST/data/','/d01/oracle/PROD/archive/','/d01/oracle/TEST/archive/' *.Memory_TARGET=1068937216

• Make proper entries in tnsnames.ora and listener.ora

Sample entry is as below mention

cat tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD) (UR=A)

    )

  )

TEST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = test.localdomain.com)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = TEST)(UR=A)

 )

 )

• Test connection from both database (PROD/TEST). We should be able to connect sqlplus from both PROD and TEST database.

sqlplus sys/Welcome123@PROD as sysdba

sqlplus sys/Welcome123@TEST as sysdba

• Connect to the duplicate instance.

$ ORACLE_SID=TEST; export ORACLE_SID $ sqlplus / as sysdba

Start the database in NOMOUNT mode.

SQL> STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/initTEST.ora';

rman target / sys/sys@prod log=$HOME/restore.log
connect auxiliary sys/sys@TEST

run
{
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk;
allocate auxiliary channel d4 type disk;
allocate auxiliary channel d5 type disk;
allocate auxiliary channel d6 type disk;
duplicate database to TEST backup location '/backups/PROD/RMAN/01Jun';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}

Your duplicate database is ready.

sqlplus > select name from v$database;

TEST

cheers!!!