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

--设置隐含参数

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> 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: {<RET>=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: {<RET>=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

其实在设置了隐含参数后，用resetlogs打开数据库后，关闭再重启数据库就已经可以正常open数据库了，很幸运地，之前并未提交的数据也都没有丢失，最后不要忘记把隐含参数设置回来，并且做一次全备

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

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.

Database opened.

SQL> show parameter _allow_resetlogs_corruption

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_allow_resetlogs_corruption          boolean     FALSE

SQL>