online redo日志文件对数据库是非常重要的，当current日志文件损坏，通常就意味着要丢失数据，但是也不是绝对的，可以通过一定的
online redo日志文件损坏恢复
[日期：2015-01-11]来源：linux社区 作者：aaron8219[字体：]
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]$ ll
total 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]$ exit
exit
--关闭数据库
sql> shutdown immediate
ora-01031: insufficient privileges
sql> conn / as sysdba
connected.
sql> shutdown immediate
ora-03113: end-of-file on communication channel
process id: 4667
session id: 40 serial number: 105
由于redo文件已经被破坏，一致性关闭数据库报错，报ora-03113错误
--重启数据库
sql> startup
ora-24324: service handle not initialized
ora-01041: internal error. hostdef extension doesn't exist
sql> exit
disconnected from oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
with 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> startup
oracle instance started.
total system global area 835104768 bytes
fixed size 2232960 bytes
variable size 494931328 bytes
database buffers 335544320 bytes
redo buffers 2396160 bytes
database mounted.
ora-00313: open failed for members of log group 2 of thread 1
ora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/zlm11g/redo02.log'
ora-27048: skgfifi: file header information is invalid
additional information: 14
发现已无法自动启动到open状态，提示非法文件头信息，报ora-27048错误，以及ora-00313、ora-00312
由于启动数据库的时候会做一致性检查，redo的损坏使一致性检查无法通过，可以通过设置隐含参数_allow_resetlogs_corruption解决
--设置隐含参数
system altered.
--关闭并重启数据库
sql> shutdown immediate
ora-01109: database not open
database dismounted.
oracle instance shut down.
sql> startup
oracle instance started.
total system global area 835104768 bytes
fixed size 2232960 bytes
variable size 494931328 bytes
database buffers 335544320 bytes
redo buffers 2396160 bytes
database mounted.
ora-00313: open failed for members of log group 2 of thread 1
ora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/zlm11g/redo02.log'
ora-27048: skgfifi: file header information is invalid
additional 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 1
ora-00289: suggestion :
/u01/app/oracle/fast_recovery_area/zlm11g/archivelog/2014_12_31/o1_mf_1_56_%u_.a
rc
ora-00280: change 1723866 for thread 1 is in sequence #56
specify log: {=suggested | filename | auto | cancel}
auto
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 status
linux-x86_64 error: 2: no such file or directory
additional 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 status
linux-x86_64 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: '/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]$ ll
total 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 1
ora-00289: suggestion :
/u01/app/oracle/fast_recovery_area/zlm11g/archivelog/2014_12_31/o1_mf_1_56_%u_.a
rc
ora-00280: change 1723866 for thread 1 is in sequence #56
specify log: {=suggested | filename | auto | cancel}
cancel
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: '/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 forced
ora-00600: internal error code, arguments: [2662], [0], [1723874], [0],
[1724203], [4194432], [], [], [], [], [], []
process id: 5139
session id: 1 serial number: 5
用resetlogs打开数据库，报了一个600错误，先不用理会
sql> select open_mode from v$database;
error:
ora-03114: not connected to oracle
此时提示未连接上数据库
sql> conn / as sysdba
connected 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 available
process id: 0
session id: 0 serial number: 0
提示数据库实例未启动
sql> startup mount
oracle instance started.
total system global area 835104768 bytes
fixed size 2232960 bytes
variable size 494931328 bytes
database buffers 335544320 bytes
redo buffers 2396160 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-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