This content has been marked as final. Show 9 replies
Hello;1 person found this helpful
I use :
But then I include Archive in the backup. Not sure if it will work without.
RMAN> RESTORE DATABASE PREVIEW;
You can try this too.
RMAN> restore archivelog all preview;
A few ways :
1a. Get the timestamps when the BACKUP ... DATABASE began and ended.
1b. Review the alert.log of the database that was backed up.
1c. From the alert.log identify the first Archivelog that was generated after the begin of the BACKUP ... DATABASE and the first Archivelog that was generated after the end of the BACKUP .. DATABASE.
1d. These (from 1c) are the minimal Archivelogs that you need to RECOVER with. You can choose to apply additional Archivelogs that were generated at the source database to contininue to "roll-forward"
2a. Do a RESTORE DATABASE alone.
2b. Query V$DATAFILE on the restored database for the lowest CHECKPOINT_CHANGE# and CHECKPOINT_TIME. Also query for the highest CHECKPOINT_CHANGE# and CHECKPOINT_TIME.
2c. Go back to the source database and query V$ARCHIVED_LOG (FIRST_CHANGE#) to identify the first Archivelog that has a higher SCN (FIRST_CHANGE#) than the lowest CHECKPOINT_CHANGE# from 2b above. Also query for the first Archivelog that has a higher SCN (FIRST_CHANGE#) than the highest CHECKPOINT_CHANGE# from 2b above.
2d. These (from 2c) are the minimal Archivelogs that you need to RECOVER with.
(why do you need to query V$ARCHIVED_LOG at the source ? If RESTORE a controlfile backup that was generated after the first Archivelog switch after the end of the BACKUP ... DATABASE, you would be able to query V$ARCHIVED_LOG at the restored database as well. That is why it is important to force an archivelog (log switch) after a BACKUP ... DATABASE and then backup the controlfile after this -- i.e. last. That way, the controlfile that you have restored to the new server has all the information needed).
3. RESTORE DATABASE PREVIEW in RMAN if you have the archivelogs and subsequent controlfile in the backup itself !
Hemant K Chitale
I generally use v$recover_file view to get this information. SImly get the minimum and maximum change# from this table and restore all archive logs generated between these SCN's. You may want to keep atleast 2 archives per thread extra during recovery, just in case.
There is also good blog post on this.. http://drdatabase.wordpress.com/2010/07/09/how-much-recovery-is-enough-recovery/.. but this post uses x$ tables.. which you may want to use carefully in production environments.
I'm trying your 1a - 1d ways.
For 1a, I have done this:
For 1b and 1c, I have reviewed both the log.xml and alert_SID.log which are located in the paths returned by the v$DIAG_INFO. There are no "Archived Log entry..." listed between the STARTTIME and ENDTIME of the BACKUP operation in 1a. So, does it mean that the backup is already consistent?
select a.operation, to_char(a.start_time, 'dd-mon-yyyy@hh24:mi:ss') starttime, to_char(a.end_time, 'dd-mon-yyyy@hh24:mi:ss') endtime from v$rman_status a where trunc(start_time) = '06-JAN-13' order by start_time; OPERATION STARTTIME ENDTIME --------------------------------- -------------------- -------------------- RMAN 06-jan-2013@10:38:28 06-jan-2013@11:05:24 BACKUP 06-jan-2013@10:38:29 06-jan-2013@10:55:12 CONTROL FILE AUTOBACKUP 06-jan-2013@10:55:11 06-jan-2013@10:55:12
I have not tired 2a - 2d yet but V$ARCHIVED_LOG does not have the information for 06-JAN-2013 anymore since control_file_record_keep_time is currently set to 7.
For 3, the backup does not include archived logs even though I still have all the archived logs.
Thanks for your help.
v$recover_file returns no row in my environment.
v$recover_file populates the information once the recovery process is started. Can you please try querying this table once the recovery is attempted?
There are no "Archived Log entry..." listed between the STARTTIME and ENDTIME of the BACKUP operation in 1a. So, does it mean that the backup is already consistent?NO. The backup is not consistent if it is made while the database is OPEN.
As I said in my post :
identify the first Archivelog that was generated after the begin of the BACKUP ... DATABASE and the first Archivelog that was generated after the end of the BACKUP .. DATABASE.Therefore, at the minimum you need the first archivelog generated after 11:05, even if it was generated at 12:00 or 13:00 !!
What you should do is put in an SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT ;' command after the BACKUP DATABASE so that you have an ArchiveLog immediately after the end of the database backup.
but V$ARCHIVED_LOG does not have the information for 06-JAN-2013 anymore since control_file_record_keep_time is currently set to 7.that is why it is risky to not include archivelog backups immediately after the database backup.
Also see my comments in this thread : rman backup with archivelog from tape
Hemant K Chitale
I overlooked the important word "after" in your sentence "after the end of the BACKUP .. DATABASE"
I totally agree with you that my backup script should be modified to include alter system archive log current and backup archivelogs.
The backup was done on RHEL 5.7. I wonder whether I can restore the backup to Oracle Linux so I can test the restoring/recovering in VirtualBox.
Excellent Post Hemant.