Using DataGuard broker to show apply lag and throughput
Posted by John Hallas on June 20, 2017
To determine how much lag there is I normally run a script similar to this
select sequence#, applied, to_date(to_char(first_time,’DD-MON-YY:HH24:MI:SS’),
‘DD-MON-YY:HH24:MI:SS’) “First Time” ,
to_char(next_time,’DD-MON-YY:HH24:MI:SS’) “Next Time”
from v$archived_log
UNION
select NULL,database_role,NULL, db_unique_name from v$database
order by “First Time”;
However there is another way which I sometime use which actually gives a lot more information. This uses the dataguard broker command line. Use the show configuration parameter to determine database name if you are not sure
DGMGRL> show configuration Configuration - DR Protection Mode: MaxPerformance Databases: xxxxxx2a - Primary database xxxxxx2b - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Show specific database details.
DGMGRL> show database "xxxxxx2b" Database - xxxxxx2b Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 4 minutes 24 seconds (computed 6 seconds ago) Apply Lag: 5 minutes 16 seconds (computed 0 seconds ago) Apply Rate: 191.08 MByte/s Real Time Query: ON Instance(s): xxxxxx2b1 (apply instance) xxxxxx2b2 xxxxxx2b3 xxxxxx2b4 xxxxxx2b5 xxxxxx2b6 xxxxxx2b7 xxxxxx2b8 Database Status: SUCCESS
Lots of good information there including which node is hosting the MRP process and also the apply rate. In our case that is normally between 150 and 400 MB per second.
Leave a comment