Oracle dataguard standby loss archive log ( ORA-16809 ) (Dataguard 遺失/未接收 archivelog 檔案)

1. 檢查 Standby 最後已接收 (recieve) 與應用 (apply) 的序號 (Check recieved & apply seq log on Standby)

select al.thread#, max(al.sequence#) "Last Seq Recieved",
max(lh.sequence#) "Last Seq Applied"
from v$archived_log al, v$log_history lh
where al.thread#=lh.thread#
group by al.thread#;

Thread$   Last Seq Recieved  Last Seq Applied
--------- -----------------  ----------------
        1             32960             32537
        2             23628             23241

2. 檢查 Standby archivelog 存放區域 (+FRADG/$ORACLE_UNQNAME/ARCHIVELOG)，發現 sequence 少了 32538、32543~32553

ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32536.1442.1057831381
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32537.2837.1057831395
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32539.814.1057831402
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32540.1609.1057831703
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32541.654.1057831765
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32542.850.1057831801
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32554.1151.1057831467
ARCHIVELOG  UNPROT  COARSE  NOV 30 12:00:00 Y thread_1_seq_32555.656.1057831479

3. 備份 Primary 上的 archivelog 序號從 32538 至 32553+1，並傳到 Standby

RMAN> backup format '/tmp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 32538 until sequence 32554;
$ scp /tmp/*.bkp oracle@10.10.10.10:/tmp

4. 在 Standby 進行 archivelog 備份檔還原

RMAN> catalog backupiece '/tmp/ORADB_10889_1_1_1057840710.arc.bkp';
run {
set archivelog destination to '+FRADG/ORADBRSTBY/ARCHIVELOG/2020_11_30';
restore archivelog from logseq 32538 until logseq 32657 thread 1;
}

5. Restart Dataguard 

SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session;

 

Note:

a. 若有多個 archivelog backup，可以指定資料夾，將檔案全部匯入

RMAN> catalog start with '/tmp/backup';