DataGuard Broker in Oracle Step-By-Step

DataGuard Broker is a tool to make automatic changes of roles in case of having a switchover or a failover, and its use is as simple as connecting to the console with the commanddgmgrlIn this case, we will make the configuration through the command line.

Previous settings

  • Listener configuration in primary and secondary databases.
  • Configuration database parameters.
  • Creating broker configuration files.

To learn more about Oracle configurations read Configuring a Physical Standby with Oracle Dataguard

DataGuard Broker Listener Configurations

We will start configuring the listeners, both in our primary database and in our standby database.

# Generated by Oracle configuration tools.

IWAY =

(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.8.161)(PORT = 1521))
 (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = IWAY)  ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
(PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = IWAY_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4) (SID_NAME = IWAY)
) )

Then reload the listener: lsnrctl reload

In the standby database proceed to create the physical files of the configuration of the broker and to activate the feature in the database itself putting the parameter dg_broker_start = start .

First create a path in the OS that is safe and then on the database we pass those same parameters.

SQL> sho parameter broker

NAME                                     TYPE         VALUE

------------------------------------ ----------- ------------------------------ dg_broker_config_file1                     string dg_broker_config_file2                     string dg_broker_start                      boolean         FALSE
SQL> alter system set 
dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/dbs/db_1.dat' scope=both sid='*';
 System altered. SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/dbs/db_2.dat' scope=both sid='*'; System altered. SQL> sho parameter broker
NAME                                     TYPE         VALUE

------------------------------------ ----------- ------------------------------------------------------ dg_broker_config_file1                     string         /u01/app/oracle/product/11.2.0.4/dbs/db_1.dat dg_broker_config_file2                     string         /u01/app/oracle/product/11.2.0.4/dbs/db_2.dat dg_broker_start                      boolean         FALSE
dg_broker_config_file1                     string         /u01/app/oracle/product/11.2.0.4/dbs/db_1.dat

dg_broker_config_file2                     string         /u01/app/oracle/product/11.2.0.4/dbs/db_2.dat

dg_broker_start                      boolean         FALSE

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

System altered.

From here, we will tell broker what will be called primary, secondary, and what roles will each have. This will serve us in the future to switchover, to failover, or maintenance itself.

[oracle@saturno bin]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/PASSXXXX

Connected.

Create configuration in dgmrl:

DGMGRL> CREATE CONFIGURATION 'DG_PROD' AS

PRIMARY DATABASE IS 'IWAY'

CONNECT IDENTIFIER IS IWAY;

Configuration "DG_PROD" created with primary database "IWAY"

DGMGRL> ADD DATABASE 'IWAYSB' AS

CONNECT IDENTIFIER IS IWAYSB

MAINTAINED AS PHYSICAL;

Database "IWAYSB" added

DGMGRL>

 

Now we can see the configuration we set and it will tell us that it is in disabled mode.

DGMGRL> show configuration;

Configuration

Name:                DG_PROD

Enabled:             NO

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

IWAY   - Primary database

IWAYSB - Physical standby database

Current status for "DG_PROD":

DISABLED

 

Enable configuration to start redo-apply

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration

Name:                DG_PROD

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

IWAY   - Primary database

IWAYSB - Physical standby database

 

Current status for "DG_PROD":

SUCCESS

 

Now, we are ready to make the switchover:

Now that we have configured the listener and parameters of the database, we could make the first database a  change of roles.

  • We can make a select in the v$archive_dest before rooting, to verify that there is connectivity and we have no network problems.
  • In the case that the database presents any problems, the broker initiates the process and, if necessary, abort leaving the base intact at the time of the start.
  • In the event that in a passage the primary base is lost, and previously we would have started the switch, we will not be able to return, to solve this we must look at the alert log and we would know how to follow the change by hand, since in the alerts shows the Commands executed, so we could verify that step is missing and continue.

[oracle@saturno01 dbs]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxxxxxx

Connected.

DGMGRL> switchover to "IWAYSB";

Performing switchover NOW, please wait...

Operation requires shutdown of instance "IWAY" on database "IWAY"

Shutting down instance "IWAY"...

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "IWAYSB" on database "IWAYSB"

Shutting down instance "IWAYSB"...

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "IWAY" on database "IWAY"

Starting instance "IWAY"...

ORACLE instance started.

Database mounted.

Operation requires startup of instance "IWAYSB" on database "IWAYSB"

Starting instance "IWAYSB"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "IWAYSB"

DGMGRL>

You may also like to read How to quickly tell if your Oracle Database is running slow or not

You may also like

RDS AWS

Oracle Database Schemas to RDS AWS

Oracle Locks

Oracle Locks, Blocking Locks and Deadlocks

Configuring a Physical Standby with Oracle Dataguard

Menu