探索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 3231457280 1 YES INACTIVE 1063037 04-JUL-12

3 1 3431457280 1 NO CURRENT 1118555 05-JUL-12

2 133 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 Area100663296 bytes

Fixed Size 1217884 bytes

Variable Size88083108 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 3531457280 1 NO CURRENT 1139915 05-JUL-12

3 1 3431457280 1 YES INACTIVE 1118555 05-JUL-12

2 1 3331457280 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 Area100663296 bytes

Fixed Size 1217884 bytes

Variable Size88083108 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_NAMESTATUS

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

WWLOPEN

SQL> select * from v$log;

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

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

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

2 1 031457280 1 YES UNUSED 0

3 1 031457280 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 1731457280 1 YES INACTIVE 1241271 06-JUL-12

2 1 1831457280 1 YES INACTIVE 1241273 06-JUL-12

3 1 1931457280 1 NO CURRENT 1241275 06-JUL-