DBA survival BLOG

DBA stuff and Oracle Data Guard

SHOW CONFIGURATION VERBOSE changes in 23.9

Posted on August 14, 2025 by Ludovico

Traditionally, the DGMGRL command SHOW CONFIGURATION VERBOSE not only retrieved detailed configuration information but also triggered a health check. The health check operation can be resource-intensive and time-consuming, especially when executed repeatedly across multiple database instances or as part of automated workflows.

Starting with Oracle 23.9 (and planned also for a future 19c Release Update), the behavior of SHOW CONFIGURATION VERBOSE changes with the introduction of the following fix:

Bug 37829413 – ‘SHOW CONFIGURATION VERBOSE’ UNNECESSARILY TRIGGERS A FORCED HEALTH CHECK

You can check it yourself in the Oracle Release Analyzer Diff Utility:

https://oradiff.oraclecorp.com/ords/r/oradiff/oradiff/search-fixes

Previous behavior

Each use of SHOW CONFIGURATION VERBOSE triggered a fresh, full health check before showing configuration details, regardless of whether up-to-date health information was needed.

New behavior

The command now returns comprehensive configuration details and property values without forcing an immediate health check.

Why this change?

This change eliminates unnecessary resource usage and network communication, improving performance especially in automated systems that repeatedly gather configuration info, such as Oracle TFA or custom scripts. The goal is to make monitoring and troubleshooting more efficient.

What’s the impact for me?

When you execute SHOW CONFIGURATION, at the bottom you see when the last health check was executed:

Configuration Status:
SUCCESS (status updated 32 seconds ago)

1 2	Configuration Status: SUCCESS (status updated 32 seconds ago)

The health check is scheduled automatically every minute.

When there was a warning, it was common to execute “SHOW CONFIGURATION VERBOSE” to force a refresh of the status and get the most recent status. This won’t work anymore, and you’ll have to wait until the next scheduled health check.

In Oracle 23ai, you can still force a health check explicitly with:

SELECT dbms_dg.health_check FROM dual;

1	SELECT dbms_dg.health_check FROM dual;

Remember, avoid running it unless you are in an emergency!

—

Ludovico

New views in Oracle Data Guard 23c

Posted on January 3, 2024 by Ludovico

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 the last role transitions that occurred in the configuration. Example:

SQL> select * from v$dg_broker_role_change;

EVENT         STANDBY_TYPE    OLD_PRIMARY       NEW_PRIMARY       FS_FAILOVER_REASON    BEGIN_TIME                         END_TIME                              CON_ID
_____________ _______________ _________________ _________________ _____________________ __________________________________ __________________________________ _________
Switchover    Physical        adghol_53k_lhr    adghol_p4n_lhr                          18-DEC-23 10.40.12.000000000 AM    18-DEC-23 10.40.32.000000000 AM            0
Switchover    Physical        adghol_p4n_lhr    adghol_53k_lhr                          18-DEC-23 10.48.55.000000000 AM    18-DEC-23 10.49.15.000000000 AM            0

SQL> select * from v$dg_broker_role_change;

EVENT STANDBY_TYPE OLD_PRIMARY NEW_PRIMARY FS_FAILOVER_REASON BEGIN_TIME END_TIME CON_ID

_____________ _______________ _________________ _________________ _____________________ __________________________________ __________________________________ _________

Switchover Physical adghol_53k_lhr adghol_p4n_lhr 18-DEC-23 10.40.12.000000000 AM 18-DEC-23 10.40.32.000000000 AM 0

Switchover Physical adghol_p4n_lhr adghol_53k_lhr 18-DEC-23 10.48.55.000000000 AM 18-DEC-23 10.49.15.000000000 AM 0

The event might be a Switchover, Failover, or Fast-Start Failover.

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.

No more need to analyze the logs to find out which database was primary at any moment in time!

V$DG_BROKER_PROPERTY

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, before joining Oracle.

Now, it’s as easy as selecting from a view, where you can get the properties per member or per configuration:

SQL> select member, property, value from V$DG_BROKER_PROPERTY where value is not null;

