How to restore RMAN backup from PROD to TEST database 10g,11gR2,12c:-
In this post we will discussed how to restore oracle database using rman.
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.
Its actually cloning the database from PROD(SOURCE) to TEST(target) using RMAN backup pieces taken on source.
Please follow the following steps outlined below.
Please note source (PROD) is where the backup pieces are available. Copy all the backup piecies (datafile,controlfile 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. (/backup01 is 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 at their defaults so we will need to set the location for the control file autobackup.
On Production server(PROD).
RMAN> show controlfile autobackup format;
Below 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 don’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# ENABLED STATUS
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
11 READ WRITE ONLINE
12 READ WRITE ONLINE
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp01.dbf' size 500m reuse;
For 12c: Multitenant -Backup and Recovery of Container Database (CDB) and Pluggable Database (PDB) you may check at https://sajidoracledba.blogspot.com/2019/09/12c-multitenant-container-and-pluggable-RMAN-Backup.html
