【故障处理】Oracle ADG MRP进程WAIT_FOR_LOG

ADG的备库mrp进程状态一直显示WAIT_FOR_LOG

select process,status ,thread#,sequence# from v$managed_standby;

查看主库传输模式

col ERROR for a20
col DESTINATION for a20
col DEST_NAME for a20
set linesize 200
SELECT DEST_ID, DEST_NAME, STATUS, DESTINATION, ARCHIVER, TRANSMIT_MODE, AFFIRM, ERROR 
FROM V$ARCHIVE_DEST
WHERE DEST_ID = 2; 

主备插入数据，备库只有当主库手动切换归档日志，备库才可以同步到主库写入的数据。

-- 查看所有在线重做日志文件位置
col MEMBER for a50
set linesize 200
SELECT 
    GROUP#,
    TYPE,
    MEMBER,
    STATUS,
    IS_RECOVERY_DEST_FILE
FROM V$LOGFILE
ORDER BY GROUP#;

主库：

备库：

检查备用日志组状态，发现都是UNASSIGNED，大小为200M，小于主库redo大小1024M

SELECT 
    GROUP#,
    THREAD#,
    SEQUENCE#,
    BYTES/1024/1024 AS CURRENT_SIZE_MB,
    STATUS,
    ARCHIVED
FROM V$STANDBY_LOG
ORDER BY GROUP#;

备库停止MRP进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

重建standbylogfile

alter database drop logfile  group 4;
alter database drop logfile  group 5;
alter database drop logfile  group 6;
alter database drop logfile  group 7;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 SIZE 1024M;

主库切换日志

alter database switch logfile;

备库启动MRP进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

观察MRP进程状态恢复正常。