How to create a clone database from physical standby database backup

Cloning a standby database for test instance:

If you want to use the server resources of standby database, you may need to clone the standby database to create test instances. By this way, you don't touch the primary instace (production), you just use the standby environment resources for backup and restore.

The problem of cloning standby database is that, you can't use rman duplicate feature. Let's get started with taking backup of standby database.

Connect to PROD catalog on standby side:

$ rman catalog oracle/oracle@catalog target /

RMAN> backup database plus archivelog;

Go to the primary side for only one time to take controlfile backup and copy this backup to standby side.

On primary RMAN;

run

{

allocate channel c1 device type disk;

backup current controlfile;

} 

On standby side start an instance on nomount mode:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

export ORACLE_SID=TEST1

$ sqlplus / as sysdba

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2260088 bytes

Variable Size             738198408 bytes

Database Buffers         3523215360 bytes

Redo Buffers               12107776 bytes

Switch to rman and restore controlfile from backup taken on primary side:

$ rman target /

RMAN> restore controlfile from '/u01/backup/PROD/c-809882354-20150723-03';

Starting restore at 23-JUL-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=522 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=+DATAC1/TEST/controlfile/cntrl01.dbf

output file name=+RECOC1/TEST/controlfile/cntrl02.dbf

Finished restore at 23-JUL-15

Let's mount the database:

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

Catalog all backup files taken on first step.

RMAN> catalog start with '/u01/backup/PROD';

Restore the database similar to following script

run

{

allocate channel dc1 device type disk;

allocate channel dc2 device type disk;

allocate channel dc3 device type disk;

allocate channel dc4 device type disk;

set newname for datafile 1 to '+DATAC1/TEST/datafile/system.dbf';

set newname for datafile 2 to '+DATAC1/TEST/datafile/sysaux.dbf';

set newname for datafile 3 to '+DATAC1/TEST/datafile/undotbs1.dbf';

set newname for datafile 4 to '+DATAC1/TEST/datafile/undotbs2.dbf';

set newname for datafile 5 to '+DATAC1/TEST/datafile/users.dbf';

set newname for datafile 6 to '+DATAC1/TEST/datafile/apps_ts_archive.dbf';

set newname for datafile 7 to '+DATAC1/TEST/datafile/apps_ts_interface.dbf';

set newname for datafile 8 to '+DATAC1/TEST/datafile/apps_ts_media.dbf';

restore database;

switch datafile all;

recover database;

}

After restore completes, start the database with open resetlogs;

RMAN> alter database open resetlogs;

Database altered.

After restoring the database, you may need to change the SID, considering it's name is still PROD.

For this purpose;

Mount the database;

SQL> startup mount;

Run the following command.

nid target=/ dbname=TEST setname=y logfile=new_name.log

Start the database on nomount mode:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1353551872 bytes

Fixed Size                  2252704 bytes

Variable Size            1107296352 bytes

Database Buffers          100663296 bytes

Redo Buffers              143339520 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      PROD

SQL> alter system set db_name=TEST scope=spfile;

System altered.