standby日志缺失恢复

今天早上检查数据库，发现凌晨告警日志出现如下信息，怀疑主库传送日志到备库没有成功：

error 12541 received logging on to the standby

Check whether the listener is up and running.

LGWR: Error 12541 creating archivelog file 'DG'

Mon Mar 19 02:30:08 2013

Errors in file /opt/oracle/admin/BOLO102/bdump/bolo1022_lns1_22938.trc:

ORA-12541: Message 12541 not found; No message file for product=RDBMS, facility=ORA

LNS: Failed to archive log 7 thread 2 sequence 13177 (12541)

在standby 备库查看验证，发现日志有断点：

SQL> set linesize 200

SQL> col name for a70

SQL> SELECT

  2  ARCH.THREAD# "Thread",name,

  3  ARCH.SEQUENCE# "Last Sequence Received",

  4  APPL.SEQUENCE# "Last Sequence Applied",

  5  (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

  6  FROM

  7  (SELECT THREAD# ,SEQUENCE#,name FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)     FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

  8  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

  9        FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

 10  WHERE ARCH.THREAD# = APPL.THREAD#

 11  ORDER BY 1;

    Thread NAME                                                                   Last Sequence Received Last Sequence Applied Difference

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

         1 /log/archivelog/arch_1_13062_794450242.arc                                              13062                 13039         23

         2 /log/archivelog/arch_2_13242_794450242.arc                                              13242                 13219         23

SQL> select * from v$archive_gap;

   THREAD#    LOW_SEQUENCE#   HIGH_SEQUENCE#

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

   1           13220             13223

在归档日志目录查看归档日志缺少了arch_2_13220 到arch_2_13223这几个归档日志，于是从主库把这几个归档日志手动传送到standby的相应位置。

停止standby日志应用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

手动注册丢失的归档日志：

alter database register logfile '/log/archivelog/arch_2_13220_794450242.arc';

alter database register logfile '/log/archivelog/arch_2_13221_794450242.arc';

alter database register logfile '/log/archivelog/arch_2_13222_794450242.arc';

alter database register logfile '/log/archivelog/arch_2_13223_794450242.arc';

重新开启standby日志应用：

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

查看standby告警日志信息，日志正在逐步应用恢复：

Completed: ALTER DATABASE RECOVER  MANAGED STANDBY DATABASE DISCONNECT FROM SESSION 

Tue Mar 19 11:09:49 2013

Media Recovery Log /log/archivelog/arch_2_13220_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13040_794450242.arc

Tue Mar 19 11:10:22 2013

Media Recovery Log /log/archivelog/arch_2_13221_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13041_794450242.arc

Tue Mar 19 11:10:52 2013

Media Recovery Log /log/archivelog/arch_2_13222_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13042_794450242.arc

Tue Mar 19 11:11:18 2013

Media Recovery Log /log/archivelog/arch_2_13223_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13043_794450242.arc

Tue Mar 19 11:11:46 2013

Media Recovery Log /log/archivelog/arch_1_13044_794450242.arc

Media Recovery Log /log/archivelog/arch_2_13224_794450242.arc

Tue Mar 19 11:12:28 2013

Media Recovery Log /log/archivelog/arch_1_13045_794450242.arc

Media Recovery Log /log/archivelog/arch_2_13225_794450242.arc

Tue Mar 19 11:12:55 2013

Media Recovery Log /log/archivelog/arch_2_13226_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13046_794450242.arc

Tue Mar 19 11:13:26 2013

Media Recovery Log /log/archivelog/arch_2_13227_794450242.arc

Media Recovery Log /log/archivelog/arch_1_13047_794450242.arc

....

最后确认一下standby日志应用情况：

SQL> SELECT

  2  ARCH.THREAD# "Thread",name,

  3  ARCH.SEQUENCE# "Last Sequence Received",

  4  APPL.SEQUENCE# "Last Sequence Applied",

  5  (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

  6  FROM

(SELECT THREAD# ,SEQUENCE#,name FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)     FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

  8  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

      FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

 10  WHERE ARCH.THREAD# = APPL.THREAD#

 11  ORDER BY 1;

    Thread NAME                                                                   Last Sequence Received Last Sequence Applied Difference

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

         1 /log/archivelog/arch_1_13063_794450242.arc                                              13063                 13063          0

         2 /log/archivelog/arch_2_13243_794450242.arc                                              13243                 13243          0

SQL>  select * from v$archive_gap;

no rows selected

日志同步应用完成，standby恢复正常。