DatabaseVerse: Journey into the World of Databases

That’s right! Sounds interesting, doesn’t it? Since previous releases, I “believe” since Oracle 11g, there exists a package called DBMS_DG that is mostly useful when you have a Fast-Start Failover (FSFO) configuration. This function is the DBMS_DG.INITIATE_FS_FAILOVER and can be called from a connection to either the primary or any standby in the configuration to trigger an immediate fast-start failover depending on a condition known to an application.

That’s great, but that was the only function available. Now in Oracle 23ai, there are more than 30 new functions! So, you can use the DBMS_DG PL/SQL APIs to create and manage broker configurations! Btw, I have an interesting topic about Data Guard in 23ai that you can take a look at here: Oracle Data Guard 23ai is soooo simple!

So let’s dig a bit into it, shall we?

1. Oracle Data Guard DBMS_DG API
   1. CREATE CONFIGURATION
   2. SET_STATE_APPLY_ON/SET_STATE_APPLY_OFF
      1. Update – 2024/10/15
   3. HEALTH_CHECK
      1. Update – 2024/10/15
   4. GET_PROPERTY
   5. All the Available Functions
2. Conclusion

Oracle Data Guard DBMS_DG API

The Oracle DBMS_DG PL/SQL APIs enable you to control and monitor an Oracle Data Guard configuration from within scripts or batch programs.

You can perform many of the activities required to manage and monitor the databases in the configuration using DBMS_DG PL/SQL APIs.

The DBMS_DG package runs with invoker’s rights and requires the SYSDBA privilege. Let see some examples:

CREATE CONFIGURATION

This is an example of creating a new configuration and adding a standby database:

SET SERVEROUTPUT ON

DECLARE
    severity BINARY_INTEGER;
    rc       BINARY_INTEGER;
BEGIN
    rc := DBMS_DG.CREATE_CONFIGURATION (
          config_name => 'myConfig'
          primary_ci  => 'site1-scan:port/service_name'
          severity    => severity
          );

    IF rc := 0 THEN
        / * handle error */
    END IF;

    rc := DBMS_DG.ADD_DATABASE (
          database_name => 'mydb_site2'
          database_ci   => 'site2-scan:port/service_name'
          severity    => severity
    );

    IF rc := 0 THEN
        / * handle error */
    END IF;

END
/

Severity parameter will return a value (value will be an integer), one of the followings:

• ORA-0: normal, successful completion
• ORA-16501: The Oracle Data Guard broker operation failed
• ORA-16502: The Oracle Data Guard broker operation succeeded with warnings

I already have a configuration created, so let’s perform some actions using the DBMS_DG PL/SQL API.

SET_STATE_APPLY_ON/SET_STATE_APPLY_OFF

The SET_STATE_APPLY_ON/SET_STATE_APPLY_OFF function can be used to set the apply state to on/off for a logical or physical standby database. Let’s take a look.

I have coded a simple PL/SQL that get a parameter to enable/disable the APPLY, parameter value should be ON/OFF:

SET SERVEROUTPUT ON
DECLARE
    apply_option VARCHAR2(10) := UPPER('&1'); -- Receiving the parameter and converting to UPPER case
    rc BINARY_INTEGER;
    severity BINARY_INTEGER := 0;
    member_name v$dg_broker_property.value%TYPE; -- Variable to hold the member_name
BEGIN
    -- Fetch the member_name dynamically from the query
    SELECT value
    INTO member_name
    FROM v$dg_broker_property
    WHERE dataguard_role = 'PHYSICAL STANDBY'
      AND property = 'DGConnectIdentifier';

    IF apply_option = 'OFF' THEN
        rc := DBMS_DG.SET_STATE_APPLY_OFF (
              member_name    => member_name,
              severity       => severity
            );

    ELSIF apply_option = 'ON' THEN
        rc := DBMS_DG.SET_STATE_APPLY_ON (
              member_name    => member_name,
              severity       => severity
             );
    ELSE
        DBMS_OUTPUT.PUT_LINE('Valid options are ON/OFF');
        RETURN;
    END IF;

    DBMS_OUTPUT.PUT_LINE('rc code: ' || rc);

    -- Handling the return code (severity)
    IF severity = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Operation completed successfully. Severity: ' || severity);
    ELSIF severity = 16501 THEN
        DBMS_OUTPUT.PUT_LINE('Operation failed. Severity: ' || severity);
    ELSIF severity = 16502 THEN
        DBMS_OUTPUT.PUT_LINE('Operation succeeded with warnings. Severity: ' || severity);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Unknown severity: ' || severity);
    END IF;

END;
/

As you can see above, the apply state was changed to OFF. Then I executed the PL/SQL to enable the apply state again, and it worked; however, the severity code was somehow empty, which is kind of weird, although I’m not sure if that is expected.

