Post Contents

Oracle 19c Create a Data Guard Broker Configuration

Oracle 19c Create a Data Guard Broker Configuration

Creating a Data Guard broker configuration in Oracle 19c is essential for managing your DataGuard setup efficiently. The DataGuard broker simplifies the configuration, management, and monitoring of DataGuard setups, ensuring high availability, data protection, and disaster recovery. This blog will guide you through the steps to create and configure a DataGuard broker.

Introduction to Data Guard Broker

The DataGuard broker is an Oracle feature that provides a comprehensive framework for managing DataGuard configurations. It automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. Using the DataGuard broker, you can manage your DataGuard setup more effectively and ensure your primary and standby databases remain synchronized.

Data Guard Broker Benefits:

  • Simplified management of Data Guard configurations.
  • Centralized control of the entire Data Guard environment.
  • Enhanced monitoring and reporting capabilities.
  • Automated failover and switchover operations.

 

Prerequisites for Data Guard Broker Configuration

Before you begin configuring the Data Guard broker, ensure that your environment meets the following prerequisites:

  • Oracle 19c installed and configured on both primary and standby servers.
  • Proper network configuration to allow communication between primary and standby servers.
  • SYSDBA privileges on both primary and standby databases.

Additionally, make sure that the Data Guard setup is functioning correctly and that the primary and standby databases are in a consistent state.

 

Step-by-Step Data Guard Broker Configuration

Enable Data Guard Broker

First, you need to enable the DataGuard broker on both the primary and standby databases. This can be done by setting the DG_BROKER_START parameter to TRUE in the database initialization parameter file.

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Create the Data Guard Configuration

Next, create the Data Guard configuration using the DGMGRL command-line utility. Start by connecting to the primary database.

DGMGRL sys/password@primary_db

Create a new Data Guard configuration with the following command:

CREATE CONFIGURATION 'dg_config' AS PRIMARY DATABASE IS 'primary_db';

Add Standby Databases

After creating the configuration, add the standby database to the configuration.

ADD DATABASE 'standby_db' AS CONNECT IDENTIFIER IS 'standby_db';

Enable the Configuration

Enable the newly created Data Guard configuration to start managing the databases.

ENABLE CONFIGURATION;

Verify the Configuration

Verify that the Data Guard broker configuration is set up correctly and that both primary and standby databases are properly integrated.

SHOW CONFIGURATION;
SHOW DATABASE 'primary_db';
SHOW DATABASE 'standby_db';

 

📢 You might also like: Oracle 19c Manage the Data Guard broker configuration (Category: DataGuard)

Monitoring and Managing Data Guard Configuration

Once the Data Guard configuration is set up, monitoring and managing it is crucial to ensure database continuity and performance. This can be effectively done using the Data Guard Manager command-line interface (DGMGRL) or Oracle Enterprise Manager. Below are some essential commands and practices for managing a Data Guard environment.

Common Management Commands

Switchover

A switchover operation switches roles between the primary and standby databases, which is useful for planned maintenance.

SWITCHOVER TO 'standby_db';

Failover

A failover operation transitions the standby database to the primary role in case of primary database failure, ensuring minimal downtime.

FAILOVER TO 'standby_db';

Status Check

Check the status of the Data Guard configuration to monitor the health and state of your databases.

SHOW CONFIGURATION;

Data Guard Broker

Data Guard Broker simplifies the management of a Data Guard configuration. Here’s how you can use it effectively:

Checking Broker Status

To check if the Data Guard Broker is running, look for the DMON process:

ps -fe | grep dmon

DMON Process:

  • A server-side background process
  • Part of each database instance in the configuration
  • Created when you start the broker
  • Performs requested functions and monitors resources
  • Communicates with other DMON processes in the configuration
  • Updates the configuration file
  • Creates drc<SID> trace file in the location set by the DIAGNOSTIC_DEST initialization parameter
  • Modifies initialization parameters during role transitions as necessary

Showing Configuration and Database Properties

To get a detailed view of the configuration and properties:

SHOW CONFIGURATION;
SHOW DATABASE VERBOSE 'db_name';
VALIDATE DATABASE VERBOSE 'db_name';

Validating Database for Role Change

Ensure the database is ready for a role change:

VALIDATE DATABASE VERBOSE 'db_name';

Logfile of Data Guard Broker

The DMON process writes information to log files, which are useful for diagnosing issues. The log files are created in the same directory as the alert log of the database.

cat /u01/app/oracle/diag/rdbms/db_name/db_name/trace/drcdb_name.log

Monitoring Data Guard Diagnostic Information

Use the following commands to monitor various aspects of Data Guard:

