Redo丢失的4种情况及处理方法

来源：互联网发布：怎么下载淘宝网编辑：程序博客网时间：2024/05/16 15:00

本文参考了尛样儿的这篇文章的整体思路，在自己的理解上添加了自己试验的脚本。

大家可以点击http://blog.itpub.net/23135684/viewspace-626935/查看原帖

这次演示的环境在线日志：3组每组1个成员，以后再试验多成员的。

一.说明：
1.以下所说的当前日志指日志状态为CURRENT,ACTIVE,非当前日志指日志状态为INACTIVE
2.不用考虑归档和非归档模式，2种模式下的Redo丢失情况一样。

二.丢失Redo的4种情况：

第一种情况：非当前日志，正常关闭。
第二种情况：非当前日志，非正常关闭。
第三种情况：当前日志，正常关闭。
第四种情况：当前日志，非正常关闭。

三.处理方法：
第一、二种情况的处理方法一样，直接把日志文件clear即可。

[oracle@xuexi1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 3 16:08:12 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>    SQL> SQL> select group#,thread#,status,archived from v$log;    GROUP#    THREAD# STATUS           ARC---------- ---------- ---------------- ---         1          1 INACTIVE         NO         2          1 INACTIVE         NO         3          1 CURRENT          NOSQL> col member for a50SQL> select * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                                             IS_---------- ------- ------- -------------------------------------------------- ---         3         ONLINE  /home/oracle/app/oradata/SBDB/redo03.log           NO         2         ONLINE  /home/oracle/app/oradata/SBDB/redo02.log           NO         1         ONLINE  /home/oracle/app/oradata/SBDB/redo01.log           NOSQL> SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>

上面是我要进行测试的环境，里面有3组日志每组一个成员文件，当前日志组是：组3，我就删除组1和组2的文件了。我是管了数据库才删除文件的。

然后我启动数据库，发现有错误，查看相关日志，发现在线日志丢失。

Additional information: 3Errors in file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_ora_15544.trc:ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'Thu Jul 03 16:15:41 2014Errors in file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_m000_15546.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3System state dump requested by (instance=1, osid=15544), summary=[abnormal instance termination].System State dumped to trace file /home/oracle/app/diag/rdbms/sbdb/SBDB/trace/SBDB_diag_15486_20140703161542.trcUSER (ospid: 15544): terminating the instance due to error 313Dumping diagnostic data in directory=[cdmp_20140703161542], requested by (instance=1, osid=15544), summary=[abnormal instance termination].Instance terminated by USER, pid = 15544[root@xuexi1 trace]# [root@xuexi1 trace]# [root@xuexi1 trace]# pwd/home/oracle/app/diag/rdbms/sbdb/SBDB/trace[root@xuexi1 trace]#

查看相关日志，大家可以看到提示，是少了。

SQL> conn / as sysdbaConnected to an idle instance.SQL> shutdown abortORACLE instance shut down.SQL> SQL> startup mountORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesDatabase mounted.SQL> select group#,thread#,status,archived from v$log;    GROUP#    THREAD# STATUS           ARC---------- ---------- ---------------- ---         1          1 INACTIVE         NO         3          1 CURRENT          NO         2          1 INACTIVE         NOSQL> alter database clear logfile group 1;            Database altered.SQL> alter database clear logfile group 2;Database altered.SQL> alter database open;Database altered.SQL>

这里我直接进入数据库，起到mount状态，然后运行alter database clear logfile group *;把2个日志文件重新建，这个时候，大家去OS里面看看，那2个文件重新创建了。数据库也启动了。

第三种情况的处理办法

[oracle@xuexi1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 3 17:13:51 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> set linesize 1000SQL> col member for a60SQL> select * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                                                       IS_---------- ------- ------- ------------------------------------------------------------ ---         3         ONLINE  /home/oracle/app/oradata/SBDB/redo03.log                     NO         2         ONLINE  /home/oracle/app/oradata/SBDB/redo02.log                     NO         1         ONLINE  /home/oracle/app/oradata/SBDB/redo01.log                     NOSQL> SQL> select group#,thread#,status,archived from v$log;    GROUP#    THREAD# STATUS           ARC---------- ---------- ---------------- ---         1          1 INACTIVE         NO         2          1 INACTIVE         NO         3          1 CURRENT          NOSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>

我把数据库正常关闭后，去删除3号对应的文件去了。

SQL> startupORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesDatabase mounted.ORA-03113: end-of-file on communication channelProcess ID: 15322Session ID: 125 Serial number: 5SQL> shutdown immediateORA-24324: service handle not initializedORA-01041: internal error. hostdef extension doesn't existSQL> SQL> conn / as sysdbaConnected to an idle instance.SQL> startup mount;ORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesDatabase mounted.SQL> recover database until cancelMedia recovery complete.SQL> SQL> alter database open resetlogs;Database altered.SQL>

这样就启动了。因为是正常关闭，online redo log里面的东西就没用了，可以都删除了，就用resetlog属性了。

第四种情况的处理方法：
1.通过备份来还原、恢复数据。
2.通过修改参数文件中的参数
_allow_resetlogs_corruption=TRUE

SQL> shutdown abortORACLE instance shut down.SQL> SQL> startupORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesDatabase mounted.ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> conn / as sysdbaConnected.SQL> select group#,thread#,status,archived from v$log;    GROUP#    THREAD# STATUS           ARC---------- ---------- ---------------- ---         1          1 CURRENT          NO         3          1 INACTIVE         NO         2          1 UNUSED           NOSQL> SQL> select file#,checkpoint_change#,fuzzy from v$datafile_header;     FILE# CHECKPOINT_CHANGE# FUZ---------- ------------------ ---         1             964567 YES         2             964567 YES         3             964567 YES         4             964567 YESSQL> alter database clear logfile group 1;alter database clear logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance SBDB (thread 1)ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/SBDB/redo01.log'SQL> recover database until cancel;ORA-00279: change 964567 generated at 07/03/2014 16:32:54 needed for thread 1ORA-00289: suggestion : /home/oracle/app/product/11.1.0/db_1/dbs/arch1_4_831201553.dbfORA-00280: change 964567 for thread 1 is in sequence #4Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/app/oradata/SBDB/system01.dbf'ORA-01112: media recovery not startedSQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/app/oradata/SBDB/system01.dbf'

上面的操作，就是模拟数据库异常关闭，然后启动后，我们尝试用前面的方法去恢复数据库，所有的方法都不成啊。于是使用在pfile添加_allow_resetlogs_corruption=TRUE来解决了。

第一步：创建pfile文件

SQL> startup nomountORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesSQL> create pfile from spfile;File created.SQL> shutdown immediateORA-01507: database not mountedORACLE instance shut down.SQL>

最后把数据库停了吧，一会还要启动到nomount状态呢，直接修改pfile添加上面的属性后

SQL> startup pfile=/home/oracle/app/product/11.1.0/db_1/dbs/initSBDB.oraORACLE instance started.Total System Global Area  730714112 bytesFixed Size                  2256832 bytesVariable Size             478150720 bytesDatabase Buffers          243269632 bytesRedo Buffers                7036928 bytesDatabase mounted.ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL> alter database open resetlogs;Database altered.SQL>

这样的话，数据库就拉起来了。但是这是不安全的，"虽然能够启动数据库到open状态，但是启动后的数据库数据字典、数据有可能导致不一致的情况出现，故需要在open下把整个数据库export，然后删除库，重建，再将export的数据import到新的数据库中。"引原帖。

四.验证数据库是否正常关闭的方法

这个暂时没有弄，大家可以看原帖

五.结论：
非正常关闭的当前日志丢失，可能导致数据库启动后的混乱，并可能造成少量数据的丢失。其他情况不会导致数据的丢失。

0 0