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;
Step 1
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.
Step 2
Next we need to login to “RMAN” but only connect to the auxiliary database, so use the command;
rman auxiliary /
Step 3
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
In May 2022, DSP, the UK's Leading Oracle Partner acquired Claremont to further expand their Oracle expertise. The acquisition builds on DSP’s “Oracle Anywhere” strategy enabling it to deliver a 360° Oracle services portfolio and form one of the most comprehensive Oracle Partner service offerings in Europe.
The NT uses Oracle E-Business Suite as its CRM System to manage member transactions. Our Managed Services team reduced the support backlog of incidents, problems, and changes in the NT’s CRM system.
Claremont has provided a solution capable of booking, managing, maintaining and analysing Unite Students’ complete property portfolio, allowing them to significantly differentiate themselves from their competitors.
Stonewater chose DSP to consolidate and migrate their assets to Oracle Cloud Infrastructure with an Oracle EBS Cloud Deployment, leading to a significantly reduced TCO.