On November 2, 2017, I had blogged about Monitoring Standby – SQLPlus or DGMGRL
Since the post, I do not recall using dgmgrl much for monitoring lag.
Almost 3 years later and 19c, let’s revisit the topic.
Here is what monitoring lag looks like from SQLPlus.
Notice BLOCK# increased which mean transfer is working.
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 175 8540 0
9059 1 1 N/A MRP0 APPLYING_LOG 175 8540 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 175 8554 0
9059 1 1 N/A MRP0 APPLYING_LOG 175 8554 0
SQL>
For 19c, show configuration lag will provide info on lag and is knowing Lag is 0 seconds good enough?
[oracle@ol7-112-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Sep 19 12:50:58 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 9 seconds ago)
Apply Lag: 0 seconds (computed 9 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 20 seconds ago)
DGMGRL> show configuration lag verbose
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 12 seconds ago)
Apply Lag: 0 seconds (computed 12 seconds ago)
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = ''
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
Using SendQEntries shows LOG_SEQ but RecvQEntries does not.
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1047346434 1 175 09/19/2020 12:32:29 2984164 23586
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
DGMGRL>
Disable apply and compare differences between SQLPlus and DGMGRL.
DGMGRL> edit database hawk_stby set state=APPLY-OFF
> ;
Succeeded.
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 8 seconds ago)
Apply Lag: 0 seconds (computed 8 seconds ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
### From Primary:
*** gv$managed_standby ***
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030 1 1 LNS LNS WRITING 180 1311 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030 1 1 LNS LNS WRITING 180 1314 0
SQL>
### From Standby:
*** gv$archived_log ***
DEST_ID THREAD# APPLIED MAX_SEQ MAX_TIME DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
1 1 NO 179 19-SEP-2020 13:12:25 5 39.93333
1 1 YES 174 19-SEP-2020 12:32:29
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 180 284 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 180 298 0
SQL>
From DGMGRL:
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 6 seconds ago)
Apply Lag: 6 minutes 48 seconds (computed 6 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 31 seconds ago)
DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1047346434 1 180 09/19/2020 13:12:25 2992416 270
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
PARTIALLY_APPLIED 1047346434 1 175 09/19/2020 12:32:29 09/19/2020 13:12:16 2984164 2992388 24709
NOT_APPLIED 1047346434 1 176 09/19/2020 13:12:16 09/19/2020 13:12:17 2992388 2992393 1
NOT_APPLIED 1047346434 1 177 09/19/2020 13:12:17 09/19/2020 13:12:20 2992393 2992400 2
NOT_APPLIED 1047346434 1 178 09/19/2020 13:12:20 09/19/2020 13:12:20 2992400 2992403 1
NOT_APPLIED 1047346434 1 179 09/19/2020 13:12:20 09/19/2020 13:12:25 2992403 2992416 3
DGMGRL>
APPLY-ON
DGMGRL> edit database hawk_stby set state=APPLY-ON;
Succeeded.
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 3 seconds ago)
Apply Lag: 0 seconds (computed 3 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL> /
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 4 seconds ago)
Apply Lag: 0 seconds (computed 4 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 6 seconds ago)
Apply Lag: 0 seconds (computed 6 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
It would be nice if show configuration lag is able to provide some high level info frequently asked by management.
What is lag time, how many sequence is the standby behind, what is the apply rate, what is LOG_SEQ at primary and standby?