19CData Guard Physical Standby无法实时同步问题

【现象】

Data Guard Physical Standby发现无法实时同步数据，需要在主库执行切换alter system switch logfile;操作，备库才能同步到数据。

【检查】

从上面现象可以看出，需要执行切换归档日志才能同步，没有实时同步说明没有用到redo log咯进行同步过来，主要查看redo log配置信息跟Data Guard的同步原理查看；

可以先判断数据库的三种模式，可以使用命令进行修改：

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE };

可以查看使用同步时候的命令查看：

alter database recover managed standby database disconnect nodelay;

2023-01-18T16:06:09.052133+08:00
PR00 (PID:7257): MRP0: Background Media Recovery cancelled with status 16037
2023-01-18T16:06:09.053257+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_7257.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:7257): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
stopping change tracking
2023-01-18T16:06:09.224782+08:00

可以查看到日志中显示不是实时同步，而且有报错信息，查看报错信息

*** 2023-01-18T16:06:09.052727+08:00
MRP0: Background Media Recovery cancelled with status 16037
<error barrier> at 0x7ffd0bb3c400 placed krd.c@25321
ORA-16037: user requested cancel of managed recovery operation
*** 2023-01-18 16:06:09.053955 [krsh.c:6348]
Managed Standby Recovery not using Real Time Apply
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 1192Kb
-- Redo read_disk statistics --
Read rate (SYNC): 1192Kb in 1102.89s => 0.00 Mb/sec
Total redo bytes: 1192Kb Longest record: 8Kb, moves: 0/1783 moved: 0Mb (0%)
Longest LWN: 162Kb, reads: 1321
Last redo scn: 0x000000000035df96 (3530646)
Change vector header moves = 240/3474 (6%)

从上面看到，显示的redo的信息报错，查看redo信息

主库：

SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;

GROUP# MEMBERS BYTE_MB STATUS
---------- ---------- ---------- ------------------------------------------------
1 1 120 INACTIVE
2 1 120 CURRENT
3 1 120 INACTIVE

从库：

select group#,bytes/1024/1024,status from v$standby_log ;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------
4 50 UNASSIGNED
5 50 UNASSIGNED
6 50 UNASSIGNED

查看到standby日志状态是UNASSIGNED，并且大小不一致，主库是120M，从库是50M

查看UNASSIGNED信息说明：

STATUS VARCHAR2(10) Log status:
UNASSIGNED - If ARCHIVED is NO, then the standby redo log has been archived and is again available. If ARCHIVED is YES, then the standby redo log has never been used and is available.

ACTIVE - If ARCHIVED is NO, then the standby redo log is complete and waiting to be archived. If ARCHIVED is YES, then the standby redo log is currently being written to and is therefore not ready to be archived. For a given thread, there should be only one such log.

从这里看从库无法实时运用redos日志，需要将standby_log跟redos日志大小保持一致

修改tandby redo log大小

alter system set standby_file_management=manual ;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;

alter database add standby logfile group 4 ('/oradata/ORCL/redo04.log') size 120m;
alter database add standby logfile group 5 ('/oradata/ORCL/redo05.log') size 120m;
alter database add standby logfile group 6 ('/oradata/ORCL/redo06.log') size 120m;

alter system set standby_file_management=auto ;

重启后查看信息，测试查看数据同步正常

SQL> select group#,bytes/1024/1024,status from v$standby_log ;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------
4 120 ACTIVE
5 120 UNASSIGNED
6 120 UNASSIGNED