Oracle Data Guard 23ai New Features – Part 3

In earlier Data Guard versions, we could only create Data Guard Broker configurations via the standard DGMGRL command-line utility.

Oracle Database 23ai has extended this and we can now create as well as manage Data Guard environments using additional easy to use and flexible methods.

• SQLcl – SQL command line option via the DG command
• REST API calls
• DBMS_DG PL/SQL API

This new feature allows for much more easier integration with DevOps and configuration pipelines.

Example

SQLCL DG Command

Data Guard management now via SQLcl, with everything under control via a single command-line tool . New in SQLcl version 22.1.

[oracle@perth bin]$ sql


SQLcl: Release 24.2 Production on Wed Nov 06 04:06:31 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Username? (''?) sys@perth:1521/orcl as sysdba
Password? (**********?) ***************

SQL>  DG CREATE CONFIGURATION "ORCL_DG"  AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS 'perth:1521/orcl';
Created.


SQL> DG ADD DATABASE orcl_sb as connect identifier IS 'sydney:1521/orcl_sb';
Database "orcl_sb" added

SQL> DG enable configuration;
Enabled.

SQL> DG show configuration;

Configuration - ORCL_DG

  Protection Mode: MaxPeformance
  Members:
  orcl - Primary database
    orcl_sb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 12 seconds ago)

SQL> dg show database orcl;
Database - orcl

  Role:                Primary database
  Intended State:      TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
Success


SQL> dg show database orcl_sb
Database - orcl_sb

  Role:                Physical standby database
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  27.00 KByte/s
  Instance(s):
    orcl

Database Status:
Success

SQL> help DG
DG
------

 Run DG commands

 DG ADD DATABASE "<database name>"
        AS CONNECT IDENTIIFIER IS <connect identifier>
        [ INCLUDE CURRENT DESTINATIONS ];
 DG CREATE CONFIGURATION "<config_name>"
        AS PRIMARY DATABASE IS <database name>
        CONNECT IDENTIFIER IS <connect_identifier>
        [ INCLUDE CURRENT DESTINATIONS ];
 DG DISABLE CONFIGURATION;
 DG DISABLE { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <member name>;
 DG EDIT CONFIGURATION RESET TAG [<tag name>];
 DG EDIT CONFIGURATION SET PROPERTY <property name> = '<property value>';
 DG EDIT CONFIGURATION SET STATE = 'APPLY-ON' | 'APPLY-OFF' |
        'TRANSPORT-ON' | 'TRANSPORT-OFF';
 DG EDIT CONFIGURATION SET TAG <tag name> = '<tag value>';
 DG EDIT { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <member name>
        RESET TAG [<tag name>];
 DG EDIT { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <member name>
        SET PROPERTY <property name> = '<property value>';
 DG EDIT { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <member name>
        SET TAG <tag name> = '<tag value>';
 DG ENABLE CONFIGURATION;
 DG ENABLE { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <member name>;
 DG FAILOVER TO <database name> [IMMEDIATE];
 DG REINSTATE DATABASE <database name>;
 DG REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
 DG REMOVE { DATABASE | RECOVERY_APPLIANCE | FAR_SYNC | MEMBER } <name>
        [PRESERVE DESTINATIONS];
 DG SHOW CONFIGURATION [<property name>];
 DG SHOW DATABASE <database name> [<property name>];
 DG SWITCHOVER TO <database name> [WAIT [<timeout in seconds>]];

DBMS_DG PL/SQL API

Create the configuration via DBMS_DG.CREATE_CONFIGURATION.

SQL> DECLARE
severity BINARY_INTEGER;
retcode BINARY_INTEGER;
BEGIN
retcode := DBMS_DG.CREATE_CONFIGURATION (
config_name => 'DG23_CONFIG',
primary_ci => 'perth:1521/orcl',
severity => severity
);
END;
/ 

PL/SQL procedure successfully completed.

Add the standby database via DBMS_DG.ADD_DATABASE.

SQL> DECLARE
    severity BINARY_INTEGER;
    rc       BINARY_INTEGER;
BEGIN
     rc := DBMS_DG.ADD_DATABASE (
          database_name => 'orcl_sb',
          database_ci   => 'sydney:1521/orcl_sb',
          severity    => severity
    );
      END;
/ 

PL/SQL procedure successfully completed.

Enable the configuration via DBMS_DG.ENABLE_CONFIGURATION.

SQL> DECLARE
    rc       BINARY_INTEGER;
    severity BINARY_INTEGER;
BEGIN
     rc := DBMS_DG.ENABLE_CONFIGURATION (
          severity    => severity);
      END;
/

PL/SQL procedure successfully completed.

Verify the health of the Data Guard configuration via DBMS_DG.HEALTH_CHECK.

SQL> DECLARE
    rc       BINARY_INTEGER;
BEGIN
     rc := DBMS_DG.HEALTH_CHECK ();
IF
 rc != 0 THEN
DBMS_OUTPUT.PUT_LINE('Data Guard configuration has errors - please check');
ELSE
DBMS_OUTPUT.PUT_LINE('Data Guard configuration health check passed!');
END IF;
END;
/

Data Guard configuration health check passed!

PL/SQL procedure successfully completed.

If we use DGMGRL command SHOW CONFIGURATION we can see that the Data Guard configuration has been successfully created using the DBMS_DG PL/SQL API.

[oracle@perth dbs]$ dgmgrl
DGMGRL for Linux: Release 23.0.0.0.0 - Limited Availability on Sat Apr 26 08:44:40 2025
Version 23.6.0.24.10

Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / as sysdg
Connected to "orcl"
Connected as SYSDG.

DGMGRL> show configuration

Configuration - DG23_CONFIG

  Protection Mode: MaxPerformance
  Members:
  orcl - Primary database
  orcl_sb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 64 seconds ago)