dataguard備庫出現GAP修復
1. 產生原因
巡檢發現備庫的日誌應用有滯後的情況
DGMGRL> show database sxcmpdg `RecvQEntries`
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
NOT_APPLIED 943612304 1 11606 09/03/2017 02:59:24 09/03/2017 03:06:03 167076036 167089250 160017
NOT_APPLIED 943612304 1 11607 09/03/2017 03:06:03 09/03/2017 03:22:06 167089250 167128494 668992
NOT_APPLIED 943612304 1 12110 09/04/2017 02:58:30 09/04/2017 03:04:30 177789138 177810714 267788
去備庫查詢滯後情況
select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
———- ————- ————–
1 11442 11605
去broker中查詢dg的配置情況
DGMGRL> show configuration;
Configuration – sxdg
Protection Mode: MaxAvailability
Databases:
sxcmpdb - Primary database
Error: ORA-16810: multiple errors or warnings detected for the database
sxcmpdg - Physical standby database
Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
查詢後臺日誌
發現有fast_recovery_area不可用的報錯，並且無法繼續歸檔。
查詢配置
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
——————– —————— ————————-
NUMBER_OF_FILES
CONTROL FILE 0 0
0
REDO LOG 2.6 0
13
ARCHIVED LOG 97.37 0
1118
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
——————– —————— ————————-
NUMBER_OF_FILES
BACKUP PIECE 0 0
0
IMAGE COPY 0 0
0
FLASHBACK LOG 0 0
0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
——————– —————— ————————-
NUMBER_OF_FILES
FOREIGN ARCHIVED LOG 0 0
0
7 rows selected.
SQL> show parameter db_recover
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
areadb_recovery_file_dest_size big integer 500G
[oracle@haixindg ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 3.3T 1.8T 1.5T 56% /
devtmpfs 126G 0 126G 0% /dev
tmpfs 126G 0 126G 0% /dev/shm
tmpfs 126G 178M 126G 1% /run
tmpfs 126G 0 126G 0% /sys/fs/cgroup
/dev/sda2 497M 119M 379M 24% /boot
/dev/sda1 200M 9.5M 191M 5% /boot/efi
tmpfs 26G 0 26G 0% /run/user/0
原來fast_recovery_area的空間被另一個備庫的歸檔所填充很大空間，並且路徑相同.
2. 修復過程
修改fast_recovery_area大小
SQL> alter system set db_recovery_file_dest_size = 3072G;
System altered.
重新啟動配置
DGMGRL> enable configuration;
Enabled.
觀察後臺日誌，備庫開始追趕滯後情況
Mon Sep 04 09:45:41 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/SXCMPDG/archivelog/2017_09_04/o1_mf_1_11860_dtsd15hp_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/SXCMPDG/archivelog/2017_09_04/o1_mf_1_11861_dtsd15wz_.arc
Mon Sep 04 09:45:48 2017
Archived Log entry 2682 added for thread 1 sequence 11863 rlc 943612304 ID 0x6e44f610 dest 2:
RFS[66]: Opened log for thread 1 sequence 11866 dbid 1850015504 branch 943612304
Mon Sep 04 09:45:54 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/SXCMPDG/archivelog/2017_09_04/o1_mf_1_11862_dtsd19fg_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/SXCMPDG/archivelog/2017_09_04/o1_mf_1_11863_dtsd1mr9_.arc
Media Recovery Waiting for thread 1 sequence 11864 (in transit)
Mon Sep 04 09:46:12 2017
Archived Log entry 2683 added for thread 1 sequence 11864 rlc 943612304 ID 0x6e44f610 dest 2:
RFS[67]: Opened log for thread 1 sequence 11867 dbid 1850015504 branch 943612304
Mon Sep 04 09:46:14 2017
Archived Log entry 2684 added for thread 1 sequence 11865 rlc 943612304 ID 0x6e44f610 dest 2:
RFS[68]: Opened log for thread 1 sequence 11868 dbid 1850015504 branch 943612304
Mon Sep 04 09:46:15 2017
Archived Log entry 2685 added for thread 1 sequence 11866 rlc 943612304 ID 0x6e44f610 dest 2:
RFS[66]: Opened log for thread 1 sequence 11869 dbid 1850015504 branch 943612304
配置重新啟用，並有告警資訊產生
DGMGRL> show configuration;
Configuration – sxdg
Protection Mode: MaxAvailability
Databases:
sxcmpdb - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
sxcmpdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
還需要追趕幾百個歸檔檔案
rw-r—–. 1 oracle oinstall 974596608 Sep 4 09:48 o1_mf_1_11878_dtsd7jsg_.arc
-rw-r—–. 1 oracle oinstall 1053221888 Sep 4 09:48 o1_mf_1_11879_dtsd7jwl_.arc
-rw-r—–. 1 oracle oinstall 1044361216 Sep 4 09:48 o1_mf_1_11880_dtsd7k0r_.arc
-rw-r—–. 1 oracle oinstall 274215936 Sep 4 03:07 o1_mf_1_12110_dtrnprd3_.arc
-rw-r—–. 1 oracle oinstall 986118144 Sep 4 09:16 o1_mf_1_12111_dtsbco0h_.arc
-rw-r—–. 1 oracle oinstall 588338688 Sep 4 09:22 o1_mf_1_12174_dtsbpy68_.arc
-rw-r—–. 1 oracle oinstall 1043307008 Sep 4 09:17 o1_mf_1_12175_dtsbfms5_.arc
-rw-r—–. 1 oracle oinstall 148306944 Sep 4 09:17 o1_mf_1_12176_dtsbg2hn_.arc
-rw-r—–. 1 oracle oinstall 927752704 Sep 4 09:37 o1_mf_1_12177_dtsckvx6_.arc
-rw-r—–. 1 oracle oinstall 357376 Sep 4 09:37 o1_mf_1_12178_dtscm96t_.arc
調整歸檔刪除天數，由原來的保留7天，改為保留2天。
追平後備庫的歸檔已經連續
-rw-r—–. 1 oracle oinstall 148306944 Sep 4 09:17 o1_mf_1_12176_dtsbg2hn_.arc
-rw-r—–. 1 oracle oinstall 927752704 Sep 4 09:37 o1_mf_1_12177_dtsckvx6_.arc
-rw-r—–. 1 oracle oinstall 357376 Sep 4 09:37 o1_mf_1_12178_dtscm96t_.arc
-rw-r—–. 1 oracle oinstall 923085824 Sep 4 10:05 o1_mf_1_12179_dtsf6dgp_.arc
-rw-r—–. 1 oracle oinstall 791576064 Sep 4 10:30 o1_mf_1_12180_dtsgozgc_.arc
[oracle@haixindg 2017_09_04]$
備庫沒有gap產生
SQL> select * from v$archive_gap;
no rows selected
後臺日誌已經應用到redo
Mon Sep 04 10:31:12 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/SXCMPDG/archivelog/2017_09_04/o1_mf_1_12180_dtsgozgc_.arc
Media Recovery Waiting for thread 1 sequence 12181 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 12181 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/SXCMPDG/onlinelog/o1_mf_7_drqfh50g_.log
dg的配置已經正常
DGMGRL> show configuration;
Configuration – sxdg
Protection Mode: MaxAvailability
Databases:
sxcmpdb - Primary database
sxcmpdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
主備庫的保護模式以及保護級別已經統一。
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE PROTECTION_LEVEL
——————– ——————–
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
3. 總結
定期巡檢不要忘記，在多例項的備庫伺服器中，要設定合理的fast_recovery_area大小，合理的儲存備庫歸檔冗餘，有助於快速恢復，如果備庫歸檔檔案已經刪除，只能通過主庫來增量恢復。