Question :
I’ve taken backup of datafiles in 31.12.2013. It completed datafile backups successfully, but archivelog backups failed.
Now, I want to determine what archivelogs should I backup to be sure that my database will be recoverable after restoring 31.12.2013 backup of datafiles.
Answer :
You may try (requires at least Oracle 10g):
RESTORE DATABASE PREVIEW;
You’ll need at least the archive logs generated during the entire backup operation.
A quick demo:
C:Users>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 8 14:34:28 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB12C (DBID=4034496039)
RMAN> list backup of database summary completed after 'sysdate - 1';
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
1 B F A DISK 08-jan-14:14:23:45 1 1 NO TAG20140108T141855
2 B F A DISK 08-jan-14:14:24:48 1 1 NO TAG20140108T141855
3 B F A DISK 08-jan-14:14:25:46 1 1 NO TAG20140108T141855
RMAN> restore database preview summary from tag = TAG20140108T141855;
Starting restore at 08-jan-14:14:34:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
1 B F A DISK 08-jan-14:14:23:44 1 1 NO TAG20140108T141855
3 B F A DISK 08-jan-14:14:25:30 1 1 NO TAG20140108T141855
2 B F A DISK 08-jan-14:14:24:31 1 1 NO TAG20140108T141855
using channel ORA_DISK_1
List of Archived Log Copies for database with db_unique_name CDB12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ------------------
1 1 13 A 03-jan-14:15:01:14
Name: C:APPORACLEFAST_RECOVERY_AREACDB12CARCHIVELOG2014_01_08O1_MF_1_13_9DTNSSGR_.ARC
Media recovery start SCN is 2475111
Recovery must be done beyond SCN 2475373 to clear datafile fuzziness
Finished restore at 08-jan-14:14:34:49
RMAN> list archivelog from scn 2475111 until scn 2475374;
List of Archived Log Copies for database with db_unique_name CDB12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ------------------
1 1 13 A 03-jan-14:15:01:14
Name: C:APPORACLEFAST_RECOVERY_AREACDB12CARCHIVELOG2014_01_08O1_MF_1_13_9DTNSSGR_.ARC
Note the scn min-max indications in the restore … preview output:
Media recovery start SCN is 2475111 Recovery must be done beyond SCN
2475373 to clear datafile fuzziness
So to go beyond the indicated scn, we search for archived logs that contain scn 2475373 + 1.
If you want to verify manually, check the following MOS Note:
How to determine minimum end point for recovery of an RMAN backup (Doc ID 1329415.1)
Depending on your requirements and what other backups you have done since then, you could use (from http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmreprt.htm#BRADV89594):
REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.
REPORT NEED BACKUP REDUNDANCY n
Displays objects requiring backup to satisfy a redundancy-based retention policy.
REPORT NEED BACKUP DAYS n
Displays files that require more than n days' worth of archived redo log files for recovery.
REPORT NEED BACKUP INCREMENTAL n
Displays files that require application of more than n incremental backups for recovery.
These will tell you which files you need to backup to provide ‘n’ days worth of restore capability or ‘n’ redundancy.