网站app建设,免费虚拟主机购买,我国档案网站建设,衡水专业做wap网站Oracle的redo log非常重要#xff0c;redo log损坏将导致数据库开法开启或数据丢失#xff0c;针对redo log在各种场景下如何打开或恢复数据库#xff0c;特别模拟测试说明#xff1a;各场景包括如下(共6个场景):场景一.非归档下inactive状态的redo 恢复场景二.非归档下act…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 listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination /kttest1-1_data1/emsdev/archOldest online log sequence 3Current 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.2790E13 30-MAY-142 1 13 104857600 1 NO CURRENT 1.2790E13 30-MAY-143 1 12 104857600 1 NO ACTIVE 1.2790E13 30-MAY-14SQL col member for a70SQL select * from v$logfile;GROUP# STATUS TYPE MEMBER---------- ------- ------- ----------------------------------------------------------------------IS_---3 ONLINE /kttest1-1_data1/emsdev/redo03.dbfNO1 ONLINE /kttest1-1_data1/emsdev/redo01.dbfNO2 ONLINE /kttest1-1_data1/emsdev/redo02.dbfNOSQL alter system checkpoint;System altered.SQL alter system checkpoint;System altered.session 2:rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo02.dbf bs512 count1000 records in00 records outsession 1:SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- ---------1 1 23 104857600 1 NO INACTIVE1.2792E13 03-JUN-142 1 22 104857600 1 NO INACTIVE1.2792E13 02-JUN-143 1 24 104857600 1 NO CURRENT1.2792E13 03-JUN-14SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00320: cannot read file header from log 2 of thread 1ORA-00312: online log 2 thread 1: /kttest1-1_data1/emsdev/redo02.dbfSQL 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 1ORA-00312: online log 2 thread 1: /kttest1-1_data1/emsdev/redo02.dbfSQL 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 - filecannot be createdORA-27038: created file already existsAdditional information: 1SQL alter database add logfile group 2 (/kttest1-1_data1/emsdev/redo02.dbf) size 100m reuse;Database altered.二.非归档下active状态的redo 恢复如果非current redo 损坏但是包含active事务那么情况完全不同。----Session 1SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- ---------1 1 26 104857600 1 NO CURRENT1.2792E13 04-JUN-142 1 25 314572800 1 NO ACTIVE1.2792E13 04-JUN-143 1 24 104857600 1 NO INACTIVE1.2792E13 03-JUN-14SQL col member for a70SQL 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:rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo02.dbf bs512 count1000 records in00 records out---Session 1SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- ---------1 1 26 104857600 1 NO CURRENT1.2792E13 04-JUN-142 1 25 314572800 1 NO ACTIVE1.2792E13 04-JUN-143 1 24 104857600 1 NO INACTIVE1.2792E13 03-JUN-14SQLSQL shutdown abortORACLE instance shut down.SQL select * from v$log;select * from v$log*ERROR at line 1:ORA-01034: ORACLE not availableProcess ID: 3544Session ID: 83 Serial number: 3SQL conn /as sysdbaConnected to an idle instance.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00320: cannot read file header from log 2 of thread 1ORA-00312: online log 2 thread 1: /kttest1-1_data1/emsdev/redo02.dbfORA-27069: attempt to do I/O beyond the range of the fileAdditional information: 1Additional information: 1SQL 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.dbfSQL 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.dbfSQL recover database until cancel;ORA-00279: change 12792226234738 generated at 06/04/2014 09:35:35 needed forthread 1ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_32_848646308.archORA-00280: change 12792226234738 for thread 1 is in sequence #32Specify log: {suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_32_848646308.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_32_848646308.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /kttest1-1_data1/emsdev/system01.dbf32没有归档不能这样恢复SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- ---------1 1 32 104857600 1 NO ACTIVE1.2792E13 04-JUN-143 1 34 104857600 1 NO CURRENT1.2792E13 04-JUN-142 1 33 104857600 1 NO ACTIVE1.2792E13 04-JUN-14SQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /kttest1-1_data1/emsdev/system01.dbfSQL show parameter pfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile stringSQL shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL在pfile文件中加入两个参数*._allow_resetlogs_corruptiontrue*._allow_error_simulationtrue ----从oracle 10g开始引入的另外一个参数。SQL conn /as sysdbaConnected to an idle instance.SQL startup mountORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase 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 CURRENT1.2792E13 04-JUN-142 1 0 104857600 1 YES UNUSED03 1 0 104857600 1 YES UNUSEDsession 2 :rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo01.dbf bs512 count1000 records in00 records outsession 1:SQL delete from test where rownum 100000;45074 rows deleted.SQL commit;Commit complete.SQL shutdown immediateORA-03113: end-of-file on communication channelProcess ID: 14382Session ID: 83 Serial number: 3SQL startupORA-24324: service handle not initializedORA-01041: internal error. hostdef extension doesnt existSQL conn /as sysdbaConnected to an idle instance.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00316: log 1 of thread 1, type 0 in header is not log fileORA-00312: online log 1 thread 1: /kttest1-1_data1/emsdev/redo01.dbfSQL recover database until cancel;ORA-00279: change 12792226263763 generated at 06/04/2014 10:45:34 needed forthread 1ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_1_849350732.archORA-00280: change 12792226263763 for thread 1 is in sequence #1Specify log: {suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_1_849350732.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_1_849350732.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /kttest1-1_data1/emsdev/system01.dbfSQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /kttest1-1_data1/emsdev/system01.dbf在pfile文件中加入两个参数*._allow_resetlogs_corruptiontrue*._allow_error_simulationtrueSQL alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL alter database open resetlogs;Database altered.如果无法直接open resetlogs打开的话要检查一下alert.log报错Thu Jul 18 16:38:42 2013SMON: enabling cache recoveryThu Jul 18 16:38:43 2013Errors 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 2013Error 600 happened during db open, shutting down databaseUSER: terminating instance due to error 600Instance terminated by USER, pid 11149ORA-1092 signalled during: alter database open resetlogs...如出现ora-00600 [2662]报错需要推进scn。推进方法如下ORA-600 [2662] [a] [b] [c] [d] [e]Arg [a] Current SCN WRAPArg [b] Current SCN BASEArg [c] dependent SCN WRAPArg [d] dependent SCN BASEArg [e] Where present this is the DBA where the dependent SCN came from.算法计算规则如下Arg [c]*4得出一个数值假设为V_Wrap,如果Arg [d]0则V_Wrap值为需要的levelArg [d] 1073741824V_Wrap1为需要的levelArg [d] 2147483648V_Wrap2为需要的levelArg [d] 3221225472V_Wrap3为需要的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 CURRENT1.2792E13 04-JUN-142 1 2 104857600 1 YES INACTIVE1.2792E13 04-JUN-143 1 3 104857600 1 YES INACTIVE1.2792E13 04-JUN-14SQL col member for a70SQL 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:rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo03.dbf bs512 count1000 records in00 records out在这一步如果不将数据库进行shutdown继续进行logfile的切换那么redo03仍然是可以写的然而存在潜在风险。session 1:SQL shutdown abortORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00320: cannot read file header from log 3 of thread 1ORA-00312: online log 3 thread 1: /kttest1-1_data1/emsdev/redo03.dbfSQL 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 1ORA-00312: online log 3 thread 1: /kttest1-1_data1/emsdev/redo03.dbfSQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase 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 INACTIVE1.2792E13 04-JUN-142 1 2 104857600 1 YES INACTIVE1.2792E13 04-JUN-143 1 5 104857600 1 NO CURRENT1.2792E13 04-JUN-14SQL alter system switch logfile;System altered.2. 归档模式下current logfile损坏场景一session 1:SQL set line 200SQL col member for a30SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------1 1 4 104857600 1 YES INACTIVE 1.2792E13 04-JUN-142 1 6 104857600 1 NO CURRENT 1.2792E13 04-JUN-143 1 5 104857600 1 YES INACTIVE 1.2792E13 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.dbfsession 2:rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo02.dbf bs512 count1000 records in00 records outsession 1:SQL shutdown immediateORA-03113: end-of-file on communication channelProcess ID: 15430Session ID: 74 Serial number: 1SQL conn /as sysdbaConnected to an idle instance.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00316: log 2 of thread 1, type 0 in header is not log fileORA-00312: online log 2 thread 1: /kttest1-1_data1/emsdev/redo02.dbfSQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------1 1 4 104857600 1 YES INACTIVE 1.2792E13 04-JUN-143 1 5 104857600 1 YES INACTIVE 1.2792E13 04-JUN-142 1 6 104857600 1 NO CURRENT 1.2792E13 04-JUN-14SQL conn /as sysdbaConnected.SQL shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.在pfile文件中加入两个参数*._allow_resetlogs_corruptiontrue*._allow_error_simulationtrueSQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL recover database until cancel;ORA-00279: change 12792226298118 generated at 06/04/2014 14:21:06 needed forthread 1ORA-00289: suggestion : /kttest1-1_data1/emsdev/arch/1_6_849351843.archORA-00280: change 12792226298118 for thread 1 is in sequence #6Specify log: {suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_6_849351843.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log/kttest1-1_data1/emsdev/arch/1_6_849351843.archORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /kttest1-1_data1/emsdev/system01.dbfSQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedProcess ID: 16850Session ID: 166 Serial number: 3检查alert.log发现如下报错Errors in file /oracle/emsdev/diag/rdbms/emsdev1/emsdev1/trace/emsdev1_ora_16850.trc (incident107308):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.trcWed Jun 04 14:38:11 2014Errors 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 databaseUSER (ospid: 16850): terminating the instance due to error 600Instance terminated by USER, pid 16850ORA-1092 signalled during: alter database open resetlogs...ORA-1092 : opiodr aborting process unknown ospid (16850_1)Wed Jun 04 14:38:15 2014ORA-1092 : opitsk aborting process/* 如出现ora-00600 [2662]报错需要推进scn。推进方法如下ORA-600 [2662] [a] [b] [c] [d] [e]Arg [a] Current SCN WRAPArg [b] Current SCN BASEArg [c] dependent SCN WRAPArg [d] dependent SCN BASEArg [e] Where present this is the DBA where the dependent SCN came from.算法计算规则如下Arg [c]*4得出一个数值假设为V_Wrap,如果Arg [d]0则V_Wrap值为需要的levelArg [d] 1073741824V_Wrap1为需要的levelArg [d] 2147483648V_Wrap2为需要的levelArg [d] 3221225472V_Wrap3为需要的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大小为11914session 1:SQL conn /as sysdbaConnected to an idle instance.SQL startup mountORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase 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--------------------------------------------------------1279222629812212792226298122127922262981221279222629812212792226298122127922262981221279222629812212792226298122127922262981221279222629812212792226298122DF_SCN--------------------------------------------------------127922262981221279222629812213 rows selected.SQL select substr(checkpoint_change#,1,14) as dfh_scn from v$datafile;DFH_SCN--------------------------------------------------------1279222629812212792226298122127922262981221279222629812212792226298122127922262981221279222629812212792226298122127922262981221279222629812212792226298122DFH_SCN--------------------------------------------------------127922262981221279222629812213 rows selected.SQL alter database open;Database altered.场景二Session 1SQL conn /as sysdbaConnected.SQL set lines 200SQL col member for a60SQL 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-132 1 5 10485760 1 YES INACTIVE 1073742213 21-JUL-133 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-13SQL select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- ------------------------------------------------------------ ---2 ONLINE /home/ora10g/oradata/roger/redo02.log NO3 ONLINE /home/ora10g/oradata/roger/redo03.log NO1 ONLINE /home/ora10g/oradata/roger/redo01.log NOSQLSQL alter system switch logfile;System altered.Session 2[ora10gkilldb ~]$ dd if/dev/null of/home/ora10g/oradata/roger/redo02.log bs512 count1000 records in00 records outSession 1SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1272600 bytesVariable Size 96470248 bytesDatabase Buffers 67108864 bytesRedo Buffers 2920448 bytesDatabase mounted.ORA-00327: log 2 of thread 1, physical size less than neededORA-00312: online log 2 thread 1: /home/ora10g/oradata/roger/redo02.logSQL 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-133 1 6 52428800 1 YES INACTIVE 1073742215 21-JUL-132 1 8 10485760 1 NO CURRENT 1073770766 22-JUL-13SQL 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 neededORA-00312: online log 2 thread 1: /home/ora10g/oradata/roger/redo02.log在pfile文件中加入两个参数*._allow_resetlogs_corruptiontrue*._allow_error_simulationtrueSQL startup mount pfile/tmp/pfile.ora;ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1272600 bytesVariable Size 96470248 bytesDatabase Buffers 67108864 bytesRedo Buffers 2920448 bytesDatabase mounted.SQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01139: RESETLOGS option only valid after an incomplete database recoverySQL recover database until cancel;ORA-00283: recovery session canceled due to errorsORA-00600: internal error code, arguments: [2130], [0], [8], [2], [], [], [], []SQL show parameter resetlogNAME TYPE VALUE------------------------------------ ----------- ------------------------------_allow_resetlogs_corruption boolean TRUESQL show parameter allowNAME TYPE VALUE------------------------------------ ----------- ------------------------------_allow_error_simulation boolean TRUE_allow_resetlogs_corruption boolean TRUESQL alter database open;Database altered.SQLSQL alter system switch logfile;System altered.3. 归档模式下active logfile损坏场景一session 1:SQL set line 300SQL select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------1 1 1 104857600 1 YES INACTIVE 1.2793E13 04-JUN-142 1 2 104857600 1 NO CURRENT 1.2793E13 04-JUN-143 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.2793E13 04-JUN-142 1 2 104857600 1 YES ACTIVE 1.2793E13 04-JUN-143 1 3 104857600 1 NO CURRENT 1.2794E13 04-JUN-14session 2:rootkttest1-1 # dd if/dev/null of/kttest1-1_data1/emsdev/redo02.dbf bs512 count1000 records in00 records outsession 1:SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 3207790592 bytesFixed Size 2119072 bytesVariable Size 381586016 bytesDatabase Buffers 2818572288 bytesRedo Buffers 5513216 bytesDatabase mounted.ORA-00320: cannot read file header from log 2 of thread 1ORA-00312: online log 2 thread 1: /kttest1-1_data1/emsdev/redo02.dbfSQL 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恢复相同
