Oracle-DG，MRP进程无法正常应用问题处理，重启大法好
一、问题概述
某客户11.2.0.4 linux 环境，dg备库检查同步是否正常，以下为完整流程；
1) 通过主库检查dg 连通性，一切正常；
2）查询dg 观察mrp status 处于 WAIT_FOR_LOG；
3）检查发现延迟几十天？？？
4）再次检查，发现wait_for_log sequence# 与主库最新的归档相差几十天！
5) 重启dg,重新开启Mrp进程，dg恢复
二、问题分析
1) 通过主库检查dg 连通性，一切正常；
SQL> select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status; DEST_NAME STATUS RECOVERY_MODE ---------------------------------------- --------- ----------------------- LOG_ARCHIVE_DEST_6 VALID MANAGED SQL> select dest_id,error from v$archive_dest; DEST_ID ERROR ---------- ----------------------------------------------------------------- 6
2）查询dg 观察mrp status 处于 WAIT_FOR_LOG；
select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS BLOCK# BLOCKS --------- -------- ---------- ------------ ---------- ---------- RFS LGWR 205192 IDLE 11 1 MRP0 N/A 205016 WAIT_FOR_LOG 0 0
3）检查发现延迟几十天？？？
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED -------------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 04/20/2021 11:18:34 apply lag +74 12:14:19 day(2) to second(0) interval 04/20/2021 11:18:34
TRANSPORT LAG - Transport lag is a measure of the degree to which the transport of redo to the standby database lags behind
the generation of redo on the primary database. If there are one or more redo gaps on the standby database,
the transport lag is calculated as if no redo has been received after the beginning of the earliest redo gap. 如果GAP，则计算延迟时间
APPLY LAG - Apply lag is a measure of the degree to which the data in a standby database lags behind the data
in the primary database, due to delays in propagating and applying redo to the standby database. This value is
relevent only to the applying instance.
时间取决于主备之间的数据传输的延迟 or DG应用归档日志与主库最新日志的延迟时间
4）再次检查，发现wait_for_log sequence# 与主库最新的归档相差几十天！
select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS BLOCK# BLOCKS --------- -------- ---------- ------------ ---------- ---------- RFS LGWR 205192 IDLE 11 1 MRP0 N/A 205016 WAIT_FOR_LOG 0 0
5) 重启dg,重新开启Mrp进程，dg恢复
检查发现DG 一直正常接收主库传输的归档日志，但是MRP进程并未应用！
$ ls -lrt total 40500044 drwxr-x---. 3 oracle oinstall 24 Jun 15 2019 ORCL -rw-r-----. 1 oracle oinstall 394691584 Feb 4 23:04 1_205015_848250756.dbf -rw-r-----. 1 oracle oinstall 24641536 Feb 5 09:03 1_205016_848250756.dbf -rw-r-----. 1 oracle oinstall 2956288 Feb 5 09:03 1_205018_848250756.dbf -rw-r-----. 1 oracle oinstall 45406208 Feb 5 09:06 1_205017_848250756.dbf
检查主库归档日志的情况，主库归档日志已删除，其它线程已传输至2个dg库 select SEQUENCE#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') as "date",name,status from v$archived_log where sequence#=205016; SEQUENCE# date NAME S ---------- ------------------- -------------------- - 205016 2021-02-04 23:13:34 D 205016 2021-02-04 23:13:34 dg1 A 205016 2021-02-04 23:13:34 dg2_tns A select name,status from v$archived_log where sequence#=205016; NAME S -------------------- - /oradata/flash_recovery_area/1_205016_848250756.dbf A Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS BLOCK# BLOCKS --------- -------- ---------- ------------ ---------- ---------- MRP0 N/A 205020 APPLYING_LOG 280 281 ······ MRP0 N/A 205022 APPLYING_LOG 179675 821187 ······ select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED -------------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 04/20/2021 12:36:36 apply lag +00 00:00:00 day(2) to second(0) interval 04/20/2021 12:36:36