MEMBER      PROPERTY                        VALUE
___________ _______________________________ _________
mydb        FastStartFailoverThreshold      180
mydb        OperationTimeout                30
mydb        TraceLevel                      USER
mydb        FastStartFailoverLagLimit       300
mydb        CommunicationTimeout            180
mydb        ObserverReconnect               0
mydb        ObserverPingInterval            0
mydb        ObserverPingRetry               0
mydb        FastStartFailoverAutoReinstate  TRUE
mydb        FastStartFailoverPmyShutdown    TRUE
...
mydb_site1  DGConnectIdentifier             mydb_site1
mydb_site1  FastStartFailoverTarget         mydb_site2
mydb_site1  LogShipping                     ON
mydb_site1  LogXptMode                      ASYNC
mydb_site1  DelayMins                       0
...
mydb_site1  StaticConnectIdentifier         (DESCRIPTION=<...>)))
mydb_site1  TopWaitEvents                   (monitor)
mydb_site1  SidName                         (monitor)
mydb_site2  DGConnectIdentifier             mydb_site2
mydb_site2  FastStartFailoverTarget         mydb_site1

SQL> select member, property, value from V$DG_BROKER_PROPERTY where value is not null;

MEMBER PROPERTY VALUE

___________ _______________________________ _________

mydb FastStartFailoverThreshold 180

mydb OperationTimeout 30

mydb TraceLevel USER

mydb FastStartFailoverLagLimit 300

mydb CommunicationTimeout 180

mydb ObserverReconnect 0

mydb ObserverPingInterval 0

mydb ObserverPingRetry 0

mydb FastStartFailoverAutoReinstate TRUE

mydb FastStartFailoverPmyShutdown TRUE

...

mydb_site1 DGConnectIdentifier mydb_site1

mydb_site1 FastStartFailoverTarget mydb_site2

mydb_site1 LogShipping ON

mydb_site1 LogXptMode ASYNC

mydb_site1 DelayMins 0

...

mydb_site1 StaticConnectIdentifier (DESCRIPTION=<...>)))

mydb_site1 TopWaitEvents (monitor)

mydb_site1 SidName (monitor)

mydb_site2 DGConnectIdentifier mydb_site2

mydb_site2 FastStartFailoverTarget mydb_site1

The example selects just three columns, but the view is rich in detailing which properties apply to which situation (scope, valid_role):

SQL> set sqlformat json-formatted
SQL> select * from v$dg_broker_property where member='adghol_p4n_lhr' and upper(property) like '%REDO%';
{
  "results" : [
    {
    ...
      "items" : [
        {
          "member" : "adghol_p4n_lhr",
          "instance" : "N/A",
          "dataguard_role" : "PHYSICAL STANDBY",
          "property" : "PreferredObserverHosts",
          "property_type" : "CONFIGURABLE",
          "value" : "",
          "value_type" : "STRING",
          "scope" : "MEMBER",
          "valid_role" : "N/A",
          "con_id" : 0
        },
        {
          "member" : "adghol_p4n_lhr",
          "instance" : "N/A",
          "dataguard_role" : "PHYSICAL STANDBY",
          "property" : "RedoRoutes",
          "property_type" : "CONFIGURABLE",
          "value" : "",
          "value_type" : "STRING",
          "scope" : "MEMBER",
          "valid_role" : "N/A",
          "con_id" : 0
        },
        {
          "member" : "adghol_p4n_lhr",
          "instance" : "N/A",
          "dataguard_role" : "PHYSICAL STANDBY",
          "property" : "RedoCompression",
          "property_type" : "CONFIGURABLE",
          "value" : "DISABLE",
          "value_type" : "STRING",
          "scope" : "MEMBER",
          "valid_role" : "STANDBY",
          "con_id" : 0
        }
      ]
    }
  ]
}

SQL> set sqlformat json-formatted

SQL> select * from v$dg_broker_property where member='adghol_p4n_lhr' and upper(property) like '%REDO%';

