Introduction: –
In this post we will discussed How to restore RMAN backup from PROD to TEST database 10g,11gR2,12c,19c:-
The situation here is we have taken an RMAN backup on PROD (Source) and we need to restore those backup pieces on different server as TEST (target) database. It’s actually cloning the database from PROD(SOURCE) to TEST (target) using RMAN backup pieces taken on source.
Best way to restore rman backup from prod to test.
Please follow the following steps outlined below.
Please note source (PROD) is where the backup pieces are available. Copy all the backup pieces
(Datafile, control file and spfile) of source (PROD) database to target (TEST) server using scp.
Once copy completes, Run the following script to list the backup pieces available: Register all the copied backup
pieces in the controlfile. (/backup01is the location of copied backup pieces in the target)
In order to let RMAN know about the backup in ‘/backup01/rman_backup’ backup we use the CATALOG command.
Start the database in nomount as below.
$rman target /
RMAN>startup nomount;
RMAN> catalog start with ‘/backup01/rman_backup’;
Or
RMAN> catalog backuppiece ‘/backup01/rman_backup/ora_df_020412_21_14_NSNDB_1635_1′;
If SPFILE is stored in the control file autobackup. All of the RMAN configuration parameters values are
defaults. so we will need to set the location for the control file autobackup.
Before going to ‘How to Restore RMAN backup’ on Production server (PROD). we need to verify the location of datafiles, controlfiles and pfile.
RMAN> show controlfile autobackup format;
BBelow we will set the location for the control file autobackup and restore the SPFILE to a PFILE and then we shutdown the database.
RMAN> set controlfile autobackup format for device type disk to ‘/backup01/oracle/oradata/orcl/backup/%F’;
Retore spfile to pfile on TEST server.
RMAN> restore spfile to pfile ‘/backup01/TEST/initorcl.ora’ from autobackup;
RMAN> shutdown immediate;
Now that we have the pfile we need to edit the parameter to reflect the new host’s file system were appropriate.
After making the necessary changes to the PFILE we now will bring the database back up in NOMOUNT mode so
we can restore the control files. When bringing the database up in NOMOUNT mode we will use the PFILE.
we edited earlier.
RMAN> startup nomount pfile=’/backup01/TEST/initorcl.ora’;
Just like the SPFILE we will use the autobackup to restore the control file after setting the control file autobackup
format. After restoring the control files we mount the database.
RMAN> set controlfile autobackup format for device type disk to ‘/u01/app/oracle/oradata’;
RMAN> restore controlfile from autobackup;
or
We can also restore the controlfile as below.
RMAN>restore controlfile from ‘/u01/backup/ccf_c-1250176543-20081031-01’;
RMAN> alter database mount;
After restoration on pfile, controlfiles we need to restore the database as below.
RMAN> {RUN
SET NEWNAME FOR DATAFILE ‘/data02/PROD/sysdbfiles/system01PROD.dbf’TO ‘/backup /TEST/ system01PROD.dbf ‘;
SET NEWNAME FOR DATAFILE 2 TO ‘/backup /TEST/ undo_APPS_UNDOTS5.dbf’;
SET NEWNAME FOR DATAFILE 3 TO ‘/backup /TEST/ prod_data01PROD.dbf’;
SET NEWNAME FOR DATAFILE 4 TO ‘/backup /TEST/ prod_data02PROD.dbf’;
SET NEWNAME FOR DATAFILE 5 TO ‘/backup /TEST/ prod_data03PROD.dbf’;
SET NEWNAME FOR DATAFILE 6 TO ‘/backup /TEST/ prod_data04PROD.dbf’;
SET NEWNAME FOR DATAFILE 7 TO ‘/backup /TEST/ prod_data05PROD.dbf’;
SET NEWNAME FOR DATAFILE 8 TO ‘/backup /TEST/ prod_data06PROD.dbf’;
SET NEWNAME FOR DATAFILE 9 TO ‘/backup /TEST/ prod_data07PROD.dbf’;
SET NEWNAME FOR DATAFILE 10 TO ‘/backup /TEST/ prod_data08PROD.dbf’;
SET NEWNAME FOR DATAFILE 11 TO ‘/backup /TEST/ prod_data09PROD.dbf’;
SET NEWNAME FOR DATAFILE 12 TO ‘/backup /TEST/ prod_data10PROD.dbf’;
SET NEWNAME FOR DATAFILE 13 TO ‘/backup /TEST/ prod_data11PROD.dbf’;
SET NEWNAME FOR DATAFILE 14 TO ‘/backup /TEST/ prod_data12PROD.dbf’;
SET NEWNAME FOR DATAFILE 15 TO ‘/backup /TEST/ prod_data13PROD.dbf’;
SET NEWNAME FOR DATAFILE 16 TO ‘/backup /TEST/ prod_data14PROD.dbf’;
SET NEWNAME FOR DATAFILE 17 TO ‘/backup /TEST/ prod_data15PROD.dbf’;
SET NEWNAME FOR DATAFILE 18 TO ‘/backup /TEST/ prod_data16PROD.dbf’;
SET NEWNAME FOR DATAFILE 19 TO ‘/backup /TEST/ prod_ndx01PROD.dbf
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Now open the database as below.
RMAN>sql’ alter database open resetlogs’;
Now we need to check the redo log file location if it doesn’t match then we will rename it as below.
SQL> select member from v$logfile;
SQL> alter database rename file ‘/data03/TEST/logfiles/redo01PROD.log’ to ‘/data01/TEST/logfiles/redo01PROD.log’;
SQL> alter database rename file ‘/data03/TEST/logfiles/redo02PROD.log’ to ‘/data01/TEST/logfiles/redo01PROD.log’;
SQL> alter database rename file ‘/data03/TEST/logfiles/redo03PROD.log’ to ‘/data01/TEST/logfiles/redo01PROD.log’;
After that we have to add the temp file if TEMP tablespace is empty as below.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp00.dbf’ size 500m;
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp01.dbf’ size 500m;
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp02.dbf’ size 500m;
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp03.dbf’ size 500m;
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp04.dbf’ size 500m;
If tempfile is available, then we can reuse it as.
SQL> select file#,enabled,status from v$tempfile;
FILE# ENABLEDSTATUS
1 READ WRITE ONLINE
2 READ WRITE ONLINE
3 READ WRITE ONLINE
4 READ WRITE ONLINE
5 READ WRITE ONLINE
6 READ WRITE ONLINE
7 READ WRITE ONLINE
8 READ WRITE ONLINE
9 READ WRITE ONLINE
10 READ WRITE ONLINE
11READ WRITE ONLINE
12 READ WRITE ONLINE
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data01/TEST/temp01.dbf’ size 500m reuse;
So we just know ‘How to Restore RMAN backup’. Apart from this if you want to know how to restore
oracle database in 19c , pls go through the link on ‘Restore and recover Pluggable Database (PDB) from rman backup‘.
Most useful: –
For 12c: Multitenant -Backup and Recovery of Container Database (CDB) and Pluggable Database (PDB) you may check Here.
Very useful article.
Thanks for this information.
Really good information.