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 commanddgmgrl
. In 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 setdg_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