實戰經驗：Oracle DG 的歸檔缺失修復
作者：AIQ
備庫上注冊歸檔文件alter database register logfile '歸檔文件絕對路徑' 或rman注冊日志catalog start with '';應用日志，查看select * from V$ARCHIVE_GAP;,監視是否還存在其它的GAP出現。如出現，如上面步驟循環操作。
本文轉載自微信公眾號「數據和云」，作者AIQ。轉載本文請聯系數據和云公眾號。
故障檢查
檢查歸檔同步情況
一、查看數據庫的情
- select database_role,flashback_on,open_mode,current_scn from v$database
- DATABASE_ROLE FLASHBACK_ON OPEN_MODE CURRENT_SCN
- ---------------- ------------------ -------------------- ---------------
- PHYSICAL STANDBY NO READ ONLY WITH APPLY 16657544972059
二、查看歸檔的最大線程與最大接收的歸檔情況。
- select thread#,max(sequence#) from v$archived_log group by thread#;
- 生產庫：
- SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
- THREAD# MAX(SEQUENCE#)
- ---------- --------------
- 1 136973
- 2 132693
- 4 149599
- 3 133277
- --DG庫
- SYS@hisnewdb> select thread#,max(sequence#) from v$archived_log group by thread#;
- THREAD# MAX(SEQUENCE#)
- ---------- --------------
- 1 136973
- 2 132693
- 4 149598
- 3 133277
- 可見4個節點歸檔是都有會過來的，sequence都能對得上。
三、查是否存在GAP
- select * from v$archived_gap;
日志應用情況
查看延時的應用情況
- select name ,value,time_computed from v$dataguard_stats where rownum<33;
- NAME VALUE TIME_COMPUTED
- -------------------------------- ---------------------------------------------------------------- ------------------------------
- transport lag +11 06:41:27 03/04/2021 16:41:20
- apply lag +11 06:41:27 03/04/2021 16:41:20
- apply finish time +00 04:23:39.868 03/04/2021 16:41:20
- estimated startup time 37 03/04/2021 16:41:20
- 可看到apply lag的應用已經延時11天6小時了。
- apply finish time應用最快的恢復時長為4小時。
恢復思路
應用日志
- alter database recover managed standby databse cancel; --取消應用日志
- alter database open read only; --打開只讀庫
- alter database recover managed standby ;
- alter database recover managed standby disconnect from session; -- 后臺應用，建議上面命令，放前臺應用。
歸檔還保留或者GAP較少的情況
1)歸檔還在主庫
方法一：
首先通過備庫sql查出相應的 node[thread#] 和歸檔位置 name：
- select name from v$archived_log where sequence# between &1 and &2 and thread# = &3;
傳輸上面文件到備庫歸檔位置 archive log list
- #1.asmcd命令 本地環境與asm存儲cp自由。
- cp arch*.pdf /home/oracle/1.dbf
備庫上注冊歸檔文件alter database register logfile '歸檔文件絕對路徑' 或rman注冊日志catalog start with '';
應用日志，查看select * from V$ARCHIVE_GAP;,監視是否還存在其它的GAP出現。如出現，如上面步驟循環操作。
方法二：
在配置 fal_client=${備庫的監聽} 和 fal_server=${主庫的監聽}
直接應用日志，由備庫尋找日志。
2)歸檔已在備庫
應用日志
歸檔已經被刪除或GAP較多的情況
查看歸檔所在的位置
alert.log日志：
- 提供等thread 線程4的序列為148164的歸檔，獲取的序號有148164-148165
- control_keep_record_keep_time是控制文件的重用記錄數據。提示在這個記錄天數內沒找到歸檔文件，建議設置更長些天數。以便GAP找到缺失的日志。
- 默認7天，1-365天范圍。
- 記錄的是歸檔日志，各種備份記錄。
- 不記錄數據文件，表空間，redo thread記錄。除非被drop，否則不會重用這部分記錄
- started logmerger process
- Thu Mar 04 16:19:53 2021
- Managed Standby Recovery not using Real Time Apply
- Parallel Media Recovery started with 16 slaves
- Waiting for all non-current ORLs to be archived...
- All non-current ORLs have been archived.
- Media Recovery Waiting for thread 4 sequence 148164
- Fetching gap sequence in thread 4, gap sequence 148164-148165
- Thu Mar 04 16:19:57 2021
- Completed: alter database recover managed standby database disconnect from session
- ----------
- Thu Mar 04 16:21:50 2021
- FAL[client]: Failed to request gap sequence
- GAP - thread 4 sequence 148164-148165
- DBID 3828421454 branch 984679630
- FAL[client]: All defined FAL servers have been attempted.
- ------------------------------------------------------------
- Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
- parameter is defined to a value that's sufficiently large
- enough to maintain adequate log switch information to resolve
- archivelog gaps.
- ------------------------------------------------------------
- Thu Mar 04 16:22:25 2021
- RFS[18]: Selected log 29 for thread 4 sequence 149600 dbid -466545842 branch 984679630
- Thu Mar 04 16:22:25 2021
1)找到當前的最小SCN
對比數據文件最后檢查點的scn，數據文件頭部檢查點的scn，缺失歸檔的對應scn(下個日志文件第一個更改號)，當前數據庫的scn：
- select thread#,low_sequence#,high_sequence# from v$archive_gap;
- col datafile_scn for 999999999999999
- col DATAFILE_HEADER_SCN for 999999999999999
- col current_scn for 999999999999999
- col next_change# for 999999999999999
- select ( select min(d.checkpoint_change#) from v$datafile d ) datafile_scn ,
- ( select min(d.checkpoint_change#) from v$datafile_header d where rownum=1) datafile_header_scn,
- (select current_scn from v$database) current_scn,
- (select next_change# from v$archived_log where sequence#=148164 and resetlogs_change# = (select d.resetlogs_change# from v$database d ) and rownum=1 ) next_change#
- from dual;
- DATAFILE_SCN DATAFILE_HEADER_SCN CURRENT_SCN NEXT_CHANGE#
- ---------------- ------------------- ---------------- ----------------
- 16657544969028 16657544972060 16657544972059
取上面最小的scn作為增量備份的SCN
2)主庫做SCN增量備份
停用備庫的日志應用
- alter database recover managed standby database cancel;
rman備份
- 切換日志
- 切記備份當前控制文件
- 增量scn備份
- run {
- allocate channel c1 device type disk;
- allocate channel c2 device type disk;
- allocate channel c3 device type disk;
- allocate channel c4 device type disk;
- allocate channel c5 device type disk;
- allocate channel c6 device type disk;
- CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
- backup as compressed backupset current controlfile for standby format '/home/oracle/backup/backup_ctl_%U.rman';
- backup as compressed backupset incremental from scn 16657544969028 database format '/home/oracle/backup/backup_%d_%s_%c_%U_%T.rman' include
- current controlfile for standby filesperset 10 tag 'forsdb_16657544969028_0304';
- release channel c1 ;
- release channel c2 ;
- release channel c3 ;
- release channel c4 ;
- release channel c5 ;
- release channel c6 ;
傳輸備份文件到備庫
- scp -rp /home/oracle/backup/backup host2:/home/oracle
介質恢復備庫
- 查出控制文件的絕對目錄位置，后停備庫
- 啟動到nomount
- 恢復控制文件
- 啟動到mount
- 恢復數據文件
- 檢查rman進展
- select name from v$controlfile;
- shu immediate;
- startup nomount;
- rman target / <<eof
- restore standby controlfile from '/home/oracle/backup/backup_ctl_%U.rman';
- alter database mount;
- eof
- 如果沒有單獨備份standby controlfile，就一個一個文件來測試恢復standby controflie
- restore standby controlfile to '/oradata/hisnewdb/control01.ctl' from '/home/oracle/backup/某個文件';
- 如果文件太多，可以先rman注冊文件后，再恢復控制文件。
- 要找開備庫mount狀態才能注冊
- rmant target / <<eof
- startup mount;
- catalog start with '/home/oracle/backup/';
- list backup of controlfile;
- restore standby controlfile automatic;
- eof
- #大概是這樣。restore standby controlfile automatic;如果不通，就采用上面list的信息，找到具體含有standby controflile的備份文件，再通過restore standby controfile from '';來恢復 。
- catalog start with '/home/oracle/backup/';
- recover database noredo;
查看rman的恢復進展：
- set line 9999
- select sid,serial#,opname,round(sofar/totalwork*100) completed,trunc(elapsed_seconds/60) elapsed ,trunc(time_remaining/60) remaining,context ,target,sofar,totalwork
- from v$session_longops
- where opname like 'RMAN%' and opname not like '%aggregate%' and totalwork!=0 and sofar<>totalwork;
應用日志
檢查standby redo files是否存在:
- select * from v$standby_log;
注冊standby redolog files
- -- 添加單個文件：
- alter database add standby logfile group {組號} 'standby redo logs files 絕對目錄文件';
- -- 添加多個standby redologs file
- alter database add standby logfile group {組號} ('standby redo logs file 1','logfiles2');
應用日志
- alter database recover managed standby database cancel ;
- startup mount;
- alter database open read only;
- select open_mode,status,protection_level,protection_mode from v$database ;
- --前臺應用日志
- alter database recover managed standby database ;
- -- 8 parallel 后臺應用日志
- alter database recover managed standby database parallel 8 disconnect from session;
檢查應用日志的情況
檢查各個線程thread#的最大應用日志的序列，與主庫進行對比。
- select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
責任編輯：武曉燕
來源：
數據和云
