RMAN backup based duplicate for specified time

Hello DBA's.

This post will help on how to duplicate the database through RMAN for a desired time. In this example i am restoring the database to 12-DEC-2016 with 12-DEC-2016 RMAN backup.

	Source	Target
Host name	PRODDB	TESTDB
Instance name	PROD	CLONE

Scp the backup pieces from prod to clone. We had scheduled level 0 backup daily so i simply moved the 12-Dec backup. I have controlfile autobackup turned on and i used control file autobackup.

Please find the backup script :

RUN

{

ALLOCATE CHANNEL t1 DEVICE TYPE DISK MAXPIECESIZE = 8G;

ALLOCATE CHANNEL t2 DEVICE TYPE DISK MAXPIECESIZE = 8G;

ALLOCATE CHANNEL t3 DEVICE TYPE DISK MAXPIECESIZE = 8G;

ALLOCATE CHANNEL t4 DEVICE TYPE DISK MAXPIECESIZE = 8G;

ALLOCATE CHANNEL t5 DEVICE TYPE DISK MAXPIECESIZE = 8G;

backup as compressed backupset incremental level 0 FILESPERSET 4 tag="LEVEL0"

format '$DIRNAME/FULL_%d_%U'

(database);

RELEASE CHANNEL t1 ;

RELEASE CHANNEL t2 ;

RELEASE CHANNEL t3 ;

RELEASE CHANNEL t4 ;

RELEASE CHANNEL t5 ;

sql 'alter system archive log current';

backup

  filesperset 20 tag="ARCH"

  format '$DIRNAME/ar_%t_%s_%p'

  (archivelog all);

delete noprompt archivelog until time 'SYSDATE - 15';

backup tag="CTLFILE"

  format '$DIRNAME/cf_%d_%U'

  (current controlfile);

}

Pfile creation :

If prod is running with spfile create a pfile from spfile and scp it to clone server.

$sqlplus / as sysdba

SQL > create pfile from spfile;

Scp pfile to $ORACLE_HOME/dbs to clone server.

Edit the pfile and edit the below parameters.

db_name                         = CLONE

control_files                   = /a02/oraclone/CLONE/data/cntrl01.dbf,/a02/oraclone/CLONE/data/cntrl02.dbf,/a02/oraclone/CLONE/data/cntrl03.dbf

db_file_name_convert='/u01/oracle/PROD/data','/a02/oraclone/CLONE/data'

log_file_name_convert='/u01/oracle/PROD/data','/a02/oraclone/CLONE/data'

Clone instance :

Once pfile is created start the instance in nomount.

RMAN Connectivity :

Check whether you are able to connect to both database through RMAN.

RMAN> connect auxiliary sys/change_on_install@CLONE

cat rman_backup_duplicate.rcv
connect auxiliary sys/change_on_install@CLONE

RUN
{
ALLOCATE AUXILIARY CHANNEL ch01 device type disk;
ALLOCATE AUXILIARY CHANNEL ch02 device type disk;
ALLOCATE AUXILIARY CHANNEL ch03 device type disk;
ALLOCATE AUXILIARY CHANNEL ch04 device type disk;
#ALLOCATE CHANNEL ch03 device type disk;
#ALLOCATE CHANNEL ch04 device type disk;


SET UNTIL TIME = "TO_DATE('2016-12-12 22:15:00', 'YYYY-MM-DD HH24:MI:SS')";
DUPLICATE DATABASE TO 'clone' backup location ='/a02/oraclone/CLONE/backup/RMAN_BKP_Mon_12Dec2016/';


RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
}

Note :

We have scheduled daily RMAN backup at 22:00:00 daily which will complete around 22:19:00. So i set the time at 22:15:00 to restore.

Nohup rman cmdfile=rman_backup_duplicate.rcv log= rman_duplicate.log &