重做日志文件丢失的恢复(二)

一、    丢失当前日志文件的恢复

丢失的日志文件的状态如果是ACTIVE或CURRENT，说明没有归档。如果没有归档的日志组中含有多个日志文件成员，那么丢失或者损坏部分日志文件时，只需要复制正常的日志文件，来替换丢失或损坏的日志文件即可解决，这样数据不会丢失，也不用做恢复操作。

    如果没有归档的重做日志组中所有日志件都丢失或者损坏，将会导致数据库数据丢失，如果没有归档的日志文件组为当前组，则数据库立即DOWN机。当这个情况发生时，就意味着数据的丢失，我们只能将数据库恢复到前一次的归档日志切换时刻

下面模拟在正常和非正常关闭数据库后丢失当前日志文件的场景，来恢复日志文件。

 

正常关闭数据库，丢失当前日志文件的恢复

因为是正常关闭，因此数据库在关闭前，做了全面检查点，日志文件对实例恢复没有意义了。

 

测试环境

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 – Production

 

SQL> select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

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

         1          2 INACTIVE

         2          1 CURRENT

         3          2 INACTIVE

 

模拟丢失文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rm /u01/oradata1/redo02.log

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  146472960 bytes

Fixed Size                  1298472 bytes

Variable Size              92278744 bytes

Database Buffers           50331648 bytes

Redo Buffers                2564096 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1: '/u01/oradata1/redo02.log'

 

SQL> !ls -l /u01/oradata1

total 102528

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:49 redo010.log

 

 进行恢复

SQL> alter database clear unarchived logfile group 2;

 

Database altered.

 

检查发现，日志文件已恢复。

SQL> !ls -l /u01/oradata1

total 102528

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:49 redo010.log

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:54 redo02.log

 

SQL> alter database open;

 

Database altered.

 

异常关闭数据库，丢失当前日志文件的恢复

 

异常关闭数据库，说明在数据库启动时进行的实例恢复一定要求有当前的日志文件，否则oracle将无法保证提交的成功的数据部丢失。

 

测试环境

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 – Production

 

SQL> select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

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

         1          2 INACTIVE

         2          1 CURRENT

         3          2 INACTIVE

 

模拟丢失文件

SQL> shutdown abort

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rm /u01/oradata1/redo02.log

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  146472960 bytes

Fixed Size                  1298472 bytes

Variable Size              92278744 bytes

Database Buffers           50331648 bytes

Redo Buffers                2564096 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1: '/u01/oradata1/redo02.log'

 

SQL> !ls -l /u01/oradata1

total 102528

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:49 redo010.log

 

进行恢复

该参数默认值为FALSE，为TRUE说明，在破坏唯一性的情况下强制重置日志，打开数据库。在打开的过程中，ORACLE会跳过一致性检查，使数据库处于不一致的状态下打开。

 

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

 

System altered.

 

修改后，重启数据库。

SQL> shutdown  immeditae

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  146472960 bytes

Fixed Size                  1298472 bytes

Variable Size              92278744 bytes

Database Buffers           50331648 bytes

Redo Buffers                2564096 bytes

Database mounted.

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 3046568 generated at 09/26/2010 00:59:56 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/ORCL/archivelog/2010_09_26/o1_mf_1_2_%u_.arc

ORA-00280: change 3046568 for thread 1 is in sequence #2

 

 

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/oradata/orcl/system01.dbf'

 

ORA-01112: media recovery not started

如果这里出现错误，跳过即可

SQL> alter database open resetlogs;

 

Database altered.

 

检查发现，日志文件已恢复。

SQL> !ls -l /u01/oradata1

total 102528

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:49 redo010.log

-rw-r-----  1 oracle oinstall 52429312 Sep 26 03:54 redo02.log

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  146472960 bytes

Fixed Size                  1298472 bytes

Variable Size              92278744 bytes

Database Buffers           50331648 bytes

Redo Buffers                2564096 bytes

Database mounted.

Database opened.

数据库被打开后，马上执行全备，shutdown数据库,修改_all_resetlogs_corrupt参数为FALSE.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12751636/viewspace-683982/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12751636/viewspace-683982/