Here is a list of things that I think are important to monitor if you have Data Guard FSFO setup. All the mentioned things are intended for automated monitoring – things to raise alerts on.<\/p>\n\n\n\n
My goal is to get as much information as possible from one place – the primary database. Since the primary database is alwas open and you can always connect your monitoring agent there.<\/p>\n\n\n\n
This is about Oracle 19c (19.16.2 at the time of writing).<\/p>\n\n\n\n
Here I’m also assuming Data Guard protection mode is set to MaxAvailability (or MaxProtection).<\/p>\n\n\n\n
I think the best way to get a quick overview of Data Guard health is to query V$DG_BROKER_CONFIG. It can be done from primary and it gives you a quick status for each destination.<\/p>\n\n\n\n
SYS @ failtesti2:>SELECT database, dataguard_role, enabled, status\n FROM V$DG_BROKER_CONFIG;\n\nDATABASE DATAGUARD_ROLE ENABL STATUS\n------------ ------------------ ----- ----------\nfailtesti1 PHYSICAL STANDBY TRUE 0\nfailtesti2 PRIMARY TRUE 0\nfailtesti3 PHYSICAL STANDBY TRUE 16809<\/code><\/pre>\n\n\n\nSTATUS column is the most interesting. It gives you the current ORA error code for this destination. If it is 0 – ORA-0 means “normal, successful completion”.<\/p>\n\n\n\n
Is FSFO target SYNCHRONIZED?<\/h2>\n\n\n\n
In case Fast-Start Failover is enabled – you need to be alerted if the FSFO target becomes UNSYNCHRONIZED. You can query it from V$DATABASE.<\/p>\n\n\n\n
SYS @ failtesti2:>SELECT FS_FAILOVER_MODE, FS_FAILOVER_STATUS, FS_FAILOVER_CURRENT_TARGET, FS_FAILOVER_THRESHOLD\n FROM V$DATABASE;\n\nFS_FAILOVER_MODE FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD\n------------------- ---------------------- ------------------------------ ---------------------\nZERO DATA LOSS SYNCHRONIZED failtesti1 25\n\n-- After bouncing failtesti1\n\nSYS @ failtesti2:>SELECT FS_FAILOVER_MODE, FS_FAILOVER_STATUS, FS_FAILOVER_CURRENT_TARGET, FS_FAILOVER_THRESHOLD\n FROM V$DATABASE;\n\nFS_FAILOVER_MODE FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD\n------------------- ---------------------- ------------------------------ ---------------------\nZERO DATA LOSS UNSYNCHRONIZED failtesti1 25<\/code><\/pre>\n\n\n\nAlert if FS_FAILOVER_STATUS has been UNSYNCHRONIZED for too long.<\/p>\n\n\n\n
Is Observer connected?<\/h2>\n\n\n\n
In FSFO you also need to alert if observer is no longer present. If there is no observer, FSFO will not happen and you also loose quorum. If both standby database and observer are down – primary databases loses quorum and also shuts down.<\/p>\n\n\n\n
Again, you can query V$DATABASE from the primary database.<\/p>\n\n\n\n
SYS @ failtesti2:>SELECT FS_FAILOVER_MODE, FS_FAILOVER_CURRENT_TARGET, FS_FAILOVER_OBSERVER_PRESENT, FS_FAILOVER_OBSERVER_HOST\n FROM V$DATABASE;\n\nFS_FAILOVER_MODE FS_FAILOVER_CURRENT_TARGET FS_FAIL FS_FAILOVER_OBSERVER_HOST\n------------------- ------------------------------ ------- ------------------------------\nZERO DATA LOSS failtesti1 YES failtest-observer\n\n-- After stopping the observer\nSYS @ failtesti2:>SELECT FS_FAILOVER_MODE, FS_FAILOVER_CURRENT_TARGET, FS_FAILOVER_OBSERVER_PRESENT\n FROM V$DATABASE;\n\nFS_FAILOVER_MODE FS_FAILOVER_CURRENT_TARGET FS_FAIL\n------------------- ------------------------------ -------\nZERO DATA LOSS failtesti1 NO<\/code><\/pre>\n\n\n\nAlert if FS_FAILOVER_OBSERVER_PRESENT has been NO for too long.<\/p>\n\n\n\n
Is the protection mode as intended?<\/h2>\n\n\n\n
Good to monitor to avoid DBA human errors. Maybe a DBA lowered the protection mode and forgot to reset it back to the original value. Again this information is available on V$DATABASE. Probably not needed, but it is an easy and check check.<\/p>\n\n\n\n
SYS @ failtesti2:>SELECT PROTECTION_MODE, PROTECTION_LEVEL\n FROM V$DATABASE;\n\nPROTECTION_MODE PROTECTION_LEVEL\n-------------------- --------------------\nMAXIMUM AVAILABILITY MAXIMUM AVAILABILITY<\/code><\/pre>\n\n\n\nMonitoring Apply and Transport lag<\/h2>\n\n\n\n
Probably this is one of the most asked about things to monitor about Data Guard. And the obvious way to do it is via Data Guard Broker.<\/p>\n\n\n\n
First – you have ApplyLagThreshold and TransportLagThreshold properties for each database and if that is breached, Data Guard Broker will raise an alarm – the database status will change.<\/p>\n\n\n\n
-- On my standby database I have ApplyLagThreshold and TransportLagThreshold set\nDGMGRL> show database failtesti3 ApplyLagThreshold;\n ApplyLagThreshold = '30'\nDGMGRL> show database failtesti3 TransportLagThreshold;\n TransportLagThreshold = '30'\n\n-- I do breach both of the thresholds\nDGMGRL> show database failtesti3;\n\nDatabase - failtesti3\n\n Role: PHYSICAL STANDBY\n Intended State: APPLY-ON\n Transport Lag: 1 minute 38 seconds (computed 49 seconds ago)\n Apply Lag: 1 minute 38 seconds (computed 49 seconds ago)\n Average Apply Rate: 6.00 KByte\/s\n Real Time Query: ON\n Instance(s):\n failtesti3\n\n Database Warning(s):\n ORA-16853: apply lag has exceeded specified threshold\n ORA-16855: transport lag has exceeded specified threshold\n ORA-16857: member disconnected from redo source for longer than specified threshold\n\nDatabase Status:\nWARNING\n\n-- If I query V$DG_BROKER_CONFIG from PRIMARY the status reflects that\nSYS @ failtesti2:>SELECT database, dataguard_role, status\n FROM V$DG_BROKER_CONFIG;\n\nDATABASE DATAGUARD_ROLE STATUS\n--------------- ------------------ ----------\nfailtesti1 PHYSICAL STANDBY 0\nfailtesti2 PRIMARY 0\nfailtesti3 PHYSICAL STANDBY 16809\n\n$ oerr ora 16809\n16809, 00000, \"multiple warnings detected for the member\"\n\/\/ *Cause: The broker detected multiple warnings for the member.\n\/\/ *Action: To get a detailed status report, check the status of the member\n\/\/ specified using either Enterprise Manager or the DGMGRL CLI SHOW\n\/\/ command.\n\n-- If I fix the transport lag\nSYS @ failtesti2:>SELECT database, dataguard_role, status\n FROM V$DG_BROKER_CONFIG;\n\nDATABASE DATAGUARD_ROLE STATUS\n--------------- ------------------ ----------\nfailtesti1 PHYSICAL STANDBY 0\nfailtesti2 PRIMARY 0\nfailtesti3 PHYSICAL STANDBY 16853\n\n$ oerr ora 16853\n16853,0000, \"apply lag has exceeded specified threshold\"\n\/\/ *Cause: The current apply lag exceeded the value specified by the\n\/\/ ApplyLagThreshold configurable property. It may be caused either by\n\/\/ a large transport lag or poor performance of apply services on the \n\/\/ standby database.\n\/\/ *Action: Check for gaps on the standby database. If no gap is present, tune\n\/\/ the apply services.<\/code><\/pre>\n\n\n\nReally good for monitoring and the monitoring agent only needs to connect to the primary database. If status != 0, we have a problem.<\/p>\n\n\n\n
OK good, but that will only tell you that the threshold was breached – but how big is the lag actually? Managers like charts. If you can query from standby database, V$DATAGUARD_STATS is your friend.<\/p>\n\n\n\n
-- Has to be executed from standby database\nWITH\n FUNCTION interval_to_seconds(p_int interval day to second) RETURN number\n DETERMINISTIC\n IS\n BEGIN\n -- Converts interval to seconds\n RETURN \n extract(day from p_int)*86400+\n extract(hour from p_int)*3600+\n extract(minute from p_int)*60+\n extract(second from p_int);\n END;\nSELECT name, interval_to_seconds(to_dsinterval(value)) lag_s\nFROM v$dataguard_stats\nWHERE name IN ('transport lag','apply lag')<\/code><\/pre>\n\n\n\nAnother view to get more details about the apply process is V$RECOVERY_PROGRESS. Again have to query it from the standby database itself. You can see apply rates for example from there.<\/p>\n\n\n\n
What if you can not (do not want to) connect to (each) standby database, maybe it is in MOUNTED mode (I object to connecting remotely as SYS)? How to get standby lag querying from primary database?<\/p>\n\n\n\n
Documented option is to query V$ARCHIVE_DEST, not great and does not work for cascaded destinations.<\/p>\n\n\n\n
SYS @ failtesti2:>SELECT dest_id, dest_name, applied_scn, scn_to_timestamp(applied_scn)\n FROM v$archive_dest\n WHERE status = 'VALID' and applied_scn > 0;\n\n DEST_ID DEST_NAME APPLIED_SCN SCN_TO_TIMESTAMP(APPLIED_SCN)\n---------- -------------------- ----------- -----------------------------\n 2 LOG_ARCHIVE_DEST_2 409036447 2023-03-11 07:40:00\n 3 LOG_ARCHIVE_DEST_3 409173280 2023-03-11 07:41:27<\/code><\/pre>\n\n\n\nNot really great for monitoring.<\/p>\n\n\n\n
Is there a possibility to query Broker data from primary database? Currently not in a documented way.<\/p>\n\n\n\n
-- Query from Broker. NB! DBMS_DRS is currently undocumented.\n-- X$DRC is X$, so it will never be documented, and you can only query it as SYS\/SYSDG.\n\nSYS @ failtesti2:>SELECT value database_name, dbms_drs.get_property_obj(object_id, 'ApplyLag') apply_lag\n FROM x$drc\n WHERE attribute='DATABASE' and value != 'failtesti2';\n\nDATABASE_NAM APPLY_LAG\n------------ ------------\nfailtesti1 0 0\nfailtesti3 1174 40\n\n-- As a comparison\nSYS @ failtesti2:>SELECT dest_id, dest_name, applied_scn, cast(systimestamp as timestamp) - scn_to_timestamp(applied_scn) lag\n FROM v$archive_dest\n WHERE status = 'VALID' and applied_scn > 0;\n\n DEST_ID DEST_NAME APPLIED_SCN LAG\n---------- -------------------- ----------- ------------------------------\n 2 LOG_ARCHIVE_DEST_2 409036447 +000000000 00:20:13.863936000\n 3 LOG_ARCHIVE_DEST_3 411005382 +000000000 00:00:40.863936000\n\n-- Comparing these numbers it seems to me that \"1174 40\" should be interpreted as \"lag_in_seconds measurement_age_in_seconds\"\n-- So failtesti3 had 1174 seconds of lag measured 40 seconds ago\n\n-- The same also works for TransportLag\nSYS @ failtesti2:>SELECT value database_name, dbms_drs.get_property_obj(object_id, 'TransportLag') transport_lag\n FROM x$drc\n WHERE attribute='DATABASE' and value != 'failtesti2';\n\nDATABASE_NAM TRANSPORT_LAG\n------------ -------------\nfailtesti1 0 0\nfailtesti3 107 56<\/code><\/pre>\n\n\n\nAlso – my favourite actually – if you have Active Data Guard licence and the standby is open – just have a pinger job in primary database updating a row every minute (or 30s??) to the current UTC timestamp sys_extract_utc(systimestamp). And have your monitoring agent check on the standby side how old that timestamp is. It is my favourite, because it is a true end-to-end check, it does not depend on any dark unseen Data Guard inner workings and wonderings if the reported numbers by Broker are correct.<\/p>\n\n\n\n
Is MRP running (the apply process)?<\/h2>\n\n\n\n
Monitoring lag via Broker is great, but if by DBA accident you set APPLY-OFF for a maintenance – and forget to start it again. Since administrator has told Broker that APPLY should be OFF, Broker will not raise ApplyLag warnings anymore. So if Failover happens – it will be very slow, since it also needs to apply all the missing logs.<\/p>\n\n\n\n
-- To demonstrate the problem\n\nDGMGRL> show database failtesti3 ApplyLagThreshold;\n ApplyLagThreshold = '30'\n\n-- ApplyLagThreshold is 30s and current Apply Lag is 25 min, but status is SUCCESS!\n-- This is because Intended State: APPLY-OFF\nDGMGRL> show database failtesti3;\n\nDatabase - failtesti3\n\n Role: PHYSICAL STANDBY\n Intended State: APPLY-OFF\n Transport Lag: 0 seconds (computed 1 second ago)\n Apply Lag: 25 minutes 3 seconds (computed 1 second ago)\n Average Apply Rate: (unknown)\n Real Time Query: OFF\n Instance(s):\n failtesti3\n\nDatabase Status:\nSUCCESS\n\n-- Same from primary, failtesti3 has status=0\nSYS @ failtesti2:> SELECT database, dataguard_role, enabled, status\n FROM V$DG_BROKER_CONFIG;\n\nDATABASE DATAGUARD_ROLE ENABL STATUS\n------------ ------------------ ----- ----------\nfailtesti1 PHYSICAL STANDBY TRUE 0\nfailtesti2 PRIMARY TRUE 0\nfailtesti3 PHYSICAL STANDBY TRUE 0\n\n-- Sure will still show Apply Lag from Broker\nSYS @ failtesti2:> SELECT value database_name, dbms_drs.get_property_obj(object_id, 'ApplyLag') apply_lag\n FROM x$drc\n WHERE attribute='DATABASE' and value = 'failtesti3';\n\nDATABASE_NAM APPLY_LAG\n------------ ------------\nfailtesti3 1792 0<\/code><\/pre>\n\n\n\nHow to check that all targets are applying? One option is to actually check on standby side, if MRP0 process is running.<\/p>\n\n\n\n
SYS @ failtesti3:>SELECT process\n FROM V$MANAGED_STANDBY\n WHERE process = 'MRP0';\n\nPROCESS\n---------\nMRP0\n\n-- I shut down apply\nDGMGRL> edit database failtesti3 set state='apply-off';\nSucceeded.\n\n-- No MRP0 anymore\nSYS @ failtesti3:>SELECT process FROM V$MANAGED_STANDBY WHERE process = 'MRP0';\n\nno rows selected<\/code><\/pre>\n\n\n\nBut for automated monitoring I don’t really like to connect to each standby, especially if no Active Data Guard is in use. How to get it from primary? It gets a little tricky.<\/p>\n\n\n\n
-- To check the intended state, could also go for the unsupported route.\n-- APPLY-READY means APPLY-OFF\nSYS @ failtesti2:>SELECT value database_name, dbms_drs.get_property_obj(object_id, 'intended_state') intended_state\n FROM x$drc\n WHERE attribute='DATABASE' and value = 'failtesti3';\n\nDATABASE_NAM INTENDED_STATE\n------------ --------------------\nfailtesti3 PHYSICAL-APPLY-READY\n\n-- After turning APPLY-ON\nDGMGRL> edit database failtesti3 set state='apply-on';\nSucceeded.\nSYS @ failtesti2:>SELECT value database_name, dbms_drs.get_property_obj(object_id, 'intended_state') intended_state\n FROM x$drc\n WHERE attribute='DATABASE' and value = 'failtesti3';\n\nDATABASE_NAM INTENDED_STATE\n------------ --------------------\nfailtesti3 PHYSICAL-APPLY-ON<\/code><\/pre>\n\n\n\nHere could check that Broker has the intended state correct for all standby databases.<\/p>\n\n\n\n
Broker also has export configuration option, that could possibly be used for some automated checks. It is a documented DGMGRL command, but the resulting XML file is placed under database trace directory and the contents of that XML file are not documented.<\/p>\n\n\n\n
[oracle@failtest-2 ~]$ dgmgrl \/ \"export configuration to auto-dg-check.xml\"\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Mar 11 09:04:17 2023\nVersion 19.16.2.0.0\n\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nWelcome to DGMGRL, type \"help\" for information.\nConnected to \"FAILTESTI2\"\nConnected as SYSDG.\nSucceeded.\n\n-- The result is an XML file with all interesting Broker configurations that could be used for automated monitoring\n\n[oracle@failtest-2 ~]$ cat \/u01\/app\/oracle\/diag\/rdbms\/failtesti2\/failtesti2\/trace\/auto-dg-check.xml\n\n...\n <Member MemberID=\"1\" CurrentPath=\"True\" Enabled=\"True\" MultiInstanced=\"True\" Name=\"failtesti1\">\n <DefaultState>STANDBY<\/DefaultState>\n <IntendedState>STANDBY<\/IntendedState>\n <Status>\n <Severity>Success<\/Severity>\n <Error>0<\/Error>\n <Timestamp>1678525406<\/Timestamp>\n <\/Status>\n <Role>\n <Condition>STANDBY<\/Condition>\n <DefaultState>PHYSICAL-APPLY-ON<\/DefaultState>\n <IntendedState>PHYSICAL-APPLY-ON<\/IntendedState>\n <\/Role>\n...\n <\/Member>\n...\n <Member MemberID=\"3\" CurrentPath=\"True\" Enabled=\"True\" MultiInstanced=\"True\" Name=\"failtesti3\">\n <Status>\n <Severity>Success<\/Severity>\n <Error>0<\/Error>\n <Timestamp>1678525396<\/Timestamp>\n <\/Status>\n <Role>\n <Condition>STANDBY<\/Condition>\n <DefaultState>PHYSICAL-APPLY-ON<\/DefaultState>\n <IntendedState>PHYSICAL-APPLY-READY<\/IntendedState>\n <\/Role>\n...\n <\/Member>\n...<\/code><\/pre>\n\n\n\nRecovery Area<\/h2>\n\n\n\n
Need to cover also the basics – like Recovery Area usage. When using Data Guard you have most likely set ArchivelogDeletionPolicy to Applied On (All) Standby… or shipped, so if any of the standby databases fall behind, the recovery are on primary (or other standby databases) will also start to grow. Keep an eye on that.<\/p>\n\n\n\n
Useful views: V$RECOVERY_FILE_DEST (or V$RECOVERY_AREA_USAGE to see the breakdown into individual consumers).<\/p>\n\n\n\n
SELECT sys_extract_utc(systimestamp) time\n , name recovery_dest_location\n , round(space_limit\/1024\/1024\/1024) size_gb\n , round((space_used-space_reclaimable)\/1024\/1024\/1024) used_gib\n , round((space_used-space_reclaimable)*100\/space_limit) used_pct\nFROM V$RECOVERY_FILE_DEST<\/code><\/pre>\n\n\n\nConnecting to the alert.log topic below… if you have been slow to react on the proactive recovery area check above, always good to keep mining alert.log for ORA-19809. If that is seen, raise the highest immediate alert – recovery area is full and archiving is stopped.<\/p>\n\n\n\n
Alert.log<\/h2>\n\n\n\n