{

"results" : [

{

...

"items" : [

{

"member" : "adghol_p4n_lhr",

"instance" : "N/A",

"dataguard_role" : "PHYSICAL STANDBY",

"property" : "PreferredObserverHosts",

"property_type" : "CONFIGURABLE",

"value" : "",

"value_type" : "STRING",

"scope" : "MEMBER",

"valid_role" : "N/A",

"con_id" : 0

{

"member" : "adghol_p4n_lhr",

"instance" : "N/A",

"dataguard_role" : "PHYSICAL STANDBY",

"property" : "RedoRoutes",

"property_type" : "CONFIGURABLE",

"value" : "",

"value_type" : "STRING",

"scope" : "MEMBER",

"valid_role" : "N/A",

"con_id" : 0

{

"member" : "adghol_p4n_lhr",

"instance" : "N/A",

"dataguard_role" : "PHYSICAL STANDBY",

"property" : "RedoCompression",

"property_type" : "CONFIGURABLE",

"value" : "DISABLE",

"value_type" : "STRING",

"scope" : "MEMBER",

"valid_role" : "STANDBY",

"con_id" : 0

}

]

}

]

}

The 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.

I wish I had this view when I was a DBA 🙂

V$FAST_START_FAILOVER_CONFIG

If you have a Fast-Start Failover configuration, this view will show its details:

SQL> SELECT fsfo_mode, status, current_target, threshold, observer_present, observer_host,
 2> protection_mode, lag_limit, auto_reinstate, observer_override, shutdown_primary FROM V$FAST_START_FAILOVER_CONFIG;

FSFO_MODE           STATUS                 CURRENT_TARGET THRESHOLD OBSERVE OBSERVER_HOST PROTECTION_MODE  LAG_LIMIT AUTO_ OBSER SHUTD
___________________ ______________________ ______________ _________ _______ _____________ ________________ _________ _____ _____ _____
POTENTIAL DATA LOSS TARGET UNDER LAG LIMIT mydb_site2           180 YES     mydb-obs      MaxPerformance         300 TRUE  FALSE TRUE

SQL> SELECT fsfo_mode, status, current_target, threshold, observer_present, observer_host,

2> protection_mode, lag_limit, auto_reinstate, observer_override, shutdown_primary FROM V$FAST_START_FAILOVER_CONFIG;

FSFO_MODE STATUS CURRENT_TARGET THRESHOLD OBSERVE OBSERVER_HOST PROTECTION_MODE LAG_LIMIT AUTO_ OBSER SHUTD

___________________ ______________________ ______________ _________ _______ _____________ ________________ _________ _____ _____ _____

POTENTIAL DATA LOSS TARGET UNDER LAG LIMIT mydb_site2 180 YES mydb-obs MaxPerformance 300 TRUE FALSE TRUE

This view replaces some columns currently in v$database, that are therefore deprecated:

SQL> desc v$database

Name                            Null?    Type
_______________________________ ________ ________________
...
FS_FAILOVER_MODE                         VARCHAR2(19)
FS_FAILOVER_STATUS                       VARCHAR2(22)
FS_FAILOVER_CURRENT_TARGET               VARCHAR2(30)
FS_FAILOVER_THRESHOLD                    NUMBER
FS_FAILOVER_OBSERVER_PRESENT             VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST                VARCHAR2(512)
...

SQL> desc v$database

Name Null? Type

_______________________________ ________ ________________

...

FS_FAILOVER_MODE VARCHAR2(19)

FS_FAILOVER_STATUS VARCHAR2(22)

FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)

FS_FAILOVER_THRESHOLD NUMBER

FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)

FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

...

V$FS_LAG_HISTOGRAM

This view is useful to calculate the optimal FastStartFailoverLagTime.

SQL> select * from v$fs_lag_histogram;

   THREAD# LAG_TYPE      LAG_TIME  LAG_COUNT LAST_UPDATE_TIME         CON_ID                        
---------- ----------- ---------- ---------- -------------------- ----------                        
         1 APPLY                5        122 01/23/2023 10:46:07           0                        
         1 APPLY               10          5 01/02/2023 16:12:42           0                        
         1 APPLY               15          2 12/25/2022 12:01:23           0                        
         1 APPLY               30          0                               0                        
         1 APPLY               60          0                               0                        
         1 APPLY              120          0                               0                        
         1 APPLY              180          0                               0                        
         1 APPLY              300          0                               0                        
         1 APPLY            65535          0                               0

SQL> select * from v$fs_lag_histogram;

THREAD# LAG_TYPE LAG_TIME LAG_COUNT LAST_UPDATE_TIME CON_ID

---------- ----------- ---------- ---------- -------------------- ----------

1 APPLY 5 122 01/23/2023 10:46:07 0

1 APPLY 10 5 01/02/2023 16:12:42 0

1 APPLY 15 2 12/25/2022 12:01:23 0

1 APPLY 30 0 0

1 APPLY 60 0 0

1 APPLY 120 0 0

1 APPLY 180 0 0

1 APPLY 300 0 0

1 APPLY 65535 0 0

It shows the frequency of Fast-Start Failover lags and the most recent occurrence for each bucket.

LAG_TIME is the upper bound of the bucket, e.g.

5 -> between 0 and 5 seconds
10 -> between 5 and 10 seconds
etc.

It’s refreshed every minute, only when Fast-Start Failover is enabled (also in observe-only mode).

