How to troubleshoot Oracle Dataguard broker output Transport Lag and Apply Lag is delayed but database status is success

1/18/2023 10:31:00 上午

check dataguard broker status show lag on Node Standby 1 , but Node Standby 2 is normal

DGMGRL> show database dbdg1

Database - DBDG1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      7 hours 45 seconds (computed 30 seconds ago)
  Apply Lag:          7 hours 45 seconds (computed 30 seconds ago)
  Average Apply Rate: 9.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    DB1

Database Status:
SUCCESS

DGMGRL> show database dbdg2

Database - DBDG2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 6.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    DB2

Database Status:
SUCCESS

check alert log on Node Standby 1 is show using receive archivelog to recovery

2023-01-18T09:08:11.642733+08:00
 rfs (PID:32846): Opened log for T-1.S-348480 dbid 3395195235 branch 1112866403
2023-01-18T09:08:12.130134+08:00
PR00 (PID:4062956): Media Recovery Log /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_348479_1112866403.arc
PR00 (PID:4062956): Media Recovery Waiting for T-1.S-348480 (in transit)

check alert log on Node Standby 2 is show using online redo log to recovery

2023-01-18T09:08:11.723430+08:00
PR00 (PID:1783308): Media Recovery Waiting for T-1.S-348480 (in transit)
2023-01-18T09:08:11.726119+08:00
Recovery of Online Redo Log: Thread 1 Group 22 Seq 348480 Reading mem 0
  Mem# 0: +DATADG/DBDGD/ONLINELOG/group_22.3050.1126283035
  Mem# 1: +FRADG/DBDGD/ONLINELOG/group_22.4817.1126283035

check standby log on Node Standby 1

SQL> select * from v$standby_log;

    GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    CON_ID
----------
	21 3395195235					     1	   348462  524288000	    512   62492672 NO  ACTIVE	     3.5763E+11 12-JAN-23   3.5763E+11 12-JAN-23   3.5763E+11 12-JAN-23
	 0

	22 3395195235					     1	   348463  524288000	    512  260909568 NO  ACTIVE	     3.5763E+11 12-JAN-23   3.5763E+11 13-JAN-23   3.5763E+11 13-JAN-23
	 0

	23 3395195235					     1	   348464  524288000	    512  262870016 NO  ACTIVE	     3.5763E+11 13-JAN-23   3.5763E+11 13-JAN-23   3.5763E+11 13-JAN-23
	 0


    GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    CON_ID
----------
	24 3395195235					     1	   348465  524288000	    512  256735744 NO  ACTIVE	     3.5763E+11 13-JAN-23   3.5763E+11 14-JAN-23   3.5763E+11 14-JAN-23
	 0

check standby log on Node Standby 2

SQL> select * from v$standby_log;

    GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    CON_ID
----------
	21 UNASSIGNED					     1		0  524288000	    512 	 0 NO  UNASSIGNED
	 0

	22 3395195235					     1	   348480  524288000	    512   32392704 YES ACTIVE	     3.5763E+11 18-JAN-23			   3.5763E+11 18-JAN-23
	 0

	23 UNASSIGNED					     0		0  524288000	    512 	 0 NO  UNASSIGNED
	 0


    GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    CON_ID
----------
	24 UNASSIGNED					     1		0  524288000	    512 	 0 NO  UNASSIGNED
	 0

the problem may be standby log group number is dumplicate between Node Standby 1 and Node Standby 2

so, we change Node Standby 1 group number start at 31 and add standby logfile on Node Standby 1 and Node Standby 2 to same on Primary

check log files are 10 on Primary

SQL> select * from v$log

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	  348471  524288000	   512		2 YES INACTIVE		  3.5763E+11 16-JAN-23	 3.5763E+11 16-JAN-23	       0
	 2	    1	  348479  524288000	   512		2 YES INACTIVE		  3.5763E+11 18-JAN-23	 3.5763E+11 18-JAN-23	       0
	 3	    1	  348480  524288000	   512		2 NO  CURRENT		  3.5763E+11 18-JAN-23	 9.2954E+18		       0
	 4	    1	  348472  524288000	   512		2 YES INACTIVE		  3.5763E+11 16-JAN-23	 3.5763E+11 16-JAN-23	       0
	 5	    1	  348473  524288000	   512		2 YES INACTIVE		  3.5763E+11 16-JAN-23	 3.5763E+11 16-JAN-23	       0
	 6	    1	  348474  524288000	   512		2 YES INACTIVE		  3.5763E+11 16-JAN-23	 3.5763E+11 17-JAN-23	       0
	 7	    1	  348475  524288000	   512		2 YES INACTIVE		  3.5763E+11 17-JAN-23	 3.5763E+11 17-JAN-23	       0
	 8	    1	  348476  524288000	   512		2 YES INACTIVE		  3.5763E+11 17-JAN-23	 3.5763E+11 17-JAN-23	       0
	 9	    1	  348477  524288000	   512		2 YES INACTIVE		  3.5763E+11 17-JAN-23	 3.5763E+11 17-JAN-23	       0
	10	    1	  348478  524288000	   512		2 YES INACTIVE		  3.5763E+11 17-JAN-23	 3.5763E+11 18-JAN-23	       0

add standby logfile group 31 to 40 and drop logfile group 21 to 24 on Node Standby 1

-- Stop Dataguard recover
SQL> alter database recover managed standby database cancel;

-- Add Standby logfile group 31 to 40
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 31 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 32 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 33 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 34 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 35 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 36 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 37 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 38 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 39 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 40 SIZE 500M;

-- Drop Standby logfile group 21 to 24
SQL> alter database drop logfile group 21;
SQL> alter database drop logfile group 22;
SQL> alter database drop logfile group 23;
SQL> alter database drop logfile group 24;

-- Start Dataguard recover
SQL> alter database recover managed standby database using current logfile disconnect;

check alert log is show using online redo on Node Standby 1

2023-01-18T09:38:19.583882+08:00
 rfs (PID:32846): Selected LNO:31 for T-1.S-348481 dbid 3395195235 branch 1112866403
2023-01-18T09:38:20.150738+08:00
PR00 (PID:1741800): Media Recovery Log /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_348480_1112866403.arc
PR00 (PID:1741800): Media Recovery Waiting for T-1.S-348481 (in transit)
2023-01-18T09:38:20.770695+08:00
Recovery of Online Redo Log: Thread 1 Group 31 Seq 348481 Reading mem 0
  Mem# 0: +DATADG/DBDG/ONLINELOG/group_31.262.1126431121
  Mem# 1: +FRADG/DBDG/ONLINELOG/group_31.7895.1126431123

add standby logfile on Node Standby 2

-- Stop Dataguard recover
SQL> alter database recover managed standby database cancel;

-- Add Standby logfile group 25 to 30
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 25 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 26 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 27 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 28 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 29 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 30 SIZE 500M;

-- Start Dataguard recover
SQL> alter database recover managed standby database using current logfile disconnect; 


DGMGRL> show database dbdg1;

Database - DBDG1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 82.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    DB1

Database Status:
SUCCESS

DGMGRL> show database dbdg2;

Database - DBDG2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    DB2

Database Status:
SUCCESS
JohnsonJ

張貼者： JohnsonJ

張貼留言

0 留言