Data guard broker - its concept and configuration

In this topic I will discuss about data guard broker - Before explaining about broker concepts and its configuration , you need to have good idea about oracle data guard and its functionality . 

So  an Oracle Data Guard configuration consists of one primary database and a combination of standby databases and far sync instances (12 feature ) that receive redo directly from the primary database. The databases in an Oracle Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located as long as they can communicate with each other. For example, you can have a standby database on the same system as the primary database, along with two standby databases on another system. In this demo I configured my data guard set up in my local computer -

The Oracle Data Guard broker logically groups these primary and standby databases 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 Oracle Enterprise Manager Cloud Control (Cloud Control) or the Oracle Data Guard command-line interface.

The advantage of using data guard broker over  SQL*Plus to manage data guard you can read here 

https://docs.oracle.com/database/121/DGBKR/concepts.htm#DGBKR3678 

Data Guard Broker is part of the normal Oracle Database Enterprise Edition installation and an integral part of Data Guard. You can do Data Guard related configuration, set transport methods, apply set-up 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 configuration made through DGMGRL

iii) Data Guard Net Server (NSVn)

NSV processes are responsible for making connection with the remote database and to send across any work 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

By default they are stored in $ORACLE_HOME/dbs directory with the filename of dr1<DB_UNIQUE_NAME>.dat and dr2<DB_UNIQUE_NAME>.dat, 

For RAC instances, you need to keep the files in shared location where all database instances can access it. We will set following Initialization parameters in Primary and standby database before enabling data guard broker in RAC environment.  

At primary ,

alter system set dg_broker_config_file1='+DATA/PRIM/DGBROKER/dr1tintin.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FLASH/PRIM/DGBROKER/dr2tintin.dat' scope=both sid='*'

At standby,

alter system set dg_broker_config_file1='+DATA/STBY/DGBROKER/dr1tintin.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FLASH/STBY/DGBROKER/dr2tintin.dat' scope=both sid='*'

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.

Step-By-Step Configuration Of Data Guard Broker in Oracle 11g

As  we  have already  discuss  about  the Data Guard Broker  and its components . Here we will configure the data Guard Broker . I configured my data guard set up in my personal computer , so here is the steps to configure data guard broker

Primary Databse   =  dstudy
Standby Database =  study

Step 1 :  Check the Data Guard Broker process 
SQL> sho parameter dg_broker
NAME                                   TYPE             VALUE
-----------------                    ----------          ----------
dg_broker_start                boolean          FALSE

Step 2  : Start the Data Guard Broker Process on Primary database   
SQL>alter system set dg_broker_start=true scope=both;
System altered.

Step 3 : Check DG_BROKER on standby database and start it 
SQL> sho parameter dg_broker
NAME                                    TYPE             VALUE
-----------------                      ----------         ----------
dg_broker_start                  boolean         FALSE

SQL>alter system set dg_broker_start=true scope=both ;
System altered.

Note:-  

This will start Data Guard monitor process(DMON) - 
if your database is  in RAC take care DG_BROKER_CONFIG_FILE1 , DG_BROKER_CONFIG_FILE2 parameter before setting dg_broker_start=true

Step 4 :   Edit the listener.ora file

Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domain value .

SQL> show parameter db_domain
NAME                              TYPE               VALUE
--------------                  -----------         --------------
db_domain                      string

Since the value of db_domain  is null so the the value of  GLOBAL_DBNAME = DSTUDY_DGMGRL for primary database and for standby  GLOBAL_DBNAME = STUDY_DGMGRL. The primary listener.ora file  is as 

LISTENER_NEW =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = Mypc)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

)

ADR_BASE_LISTENER_NEW = C:\app\mahekarthya

SID_LIST_LISTENER_NEW =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\mahekarthya\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    

    (SID_DESC =

       (GLOBAL_DBNAME = DSTUDY_DGMGRL)

      (SID_NAME = DSTUDY)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      )

      (SID_DESC =

       (GLOBAL_DBNAME = STUDY_DGMGRL)

      (SID_NAME = STUDY)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      )

  )

Here I have configured both my primary and standby databases  in the same machine ,so I added these entries in the same listener.log file.

Step 5 : Configure the Data Guard Configuration 
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> Now connect either to primary or to standby database , Here I connected to my primary database.
DGMGRL> connect sys/xxxx@dstudy 
Connected.

DGMGRL> show configuration

ORA-16532: Data Guard broker configuration does not exist

Since this is new setup, there is no configuration currently in database. Let's add 'dstudy' database to the configuration.

Now we have to add  data guard configuration, the syntax is

CREATE CONFIGURATION configuration-name AS

PRIMARY DATABASE IS database-name

CONNECT IDENTIFIER IS connect-identifier;

Where

database-name:-

The name that will be used by the broker to refer to the primary database. It must match (case-insensitive) the value of the corresponding database DB_UNIQUE_NAME initialization parameter.

connect-identifier:-

A fully specified connect descriptor or a name to be resolved by an Oracle Net Services naming method (for example, TNS). The value you specify is also used as the initial value of the DGConnectIdentifier configurable database property.

DGMGRL> create configuration 'DgStudyConfig'
> as primary database is 'dstudy'
> connect identifier is dstudy ;

Configuration "DgStudyConfig" created with primary database "dstudy"

Once the configuration is created then check the status of configuration .

DGMGRL> show configuration
Configuration            - dgdstudy
Protection Mode       : MaxPerformance
Databases                : dstudy - Primary database
Fast-Start Failover    : DISABLED
Configuration Status : DISABLED

