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

 

--设置隐含参数

 

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