Oracle当前联机日志组损坏的处理

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle当前联机日志组损坏的处理

余二五 2017-11-16 18:56:00 浏览504
展开阅读全文
Oracle当前联机日志组损坏的处理
 
oracle 日志的特性总结
 
oracle 日志切换规律(从最大sequence#号切换到最小sequence#号)
 
eg 如下所示:下个当前日志组会是sequence#号为27的5号日志组
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 NO         32 CURRENT
         2 YES         28 INACTIVE
         3 YES         29 INACTIVE
         4 YES         30 INACTIVE
         5 YES         27 INACTIVE   <------众日志组中sequence#号最小
         6 YES         31 INACTIVE
 
6 rows selected.
 
SQL> alter system switch logfile ;
 
System altered.
 
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         32 ACTIVE
         2 YES         28 INACTIVE
         3 YES         29 INACTIVE
         4 YES         30 INACTIVE
         5 NO         33 CURRENT
         6 YES         31 INACTIVE
 
6 rows selected.
 
快速转换oracle日志组状态active为inactive
 
日志组切换后,上一个当前日志组状态由current变成active,实际上是由于当前数据文件头部的scn值还位于状态为active日志组的low scn 和next scn 内,所以我们此刻如果立即发起alter sytem checkpoint 命令推进数据文件头部scn  变可使日志状态由active 变为inactive。
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         32 INACTIVE
         2 YES         34 INACTIVE
         3 YES         35 INACTIVE
         4 NO          36 CURRENT
         5 YES         33 INACTIVE
         6 YES         31 INACTIVE
 
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         32 INACTIVE
         2 YES         34 INACTIVE
         3 YES         35 INACTIVE
         4 YES         36 ACTIVE     <------sequence号为36的日志为active
         5 YES         33 INACTIVE
         6 NO          37 CURRENT
 
6 rows selected.
 
查询数据文件头部scn情况如下:
SQL> select hxfil,fhscn from x$kcvfh;
 
     HXFIL FHSCN
---------- ----------------
         1 2781239221
         2 2781239221
         3 2781239221
         4 2781239221
         5 2781239221
         6 2781239221
         7 2781239221
         8 2781239221
        11 2781239221
        12 2781239221
        13 2781239221
 
SQL> select sequence#,first_change#,next_change# from v$log_history;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
        32    2781218249   2781218300
        33    2781218300   2781219174
        34    2781219174   2781219184
        35    2781219184   2781239220
        36    2781239220   2781239424
数据文件头部的scn:2781239221 处于sequence号为36的日志scn范围 内 2781239220   2781239424
 
SQL> alter system checkpoint;
 
System altered.
 
 
SQL> select hxfil,fhscn from x$kcvfh;
 
     HXFIL FHSCN
---------- ----------------
         1 2781239456
         2 2781239456
         3 2781239456
         4 2781239456
         5 2781239456
         6 2781239456
         7 2781239456
         8 2781239456
        11 2781239456
        12 2781239456
        13 2781239456
 
11 rows selected.
 
 
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         32 INACTIVE
         2 YES         34 INACTIVE
         3 YES         35 INACTIVE
         4 YES         36 INACTIVE 
         5 YES         33 INACTIVE
         6 NO          37 CURRENT
 
clear 日志组的执行条件
 
clear的日志组状态不能是acitve 或者当前日志组
eg
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 NO          38 CURRENT
         2 YES         34 INACTIVE
         3 YES         35 INACTIVE
         4 YES         36 INACTIVE
         5 YES         33 INACTIVE
         6 YES         37 ACTIVE
 
SQL> alter database clear logfile group 6;
alter database clear logfile group 6
*
ERROR at line 1:
ORA-01624: log 6 needed for crash recovery of instance CRM (thread 1)
ORA-00312: online log 6 thread 1: '/oracle/CRM2/CRM/redo06.log'
ORA-00312: online log 6 thread 1: '/oracle/CRM2/CRM/redo06b.log'
 
 
二  数据库一直open当前联机日志组内成员全部损坏
 
此种情况最终就是日志不能切换,影响到数据库的应用时我们才可能发现。如下所示日志状态:
 
1 数据库日志状态如下:
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 NO          44 INACTIVE
         2 NO          40 INACTIVE
         3 NO          41 INACTIVE
         4 NO          42 INACTIVE
         5 NO          45 CURRENT
         6 NO          43 INACTIVE
 
6 rows selected.
 
当告警日志中出现如下字样
 
SQL> alter database clear unarchived logfile group 2;
 
Database altered.
 
select group#,archived,sequence#,status from v$log
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         44 INACTIVE
         2 YES          0 UNUSED
         3 YES        41 INACTIVE
         4 YES        42 INACTIVE
         5 NO         45 CURRENT
         6 YES         43 INACTIVE
 
注意虽然现在的日志归档状态已经正常,但初始化后的日志组2内的日志文件可能并未生效。我们再数次切换下日志,告警文件便会产生如下报错:
ORA-00314: log 2 of thread 1, expected sequence# 46 doesn't match 0
ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'
ORA-00314: log 2 of thread 1, expected sequence# 46 doesn't match 0
ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'
 
而此刻的日志归档状态如下:
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 NO          50 INACTIVE
         2 NO          46 INACTIVE
         3 NO          47 INACTIVE
         4 NO          48 INACTIVE
         5 NO          51 CURRENT
         6 NO          49 INACTIVE
 
6 rows selected.
 
总结:以上测试为10g环境,若使用alter database clear unarchived logfile group n 初始化了之前的当前日志后,为了避免上面的问题。我们可以通过重启数据库,或者重建该日志组。11g测试没发现此类问题。
 
 
 
三 数据库启动时报当前联机日志文件丢失
 
1
SQL> startup
ORACLE instance started.
 
Total System Global Area 322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'
ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'
 
 
SQL> select group#,archived,sequence#,status from v$log;
 
    GROUP# ARC SEQUENCE# STATUS
---------- --- ---------- ----------------
         1 YES         50 INACTIVE
         2 NO          52 CURRENT
         6 YES         49 INACTIVE
         4 YES         48 INACTIVE
         5 YES         51 INACTIVE
         3 YES         47 INACTIVE
 
6 rows selected.
 
注意,由于是当前日志组,所以不能用alter database clear unarchived logfile group 2 对该日志组进行初始化。不过我们可以执行基于取消的恢复如下所示:
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/CRM2/CRM/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/oracle/app/db1/dbs/log2CRM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
2 执行基于取消的恢复
 
SQL> recover database until cancel;
Media recovery complete.
 
 
SQL> alter database open resetlogs;
 
Database altered.
 
 
总结:
1  对于当前日志组所有成员损坏的情况,按上面方式处理即可。不过仍需注意的是,初始化日志组后,会丢失该日志组对应的归档,造成归档不连续,应该做一次全库备份。
 
2  对于当前日志组成员的损坏,切换当前日志后,drop掉损坏的日志成员然后再添加新的成员。
 
3 对于非当前的日志组成员损坏,直接drop掉损坏的日志成员,然后添加新的日志成员。
 
4  对于非当前日志组损坏的情况,可以直接用alter database clear unarchived logfile group n 或者alter database clear logfile group n 进行初始化处理。
 









本文转自 zhangxuwl 51CTO博客,原文链接:http://blog.51cto.com/jiujian/1114957,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注