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

Oracle当前联机日志组损坏的处理一oracle日志的特性总结1oracle日志切换规律（从最大sequence#号切换到最小sequence#号）eg如下所示：下个当前日志组会是sequenc

 

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

 

一oracle 日志的特性总结

 

1 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  

         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.

 

2 快速转换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    

         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

 

3 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.