=====================================================================
Using RMAN ( base on sequence# )
=====================================================================
SYS@orcl>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
1 1 1 52428800 512 2 YES INACTIVE 1088527 2015-10-13:10:20:36 1090854
2015-10-13:10:30:01
2 1 2 52428800 512 2 YES INACTIVE 1090854 2015-10-13:10:30:01 1091005
2015-10-13:10:30:17
3 1 3 52428800 512 2 NO CURRENT 1091005 2015-10-13:10:30:17 2.8147E+14
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ARC
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
[oracle@lnx04 orcl]$ ls
control01.ctl redo01_b.log redo02.log rman_tbs.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03_b.log sysaux01.dbf temp02.dbf
example01.dbf redo02_b.log redo03.log system01.dbf undotbs01.dbf
[oracle@lnx04 orcl]$ rm redo03*
[oracle@lnx04 orcl]$ ls
control01.ctl redo01_b.log redo02.log system01.dbf undotbs01.dbf
control02.ctl redo01.log rman_tbs.dbf temp01.dbf users01.dbf
example01.dbf redo02_b.log sysaux01.dbf temp02.dbf
SYS@orcl>shutdown abort
ORACLE instance shut down.
SYS@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx04 ~]$ rman target system/oracle
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 13 10:40:56 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
RMAN> run {
2> set until sequence=3 thread=1;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
executing command: SET until clause
Starting restore at 2015-10-13:10:41:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
~
channel ORA_DISK_1: restore complete, elapsed time: 00:00:13
Finished restore at 2015-10-13:10:42:06
Starting recover at 2015-10-13:10:42:06
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /ARC/ORCL_892981236_1_2.arc
archived log file name=/ARC/ORCL_892981236_1_2.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-10-13:10:42:06
database opened
[oracle@lnx04 orcl]$ ls
control01.ctl redo01_b.log redo02.log rman_tbs.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03_b.log sysaux01.dbf temp02.dbf
example01.dbf redo02_b.log redo03.log system01.dbf undotbs01.dbf
=====================================================================
Using _allow_resetlogs_corruption = true
=====================================================================
SYS@orcl>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
1 1 4 52428800 512 2 NO CURRENT 1091630 2015-10-13:10:43:02 2.8147E+14
2 1 2 52428800 512 2 YES INACTIVE 1091622 2015-10-13:10:42:57 1091625
2015-10-13:10:42:58
3 1 3 52428800 512 2 YES INACTIVE 1091625 2015-10-13:10:42:58 1091630
2015-10-13:10:43:02
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@lnx04 orcl]$ rm -rf redo01*
[oracle@lnx04 orcl]$ ls
control01.ctl redo02_b.log redo03.log system01.dbf undotbs01.dbf
control02.ctl redo02.log rman_tbs.dbf temp01.dbf users01.dbf
example01.dbf redo03_b.log sysaux01.dbf temp02.dbf
SYS@orcl>startup
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 16783
Session ID: 125 Serial number: 5
alert log
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_16671.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16783.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01_b.log'
USER (ospid: 16783): terminating the instance due to error 313
SYS@orcl>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
1 1 4 52428800 512 2 NO CURRENT 1091630 2015-10-13:10:43:02 2.8147E+14
3 1 3 52428800 512 2 YES INACTIVE 1091625 2015-10-13:10:42:58 1091630
2015-10-13:10:43:02
2 1 2 52428800 512 2 YES INACTIVE 1091622 2015-10-13:10:42:57 1091625
2015-10-13:10:42:58
SYS@orcl>select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01_b.log
NO
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03_b.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02_b.log
NO
6 rows selected.
SYS@orcl>shutdown abort
ORACLE instance shut down.
SYS@orcl>create pfile from spfile;
[oracle@lnx04 dbs]$ vi initorcl.ora
*._allow_resetlogs_corruption=true
SYS@orcl>startup mount;
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
SYS@orcl>recover database until cancel;
Media recovery complete.
SYS@orcl>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@orcl>alter database open resetlogs;
Database altered.
[oracle@lnx04 orcl]$ ls
control01.ctl redo01_b.log redo02.log rman_tbs.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03_b.log sysaux01.dbf temp02.dbf
example01.dbf redo02_b.log redo03.log system01.dbf undotbs01.dbf
SYS@orcl>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
1 1 1 52428800 512 2 NO CURRENT 1094373 2015-10-13:11:07:25 2.8147E+14
2 1 0 52428800 512 2 YES UNUSED 0 0
3 1 0 52428800 512 2 YES UNUSED 0 0
SYS@orcl>select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01_b.log
NO
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03_b.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02_b.log
NO
6 rows selected.