探索ORACLE之RMAN_07 重做日志redu文件丢失恢复

探索ORACLE之RMAN_07 重做日志redu文件丢失恢复

作者：吴伟龙Name：Prodence Woo

QQ：286507175  msn:hapy-wuweilong@hotmail.com       

 

 

       重做日志文件记录了数据库的变更数据。一般重做日志文件的失败不会使数据库数据丢失，但是会影响数据库的恢复。重做日志分为两种状态当前联机重做日志和非当前的联机重做日志

4.1 非当前redo(联机重做日志)文件丢失恢复

 

数据库运行的时候，日志中报如下错误：

ORA-00313: openfailed for members of log group 1 of thread 1

ORA-00312: onlinelog 1 thread 1: /DBData/WWL/redo01.log

 

查看日志组，判断损坏的日志组是否为当前日志组

SQL select * from v$log;

 

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1         32  31457280          1 YES INACTIVE               1063037 04-JUL-12

         3          1         34  31457280          1 NO  CURRENT                1118555 05-JUL-12

         2          1        33   31457280          1 YES INACTIVE               1086278 05-JUL-12

 

     我们可以看到损坏的那组日志不是当前的日志，这个时候我么可以通过使用clear命令来重建该日志文件组。

 

通过重建来恢复非当前日志组，实现数据库的打开。

SQL startup

ORACLE instancestarted.

 

Total SystemGlobal Area  100663296 bytes

Fixed Size                  1217884 bytes

Variable Size              88083108 bytes

DatabaseBuffers            8388608 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-00313: openfailed for members of log group 1 of thread 1

ORA-00312: onlinelog 1 thread 1: /DBData/WWL/redo01.log

 

SQL alter database clear logfile group 1;

Databasealtered.

 

重建完之后数据库可以打开了，至此恢复完成

SQL alter database open;

Database altered.

 

 

4.2当前redo(联机重做日志)文件丢失恢复

数据库启动的时候报如下错误

SQL startup

ORACLE instance started.

 

Total System Global Area 100663296 bytes

Fixed Size                  1217884 bytes

Variable Size             88083108 bytes

Database Buffers            8388608 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 ofthread 1

ORA-00312: online log 1 thread 1:/DBData/WWL/redo01.log

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

查看日志组，判断损坏的日志组是否为当前日志组

SQL select * from v$log;

 

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1         35  31457280          1 NO  CURRENT                1139915 05-JUL-12

         3          1         34  31457280          1 YES INACTIVE               1118555 05-JUL-12

         2          1         33  31457280          1 YES INACTIVE               1086278 05-JUL-12

    在这里可以看到损坏的为当前日志组，那么意味着会有在线数据丢失，因为重做日志里面当前状态里面存放的是是没有归档及写入到数据文件的活动数据，那么这种恢复必然是会导致数据的不同步，从而使数据丢失。

 

我们可以首先尝试清空日志组信息并重建的方式来进行恢复：

SQL alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instanceWWL (thread 1)

ORA-00312: online log 1 thread 1:/DBData/WWL/redo01.log

 

如上方法不行，可以采取基于SCN，取消的方法来恢复数据库。

 

使用基于控制文件的redo恢复：

SQL RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 1139916 generated at 07/05/201221:49:48 needed for thread 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf

ORA-00280: change 1139916 for thread 1 is in sequence #35

 

Specify log: { RET =suggested | filename | AUTO |CANCEL}

auto

ORA-00308: cannot open archived log /DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

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 mount;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size                  1217884 bytes

Variable Size             88083108 bytes

Database Buffers            8388608 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SQL alter system reset"_allow_resetlogs_corruption" scope = spfile sid = *;

System altered.

 

SQL alter database open resetlogs;

Database altered.

 

SQL select instance_name,status from v$instance;

 

INSTANCE_NAME   STATUS

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

WWL             OPEN

 

SQL select * from v$log;

 

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1          1  31457280          1 NO  CURRENT                1200799 06-JUL-12

         2          1          0  31457280          1 YES UNUSED                       0

         3          1          0  31457280          1 YES UNUSED                       0

 

SQL

System altered.

 

SQL

System altered.

 

SQL

System altered.

 

SQL

System altered.

 

SQL select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1         17  31457280          1 YES INACTIVE               1241271 06-JUL-12

         2          1         18  31457280          1 YES INACTIVE               1241273 06-JUL-12

         3          1         19  31457280          1 NO  CURRENT                1241275 06-JUL-

 

prudentwoo 10g/11g OCP 11g OCM，ITPUB和CSDN专家及专家讲师；有着多年数据库从业经验，资深Oracle数据库专家，现就职于北京海量数据技术股份有限公司担任高级dba职务，为央视，银行，电信等各行业及企业提供过技术支持服务