I did another test stopping the listener in the DR, and the same behavior occurred when trying to enable the apply state; the severity output is empty. When trying to disable apply, it returned 16501, and that’s is expected as per doc. I’m not sure why enabling it is not returning any value for severity; it seems to be some kind of bug. You should consider the return code instead if you want to handle any errors, just in case!

Update – 2024/10/15

I received an update from Oracle regarding a bug when using DBMS_DB.SET_STATE_APPLY_ON that does not return any value in the severity out parameter. Please bear in mind, and as I mentioned before, use the return code to determine if something has failed for any reason. Bug reference is: 37169709: DBMS_DG.SET_STATE_APPLY_ON DOES NOT RETURN THE SEVERITY. Thanks to Ludovico for checking on this and updating quickly about it!

HEALTH_CHECK

There is another interesting function that can be used to get the broker to evaluate the health of all configuration members. As per the doc, let’s review it:

SET SERVEROUTPUT ON

DECLARE
    rc       BINARY_INTEGER;
BEGIN
    rc := DBMS_DG.HEALTH_CHECK();

    IF rc != 0 THEN
        DBMS_OUTPUT.PUT_LINE('DG configuration has errors!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('DG configuration health check is OK!');
    END IF;

END;
/

It looks okay; let’s stop the listener on the server where the physical standby is running and check the status again:

What an unexpected result! The DBMS_DG.HEALTH_CHECK function has returned 0; however, the DG broker command line interface is able to detect that something is wrong. I was trying to find more information about it, but couldn’t find anything at least yet. It seems that some functions do not work 100% correctly. I will try to check with Oracle on this and update the post whenever I get any updates about it.

Update – 2024/10/15

I have checked on this with Oracle and got the following update: DGMGRL SHOW CONFIGURATION gives you the current status and it also shows a message at the end as: “Status updated X seconds ago.” This update happens most of the time in less than a minute because health_check runs every minute, so when we run the dbms_dg.health_check function internally, it triggers this additional health check, ensuring that the “SHOW CONFIGURATION” or other health check dependent information is up to date. Thanks to Ludovico for the update on this!

GET_PROPERTY

The GET_PROPERTY function can be used to get the value of a member property. Let’s see:

SET SERVEROUTPUT ON
DECLARE
    rc BINARY_INTEGER;
    value varchar2(50);
    severity BINARY_INTEGER := 0;
    member_name v$dg_broker_property.value%TYPE; -- Variable to hold the member_name
BEGIN
    -- Fetch the member_name dynamically from the query
    SELECT value
    INTO member_name
    FROM v$dg_broker_property
    WHERE dataguard_role = 'PHYSICAL STANDBY'
      AND property = 'DGConnectIdentifier';

    rc := DBMS_DG.GET_PROPERTY (
           member_name    => member_name,
           property_name  => 'ApplyLagThreshold',
           value          => value,
           severity       => severity
        );

    IF severity = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Operation completed successfully. Severity: ' || severity);
        DBMS_OUTPUT.PUT_LINE('ApplyLagThreshold for member ' || member_name || ' is set to: ' || value);
    ELSIF severity = 16501 THEN
        DBMS_OUTPUT.PUT_LINE('Operation failed. Severity: ' || severity);
    ELSIF severity = 16502 THEN
        DBMS_OUTPUT.PUT_LINE('Operation succeeded with warnings. Severity: ' || severity);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Unknown severity: ' || severity);
    END IF;

END;
/

Output, as you can see, is the same using the PL/SQL API and DG broker; that was only an example to get the value for ApplyLagThreshold. You can use the same code for other properties and loop for each one to get the information you want to.

All the Available Functions

If you want to try more about you can find all the available functions from the official doc here: Oracle Data Guard DBMS_DG API Summary

Conclusion

In this post, we explored the enhancements in Oracle Data Guard’s DBMS_DG PL/SQL APIs introduced in Oracle 23ai. The addition of over 30 new functions allows a better control and management of broker config through PL/SQL.

We examined some key functionalities, such as creating configurations, managing apply states, performing health checks, and retrieving property values from members in the config. While the improvements are noteworthy, it became clear that some functions exhibit unexpected behavior, or at least that has been my first impression when checking the values returned in the severity status for each function. This suggests that further investigation may be required to understand and resolve these issues; I will take a look at this further, and if there are any updates, I will post it later!

Overall, the Oracle Data Guard DBMS_DG API offers new features to control the configuration of your DG configuration. It is true that I have to leverage a couple of things like member name information from some views like v$dg_broker_property , as there is not a function available to get the members of the configuration. I think that would be a high-value output from the API; hopefully, Oracle will add this soon!. Be sure to consult the official documentation for a comprehensive understanding of the available functions and best practices.