Oracle Data Guard Broker: DGMGRL Fast-Start Failover Configuration

Understanding Oracle Data Guard Broker with Fast-Start Failover

As Someshwar Thakur, a senior technology writer at TechNews Venture, I’ve witnessed firsthand how critical uninterrupted database operations are for modern enterprises. For organizations running mission-critical applications like Oracle PeopleSoft, database availability is not just a feature; it's a fundamental requirement. Oracle Data Guard stands as the cornerstone for achieving high availability and disaster recovery for Oracle databases, and its management is significantly streamlined through the Data Guard Broker. When combined with Fast-Start Failover (FSFO), the solution provides an unparalleled level of automated disaster recovery, minimizing downtime and human intervention during unforeseen outages.

This article delves into the intricacies of configuring Oracle Data Guard Broker with Fast-Start Failover. We will explore its architecture, prerequisites, a detailed step-by-step implementation, crucial security considerations, best practices, and address common questions. Our goal is to equip database administrators and architects with the knowledge to implement a robust, highly available database solution that can withstand failures and ensure business continuity, particularly for demanding environments like PeopleSoft.

Overview: The Power of Automated Disaster Recovery

Oracle Data Guard is a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. It ensures high availability, data protection, and disaster recovery for enterprise data. A Data Guard configuration consists of one production database (the primary database) and one or more standby databases. These standby databases are transactional consistent copies of the primary database.

The Data Guard Broker (DGMGRL) is a command-line interface that simplifies the creation, maintenance, and monitoring of Data Guard configurations. It automates many tasks, such as creating a physical standby database, enabling redo transport, and performing switchovers and failovers. The Broker configuration stores metadata about the Data Guard configuration, allowing for easier management and consistent operations across the environment.

Fast-Start Failover (FSFO) is an advanced feature of Data Guard Broker that enables automatic failover to a pre-determined standby database without requiring any manual intervention. In the event of a primary database failure, the Data Guard Broker, in conjunction with an observer process, automatically initiates a failover. This significantly reduces Recovery Time Objective (RTO) to mere seconds or minutes, a critical factor for applications like PeopleSoft where every minute of downtime can translate into substantial financial losses and operational disruptions. The observer is a lightweight client that runs on a separate machine, continuously monitoring the health of the primary and standby databases and making the decision to initiate a failover based on predefined conditions.

Prerequisites for a Robust Data Guard FSFO Setup

Before embarking on the Data Guard Broker and FSFO configuration, several prerequisites must be met to ensure a smooth and successful deployment. These foundational steps ensure that your environment is ready to support a highly available Data Guard configuration:

• Two Oracle Database Instances: You need at least two distinct Oracle Database instances running on separate servers (physical or virtual). One will serve as the primary database, and the other as the physical standby database.
• Identical Oracle Database Version and Patch Level: Both primary and standby databases must run the exact same Oracle Database version and patch level. For instance, if your primary is 19.12, your standby must also be 19.12.
• ARCHIVELOG Mode Enabled: The primary database must be operating in ARCHIVELOG mode. This is fundamental for redo log transport to the standby database.

  
          SQL> SELECT LOG_MODE FROM V$DATABASE;
          LOG_MODE
          ------------
          ARCHIVELOG
          

• FORCE LOGGING Enabled: The primary database should have FORCE LOGGING enabled to ensure all transactions are logged, including NOLOGGING operations, which is crucial for data consistency on the standby.

  
          SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
          FOR
          ---
          YES
          

• STANDBY_FILE_MANAGEMENT=AUTO: This parameter must be set to AUTO on the standby database. It allows Data Guard to automatically manage the addition, renaming, or dropping of data files on the standby when these operations occur on the primary.
• Network Connectivity and Configuration:
  • Listener Configuration: A properly configured Oracle Net Listener must be running on both primary and standby servers, listening on a dedicated port (e.g., 1521).
  • tnsnames.ora Entries: Valid TNS aliases for both the primary and standby databases must be configured in the tnsnames.ora file on both servers and the observer server. These entries should point to the respective listeners.
  • Firewall Rules: Ensure that network firewalls allow TCP/IP communication between the primary, standby, and observer hosts on the listener ports.
