Data guard archive GAP 故障處理案例

1、主庫伺服器日誌報錯如下：

Thu Jun 17 10:47:30 2010

Error 12541 received logging on to the standby

Check whether the listener is up and running.

Thu Jun 17 10:48:30 2010

Error 12541 received logging on to the standby

Check whether the listener is up and running.

Thu Jun 17 10:49:31 2010

Error 12541 received logging on to the standby

Check whether the listener is up and running.

查詢Oracle alertlog 日誌，發現故障發生時間的日誌資訊：

Mon Jun 14 11:03:39 2010

Thread 1 advanced to log sequence 154816

Current log# 8 seq# 154816 mem# 0: /oradata/boss/redo08.log

Mon Jun 14 11:20:52 2010

Thread 1 advanced to log sequence 154817

Current log# 9 seq# 154817 mem# 0: /oradata/boss/redo09.log

Mon Jun 14 11:44:53 2010

ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)

[@more@]

ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

PING[ARC0]: Error 3135 when pinging standby standby.

Mon Jun 14 11:44:53 2010

ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)

ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

PING[ARC0]: Error 3135 when pinging standby standby.

Mon Jun 14 11:45:53 2010

ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1041)

ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

PING[ARC0]: Error 1041 when pinging standby standby.

Mon Jun 14 11:46:53 2010

ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1041)

ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

PING[ARC0]: Error 1041 when pinging standby standby.

Mon Jun 14 11:46:59 2010

Thread 1 advanced to log sequence 154818

Current log# 6 seq# 154818 mem# 0: /oradata/boss/redo06.log

Mon Jun 14 11:50:49 2010

Error 12170 received logging on to the standby

Mon Jun 14 11:50:49 2010

Errors in file /oracle/admin/boss/bdump/boss_arc0_13050.trc:

ORA-12170: TNS:Connect timeout occurred

FAL[server, ARC0]: Error 12170 creating remote archivelog file 'standby'

FAL[server, ARC0]: FAL archive failed, see trace file.

Mon Jun 14 11:50:49 2010

Errors in file /oracle/admin/boss/bdump/boss_arc0_13050.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

Mon Jun 14 11:50:49 2010

ORACLE Instance boss - Archival Error. Archiver continuing.

Mon Jun 14 11:55:33 2010

Error 12170 received logging on to the standby

Mon Jun 14 11:55:33 2010

Errors in file /oracle/admin/boss/bdump/boss_arc0_13050.trc:

ORA-12170: TNS:Connect timeout occurred

PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 12170.

Mon Jun 14 12:00:18 2010

Error 12170 received logging on to the standby

檢視主庫上的archived log 檔案所在目錄，發現檔案1_154816_657664567.dbf還在

對未傳送過去的所有archived log 檔案進行壓縮：

tar -cvf archive14_17.tar /backup/archlog

將檔案傳輸到備用伺服器：

將archive14_17.tar透過ftp下載到備庫，在備庫透過FTP方式登入到主庫：

ftp 192.168.1.5

get archive14_17.tar

解壓archive14_17.tar

tar –xvf archive14_17.tar

GAP處理 :

• 在備庫檢查是否有日誌缺失

SQL> select * from V$ARCHIVE_GAP;

• 在備庫上手工註冊上一步中從主庫複製來的日誌(要求備庫在mount狀態)

SQL>ALTER DATABASE REGISTER LOGFILE'/backup/archlog14_17/1_154816_657664567.dbf';

SQL>ALTER DATABASE REGISTER LOGFILE'/backup/archlog14_17/1_154817_657664567.dbf';

SQL>ALTER DATABASE REGISTER LOGFILE'/backup/archlog14_17/1_154818_657664567.dbf';

SQL>ALTER DATABASE REGISTER LOGFILE'/backup/archlog14_17/1_154819_657664567.dbf';

SQL>ALTER DATABASE REGISTER LOGFILE'/backup/archlog14_17/1_154820_657664567.dbf';

…

按照以上語句一個一個註冊完即可；

另外，除了以上的一個一個註冊方式外，Oracle提供了另外一種自動註冊方式，該方式中將從主庫拷過來的以上歸檔日誌移到LOG_ARCHIVE_DEST_2 後執行如下語句(要求備庫在mount狀態)：

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

當該語句執行完畢之後重新登入SQL*plus後執行了如下語句：

$sqlplus “/as sysdba”

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

如下是像是原文解釋：

$sqlplus “/as sysdba”

SQL>startup mount

To apply the archived redo log files in the archive gap

Start up and mount the standby database (if it is not already mounted). For example, enter:

SQL> STARTUP MOUNT PFILE=/oracle/admin/pfile/initSTBY.ora

Recover the database using the AUTOMATIC option:

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

The AUTOMATIC option automatically generates the name of the next archived redo log file needed to continue the recovery operation.

After recovering the available log files, the Oracle database prompts for the name of a log file that does not exist. For example, you might see:

ORA-00308: cannot open archived log '/oracle/standby/standby_logs/arcr_1_540.arc'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}

Cancel recovery after the Oracle database applies the available log files by typing CTRL/C:

SQL>

Media recovery cancelled.

本人操作時，沒有出現以上提示，而直接推出了SQL Plus，當 時在alert log 中提示如下：

Thu Jun 17 16:43:09 2010

*************************************************************

Warning: Recovery session ended without issuing ALTER DATABASE RECOVER CANCEL

*************************************************************

The following error messages are acceptable after recovery cancellation and do not indicate a problem:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'some_filename'

ORA-01112: media recovery not started

After you finish manually applying the missing log file, you can restart log apply services on the standby database, as follows:

重新登入後執行了如下語句：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;