![\"\"](\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2024\/01\/dg23c-new-views.png\")
<\/a>V$DG_BROKER_ROLE_CHANGE<\/b><\/p>\nThis view tracks the last role transitions that occurred in the configuration. Example:<\/p>\n
SQL> select * from v$dg_broker_role_change;\r\n\r\nEVENT STANDBY_TYPE OLD_PRIMARY NEW_PRIMARY FS_FAILOVER_REASON BEGIN_TIME END_TIME CON_ID\r\n_____________ _______________ _________________ _________________ _____________________ __________________________________ __________________________________ _________\r\nSwitchover Physical adghol_53k_lhr adghol_p4n_lhr 18-DEC-23 10.40.12.000000000 AM 18-DEC-23 10.40.32.000000000 AM 0\r\nSwitchover Physical adghol_p4n_lhr adghol_53k_lhr 18-DEC-23 10.48.55.000000000 AM 18-DEC-23 10.49.15.000000000 AM 0\r\n<\/pre>\nThe event might be a Switchover, Failover, or Fast-Start Failover.<\/p>\n
In the case of Fast-Start Failover, you will see the reason (typically “Primary Disconnected” if it comes from the observer, or whatever reason you put in DBMS_DG.INITIATE_FS_FAILOVER.<\/p>\n
No more need to analyze the logs to find out which database was primary at any moment in time!<\/p>\n
V$DG_BROKER_PROPERTY<\/h2>\n
Before 23c, the only possible way to get a broker property from SQL was to use undocumented (unsupported) procedures in the fixed package DBMS_DRS. I’ve blogged about it in the past<\/a>, before joining Oracle.<\/p>\n
Now, it’s as easy as selecting from a view, where you can get the properties per member or per configuration:<\/p>\n
SQL> select member, property, value from V$DG_BROKER_PROPERTY where value is not null;\r\n\r\nMEMBER PROPERTY VALUE\r\n___________ _______________________________ _________\r\nmydb FastStartFailoverThreshold 180\r\nmydb OperationTimeout 30\r\nmydb TraceLevel USER\r\nmydb FastStartFailoverLagLimit 300\r\nmydb CommunicationTimeout 180\r\nmydb ObserverReconnect 0\r\nmydb ObserverPingInterval 0\r\nmydb ObserverPingRetry 0\r\nmydb FastStartFailoverAutoReinstate TRUE\r\nmydb FastStartFailoverPmyShutdown TRUE\r\n...\r\nmydb_site1 DGConnectIdentifier mydb_site1\r\nmydb_site1 FastStartFailoverTarget mydb_site2\r\nmydb_site1 LogShipping ON\r\nmydb_site1 LogXptMode ASYNC\r\nmydb_site1 DelayMins 0\r\n...\r\nmydb_site1 StaticConnectIdentifier (DESCRIPTION=<...>)))\r\nmydb_site1 TopWaitEvents (monitor)\r\nmydb_site1 SidName (monitor)\r\nmydb_site2 DGConnectIdentifier mydb_site2\r\nmydb_site2 FastStartFailoverTarget mydb_site1\r\n<\/pre>\nThe example selects just three columns, but the view is rich in detailing which properties apply to which situation (scope, valid_role):<\/p>\n
SQL> set sqlformat json-formatted\r\nSQL> select * from v$dg_broker_property where member='adghol_p4n_lhr' and upper(property) like '%REDO%';\r\n{\r\n \"results\" : [\r\n {\r\n ...\r\n \"items\" : [\r\n {\r\n \"member\" : \"adghol_p4n_lhr\",\r\n \"instance\" : \"N\/A\",\r\n \"dataguard_role\" : \"PHYSICAL STANDBY\",\r\n \"property\" : \"PreferredObserverHosts\",\r\n \"property_type\" : \"CONFIGURABLE\",\r\n \"value\" : \"\",\r\n \"value_type\" : \"STRING\",\r\n \"scope\" : \"MEMBER\",\r\n \"valid_role\" : \"N\/A\",\r\n \"con_id\" : 0\r\n },\r\n {\r\n \"member\" : \"adghol_p4n_lhr\",\r\n \"instance\" : \"N\/A\",\r\n \"dataguard_role\" : \"PHYSICAL STANDBY\",\r\n \"property\" : \"RedoRoutes\",\r\n \"property_type\" : \"CONFIGURABLE\",\r\n \"value\" : \"\",\r\n \"value_type\" : \"STRING\",\r\n \"scope\" : \"MEMBER\",\r\n \"valid_role\" : \"N\/A\",\r\n \"con_id\" : 0\r\n },\r\n {\r\n \"member\" : \"adghol_p4n_lhr\",\r\n \"instance\" : \"N\/A\",\r\n \"dataguard_role\" : \"PHYSICAL STANDBY\",\r\n \"property\" : \"RedoCompression\",\r\n \"property_type\" : \"CONFIGURABLE\",\r\n \"value\" : \"DISABLE\",\r\n \"value_type\" : \"STRING\",\r\n \"scope\" : \"MEMBER\",\r\n \"valid_role\" : \"STANDBY\",\r\n \"con_id\" : 0\r\n }\r\n ]\r\n }\r\n ]\r\n}\r\n<\/pre>\nThe monitorable properties can be monitored using DBMS_DG.GET_PROPERTY(). I’ll write a blog post about the new PL\/SQL APIs in the upcoming weeks.<\/p>\n
I wish I had this view when I was a DBA \ud83d\ude42<\/p>\n
V$FAST_START_FAILOVER_CONFIG<\/b><\/h2>\n
If you have a Fast-Start Failover configuration, this view will show its details:<\/p>\n
SQL> SELECT fsfo_mode, status, current_target, threshold, observer_present, observer_host,\r\n 2> protection_mode, lag_limit, auto_reinstate, observer_override, shutdown_primary FROM V$FAST_START_FAILOVER_CONFIG;\r\n\r\nFSFO_MODE STATUS CURRENT_TARGET THRESHOLD OBSERVE OBSERVER_HOST PROTECTION_MODE LAG_LIMIT AUTO_ OBSER SHUTD\r\n___________________ ______________________ ______________ _________ _______ _____________ ________________ _________ _____ _____ _____\r\nPOTENTIAL DATA LOSS TARGET UNDER LAG LIMIT mydb_site2 180 YES mydb-obs MaxPerformance 300 TRUE FALSE TRUE \r\n<\/pre>\nThis view replaces some columns currently in v$database, that are therefore deprecated:<\/p>\n
SQL> desc v$database\r\n\r\nName Null? Type\r\n_______________________________ ________ ________________\r\n...\r\nFS_FAILOVER_MODE VARCHAR2(19)\r\nFS_FAILOVER_STATUS VARCHAR2(22)\r\nFS_FAILOVER_CURRENT_TARGET VARCHAR2(30)\r\nFS_FAILOVER_THRESHOLD NUMBER\r\nFS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)\r\nFS_FAILOVER_OBSERVER_HOST VARCHAR2(512)\r\n...<\/pre>\nV$FS_LAG_HISTOGRAM<\/b><\/h2>\n
This view is useful to calculate the optimal FastStartFailoverLagTime.<\/p>\n
SQL> select * from v$fs_lag_histogram;\r\n\r\n THREAD# LAG_TYPE LAG_TIME LAG_COUNT LAST_UPDATE_TIME CON_ID \r\n---------- ----------- ---------- ---------- -------------------- ---------- \r\n 1 APPLY 5 122 01\/23\/2023 10:46:07 0 \r\n 1 APPLY 10 5 01\/02\/2023 16:12:42 0 \r\n 1 APPLY 15 2 12\/25\/2022 12:01:23 0 \r\n 1 APPLY 30 0 0 \r\n 1 APPLY 60 0 0 \r\n 1 APPLY 120 0 0 \r\n 1 APPLY 180 0 0 \r\n 1 APPLY 300 0 0 \r\n 1 APPLY 65535 0 0 \r\n<\/pre>\nIt shows the frequency of Fast-Start Failover lags and the most recent occurrence for each bucket.<\/p>\n
LAG_TIME is the upper bound of the bucket, e.g.<\/p>\n
\n
- 5 -> between 0 and 5 seconds<\/li>\n
- 10 -> between 5 and 10 seconds<\/li>\n
- etc.<\/li>\n<\/ul>\n
It’s refreshed every minute, only when Fast-Start Failover is enabled (also in observe-only mode).<\/p>\n
V$FS_FAILOVER_OBSERVERS<\/h2>\n
This view is not new, however, its definition now contains more columns:<\/p>\n
SQL> desc v$fs_failover_observers\r\n Name Null? Type\r\n ------------------------------ -------- -----------------\r\n NAME VARCHAR2(513)\r\n REGISTERED VARCHAR2(4)\r\n HOST VARCHAR2(513)\r\n ISMASTER VARCHAR2(4)\r\n TIME_SELECTED TIMESTAMP(9)\r\n PINGING_PRIMARY VARCHAR2(4)\r\n PINGING_TARGET VARCHAR2(4)\r\n CON_ID NUMBER\r\n \r\n -- new in 23c:\r\n LAST_PING_PRIMARY NUMBER\r\n LAST_PING_TARGET NUMBER\r\n LOG_FILE VARCHAR2(513)\r\n STATE_FILE VARCHAR2(513)\r\n CURRENT_TIME TIMESTAMP(9)<\/pre>\nThis gives important additional information about the observers, for example, the last time a specific observer was able to ping the primary or the target (in seconds).<\/p>\n
Also, the path of the log file and runtime data file are available, making it easier to find them on the observer host in case of a problem.<\/p>\n
Conclusion<\/h2>\n
These new views should greatly improve the experience when monitoring or diagnosing problems with Data Guard. But they are just a part of many improvements we introduced in 23c. Stay tuned for more \ud83d\ude42<\/p>\n
—<\/p>\n
Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle Data Guard 23c comes with many nice improvements for observability, which greatly increase the usability of Data Guard in environments with a high level of automation. For the 23c version, we have the following new views.V$DG_BROKER_ROLE_CHANGE This view tracks … Continue reading