V$FS_FAILOVER_OBSERVERS

This view is not new, however, its definition now contains more columns:

SQL> desc  v$fs_failover_observers
 Name                           Null?    Type
 ------------------------------ -------- -----------------
 NAME                                    VARCHAR2(513)
 REGISTERED                              VARCHAR2(4)
 HOST                                    VARCHAR2(513)
 ISMASTER                                VARCHAR2(4)
 TIME_SELECTED                           TIMESTAMP(9)
 PINGING_PRIMARY                         VARCHAR2(4)
 PINGING_TARGET                          VARCHAR2(4)
 CON_ID                                  NUMBER
 
 -- new in 23c:
 LAST_PING_PRIMARY                       NUMBER
 LAST_PING_TARGET                        NUMBER
 LOG_FILE                                VARCHAR2(513)
 STATE_FILE                              VARCHAR2(513)
 CURRENT_TIME                            TIMESTAMP(9)

SQL> desc v$fs_failover_observers

Name Null? Type

------------------------------ -------- -----------------

NAME VARCHAR2(513)

REGISTERED VARCHAR2(4)

HOST VARCHAR2(513)

ISMASTER VARCHAR2(4)

TIME_SELECTED TIMESTAMP(9)

PINGING_PRIMARY VARCHAR2(4)

PINGING_TARGET VARCHAR2(4)

CON_ID NUMBER

-- new in 23c:

LAST_PING_PRIMARY NUMBER

LAST_PING_TARGET NUMBER

LOG_FILE VARCHAR2(513)

STATE_FILE VARCHAR2(513)

CURRENT_TIME TIMESTAMP(9)

This 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).

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.

Conclusion

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 🙂

—

Ludovico

New in Data Guard 21c and 23c: Automatic preparation of the primary

Posted on December 22, 2023 by Ludovico

Oracle Data Guard 21c came with a new command:

prepare database for data guard
with db_unique_name is {db_unique_name}
db_recovery_file_dest_size is "{size}"
db_recovery_file_dest is "{dest}" ;

prepare database for data guard

with db_unique_name is {db_unique_name}

db_recovery_file_dest_size is "{size}"

db_recovery_file_dest is "{dest}" ;

This command prepares a database to become primary in a Data Guard configuration.

It sets many recommended parameters:

DB_FILES                      = 1024
LOG_BUFFER                    = 256M
DB_BLOCK_CHECKSUM             = TYPICAL
DB_LOST_WRITE_PROTECT         = TYPICAL
DB_FLASHBACK_RETENTION_TARGET = 120
PARALLEL_THREADS_PER_CPU      = 1
STANDBY_FILE_MANAGEMENT       = AUTO
DG_BROKER_START               = TRUE

DB_FILES = 1024

LOG_BUFFER = 256M

DB_BLOCK_CHECKSUM = TYPICAL

DB_LOST_WRITE_PROTECT = TYPICAL

DB_FLASHBACK_RETENTION_TARGET = 120

PARALLEL_THREADS_PER_CPU = 1

STANDBY_FILE_MANAGEMENT = AUTO

DG_BROKER_START = TRUE

Sets the RMAN archive deletion policy, enables flashback and force logging, creates the standby logs according to the online redo logs configuration, and creates an spfile if the database is running with an init file.

If you tried this in 21c, you have noticed that there is an automatic restart of the database to set all the static parameters. If you weren’t expecting this, the sudden restart could be a bit brutal approach.

In 23c, we added an additional keyword “restart” to specify that you are OK with the restart of the database. If you don’t specify it, the broker will complain that it cannot proceed without a restart:

DGMGRL> prepare database for data guard
> with db_unique_name is chol23c_hwq_lhr
> db_recovery_file_dest_size is "200g"
> db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"
> ;
Validating database "cdb1" before executing the command.
  DGM-17552: Primary database must be restarted after setting static initialization parameters.
  DGM-17327: Primary database must be restarted to enable archivelog mode.
Failed.
DGMGRL>

DGMGRL> prepare database for data guard

> with db_unique_name is chol23c_hwq_lhr

> db_recovery_file_dest_size is "200g"

> db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"

> ;

Validating database "cdb1" before executing the command.

DGM-17552: Primary database must be restarted after setting static initialization parameters.

DGM-17327: Primary database must be restarted to enable archivelog mode.

Failed.

DGMGRL>

If you specify it, it will proceed with the restart:

