Normally recovering an Oracle database is a pretty straight forward affair these days when using RMAN, however, what about when you want to recover or clone a copy of a database to a previous date/time when you’re not using a recovery catalog?
A requirement arose recently whereby we needed to duplicate a LIVE database to a point in time that was several days older than the details held in RMAN. Issuing the usual duplicate commands with the “until time” values results in error, as shown below;
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: =========================================================
RMAN-03002: failure of Duplicate Db command at 30/09/2014 20:10:07
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore
The obvious thing to do here is to check that a control file for the time and date required for recovery exists in the FRA (Flash Recovery Area), if it doesn’t then restoring it from a previous tape backup along with any necessary archivelogs is required.
So having recovered the necessary controlfile and archivelogs, we try the command again; this results in the exact same error.
One thing you can do is check if the RMAN catalog is aware of the controlfile, so using the command below results in ;
RMAN> list backup of controlfile completed before '30-SEP-2014';
Specification does not match any backup in the repository
This suggests RMAN has nothing in its catalog for the date required, even though you know there is a controlfile there for the correct date/time required.
You can make RMAN read the contents of the FRA and reload into its catalog, using the command;
RMAN> catalog recovery area ;
or
RMAN> catalog controfilecopy ‘<full path and filename>’ ;
Both of these commands will be successful, however, when you try and issue the list backup command again, you get the same message ;
RMAN> list backup of controlfile completed before '30-SEP-2014';
Specification does not match any backup in the repository
So what’s the answer? Well to affect a successful recovery you need to remove any reference to the source database catalog, so on the machine you want to create the new environment/clonetarget machine, complete the following steps;
Start up the target environment in a state ready to be duplicated, so we started up the instance using a predefined init.ora file as shown below;
startup nomount pfile=/app/db/tech_st/11.2.0.4/dbs/duplicatedb.ora
The “pfile” should include the “db_file_name_convert” and “db_log_file_name_convert” variables and be set correctly.
Next we need to login to “RMAN” but only connect to the auxiliary database, so use the command;
rman auxiliary /
Next we run the duplicate command, but specify where it should find the backups and what time we want it restoring back to duplicate database to NEWDB until time “to_date(‘2014-09-30:03:50:00′,’YYYY-MM-DD:hh24:mi:ss’)” backup location ‘/backup/fast_recovery_area/DBLIVE’; We picked 3.50am here since the controlfile backup timestamp was 3.49, so 1 minute later RMAN will then start restoring controlfiles and mounting as normal;
Starting restore at 03-OCT-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=506 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/DBLIVE/control01.dbf
output file name=/redo1/DBLIVE/control02.dbf
output file name=/redo2/DBLIVE/control03.dbf
Finished restore at 03-OCT-14 database mounted
RMAN will then continue as any other duplicate/restore situation:
channel ORA_AUX_DISK_8: reading from backup piece
/backup/fast_recovery_area/DBLIVE/backupset/2014_09_30/o1_mf_nnnd0_FULL_b049k9fq_.bkp
channel ORA_AUX_DISK_9: starting datafile backup set restore
channel ORA_AUX_DISK_9: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_9: restoring datafile 00009 to /oradata/NEWDB/odm.dbf
channel ORA_AUX_DISK_9: restoring datafile 00010 to /oradata/NEWDB/olap.dbf
channel ORA_AUX_DISK_9: restoring datafile 00017 to /oradata/NEWDB/a_int02.dbf
channel ORA_AUX_DISK_9: restoring datafile 00024 to /oradata/NEWDB/undotbs2_1.dbf
channel ORA_AUX_DISK_9: restoring datafile 00032 to /oradata/NEWDB/a_txn_data10.dbf
So in conclusion, it seems that RMAN whilst using a controlfile rather than a specific catalog, will only store records of the most recent controlfile backup and we have to be more explicit when running a restore to a point in time before that most recent controlfile backup