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恢複正常。