• SYSDBA Credentials: You need a user with SYSDBA privileges (typically SYS) and a strong password for Data Guard operations.
• DG_BROKER_START=TRUE: This initialization parameter should be set to TRUE on both primary and standby databases to enable the Data Guard Broker monitor process.
• Unique Database Names: The DB_UNIQUE_NAME parameter must be distinct for the primary and standby databases. For example, PRODDB for primary and PRODDR for standby. The DB_NAME parameter, however, should be identical.
• Sufficient Disk Space: Ensure ample disk space for archived redo logs on both primary and standby servers.
• Observer Host: A third, independent server is required to host the Data Guard observer process. This server should have network connectivity to both the primary and standby databases. It is crucial that the observer is not co-located on either the primary or standby server to prevent a single point of failure.
• Physical Standby Database: This guide assumes you already have a functional physical standby database configured and synchronized with the primary. If not, you would typically create one using RMAN DUPLICATE or a backup/restore method before proceeding with the Broker configuration. For example, ensuring parameters like LOG_ARCHIVE_DEST_n are configured for redo transport.

Step-by-Step Implementation: Configuring Data Guard Broker with FSFO

This section provides a detailed walkthrough for configuring Data Guard Broker and enabling Fast-Start Failover. We'll use the following example setup:

• Primary Database: PROD (DB_NAME), PRODDB (DB_UNIQUE_NAME), SID: PROD, Host: dbhost1.example.com (IP: 192.168.1.10)
• Standby Database: PROD (DB_NAME), PRODDR (DB_UNIQUE_NAME), SID: PRODDR, Host: dbhost2.example.com (IP: 192.168.1.11)
• Observer Host: observerhost.example.com (IP: 192.168.1.12)
• SYS Password: oracle_sys_pw

Step 1: Initial Database Configuration (Primary and Standby)

Ensure your primary and standby databases meet the prerequisites. Specifically, confirm ARCHIVELOG mode, FORCE LOGGING, and set DG_BROKER_START=TRUE.

On Primary (dbhost1):

SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDB,PRODDR)' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRODDR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDR' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_SERVER=PRODDR SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_CLIENT=PRODDB SCOPE=BOTH;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

On Standby (dbhost2):

Ensure the standby database is mounted but not open, and managed by MRP. Also, set its specific parameters.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDB,PRODDR)' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDR' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRODDB ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDB' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_SERVER=PRODDB SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_CLIENT=PRODDR SCOPE=BOTH;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

Step 2: Configure tnsnames.ora on All Hosts (Primary, Standby, Observer)

Ensure consistent TNS entries. These should be accessible from the respective hosts.

# On dbhost1, dbhost2, observerhost
PRODDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.example.com)
    )
  )

PRODDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.example.com)
    )
  )

Step 3: Create the Data Guard Broker Configuration

Connect to the DGMGRL utility, typically from the primary server, and create the configuration.

On Primary (dbhost1):

[oracle@dbhost1 ~]$ dgmgrl
DGMGRL> CONNECT SYS/oracle_sys_pw@PRODDB

DGMGRL> CREATE CONFIGURATION 'PROD_DG_CONFIG' AS
> PRIMARY DATABASE IS 'PRODDB'
> CONNECT IDENTIFIER IS 'PRODDB';
Configuration "PROD_DG_CONFIG" created with primary database "PRODDB"

DGMGRL> ADD DATABASE 'PRODDR' AS
> CONNECT IDENTIFIER IS 'PRODDR'
> MAINTAINED AS PHYSICAL STANDBY;
Database "PRODDR" added

DGMGRL> ENABLE CONFIGURATION 'PROD_DG_CONFIG';
Enabled.

Verify the configuration status:

DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - PROD_DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRODDB  - Primary database
    PRODDR  - Physical standby database 

  Fast-Start Failover:  Disabled

  Configuration Status:
  SUCCESS   (Status is shown for all members of the configuration)

Also, check the status of individual databases:

DGMGRL> SHOW DATABASE PRODDB VERBOSE;
DGMGRL> SHOW DATABASE PRODDR VERBOSE;

Ensure both show Status: SUCCESS and the standby is applying redo logs.

Step 4: Configure and Enable Fast-Start Failover (FSFO)

Now, enable FSFO and set its parameters. This is done from the DGMGRL prompt while connected to the primary.

On Primary (dbhost1):

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

DGMGRL> SET FAST_START FAILOVER THRESHOLD IS 30;
Property "FastStartFailoverThreshold" updated

DGMGRL> SET FAST_START FAILOVER TARGET IS 'PRODDR';
Property "FastStartFailoverTarget" updated

DGMGRL> SET FAST_START FAILOVER OBSERVER OVERRIDE IS TRUE;
Property "FastStartFailoverObserverOverride" updated

