In this blog I will show you how to use an RMAN backup to create a database clone.
In my last blog “Recovery with RMAN is easy!” I explained how RMAN can assist to recover from a data file corruption.
Fortunately failures like corrupted data files or loss of the entire database are very rarely. More often you might have the challenge to create a copy of the database on a different server for testing or Q&A purposes. RMAN can assist directly with the duplicate command. But it might be a good idea to validate the backup while creating a new database.
So let’s restore the backup to our test server.
In the first step we need to copy the backup files to the new server (clapton). In addition we need an spfile but probably only once because by chance you need different parameters (e.g. sga_target) for your test environment.
Source (simon)
SQL> CREATE pfile='/tmp/initPAUL.ora' from spfile; simon:% scp /tmp/initPAUL.ora clapton:/tmp simon:% scp -r /u03/orabackup/PAUL clapton:/u03/orabackup/PAUL
Before we can start the instance, some directory must exists. Esp. the audit dump directry is important. The instance will not start if it doesn’t exist.
clapton:% mkdir –p /opt/oracle/admin/PAUL/adump clapton:% mkdir –p /opt/oracle/admin/PAUL/dpdump clapton:% mkdir –p /u02/oradata clapton:% mkdir –p /u03/orabackup clapton:% chown –R oracle:oinstall /opt/oracle/admin /u02/oradata /u03/orabackup
Now we are able to start the instance on clapton (probably with different parameters):
clapton:% sqlplus / as sysdba SQL> CREATE SPFILE FROM PFILE='/tmp/initPAUL.ora’; SQL> startup nomount
Because the backup location is specified with the parameter db_recovery_file_dest the database or RMAN knows where to look for the backups. You can of course specify a different directory if you change the parameter accordingly. Now we are arble to restore the controlfile from the backup:
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; Starting restore at 29-MAY-19 using channel ORA_DISK_1 recovery area destination: /u03/orabackup database name (or database unique name) used for search: PAUL channel ORA_DISK_1: AUTOBACKUP /u03/orabackup/PAUL/autobackup/2019_05_29/o1_mf_s_1009541851_ggwq1w6x_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/orabackup/PAUL/autobackup/2019_05_29/o1_mf_s_1009541851_ggwq1w6x_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u02/oradata/PAUL/controlfile/o1_mf_gfqy132z_.ctl output file name=/u03/orabackup/PAUL/controlfile/o1_mf_gfqy13hk_.ctl Finished restore at 29-MAY-19
In the next step we are going to mount the controlfile and restore the database:
RMAN> ALTER DATABASE MOUNT; released channel: ORA_DISK_1 Statement processed RMAN> RESTORE DATABASE; Starting restore at 29-MAY-19 Starting implicit crosscheck backup at 29-MAY-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK Crosschecked 23 objects Finished implicit crosscheck backup at 29-MAY-19 Starting implicit crosscheck copy at 29-MAY-19 using channel ORA_DISK_1 Finished implicit crosscheck copy at 29-MAY-19 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u03/orabackup/PAUL/autobackup/2019_05_29/o1_mf_s_1009547148_ggww7djy_.bkp using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/PAUL/datafile/o1_mf_system_gfqxv3xm_.dbf channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/PAUL/datafile/o1_mf_sysaux_gfqxy3cn_.dbf channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/PAUL/datafile/o1_mf_undotbs1_gfqxztmj_.dbf channel ORA_DISK_1: restoring datafile 00007 to /u02/oradata/PAUL/datafile/o1_mf_users_gfqxzvw6_.dbf channel ORA_DISK_1: reading from backup piece /u03/orabackup/PAUL/backupset/2019_05_29/o1_mf_nnndf_TAG20190529T134448_ggww5jj6_.bkp channel ORA_DISK_1: piece handle=/u03/orabackup/PAUL/backupset/2019_05_29/o1_mf_nnndf_TAG20190529T134448_ggww5jj6_.bkp tag=TAG20190529T134448 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile backup set restore … channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 29-MAY-19
Finally we need to recover the database by applying the archive logs.
RMAN> RECOVER DATABASE; Starting recover at 29-MAY-19 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /u03/orabackup/PAUL/archivelog/2019_05_29/o1_mf_1_13_ggww7bkc_.arc archived log file name=/u03/orabackup/PAUL/archivelog/2019_05_29/o1_mf_1_13_ggww7bkc_.arc thread=1 sequence=13 unable to find archived log archived log thread=1 sequence=14 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/29/2019 14:16:46 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and starting SCN of 1599466
You can ignore the error message because it only warns about the missing archive log – which is always the not yet created one.
Now we are able to open the database and look for the content.
We killedtwo birds with one stone:
- We have a procedure to create database clones as needed.
- We verified the RMAN backup.