Steps to Refresh RAC Database using RMAN Backup

Environment Details:

Source : (Two Node RAC)
Database  :   RJ1PRD
ASM DG :  +RJ1PRD_DG

Target: (Two Node RAC)
Database  :  RJ1UAT
ASM DG :  +RJ1UAT_DG


1. Copy the RMAN backup pieces to Target Database Server.

2. Take a backup of db_link and proxy users etc...from Target Database.

3. Shut-down the Target Database RJ1UAT .(Both instances)

4.  Please Add below *convert* parameters and modify cluster_database parameter from true to false on first node.

Ex:
# added for cloning
db_file_name_convert=('+RJ1PRD_DG','+RJ1UAT_DG')
log_file_name_convert=('+RJ1PRD_DG','+RJ1UAT_DG')
*.cluster_database=FALSE

5. Start the database on first node.

6. Make sure we have enough space on Target ASM diskgroup.

SELECT name, total_mb/1024/1024 TOTAL_TB,free_mb/1024/1024 FREE_TB, free_mb/total_mb*100 as percentage  FROM v$asm_diskgroup where name='RJ1UAT_DG';

7.Drop Target Database.

shurtdown the database and startup mount in restrict mode.

SQL> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area 2.9931E+10 bytes
Fixed Size                  2267792 bytes
Variable Size            1.2885E+10 bytes
Database Buffers         1.6979E+10 bytes
Redo Buffers               64827392 bytes
Database mounted.

SQL> select logins,parallel from v$instance;

LOGINS     PAR
---------- ---
RESTRICTED NO

SQL> select name from v$database;

NAME
---------
RJ1UAT

SQL> drop database;

Database dropped.

Then make sure all the data& control files are deleted properly or not.you can check the free space using below query .

SELECT name, total_mb/1024/1024 TOTAL_TB,free_mb/1024/1024 FREE_TB, free_mb/total_mb*100 as percentage  FROM v$asm_diskgroup where name='RJ1UAT_DG';



8.Startup nomount and issue duplicate command on first node.

 startup nomount;

Save the file restore.rman with below commands.

run
{
 allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
allocate auxiliary channel ch5 type disk;
allocate auxiliary channel ch6 type disk;
allocate auxiliary channel ch7 type disk;
allocate auxiliary channel ch8 type disk;
duplicate database to RJ1UAT
BACKUP LOCATION '/backup/RMAN_BACKUPS/Sat/'
 logfile
    group 1 ('+RJ1UAT_DG', '+RJ1UAT_DG') size 500M REUSE,
    group 2 ('+RJ1UAT_DG', '+RJ1UAT_DG') size 500M REUSE,
    group 3 ('+RJ1UAT_DG', '+RJ1UAT_DG') size 500M REUSE,
    group 4 ('+RJ1UAT_DG', '+RJ1UAT_DG') size 500M REUSE,
    group 5 ('+RJ1UAT_DG', '+RJ1UAT_DG') size 500M REUSE
 ;
}

rman auxiliary / nocatalog log=/backup/RMAN_BACKUPS/logs/RJ1UAT.log

@restore.rman

9. Once the dupicate command completed,remove the below parameters from init.ora and change the cluster_database value on first node.

# Remove after cloning
db_file_name_convert=('+RJ1PRD_DG','+RJ1UAT_DG')
log_file_name_convert=('+RJ1PRD_DG','+RJ1UAT_DG')
#change after cloning
*.cluster_database=TRUE

Recycle the database

10.  Add the redo log thread for instacne 2.

alter database add logfile thread 2 group 6 ('+RJ1UAT_DG') size 500m reuse;

alter database add logfile thread 2 group 7 ('+RJ1UAT_DG') size 500m reuse;

alter database add logfile thread 2 group 8 ('+RJ1UAT_DG') size 500m reuse;

alter database add logfile thread 2 group 9 ('+RJ1UAT_DG') size 500m reuse;

alter database add logfile thread 2 group 10 ('+RJ1UAT_DG') size 500m reuse;

alter database enable public thread 2;

11.Then start the database on second node and verify gv$instance & Start the listener

Note : In case if you want to find out the time of restored data,you can grep "set until scn" from /backup/RMAN_BACKUPS/logs/RJ1UAT.log .Based on that SCN,you can find the timestamp.

select scn_to_timestamp(2880392)from dual;