- Startup the database to mount.
- Find out the archive gap.
- Find out the earliest gap time.
- Restore the database.
- Recover the database until a specific sequence number of archived log.
- Open the database with resetlogs.
[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
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.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
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.
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
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
SQL> alter database open resetlogs;
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