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