When Some of the Archived Logs are Missing

  • by
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.
  2. [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.

  3. Find out the archive gap.
  4. 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.
  5. Find out the earliest gap time.
  6. 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.
  7. Restore the database.
  8. 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

  9. Recover the database until a specific sequence number of archived log.
  10. 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>

  11. Open the database with resetlogs.
  12. 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:

Leave a Reply

Your email address will not be published. Required fields are marked *