Pages

Monday, February 10, 2020

Dataguard Broker Basics

Dataguard Broker Basics


- The Data Guard broker logically groups primary and standby databases in a Data Guard setup into a broker configuration that allows the broker to manage and monitor them together as an integrated unit. You can manage a broker configuration using either the Oracle Enterprise Manager graphical user interface or the Data Guard command-line interface.

- Data Guard Broker is part of the normal Oracle Database Enterprise Edition installation and an integral part of dataguard. You can do Data Guard related configuration, set transport methods, apply setup and role change services and also change overall protection mode.

The Broker consists of three major parts:

A) BACKGROUND PROCESSES ON EACH NODE
B) BROKER CONFIGURATION FILES
C) COMMAND LINE INTERFACE KNOWN AS DGMGRL.

A) BACKGROUND PROCESSES ON EACH NODE
All of the processes run on every node of Data Guard setup.

i) Data Guard Monitor (DMON)
Coordinates all Broker actions as well as maintains the Broker configuration files
Enabled or disabled with the DG_BROKER_START parameter.

ii) Broker Resource Manager (RSM)
RSM process comes in play whenever broker need to run any SQL command in the database.
SQL may be required to be run during Data Guard setup or because of result of a change to the conguration made through DGMGRL

iii) Data Guard Net Server (NSVn)
NSV processes are responsible for making connection with the remote database and to send across anywork items to the remote database. So, DMON or RSM process may ask NSV process to connect to other node for any work request. DMON may need to send some communication to the other node and RSM may have to get some data through SQL from other node.

iv) DRCn
NSV processes contact DRC process running on other node to establish the connection, so DRC process acts like a receiver on other node. Each NSV process will have a partner DRC process on the target database, which will perform the actual work on behalf of the source database NSV process and return the results or status.

v) Internode servers (INSVs)
If Data Guard setup involves Real Application Clusters (RAC), INSV process come into play. It maintain a connection between the RAC database nodes in the cluster to ensure that the Broker on each node knows the state of the cluster.

WHAT HAPPENS AT DATABASE STARTUP:

Step 1) Primary database startup.
Step 2) DMON process at primary ask local NSV process to connect to each standby database.
Step 3) NSV process in turn will contact DRC process on standby and get confirmation.
Step 4) DMON process at primary will then ask local RSM to send the setup commands to the standby database.
Step 5) RSM will send the setup commands to NSV.
Step 6) NSV process in turn will contact DRC process on standby to run the setup commands.


B) BROKER CONFIGURATION FILES

Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration. Additionally two copies of the configuration files are always stored on each database for redundancy.

Below parameters control where the configuration files will be stored.

DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

For RAC instances, you need to keep the files in shared location where all database instances can access it. Broker keeps in sync all of these configuration files but DMON process running on the primary database is the owner of the master copy of the files.

This means that if you started the standby database while Data guard process are down at primary node, then no data guard related activities will be done until the standby database can connect to the primary database.

Primary purpose of keeping configuration files on each node is when your primary node is gone and you need to failover, Data Guard processes can determine the original settings for the entire configuration from the configuration files on other surviving node.

C) COMMAND LINE INTERFACE (CLI) KNOWN AS DGMGRL

The third important part of Broker setup is the interface through which users will interact with Broker. The Broker CLI DGMGRL is included in the Oracle Database Enterprise Edition.

To access DGMGRL, type dgmgrl at the command prompt. Use the CONNECT command after you have started DGMGRL to connect to databases.

Below steps to configure Dataguard Broker

SQL> select NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;
NAME      DATABASE_ROLE    DATAGUAR
--------- ---------------- --------
DELL      PRIMARY          DISABLED

- Change the location of config files

Since our Primary and Standby databases are RAC, we will change the default location of DG Broker files to a centralized location so that all nodes can access them. In our case, we will be using the ACFS mount point.

Login as oracle user on Primary and execute below commands.

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/products/12.1.0/db/dbs/dr1orcldb.dat
dg_broker_config_file2               string      /u01/app/oracle/products/12.1.0/db/dbs/dr2orcldb.dat

SQL> !mkdir -p /u01/app/acfs/reco/dg_broker_config_file

SQL> alter system set dg_broker_config_file1='/u01/app/acfs/reco/dg_broker_config_file/dr1orcldb.dat';
System altered.

SQL> alter system set dg_broker_config_file2='/u01/app/acfs/reco/dg_broker_config_file/dr2orcldb.dat';
System altered.

