logical停止 apply解决一例
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';