The THRESHOLD defines how long the primary must be unreachable before a failover is initiated (in seconds). TARGET specifies which standby database will become the new primary. OBSERVER OVERRIDE allows the observer to override certain conditions, like delayed apply, if necessary for FSFO. Always review the implications of such settings.

Verify FSFO status:

DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
  Threshold:          30 seconds
  Target:             PRODDR
  Observer:           (monitor)
  Lag Limit:          30 seconds (not in MaxPerformance mode)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Bystanders:         NONE
  Observer Reconnect: (monitor)
  Status:             FSFO is ENABLED

Step 5: Start the Data Guard Observer

The observer process is crucial for FSFO. It runs on a separate host and continuously monitors the health of the primary and standby databases.

On Observer Host (observerhost):

First, ensure the Oracle client software (or full database software) is installed and configured with the correct ORACLE_HOME and PATH, and that tnsnames.ora is correctly configured.

[oracle@observerhost ~]$ dgmgrl
DGMGRL> CONNECT SYS/oracle_sys_pw@PRODDB
Connected as SYSDBA.

DGMGRL> START OBSERVER;
Observer started.

You should see a message indicating the observer has started. It will continue to run in the background. It's good practice to run the observer in a screen session or as a systemd service for persistence.

Verify FSFO status again from the primary DGMGRL prompt:

DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
  Threshold:          30 seconds
  Target:             PRODDR
  Observer:           observerhost.example.com
  Lag Limit:          30 seconds (not in MaxPerformance mode)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Bystanders:         NONE
  Observer Reconnect: (monitor)
  Status:             OBSERVER is running

Notice that the "Observer" field now shows the observer host name, and "Status" confirms the observer is running.

Step 6: Test Fast-Start Failover

It's crucial to test your FSFO configuration. A common way to simulate a primary failure is to shut down the primary database instance abruptly (e.g., using shutdown abort or killing the process). This should trigger the observer to initiate a failover.

On Primary (dbhost1):

SQL> SHUTDOWN ABORT;

On Standby (dbhost2) / Observer Host (observerhost):

Monitor the DGMGRL output or the alert logs. You should see messages indicating the primary has failed and the standby is being promoted. This might take a few moments based on your threshold.

-- On observerhost's DGMGRL session (or in observer log file)
Observer started.
...
Initiating fast-start failover to database "PRODDR"...
Performing failover to database "PRODDR", please wait...
Failover succeeded. New primary is "PRODDR"

On the new Primary (PRODDR on dbhost2):

Connect to DGMGRL and verify the configuration:

[oracle@dbhost2 ~]$ dgmgrl
DGMGRL> CONNECT SYS/oracle_sys_pw@PRODDR
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - PROD_DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRODDR  - Primary database
    PRODDB  - (Error: ORA-16723: the state of the standby database is not consistent with the fast-start failover target)

  Fast-Start Failover:  ENABLED

  Configuration Status:
  SUCCESS   (Status is shown for all members of the configuration)

The old primary (PRODDB) will be in an error state. You will need to reinstate it.

Step 7: Reinstating the Old Primary

After a failover, the old primary database needs to be reinstated as a standby. DGMGRL simplifies this process.

On the new Primary (PRODDR on dbhost2):

DGMGRL> SHOW DATABASE PRODDB;
Database - PRODDB

  Role:            UNAVAILABLE
  ...
  Current status for "PRODDB":
  ERROR
  ORA-16723: the state of the standby database is not consistent with the fast-start failover target

On the old Primary (PRODDB on dbhost1):

Start the database in mount mode.

[oracle@dbhost1 ~]$ sqlplus / as sysdba
SQL> STARTUP MOUNT;

On the new Primary (PRODDR on dbhost2):

DGMGRL> REINSTATE DATABASE PRODDB;
Reinstating database "PRODDB", please wait...
Database "PRODDB" reinstated.

After reinstatement, the old primary (PRODDB) will become a physical standby to the new primary (PRODDR) and start applying redo logs. Verify the configuration again:

DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - PROD_DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRODDR  - Primary database
    PRODDB  - Physical standby database 

  Fast-Start Failover:  ENABLED

  Configuration Status:
  SUCCESS   (Status is shown for all members of the configuration)

The process of switchover (planned role transition) is similar but initiated with the SWITCHOVER TO <standby_db_unique_name> command. Always test both failover and switchover scenarios.

Security Considerations

