online redo日志文件损坏恢复

online redo日志文件对数据库是非常重要的，当current日志文件损坏，通常就意味着要丢失数据，但是也不是绝对的，可以通过一定的手段对redo日志文件进行恢复，运气好的话，未提交数据还是不会丢失的。

[Oracle@zlm2 backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:53:23 2014

Copyright （c） 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn zlm/zlm
Connected.

--创建测试表
SQL> create table test（tbid number（10））;

Table created.

--插入数据
SQL> insert into test values（1）;1 row created. 注意，并未对此insert操作commit SQL> select group#,sequence#,status,first_change# from v$log;   GROUP# SEQUENCE# STATUS FIRST_CHANGE#---------- ---------- ---------------- ------------- 1 55 ACTIVE 1723785 2 56 CURRENT 1723866 3 54 INACTIVE 1723562 SQL> ！[oracle@zlm2 backup]$ cd /u01/app/oracle/oradata/zlm11g/[oracle@zlm2 zlm11g]$ lltotal 2572744-rwxrwxr-x 1 oracle oinstall 9748480 Dec 31 22:56 control01.ctl-rw-r----- 1 oracle oinstall 362422272 Dec 31 22:49 example01.dbf-rw-r----- 1 oracle oinstall 52429312 Dec 31 22:51 redo01.log-rw-r----- 1 oracle oinstall 52429312 Dec 31 22:56 redo02.log-rw-r----- 1 oracle oinstall 52429312 Dec 31 22:49 redo03.log-rw-r----- 1 oracle oinstall 608182272 Dec 31 22:55 sysaux01.dbf-rw-r----- 1 oracle oinstall 775954432 Dec 31 22:55 system01.dbf-rw-r----- 1 oracle oinstall 20979712 Dec 31 22:05 temp01.dbf-rw-r----- 1 oracle oinstall 178266112 Dec 31 22:55 undotbs01.dbf-rw-r----- 1 oracle oinstall 13115392 Dec 31 22:49 users01.dbf-rw-r----- 1 oracle oinstall 524296192 Dec 31 22:49 zlm01.dbf --模拟在线破坏3个redo日志[oracle@zlm2 zlm11g]$ echo > redo01.log[oracle@zlm2 zlm11g]$ echo > redo02.log[oracle@zlm2 zlm11g]$ echo > redo03.log[oracle@zlm2 zlm11g]$ exitexit --关闭数据库
SQL> shutdown immediateORA-01031: insufficient privilegesSQL> conn / as sysdbaConnected.SQL> shutdown immediateORA-03113: end-of-file on communication channelProcess ID: 4667Session ID: 40 Serial number: 105 由于redo文件已经被破坏，一致性关闭数据库报错，报ora-03113错误 --重启数据库SQL> startupORA-24324: service handle not initializedORA-01041: internal error. hostdef extension doesn"t existSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@zlm2 backup]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:57:34 2014 Copyright （c） 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startupORACLE instance started. Total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 494931328 bytesDatabase Buffers 335544320 bytesRedo Buffers 2396160 bytesDatabase mounted.ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: "/u01/app/oracle/oradata/zlm11g/redo02.log"ORA-27048: skgfifi: file header information is invalidAdditional information: 14 发现已无法自动启动到open状态，提示非法文件头信息，报ora-27048错误，以及ora-00313、ora-00312由于启动数据库的时候会做一致性检查，redo的损坏使一致性检查无法通过，可以通过设置隐含参数_allow_resetlogs_corruption解决
--设置隐含参数SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. --关闭并重启数据库SQL> shutdown immediate
ORA-01109: database not open  Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started. Total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 494931328 bytesDatabase Buffers 335544320 bytesRedo Buffers 2396160 bytesDatabase mounted.ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: "/u01/app/oracle/oradata/zlm11g/redo02.log"ORA-27048: skgfifi: file header information is invalidAdditional information: 14  SQL> show parameter _allow_resetlogs_corruption NAME TYPE VALUE------------------------------------ ----------- ------------------------------_allow_resetlogs_corruption boolean TRUE 可以看到，刚才设置的隐含参数已经生效 --做一次基于cancel的不完全恢复SQL> recover database until cancel;ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.arcORA-00280: change 1723866 for thread 1 is in sequence #56  Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log"/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.arc"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3  ORA-00308: cannot open archived log"/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.arc"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3  ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: "/u01/app/oracle/oradata/zlm11g/system01.dbf"  SQL> ！[oracle@zlm2 backup]$ cd /u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/[oracle@zlm2 2014_12_31]$ lltotal 56076-rw-r----- 1 oracle oinstall 33343488 Dec 31 20:59 o1_mf_1_50_bb7wtcln_.arc-rw-r----- 1 oracle oinstall 30720 Dec 31 21:02 o1_mf_1_51_bb7wzzlk_.arc-rw-r----- 1 oracle oinstall 23774208 Dec 31 22:38 o1_mf_1_52_bb82m1dp_.arc-rw-r----- 1 oracle oinstall 172032 Dec 31 22:41 o1_mf_1_53_bb82rsqv_.arc-rw-r----- 1 oracle oinstall 6656 Dec 31 22:49 o1_mf_1_54_bb837jy3_.arc-rw-r----- 1 oracle oinstall 14848 Dec 31 22:51 o1_mf_1_55_bb83cxqy_.arc 最新的归档只有到55，第56号归档还没有生成，用auto最多只能恢复到55号归档 --再次执行recover database until cancel，这次输入cancel
SQL> recover database until cancel;
ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.arcORA-00280: change 1723866 for thread 1 is in sequence #56  Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: "/u01/app/oracle/oradata/zlm11g/system01.dbf"  ORA-01112: media recovery not started  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;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [1723874], [0],[1724203], [4194432], [], [], [], [], [], []Process ID: 5139Session ID: 1 Serial number: 5 用resetlogs打开数据库，报了一个600错误，先不用理会 SQL> select open_mode from v$database;ERROR:ORA-03114: not connected to ORACLE 此时提示未连接上数据库 SQL> conn / as sysdbaConnected to an idle instance.SQL> select open_mode from v$database;select open_mode from v$database*ERROR at line 1:ORA-01034: ORACLE not availableProcess ID: 0Session ID: 0 Serial number: 0 提示数据库实例未启动 SQL> startup mountORACLE instance started. Total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 494931328 bytesDatabase Buffers 335544320 bytesRedo Buffers 2396160 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 recovery  SQL> recover database until cancel;ORA-00283: recovery session canceled due to errorsORA-16433: The database must be opened in read/write mode.  SQL> alter database open; Database altered.  SQL> select count（*） from zlm.test;   COUNT（*）---------- 1 SQL> select * from zlm.test;   TBID---------- 1 其实在设置了隐含参数后，用resetlogs打开数据库后，关闭再重启数据库就已经可以正常open数据库了，很幸运地，之前并未提交的数据也都没有丢失，最后不要忘记把隐含参数设置回来，并且做一次全备 SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile; System altered. SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started. Total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 494931328 bytesDatabase Buffers 335544320 bytesRedo Buffers 2396160 bytesDatabase mounted.Database opened.SQL> show parameter _allow_resetlogs_corruption NAME TYPE VALUE------------------------------------ ----------- ------------------------------_allow_resetlogs_corruption boolean FALSESQL>  在CentOS 5.5 i386 上安装 Oracle 10G XE http://www.linuxidc.com/Linux/2011-02/31928p2.htmLinux下Oracle 11G XE 安装笔记 http://www.linuxidc.com/Linux/2012-08/66974.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址