DGMGRL> prepare database for data guard
>   with db_unique_name is chol23c_hwq_lhr
>   db_recovery_file_dest_size is "200g"
>   db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"
>   restart;
Validating database "chol23c_hwq_lhr" before executing the command.
Preparing database "chol23c_hwq_lhr" for Data Guard.
Initialization parameter DB_FILES set to 1024.
Initialization parameter LOG_BUFFER set to 268435456.
Primary database must be restarted after setting static initialization parameters.
Shutting down database "chol23c_hwq_lhr".
Database closed.
Database dismounted.
ORACLE instance shut down.
Starting database "chol23c_hwq_lhr" to mounted mode.
ORACLE instance started.
Database mounted.
Initialization parameter DB_FLASHBACK_RETENTION_TARGET set to 120.
Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.
RMAN configuration archivelog deletion policy set to SHIPPED TO ALL STANDBY.
Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '200g'.
Initialization parameter DB_RECOVERY_FILE_DEST set to '/u03/app/oracle/fast_recovery_area'.
LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.
Initialization parameter LOG_ARCHIVE_DEST_2 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.
Initialization parameter LOG_ARCHIVE_DEST_STATE_2 set to 'Enable'.
Adding standby log group size 1073741824 and assigning it to thread 1.
Adding standby log group size 1073741824 and assigning it to thread 1.
Adding standby log group size 1073741824 and assigning it to thread 1.
Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.
Initialization parameter DG_BROKER_START set to TRUE.
Database set to FLASHBACK ON.
Database opened.
Succeeded.
DGMGRL>

DGMGRL> prepare database for data guard

> with db_unique_name is chol23c_hwq_lhr

> db_recovery_file_dest_size is "200g"

> db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"

> restart;

Validating database "chol23c_hwq_lhr" before executing the command.

Preparing database "chol23c_hwq_lhr" for Data Guard.

Initialization parameter DB_FILES set to 1024.

Initialization parameter LOG_BUFFER set to 268435456.

Primary database must be restarted after setting static initialization parameters.

Shutting down database "chol23c_hwq_lhr".

Database closed.

Database dismounted.

ORACLE instance shut down.

Starting database "chol23c_hwq_lhr" to mounted mode.

ORACLE instance started.

Database mounted.

Initialization parameter DB_FLASHBACK_RETENTION_TARGET set to 120.

Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.

RMAN configuration archivelog deletion policy set to SHIPPED TO ALL STANDBY.

Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '200g'.

Initialization parameter DB_RECOVERY_FILE_DEST set to '/u03/app/oracle/fast_recovery_area'.

LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.

Initialization parameter LOG_ARCHIVE_DEST_2 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.

Initialization parameter LOG_ARCHIVE_DEST_STATE_2 set to 'Enable'.

Adding standby log group size 1073741824 and assigning it to thread 1.

Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.

Initialization parameter DG_BROKER_START set to TRUE.

Database set to FLASHBACK ON.

Database opened.

Succeeded.

DGMGRL>

Notice that if you already have these static parameters set, the broker will just set the missing dynamic parameters without the need for a restart:

DGMGRL> prepare database for data guard
>   with db_unique_name is chol23c_hwq_lhr
>   db_recovery_file_dest_size is "200g"
>   db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"
> ;
Validating database "chol23c_hwq_lhr" before executing the command.
Preparing database "chol23c_hwq_lhr" for Data Guard.
Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '200g'.
Initialization parameter DB_RECOVERY_FILE_DEST set to '/u03/app/oracle/fast_recovery_area'.
LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.
Initialization parameter LOG_ARCHIVE_DEST_1 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.
Initialization parameter LOG_ARCHIVE_DEST_STATE_1 set to 'Enable'.
Succeeded.

DGMGRL> prepare database for data guard

> with db_unique_name is chol23c_hwq_lhr

> db_recovery_file_dest_size is "200g"

> db_recovery_file_dest is "/u03/app/oracle/fast_recovery_area"

> ;

Validating database "chol23c_hwq_lhr" before executing the command.

Preparing database "chol23c_hwq_lhr" for Data Guard.

Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '200g'.

Initialization parameter DB_RECOVERY_FILE_DEST set to '/u03/app/oracle/fast_recovery_area'.

LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.

Initialization parameter LOG_ARCHIVE_DEST_1 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.

Initialization parameter LOG_ARCHIVE_DEST_STATE_1 set to 'Enable'.

Succeeded.

This new command greatly simplifies the preparation of a Data Guard configuration!

Before 21c, you had to do everything by hand.

—

Ludo