Implementing Data Guard Broker with FSFO introduces several security aspects that require careful attention:

• Network Security: All communication channels between the primary, standby, and observer hosts must be secured. This includes configuring firewalls to restrict access to Oracle listener ports (typically 1521) to only authorized hosts. Consider using Oracle Net Services encryption and integrity to protect redo transport.
• SYSDBA Password Security: The SYSDBA password used for Data Guard operations is highly privileged. It should be strong, complex, and managed securely. Avoid hardcoding passwords in scripts where possible, or use secure credential stores.
• Observer Host Security: The observer host is a critical component for FSFO. It must be as secure as your database servers. Restrict physical and logical access, apply security patches regularly, and ensure it's not used for other non-related services that could introduce vulnerabilities.
• Least Privilege: While DGMGRL typically requires SYSDBA privileges, ensure that any scripts or automation interacting with DGMGRL adhere to the principle of least privilege.
• Auditing: Implement robust auditing for Data Guard operations, especially for switchovers and failovers, to track who initiated these actions and when.

Best Practices for Data Guard FSFO

To maximize the effectiveness and reliability of your Data Guard FSFO setup, adhere to these best practices:

• Dedicated Observer Host: Always deploy the observer on a separate host, distinct from both the primary and standby databases. This prevents the observer from failing along with either database.
• Multiple Observers (Oracle 12.2+): For even higher availability of the FSFO mechanism itself, consider configuring multiple observers (up to three starting with Oracle 12.2). If the primary observer fails, another observer can take over.
• Regular Monitoring: Continuously monitor the Data Guard configuration health using DGMGRL SHOW CONFIGURATION VERBOSE and check alert logs. Implement automated alerts for any status changes or errors.
• Periodic Testing: Schedule regular drills for both switchover and failover scenarios. This validates your configuration, tests your recovery procedures, and familiarizes your team with the process.
• Patch Synchronization: Keep primary and standby databases at the exact same Oracle Database version and patch level. Inconsistent patching can lead to instability or prevent successful Data Guard operations.
• Network Reliability: Ensure high-quality, low-latency network connectivity between primary, standby, and observer hosts. Network issues can lead to false failovers or prevent redo transport.
• Proper Sizing: Adequately size the redo log files and archive log destinations on both primary and standby to accommodate transaction volume and retention policies.
• DB_FILE_NAME_CONVERT / LOG_FILE_NAME_CONVERT: While not strictly a broker parameter, ensure these are correctly set in the standby's initialization parameters if your primary and standby have different directory structures for datafiles and logfiles.
• Consider FastStartFailoverPmyShutdown: The default is TRUE, meaning the primary is shut down gracefully if possible before failover. Review if this behavior is desired for your RTO requirements.
• FastStartFailoverBystandersFollow: For cascading standby configurations, this property (available in newer versions) ensures bystanders follow the new primary automatically.

Frequently Asked Questions (FAQ)

Q1: What happens if the Data Guard observer itself fails?

A1: If the single observer fails, Fast-Start Failover will be disabled. The Data Guard Broker will report that FSFO is disabled because the observer is not running. In this scenario, automatic failover cannot occur. You would need to manually restart the observer. To mitigate this single point of failure, Oracle 12.2 and later versions support configuring multiple observers (up to three). If one observer fails, another can take over, ensuring continuous FSFO capability.

Q2: Can Fast-Start Failover be used with a logical standby database?

A2: No, Fast-Start Failover is exclusively supported with physical standby databases. Logical standby databases, which apply SQL statements rather than redo, have a different architecture and are not compatible with the automatic failover mechanism provided by FSFO.

Q3: How do I revert my database roles after a Fast-Start Failover?

A3: After an FSFO event, the old primary database is typically left in a disabled or error state. To revert roles (i.e., make the original primary database the primary again), you first need to reinstate the old primary as a physical standby to the new primary using the REINSTATE DATABASE <old_primary_db_unique_name> DGMGRL command. Once reinstated and synchronized, you can then perform a planned switchover operation using the SWITCHOVER TO <original_primary_db_unique_name> command from the new primary.

Conclusion

Oracle Data Guard Broker with Fast-Start Failover is an indispensable technology for any organization demanding high availability and robust disaster recovery capabilities for their Oracle databases. For critical applications like Oracle PeopleSoft, where business continuity is paramount, FSFO ensures minimal downtime through automated failover, drastically reducing RTO and operational overhead during unexpected outages.

By diligently following the prerequisites