SHOW DATABASE 'db_name' 'StatusReport'; -- Lists all problems detected by the broker
SHOW DATABASE 'db_name' 'LogXptStatus'; -- Lists all log transport errors
SHOW DATABASE 'db_name' 'InconsistentProperties'; -- Lists properties with inconsistent values
SHOW DATABASE 'db_name' 'InconsistentLogXptProps'; -- Lists inconsistent redo transport-related properties
SHOW DATABASE 'db_name' 'LsbyFailedTxnInfo'; -- Identifies a failed transaction that caused log apply service to stop
SHOW DATABASE 'db_name' 'LsbyParameters'; -- Shows parameters for log apply services
SHOW DATABASE 'db_name' 'RecvQEntries'; -- Shows received log files not yet applied
SHOW DATABASE 'db_name' 'SendQEntries'; -- Shows log files not successfully archived to standby
SHOW DATABASE 'db_name' 'TopWaitEvents'; -- Specifies the top 5 events with the longest wait time
SHOW FAR_SYNC 'far_sync_name' 'StatusReport'; -- Shows status report for Far Sync instance

Editing Data Guard Configuration Examples

Editing the Data Guard configuration can optimize the performance and reliability of your setup. Here are some examples:

EDIT DATABASE 'db_name' SET STATE='APPLY-ON';
EDIT DATABASE 'db_name' SET STATE='APPLY-OFF';
EDIT DATABASE 'db_name' SET PROPERTY 'ApplyParallel'='40';
EDIT DATABASE 'db_name' SET PROPERTY 'ApplyInstances'='0';
EDIT DATABASE 'db_name' SET STATE='APPLY-ON' WITH APPLY INSTANCE='instance_name';
EDIT DATABASE 'db_name' SET PROPERTY 'RedoCompression'='ENABLE';
EDIT DATABASE 'db_name' SET PROPERTY 'ApplyLagThreshold'='1800';
EDIT DATABASE 'db_name' SET PROPERTY 'TransportLagThreshold'='1800';
EDIT DATABASE 'db_name' SET PROPERTY 'LogFileNameConvert'='';
EDIT DATABASE 'cdb_name' SET PROPERTY 'LogXptMode'='SYNC'; -- Transport mode

Changing Protection Mode

Adjusting the protection mode using Data Guard Broker enhances the balance between performance and data safety:

EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Converting to Snapshot Standby

Convert a physical standby to a snapshot standby for testing purposes:

CONVERT DATABASE 'db_name' TO SNAPSHOT STANDBY;

Managing FarSync Routes

FarSync instances help improve data synchronization. Here’s how you can manage them:

EDIT DATABASE 'primary_db' SET PROPERTY 'RedoRoutes'='(LOCAL : far_sync FASTSYNC)';
EDIT FAR_SYNC 'far_sync' SET PROPERTY 'RedoRoutes'='(primary_db : standby_db ASYNC)';
SHOW CONFIGURATION;

Note: In this setup, the primary database sends logs to the FarSync instance in SYNC mode, and the FarSync instance sends logs to the standby database in ASYNC mode.

Monitoring Data Guard via SQL Commands

Using SQL commands, you can monitor and manage the Data Guard environment more granularly.

Verifying Lag

Check the delay between the primary and standby databases:

SET LINESIZE 200
COLUMN value FORMAT A30
SELECT INST_ID, name, value, time_computed FROM gv$dataguard_stats;
SELECT * FROM v$standby_event_histogram WHERE name LIKE '%lag%' AND count > 0;

Checking the Latest Sequence

Run this query on both primary and standby databases to ensure they are in sync:

SELECT MAX(SEQUENCE#), thread#, APPLIED, SYSDATE FROM V$ARCHIVED_LOG WHERE APPLIED='YES' GROUP BY thread#, applied, SYSDATE ORDER BY thread#;

Verifying Differences in GAP

Identify the difference in applied log sequences:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference", SYSDATE
FROM
  (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  (SELECT THREAD#, SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Verifying MRP

Check the Managed Recovery Process (MRP) status:

SELECT INST_ID, PROCESS, PID, STATUS FROM gv$managed_standby WHERE process LIKE 'MRP%';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; -- Start redo-apply in real-time if disabled

Monitoring Alert Logs

Review recent messages in the alert log or server process trace files:

SELECT timestamp, facility, message FROM gv$dataguard_status ORDER BY timestamp;

 

Conclusion

Configuring a DataGuard broker in Oracle 19c simplifies the management and monitoring of your Data Guard setup. With the broker’s centralized control and automated operations, you can ensure high availability, data protection, and disaster recovery for your Oracle databases. Follow the steps outlined in this guide to set up and manage your DataGuard broker configuration effectively.

By leveraging the DataGuard broker, you can enhance the reliability and performance of your DataGuard environment, ensuring that your critical data remains protected and available at all times.

See more on Oracle’s website!

Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top