Step  6 :  Add standby database to the data broker configuration 

Creates a new standby database profile and adds it to the existing broker configuration. The AS CONNECT IDENTIFIER clause is optional. If you do not specify this clause, the broker will search the LOG_ARCHIVE_DEST_n initialization parameters on the primary database for an entry that corresponds to the database being added.

Format

ADD DATABASE database-name

[AS CONNECT IDENTIFIER IS connect-identifier]

Where

database-name:-

The name that will be used by the broker to refer to this standby database. It must match (case-insensitive) the value of the corresponding database DB_UNIQUE_NAME initialization parameter.

connect-identifier:-

A fully specified connect descriptor or a name to be resolved by an Oracle Net Services naming method (for example, TNS). The value you specify is also used as the initial value of the DGConnectIdentifier configurable database property. If you do not specify this option, the broker will search the primary database LOG_ARCHIVE_DEST_n parameters for a corresponding entry to the standby database and use its SERVICE value for the connect-identifier.

DGMGRL>  add database 'study' as
> connect identifier is study
> maintained as physical ;
Database "study" added

DGMGRL> show configuration
Configuration             -  dgdstudy
Protection Mode         :  MaxPerformance
Databases                   :  dstudy - Primary database
                                    : study - Physical standby database
Fast-Start Failover      :  DISABLED
Configuration Status    :  DISABLED

Now we see that show configuration is showing that both databases have been added to configuration. Configuration Status is still set to Disabled. You can use below command to enable the configuration (meaning DG broker will start managing dataguard now)

Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    dstudy - Primary database

    study  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ORA-16610: command "Broker automatic health check" in progress

DGM-17017: unable to determine configuration status

DGMGRL> show configuration

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    dstudy - Primary database

    study  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Step 8 : View the Primary and Standby database properties 

DGMGRL> show database verbose dstudy

Database - dstudy

Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s): dstudy

  Properties:

    DGConnectIdentifier             = 'dstudy'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'BDC7-L-2JRRWQ1'

    SidName                         = 'dstudy'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE

_NAME=DSTUDY_DGMGRL)(INSTANCE_NAME=dstudy)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'ARC%S_%R.%T'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

DGMGRL> show database verbose study

Database - study

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: ON

  Instance(s):

    study

  Properties:

    DGConnectIdentifier             = 'study'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'ENABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'MANUAL'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'BDC7-L-2JRRWQ1'

    SidName                         = 'study'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE

_NAME=study_DGMGRL)(INSTANCE_NAME=study)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'C:\oradata\archive'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'log%t_%s_%r_%d.arc'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

Our data guard broker set up is ready now - we will do a switch over in the next topic using data guard broker.

Shell scritp to check space utilization for normal/high redundancy disk group

The space allocation, utilization as well as the space availability in a ASM diskgroup is completely dependent on the type of redundancy defined for a ASM diskgroup.
The free_mb from v$asm_diskgroup is valid only when we are using a external redundancy disk group . why it differ for disk group having redundancy NORMAL/HIGH ?  Its pretty clear from the following facts 

Normal Redundancy: In Normal redundancy, the disks are two way mirrored, therefore we need at least two disks of same size to create a diskgroup with normal redundancy.
High Redundancy: In High redundancy, the disks are three way mirrored, therefore we need at least three disks of same size to create a diskgroup with high redundancy.
External Redundancy: In External redundancy, the disks are not mirrored by ASM. In this case, we may want the disk mirroring to be maintained external to ASM by means of storage array or we may decide not to mirror the disks at all

So in a normal redundancy diskgroup, we should divide the TOTAL_MB/FREE_MB by two to get the actual space allocation and for a high redundancy diskgroup we have to devide by three.

However, this is a lengthy approach; as we need to first identify the diskgroup redundancy level and then adjust our query to get the actual result. There must be some simple method available to identify the actual diskgroup space availability. Yes, it is there……
We can query the USABLE_FILE_MB from V$ASM_DISKGROUP to know the actual free space available within a diskgroup.

I created a shell script to check the space utilization for normal/high redundancy disk group - as I am using exadata in my firm , by default  I can't find any external redundancy  disk group .

 #! /bin/bash
clear
export TERM=xterm
ccc='\033[0;33m'
NC='\033[0m'
green='\033[0;32m'
under='\033[4m\033[1;36m'
green2='\033[30;42m'
for database in $(ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3)
do
export ORACLE_SID=$database
#export ORACLE_SID=${database}1
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
echo -e "${under}  ASM Disk Group Usage for - `hostname -f` (`hostname -i`) ${NC}"
echo -e " "
echo -e "Connected ASM instance is ${green2}$ORACLE_SID${NC}"
sqlplus -s " / as sysdba" <<\EOF
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name         FORMAT a20           HEAD 'Disk Group|Name'
COLUMN state                    FORMAT a11           HEAD 'State'
COLUMN type                     FORMAT a6            HEAD 'Type'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN free_gb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN pct_free               FORMAT 999.99        HEAD '%Free'
SELECT
    name                                         group_name
  , state                                        state
  , type                                         type
  , Round((TOTAL_MB/1024),2)                     total_gb
  , Round(((TOTAL_MB - USABLE_FILE_MB)/1024),2)  used_gb
  , Round((USABLE_FILE_MB/1024),2)               free_gb
  , Round((USABLE_FILE_MB/TOTAL_MB)*100,2)       pct_free
FROM
    v$asm_diskgroup
ORDER BY pct_free desc;
exit;
EOF
done

Sample output :- 



 Enjoy :)