Data Guard 归档传输 GAP 排查与修复

Data Guard 归档传输 GAP 排查与修复一、问题现象主备库v$managed_standby显示主库 LNS WRITING sequence 480 -- 实时 redo 在传 主库 ARCH CLOSING sequence 478 -- 主库已归档 备库 RFS IDLE sequence 480 -- 备库已收到当前 redo 备库 MRP0 WAIT_FOR_GAP sequence 469 ← apply 卡在 469判断实时 redo 流LNS → RFS通归档传输ARCH 路径从 469 之后断开备库 apply 进程卡住。二、诊断步骤1. 备库确认归档接收情况-- 备库SELECT*FROMv$archive_gap;SELECTsequence#, applied, archived, deleted, nameFROMv$archived_logWHEREsequence# BETWEEN 466 AND 480ORDERBYsequence#;备库v$archive_gap可能为空——MRP0 在等但 FAL 还没主动发现。备库归档只到 468469 之后未到达。2. 主库确认本地归档与传输状态-- 主库SELECTsequence#, applied, archived, deleted, nameFROMv$archived_logWHEREsequence# BETWEEN 466 AND 480ORDERBYsequence#;主库 469-479 都在本地但传到 dest_2 的记录从 469 开始缺失。-- 主库查 dest_2 状态SELECTdest_id,status,error,gap_status,db_unique_name,archived_seq#, applied_seq#FROMv$archive_dest_statusWHEREdest_id2;报错示例STATUS: ERROR ERROR : ORA-00270: error creating archive log GAP_STATUS: RESOLVABLE GAP3. 看备库 alert log 找具体子错误tail-500$ORACLE_BASE/diag/rdbms/dbname/sid/trace/alert_sid.log|grep-A5ORA-00270常见子错误子错误含义ORA-15041: diskgroup space exhaustedASM 磁盘组满ORA-19809: limit exceeded for recovery filesFRA 满ORA-19504 / ORA-17502路径不存在 / 权限问题ORA-16191密码文件不一致三、根因定位ORA-00270 主库往备库写归档时备库这一端创建文件失败。主备网络通否则报 12541 / 12154问题在备库的归档落地路径或空间。四、修复路径 A备库归档目的地空间不足-- 备库查 ASM 磁盘组SELECTname,total_mb,free_mb,ROUND(free_mb/total_mb*100,2)ASpct_freeFROMv$asm_diskgroup;-- 看 FRA 占用SELECT*FROMv$recovery_area_usage;清理过期归档rman target / RMANDELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFOREsysdate-3;或扩 FRAALTERSYSTEMSETdb_recovery_file_dest_size200G SCOPEBOTH;路径 B手动补归档最稳不依赖传输修复# 主库上把缺失的归档拷贝到备库scpDATA01/CCRB06/ARCHIVELOG/.../thread_1_seq_469.dbf\oraclestandby:/u01/archive/# 469 到 479 都拷过去-- 备库逐个注册ALTERDATABASEREGISTER LOGFILE/u01/archive/thread_1_seq_469.dbf;ALTERDATABASEREGISTER LOGFILE/u01/archive/thread_1_seq_470.dbf;...注册完 MRP0 自动开始 apply。路径 C触发主库重传修复传输问题后-- 主库ALTERSYSTEMSETLOG_ARCHIVE_DEST_STATE_2DEFER;ALTERSYSTEMSETLOG_ARCHIVE_DEST_STATE_2ENABLE;-- 强制切换日志触发归档ALTERSYSTEM ARCHIVE LOGCURRENT;五、修复后验证-- 主库SELECTdest_id,status,error,archived_seq#, applied_seq#FROMv$archive_dest_statusWHEREdest_id2;-- 备库SELECTprocess,status,sequence#FROMv$managed_standbyWHEREprocessIN(MRP0,RFS);SELECT*FROMv$archive_gap;期望主库 dest_2 STATUS VALIDerror 为空备库 MRP0 状态从WAIT_FOR_GAP变成APPLYING_LOGarchived_seq# 与 applied_seq# 持续追平附常用诊断 SQL 速查-- 备库 apply 进度SELECTsequence#, applied FROM v$archived_logWHEREappliedYESORDERBYsequence# DESC;-- 主备 lagSELECTname,value,time_computedFROMv$dataguard_statsWHEREnameIN(transport lag,apply lag);-- Broker 全局状态如启用-- DGMGRL SHOW CONFIGURATION VERBOSE;