What is the Oracle Dataguard Broker and How to Enable / Disable Dataguard Broker Configurations

Hi,

I will explain What is the Dataguard Broker and How to Enable / Disable Dataguard Broker Configurations in this article.

 

If you don’t know what is the Oracle Dataguard and Oracle dataguard architecture, you can read the following article.

https://ittutorial.org/oracle-dataguard-architecture-3/

 

You can also read the following article if you don’t know how to install Oracle Dataguard.

Oracle DataGuard Physical Standby Installation Step by Step Using RMAN -2

 

 

The Oracle Data Guard broker is as an integrated unit that can manage and monitor Primary and Standby databases together. You can access and use the Broker configuration via both Oracle Enterprise Manager Cloud Control and the Oracle Data Guard command-line interface ( DGMGRL ).

 

You can perform the following task using the Dataguard broker.

• Create Dataguard configurations
• Monitor the Dataguard and Broker configurations.
• Add the new Standby databases (physical, logical, snapshot, far sync, Oracle RAC or non-Oracle RAC) to an existing Data Guard configuration.
• You can perform the Switchover very easily.
• You can perform the Failover very easily.
• You can set the protection mode of dataguard in the broker configuration.

 

You can use Dataguard Broker via Cloud Control 13c as follows.

 

 

You can use Dataguard Broker via DGMGRL ( Data Guard command-line interface ) Tool as follows.

 

 

You can enable Dataguard broker parameter as follows.

SQL> alter system set dg_broker_start=true scope=both sid='*';

System altered.

SQL>

 

You can disable Dataguard broker parameter as follows.

 

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL>

 

You can create Dataguard broker configuration as follows.

 

[oracle@msddbadm01 ~]$ dgmgrl sys/Password1@MSDB

DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS MSDB CONNECT IDENTIFIER IS MSDB;
Configuration "my_dg_config" created with primary database "MSDB"
DGMGRL>

 

You can edit the Broker configuration Protection mode and properties as follows.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;



DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = FALSE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = TRUE;

DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;
DGMGRL> EDIT CONFIGURATION SET PROPERTY BYSTANDERSFOLLOWROLECHANGE= 'NONE';

DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;

DGMGRL> EDIT DATABASE devdb SET PROPERTY 'StandbyArchiveLocation'='/u01/Archivelog/';
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'DbFileNameConvert' = '/oradata/msdb/datafile/, /u01/msdb/datafile/';
DGMGRL> EDIT DATABASE testdb SET PROPERTY DelayMins='720';
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='ENABLE'
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression ='DISABLE'
DGMGRL> EDIT DATABASE testdb SET PROPERTY LogArchiveMinSucceedDest =1

DGMGRL> EDIT DATABASE devdb SET PROPERTY 'ReopenSecs'=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=ON;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget='STANDBY_NAME';

 

 

You can add the standby database as follows.

DGMGRL> ADD DATABASE MSDG AS CONNECT IDENTIFIER IS MSDG MAINTAINED AS PHYSICAL;
Database "MSDB_stby" added
DGMGRL>

 

 

Once you create Broker Configuration, you can enable it as follows.

DGMGRL> ENABLE CONFIGURATION;
Enabled.

 

You can disable Broker configuration as follows.

DGMGRL> DISABLE CONFIGURATION
Disabled.

 

You can remove the Broker configuration as follows.

DGMGRL> REMOVE CONFIGURATION
Removed configuration

 

You can show the Broker configuration as follows.

DGMGRL> SHOW CONFIGURATION;

 

You can show databases configuration as follows.

 

DGMGRL> SHOW DATABASE 'MSDB';

Database
Name: MSDB
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
MSDB1

Current status for "MSDB":
SUCCESS

 

DGMGRL> SHOW DATABASE MSDB_stby;

 

 

You can perform switchover as follows.

 

DGMGRL> SWITCHOVER TO MSDB_stby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "MSDB" on database "MSDB_stby"
Connecting to instance "MSDB"...
Connected.
New primary database "MSDB_stby" is opening...
Operation requires startup of instance "MSDB" on database "MSDB"
Starting instance "MSDB"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "MSDB_stby"

 

 

 

You can perform the Failover as follows.

DGMGRL> FAILOVER TO MSDB_stby;

 

You can perform Snapshot standby as follows.

DGMGRL> CONVERT DATABASE MSDB_stby TO SNAPSHOT STANDBY;

 

 

You can list all configuration options and failover parameters as follows.

DGMGRL> SHOW CONFIGURATION VERBOSE
Configuration - my_dg_config
Protection Mode: MaxAvailability\n
Databases:
MSDB - Primary database
MSDB_stby - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

 

 

You can list summary of the fast-start failover configuration as follows.

DGMGRL> SHOW FAST_START FAILOVER;
 
Fast-Start Failover: DISABLED
 Threshold:           30 seconds
 Target:              (none)
 Observer:            (none)
 Lag Limit:           30 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE
 
Configurable Failover Conditions
 Health Conditions:
   Corrupted Controlfile          YES
   Corrupted Dictionary           YES
   Inaccessible Logfile            NO
   Stuck Archiver                  NO
   Datafile Offline               YES
 
 Oracle Error Conditions:
   ORA-27102: out of memory

 

You can execute any SQL statement via DGMGRL as follows.

DGMGRL> SQL "SQL_STATEMENT";

 

You can also shutdown and startup Oracle Instance as follows.

DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

 

 

DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT | MOUNT | OPEN [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;
DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initMSDB.ora NOMOUNT;

 

 

 

You can start the fast-start failover observer as follows.

DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/