Similarly, change the settings on Standby database server.

SQL> !mkdir -p /u01/app/acfsreco/dg_broker_config_file

SQL> alter system set dg_broker_config_file1='/u01/app/acfsreco/dg_broker_config_file/dr1orcldbs.dat';
System altered.

SQL> alter system set dg_broker_config_file2='/u01/app/acfsreco/dg_broker_config_file/dr2orcldbs.dat';
System altered.

- Change the dg_broker_start parameter value

Now change the parameter 'dg_broker_start' to TRUE on both Primary and Standby databases as shown below.
SQL> alter system set dg_broker_start=TRUE;
System altered.

- Register databases using dgmgrl

On the primary server, login to command line interface using dgmgrl and register the primary database in the broker configuration.

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password@orcldb
Connected as SYSDBA.
DGMGRL>

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

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password@orcldb
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS orcldb CONNECT IDENTIFIER IS orcldb;
Configuration "dg_config" created with primary database "orcldb"
Now add the standby database in broker configuration.

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

- Enable and Check the broker configuration
We need to enable the broker configuration and check if the configuration is enabled successfully or not.

DGMGRL> ENABLE CONFIGURATION;
Enabled.

DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
orcldb  - Primary database
orcldbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 13 seconds ago)

DGMGRL> ENABLE CONFIGURATION;
Enabled.

DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
orcldb  - Primary database
orcldbs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 13 seconds ago)

Once the broker configuration is enabled, the MRP process should get start on the Standby database server. You can check using below command.

DGMGRL> show database orcldbs
Database - orcldbs
Role:               PHYSICAL STANDBY
Intended State:     APPLY-ON
Transport Lag:      0 seconds (computed 0 seconds ago)
Apply Lag:          0 seconds (computed 0 seconds ago)
Average Apply Rate: 39.00 KByte/s
Real Time Query:    OFF
Instance(s):
orcldbs1 (apply instance)
orcldbs2
Database Status:
SUCCESS

DGMGRL> show database orcldbs
Database - orcldbs
Role:               PHYSICAL STANDBY
Intended State:     APPLY-ON
Transport Lag:      0 seconds (computed 0 seconds ago)
Apply Lag:          0 seconds (computed 0 seconds ago)
Average Apply Rate: 39.00 KByte/s
Real Time Query:    OFF
Instance(s):
orcldbs1 (apply instance)
orcldbs2
Database Status:
SUCCESS

The output of above command shows that the MRP process is started on instance1 (orcldbs1) server. You can login to standby Node1 server and check whether MRP is running or not as shown below.
$ ps -ef | grep mrp
oracle   26667     1  0 15:17 ?        00:00:00 ora_mrp0_orcldbs1
oracle   27826 20926  0 15:21 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp

$ ps -ef | grep mrp
oracle   26667     1  0 15:17 ?        00:00:00 ora_mrp0_orcldbs1
oracle   27826 20926  0 15:21 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
Now that the MRP process is running, login to both Primary and Standby database and check whether the logs are in sync or not.

- Dataguard Broker commands
Below are some extra commands which you can use and check status of database.

DGMGRL> VALIDATE DATABASE orcldb;
Database Role:    Primary database
Ready for Switchover:  Yes
Flashback Database Status:
orcldb:  Off

DGMGRL> VALIDATE DATABASE orcldb;
Database Role:    Primary database
Ready for Switchover:  Yes
Flashback Database Status:
orcldb:  Off

DGMGRL> VALIDATE DATABASE orcldbs;
Database Role:     Physical standby database
Primary Database:  orcldb
Ready for Switchover:  Yes
Ready for Failover:    Yes (Primary Running)
Flashback Database Status:
orcldb:   Off
orcldbs:  Off

DGMGRL> VALIDATE DATABASE orcldbs;

Database Role:     Physical standby database
Primary Database:  orcldb
Ready for Switchover:  Yes
Ready for Failover:    Yes (Primary Running)
Flashback Database Status:
orcldb:   Off
orcldbs:  Off
STANDBY DB (192.168.1.12)

SQL> select NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME   DATABASE_ROLE    DATAGUAR
------ ---------------- --------
DELL   PHYSICAL STANDBY ENABLED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/DELL/arch
Oldest online log sequence     9
Next log sequence to archive   0
Current log sequence           11


No comments:

Post a Comment