Oracle的redo log在各场景下的恢复

Oracle的redo log非常重要，redo log损坏将导致数据库开法开启或数据丢失，针对redo log在各种场景下如何打开或恢复数据库，特别模拟测试说明：各场景包括如下（共6个场景）: 场景一.非归档下inactive状态的redo 恢复场景二.非归档下active状态的redo 恢复场景三.非归档下current状态的redo恢复场景四.归档模式下inactive状态的redo 恢复场景五.归档模式下的active状态的redo 恢复场景六.归档模式下的current状态的redo恢复oracle版本为oracle 11.1.0.7各场景恢复操作如下：一.非归档下inactive状态的redo 恢复session 1:
查看归档模式：---非归档
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /kttest1-1_data1/emsdev/arch
Oldest online log sequence 3
Current log sequence 8查看log情况：SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 10 104857600 1 NO INACTIVE 1.2790E+13 30-MAY-14
2 1 13 104857600 1 NO CURRENT 1.2790E+13 30-MAY-14
3 1 12 104857600 1 NO ACTIVE 1.2790E+13 30-MAY-14SQL> col member for a70
SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------
IS_
---
3 ONLINE /kttest1-1_data1/emsdev/redo03.dbf
NO1 ONLINE /kttest1-1_data1/emsdev/redo01.dbf
NO2 ONLINE /kttest1-1_data1/emsdev/redo02.dbf
NOSQL> alter system checkpoint;System altered.SQL> alter system checkpoint;System altered.
session 2:
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
0+0 records in
0+0 records out
session 1:
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 23 104857600 1 NO INACTIVE
1.2792E+13 03-JUN-142 1 22 104857600 1 NO INACTIVE
1.2792E+13 02-JUN-143 1 24 104857600 1 NO CURRENT
1.2792E+13 03-JUN-14
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"SQL> alter database clear logfile group 2;Database altered.SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"
SQL> alter database drop logfile group 2;Database altered.SQL> alter database open;Database altered.SQL> alter database add logfile group 2 （"/kttest1-1_data1/emsdev/redo02.dbf"） size 100m;
alter database add logfile group 2 （"/kttest1-1_data1/emsdev/redo02.dbf"） size 300m
*
ERROR at line 1:
ORA-00301: error in adding log file "/kttest1-1_data1/emsdev/redo02.dbf" - file
cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> alter database add logfile group 2 （"/kttest1-1_data1/emsdev/redo02.dbf"） size 100m reuse;Database altered.
二.非归档下active状态的redo 恢复如果非current redo 损坏，但是包含active事务，那么情况完全不同。----Session 1
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 26 104857600 1 NO CURRENT
1.2792E+13 04-JUN-142 1 25 314572800 1 NO ACTIVE
1.2792E+13 04-JUN-143 1 24 104857600 1 NO INACTIVE
1.2792E+13 03-JUN-14SQL> col member for a70
SQL> select * from v$logfile;GROUP# STATUS TYPE
---------- ------- -------
MEMBER IS_
---------------------------------------------------------------------- ---
3 ONLINE
/kttest1-1_data1/emsdev/redo03.dbf NO1 ONLINE
/kttest1-1_data1/emsdev/redo01.dbf NO2 ONLINE
/kttest1-1_data1/emsdev/redo02.dbf NOsession 2:
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
0+0 records in
0+0 records out---Session 1
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 26 104857600 1 NO CURRENT
1.2792E+13 04-JUN-142 1 25 314572800 1 NO ACTIVE
1.2792E+13 04-JUN-143 1 24 104857600 1 NO INACTIVE
1.2792E+13 03-JUN-14 SQL>SQL> shutdown abort
ORACLE instance shut down.
SQL> select * from v$log;
select * from v$log
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 3544
Session ID: 83 Serial number: 3
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance emsdev1 （thread 1）
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance emsdev1 （thread 1）
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"SQL> recover database until cancel;
ORA-00279: change 12792226234738 generated at 06/04/2014 09:35:35 needed for
thread 1
ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_32_848646308.arch
ORA-00280: change 12792226234738 for thread 1 is in sequence #32
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_32_848646308.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_32_848646308.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: "/kttest1-1_data1/emsdev/system01.dbf"32没有归档，不能这样恢复SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 32 104857600 1 NO ACTIVE
1.2792E+13 04-JUN-143 1 34 104857600 1 NO CURRENT
1.2792E+13 04-JUN-142 1 33 104857600 1 NO ACTIVE
1.2792E+13 04-JUN-14
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: "/kttest1-1_data1/emsdev/system01.dbf"SQL> show parameter pfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>在pfile文件中加入两个参数：
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true ----从oracle 10g开始，引入的另外一个参数。SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
SQL> alter database open resetlogs;Database altered.SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> /System altered. 三.非归档下current状态的redo恢复
session 1:
SQL> create table test as select * from dba_extents;Table created.SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 1 104857600 1 NO CURRENT
1.2792E+13 04-JUN-142 1 0 104857600 1 YES UNUSED
03 1 0 104857600 1 YES UNUSED session 2 :
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo01.dbf bs=512 count=10
0+0 records in
0+0 records outsession 1:SQL> delete from test where rownum <100000;45074 rows deleted.
SQL> commit;Commit complete.SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 14382
Session ID: 83 Serial number: 3SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn"t exist
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: "/kttest1-1_data1/emsdev/redo01.dbf"SQL> recover database until cancel;
ORA-00279: change 12792226263763 generated at 06/04/2014 10:45:34 needed for
thread 1
ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_1_849350732.arch
ORA-00280: change 12792226263763 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_1_849350732.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_1_849350732.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: "/kttest1-1_data1/emsdev/system01.dbf"
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: "/kttest1-1_data1/emsdev/system01.dbf"
在pfile文件中加入两个参数：
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;Database altered.
如果无法直接open resetlogs打开的话，要检查一下alert.log报错：Thu Jul 18 16:38:42 2013
SMON: enabling cache recovery
Thu Jul 18 16:38:43 2013
Errors in file /oracle/admin/orcl/udump/orcl_ora_11149.trc:
ORA-00600: internal error code, arguments: [2662], [0], [4099916], [0], [4100136], [4194313], [], []
Thu Jul 18 16:38:43 2013
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 11149
ORA-1092 signalled during: alter database open resetlogs...如出现ora-00600 [2662]报错，需要推进scn。推进方法如下：ORA-600 [2662] [a] [b] [c] [d] [e]Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.算法计算规则如下：Arg [c]*4得出一个数值，假设为V_Wrap,
如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level数据库处于mount状态下，执行:alter session set events "10015 trace name adjust_scn level 1";再alter database open;可以检查v$datafile与v$datafile_header的substr（checkpoint_change#,1,14）大小相同
四.归档模式下的red log恢复
1）inactive logfile损坏的情况
session 1:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 4 104857600 1 NO CURRENT
1.2792E+13 04-JUN-142 1 2 104857600 1 YES INACTIVE
1.2792E+13 04-JUN-143 1 3 104857600 1 YES INACTIVE
1.2792E+13 04-JUN-14
SQL> col member for a70
SQL> select * from v$logfile;GROUP# STATUS TYPE
---------- ------- -------
MEMBER IS_
---------------------------------------------------------------------- ---
3 ONLINE
/kttest1-1_data1/emsdev/redo03.dbf NO1 ONLINE
/kttest1-1_data1/emsdev/redo01.dbf NO2 ONLINE
/kttest1-1_data1/emsdev/redo02.dbf NO
session 2:
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo03.dbf bs=512 count=10
0+0 records in
0+0 records out在这一步，如果不将数据库进行shutdown，继续进行logfile的切换，那么redo03仍然是可以写的，然而存在潜在风险。session 1:
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00320: cannot read file header from log 3 of thread 1
ORA-00312: online log 3 thread 1: "/kttest1-1_data1/emsdev/redo03.dbf"SQL> alter database clear logfile group 3;Database altered.SQL> alter database drop logfile group 3;Database altered.SQL> alter database add logfile group 3 （"/kttest1-1_data1/emsdev/redo03.dbf"） size 100m reuse;Database altered.SQL> alter database open; ++++ 这里地方由于原来旧有的redo03被进程持有，可能句柄未释放，所以仍然报错，重启实例即可.alter database open
*
ERROR at line 1:
ORA-00320: cannot read file header from log 3 of thread 1
ORA-00312: online log 3 thread 1: "/kttest1-1_data1/emsdev/redo03.dbf"SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
Database opened.
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 4 104857600 1 YES INACTIVE
1.2792E+13 04-JUN-142 1 2 104857600 1 YES INACTIVE
1.2792E+13 04-JUN-143 1 5 104857600 1 NO CURRENT
1.2792E+13 04-JUN-14
SQL> alter system switch logfile;System altered.
2. 归档模式下current logfile损坏场景一：
session 1:
SQL> set line 200
SQL> col member for a30
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
2 1 6 104857600 1 NO CURRENT 1.2792E+13 04-JUN-14
3 1 5 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /kttest1-1_data1/emsdev/redo03 NO
.dbf1 ONLINE /kttest1-1_data1/emsdev/redo01 NO
.dbf2 ONLINE /kttest1-1_data1/emsdev/redo02 NO
.dbf
session 2:
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
0+0 records in
0+0 records outsession 1:SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 15430
Session ID: 74 Serial number: 1SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00316: log 2 of thread 1, type 0 in header is not log file
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
3 1 5 104857600 1 YES INACTIVE 1.2792E+13 04-JUN-14
2 1 6 104857600 1 NO CURRENT 1.2792E+13 04-JUN-14
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.在pfile文件中加入两个参数：
*._allow_resetlogs_corruption=true
*._allow_error_simulation=trueSQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database until cancel;
ORA-00279: change 12792226298118 generated at 06/04/2014 14:21:06 needed for
thread 1
ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_6_849351843.arch
ORA-00280: change 12792226298118 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_6_849351843.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
"/kttest1-1_data1/emsdev/arch/1_6_849351843.arch"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: "/kttest1-1_data1/emsdev/system01.dbf"
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 16850
Session ID: 166 Serial number: 3
检查alert.log发现如下报错：
Errors in file /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/trace/emsdev1_ora_16850.trc （incident=107308）:
ORA-00600: internal error code, arguments: [2662], [2978], [1813690638], [2978], [1813691502], [4194432], [], [], [], [], [], []
Incident details in: /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/incident/incdir_107308/emsdev1_ora_16850_i107308.trc
Wed Jun 04 14:38:11 2014
Errors in file /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/trace/emsdev1_ora_16850.trc:
ORA-00600: internal error code, arguments: [2662], [2978], [1813690638], [2978], [1813691502], [4194432], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER （ospid: 16850）: terminating the instance due to error 600
Instance terminated by USER, pid = 16850
ORA-1092 signalled during: alter database open resetlogs...
ORA-1092 : opiodr aborting process unknown ospid （16850_1）
Wed Jun 04 14:38:15 2014
ORA-1092 : opitsk aborting process
/* 如出现ora-00600 [2662]报错，需要推进scn。推进方法如下：ORA-600 [2662] [a] [b] [c] [d] [e]Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.算法计算规则如下：Arg [c]*4得出一个数值，假设为V_Wrap,
如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level数据库处于mount状态下，执行:alter session set events "10015 trace name adjust_scn level 1";再alter database open;可以检查v$datafile与v$datafile_header的substr（checkpoint_change#,1,14）大小相同 */
按以上方法计算出需要推进的scn大小为11914
session 1:
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
SQL> alter session set events "10015 trace name adjust_scn level 11914";Session altered.SQL> select substr（checkpoint_change#,1,14） as df_scn from v$datafile;DF_SCN
--------------------------------------------------------
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122DF_SCN
--------------------------------------------------------
12792226298122
1279222629812213 rows selected.SQL> select substr（checkpoint_change#,1,14） as dfh_scn from v$datafile;DFH_SCN
--------------------------------------------------------
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122
12792226298122DFH_SCN
--------------------------------------------------------
12792226298122
1279222629812213 rows selected.SQL> alter database open;Database altered.场景二：+++++Session 1
SQL> conn /as sysdba
Connected.
SQL> set lines 200
SQL> col member for a60
SQL> select * from v$Log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7 10485760 1 NO CURRENT 1073767933 22-JUL-13
2 1 5 10485760 1 YES INACTIVE 1073742213 21-JUL-13
3 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-13
SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
2 ONLINE /home/ora10g/oradata/roger/redo02.log NO
3 ONLINE /home/ora10g/oradata/roger/redo03.log NO
1 ONLINE /home/ora10g/oradata/roger/redo01.log NOSQL>
SQL> alter system switch logfile;System altered.++++Session 2[ora10g@killdb ~]$ dd if=/dev/null of=/home/ora10g/oradata/roger/redo02.log bs=512 count=10
0+0 records in
0+0 records out++++Session 1SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 96470248 bytes
Database Buffers 67108864 bytes
Redo Buffers 2920448 bytes
Database mounted.
ORA-00327: log 2 of thread 1, physical size less than needed
ORA-00312: online log 2 thread 1: "/home/ora10g/oradata/roger/redo02.log"
SQL> select * from v$Log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7 10485760 1 YES INACTIVE 1073767933 22-JUL-13
3 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-13
2 1 8 10485760 1 NO CURRENT 1073770766 22-JUL-13 SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00327: log 2 of thread 1, physical size less than needed
ORA-00312: online log 2 thread 1: "/home/ora10g/oradata/roger/redo02.log"在pfile文件中加入两个参数：
*._allow_resetlogs_corruption=true
*._allow_error_simulation=trueSQL> startup mount pfile="/tmp/pfile.ora";
ORACLE instance started.Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 96470248 bytes
Database Buffers 67108864 bytes
Redo Buffers 2920448 bytes
Database mounted.SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [2130], [0], [8], [2], [], [], [], []
SQL> show parameter resetlogNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL> show parameter allowNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_error_simulation boolean TRUE
_allow_resetlogs_corruption boolean TRUE
SQL> alter database open;Database altered.SQL>SQL> alter system switch logfile;System altered. 3. 归档模式下active logfile损坏场景一：session 1:SQL> set line 300
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 104857600 1 YES INACTIVE 1.2793E+13 04-JUN-14
2 1 2 104857600 1 NO CURRENT 1.2793E+13 04-JUN-14
3 1 0 104857600 1 YES UNUSED 0SQL> alter system switch logfile;System altered.SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 104857600 1 YES INACTIVE 1.2793E+13 04-JUN-14
2 1 2 104857600 1 YES ACTIVE 1.2793E+13 04-JUN-14
3 1 3 104857600 1 NO CURRENT 1.2794E+13 04-JUN-14session 2:
root@kttest1-1 # dd if=/dev/null of=/kttest1-1_data1/emsdev/redo02.dbf bs=512 count=10
0+0 records in
0+0 records outsession 1:SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 3207790592 bytes
Fixed Size 2119072 bytes
Variable Size 381586016 bytes
Database Buffers 2818572288 bytes
Redo Buffers 5513216 bytes
Database mounted.
ORA-00320: cannot read file header from log 2 of thread 1
ORA-00312: online log 2 thread 1: "/kttest1-1_data1/emsdev/redo02.dbf"
SQL> alter database clear logfile group 2;Database altered.SQL> alter database drop logfile group 2;Database altered.SQL> alter database open;Database altered.SQL> alter database add logfile group 2 （"/kttest1-1_data1/emsdev/redo02.dbf"） size 100m reuse;Database altered.场景二：
恢复方法与非归档模式下的active redo log恢复相同本文永久更新链接地址