This discussion is archived
9 Replies Latest reply: Jan 24, 2013 12:10 AM by Tom RSS

How to find out which archived logs needed to recover a hot backup?

884659 Newbie
Currently Being Moderated
I'm using Oracle 11gR2 (11.2.0.1.0).

I have backed up a database when it is online using the following backup script through RMAN
connect target /
run {
allocate channel d1 type disk;
backup
incremental level=0 cumulative
filesperset 4
format '/san/u01/app/backup/DB_%d_%T_%u_%c.rman'
(
database
);
}
The backup set contains the backup of datafiles and control file. I have copied all the backup pieces to another server where I will restore/recover the database but I don't know which archived logs are needed in order to restore/recover the database to a consistent state.
I have not deleted any archived log.

How can I find out which archived logs are needed to recover the hot backup to a consistent state? Can this be done by querying V$BACKUP_DATAFILE and V$ARCHIVED_LOG? If yes, which columns should I query?

Thanks for any help.
  • 1. Re: How to find out which archived logs needed to recover a hot backup?
    mseberg Guru
    Currently Being Moderated
    Hello;

    I use :
    RMAN> RESTORE DATABASE PREVIEW;
    But then I include Archive in the backup. Not sure if it will work without.

    You can try this too.
    RMAN> restore archivelog all preview;
    Best Regards

    mseberg
  • 2. Re: How to find out which archived logs needed to recover a hot backup?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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
  • 3. Re: How to find out which archived logs needed to recover a hot backup?
    490265 Explorer
    Currently Being Moderated
    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.

    HTH
  • 4. Re: How to find out which archived logs needed to recover a hot backup?
    884659 Newbie
    Currently Being Moderated
    Hi Hemant,

    I'm trying your 1a - 1d ways.

    For 1a, I have done this:
    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 
    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?

    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.
  • 5. Re: How to find out which archived logs needed to recover a hot backup?
    884659 Newbie
    Currently Being Moderated
    v$recover_file returns no row in my environment.
  • 6. Re: How to find out which archived logs needed to recover a hot backup?
    490265 Explorer
    Currently Being Moderated
    v$recover_file populates the information once the recovery process is started. Can you please try querying this table once the recovery is attempted?
  • 7. Re: How to find out which archived logs needed to recover a hot backup?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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
  • 8. Re: How to find out which archived logs needed to recover a hot backup?
    884659 Newbie
    Currently Being Moderated
    Hi Hemant,

    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.

    Thanks.
  • 9. Re: How to find out which archived logs needed to recover a hot backup?
    Tom Newbie
    Currently Being Moderated
    Excellent Post Hemant.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points