When Some of the Archived Logs are Missing

If the missing archived logs are created before at least one database backup sets, there would be no problem at all while restoring a database. But if the missing archived logs are created after all the database backup sets and no other archived logs backups, the only way to recover the database is Database Point-In-Time Recovery (DBPITR).

1. Startup the database to mount.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             792725496 bytes
Database Buffers           41943040 bytes
Redo Buffers                2396160 bytes
Database mounted.

2. Find out the archive gap.
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1           256            258
         2           249            249
The missing archived logs of thread #1 and #2 are listed as above, that is, the last possible archived logs are: sequence 255 thread 1 and sequence 248 thread 2. Let's find out which thread occurred the earliest gap.

3. Find out the earliest gap time.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select thread#, sequence#, max(next_time) from v$archived_log where (thread#=1 and sequence#=255) or (thread#=2 and sequence#=248) group by thread#, sequence#;

   THREAD#  SEQUENCE# MAX(NEXT_TIME)
---------- ---------- -------------------
         1        255 2012-12-22 20:31:19
         2        248 2013-01-08 10:32:14
You can see the thread 1 came the earliest gap time, so we choose sequence 256 thread 1 as our until predicate in recovery.

4. Restore the database.
RMAN> restore database;

Starting restore at 08-JAN-13
using channel ORA_DISK_1

skipping datafile 4; already restored to file +DATA/primdb/datafile/users.1515.804164973
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/primdb/datafile/system.897.797943475
channel ORA_DISK_1: restoring datafile 00002 to +DATA/primdb/datafile/sysaux.898.797943569
channel ORA_DISK_1: restoring datafile 00003 to +DATA/primdb/datafile/undotbs1.899.797943687
channel ORA_DISK_1: restoring datafile 00005 to +DATA/primdb/datafile/example.900.797943711
channel ORA_DISK_1: restoring datafile 00006 to +DATA/primdb/datafile/undotbs2.901.797943737
channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461
channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461 tag=TAG20121217T113005
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 08-JAN-13

5. Recover the database until a specific sequence number of archived log.
RMAN> recover database until sequence 256 thread 1;

Starting recover at 08-JAN-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 75 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917
archived log for thread 1 with sequence 76 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_76.1099.802524949
...
archived log for thread 1 with sequence 254 is already on disk as file +DATA/primdb/archivelog/2012_12_22/thread_1_seq_254.969.802729861
archived log for thread 1 with sequence 255 is already on disk as file +DATA/primdb/archivelog/2012_12_22/thread_1_seq_255.966.802729881
archived log for thread 2 with sequence 70 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_2_seq_70.1097.802524925
archived log for thread 2 with sequence 71 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_2_seq_71.1098.802524941
...
archived log for thread 2 with sequence 243 is already on disk as file +DATA/primdb/archivelog/2012_12_22/thread_2_seq_243.1598.802729763
archived log for thread 2 with sequence 244 is already on disk as file +DATA/primdb/archivelog/2012_12_22/thread_2_seq_244.968.802729767
archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_2_seq_70.1097.802524925 thread=2 sequence=70
archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917 thread=1 sequence=75
...
archived log file name=+DATA/primdb/archivelog/2012_12_22/thread_2_seq_244.968.802729767 thread=2 sequence=244
archived log file name=+DATA/primdb/archivelog/2012_12_22/thread_1_seq_255.966.802729881 thread=1 sequence=255
media recovery complete, elapsed time: 00:11:56
Finished recover at 08-JAN-13

RMAN>

6. Open the database with resetlogs.
SQL> alter database open resetlogs;

Database altered.

If you don't know how to check the archive gap, you can use recover database until cancel under SQL prompt after restoring database.
SQL> recover database until cancel;
You can go as far as you want until you enter CANCEL to indicate the database to stop.

More related posts that you may be interested in:

• When the SPFILE is Missing
• When the Controlfile is Missing
• When Some of the Archived Logs are Missing
• When Several Datafiles Are Missing - Restore From Local Backups
• When Several Datafiles Are Missing - Restore From the Standby Database
• When Almost Everything of a Database is Missing
• How Will the Database React to Missing Tempfiles