logical停止 apply解決一例

今年五一因更換儲存櫃，更換伺服器，DB重作，logical standby也重做了。三號檢查狀況，如下：

SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID STATE

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

         1 APPLYING

 

SQL>SELECT NEWEST_SCN-APPLIED_SCN scn FROM DBA_LOGSTDBY_PROGRESS;

發現大量SQL沒有Apply

等了很久，發現沒有apply的越來越多。查看log,發現如下log.

Media Recovery Log D:\MES\ARCHIVE\ARC001_071784095401134.ARC

Wed May 05 12:36:53 2010

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Wed May 05 12:36:53 2010

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Wed May 05 12:36:53 2010

Media Recovery Log D:\MES\ARCHIVE\ARC001_071784095401135.ARC

Wed May 05 12:37:24 2010

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Wed May 05 12:37:24 2010

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Wed May 05 12:37:24 2010

Media Recovery Log D:\MES\ARCHIVE\ARC001_071784095401136.ARC

Wed May 05 12:37:54 2010

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Wed May 05 12:37:54 2010

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Wed May 05 12:37:54 2010

Media Recovery Log D:\MES\ARCHIVE\ARC001_071784095401137.ARC

Wed May 05 12:38:23 2010

ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Wed May 05 12:38:23 2010

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Wed May 05 12:38:23 2010

Media Recovery Log D:\MES\ARCHIVE\ARC001_071784095401138.ARC

以上是physical時資料庫的log,在變為logical後的log如下。發現之前已經recover的某些log在logical又重新apply。

 

LOGMINER: Log Auto Delete - deleting: D:\mes\archive\ARC001_071784095401129.arc

Deleted file D:\mes\archive\ARC001_071784095401129.arc

Wed May 05 13:07:19 2010

LOGMINER: Log Auto Delete - deleting: D:\mes\archive\ARC001_071784095401130.arc

Deleted file D:\mes\archive\ARC001_071784095401130.arc

Wed May 05 13:07:19 2010

LOGMINER: Log Auto Delete - deleting: D:\mes\archive\ARC001_071784095401131.arc

Deleted file D:\mes\archive\ARC001_071784095401131.arc

Wed May 05 13:07:19 2010

LOGMINER: Log Auto Delete - deleting: D:\mes\archive\ARC001_071784095401132.arc

Deleted file D:\mes\archive\ARC001_071784095401132.arc

Wed May 05 13:07:19 2010

LOGMINER: Log Auto Delete - deleting: D:\mes\archive\ARC001_071784095401133.arc

Deleted file D:\mes\archive\ARC001_071784095401133.arc

Wed May 05 13:07:20 2010

LOGMINER: End mining logfile: D:\mes\archive\ARC001_071784095401137.arc

 

Thu May 06 08:32:39 2010

LOGMINER: End mining logfile: D:\mes\archive\ARC001_071784095401134.arc

Thu May 06 08:32:39 2010

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1135, D:\mes\archive\ARC001_071784095401135.arc

Thu May 06 08:32:40 2010

LOGMINER: End mining logfile: D:\mes\archive\ARC001_071784095401135.arc

Thu May 06 08:32:40 2010

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1136, D:\mes\archive\ARC001_071784095401136.arc

Thu May 06 08:32:42 2010

LOGMINER: End mining logfile: D:\mes\archive\ARC001_071784095401136.arc

Thu May 06 08:32:42 2010

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1137, D:\mes\archive\ARC001_071784095401137.arc

Thu May 06 08:32:44 2010

LOGMINER: End mining logfile: D:\mes\archive\ARC001_071784095401137.arc

 

資料庫apply到1133後停了。1134—1137已經end mining.檢查狀態，仍然在applying.可是資料庫的確沒有動作，百思不得其解。

 

發現如下log.沒有辦法的時候，決定試一下再手工註冊一下D:\mes\archive\ARC001_071784095401138.arc這個log,探索資料庫開始apply.再手工註冊剩餘未註冊的log,資料庫正常工作。

 

SQL>select sid,type,status_code,status from v$logstdby_process;

SID      TYPE   STATUS_CODE          STATUS

2730    COORDINATOR        16116 ORA-16116: no work available

2728    READER         16240 ORA-16240: Waiting for logfile (thread# 1sequence# 1138)

2726    BUILDER        16116 ORA-16116: no work available

2727    PREPARER     16116 ORA-16116: no work available

2721    ANALYZER    16117 ORA-16117: processing

2725    APPLIER         16116 ORA-16116: no work available

2724    APPLIER         16116 ORA-16116: no work available

2722    APPLIER         16116 ORA-16116: no work available

2720    APPLIER         16116 ORA-16116: no work available

2723    APPLIER         16116 ORA-16116: no work available

 

手工註冊log的語句如下：

ALTER DATABASE   REGISTER logfile 'D:\MES\ARCHIVE\ARC001_071784095401138.ARC';