online redo日志文件损坏恢复

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解决

--设置隐含参数