How to Configure the Data Guard broker?

-

 Data Guard broker is a nice tool to switch over and fail over the database in data guard environment. Using data guard broker is like a driving a car with navigation system. Hence, if broker fails in the middle of switch over, then we don't know where we are and we got to troubleshoot and see which step it failed.


I personally use manual switch over and of course there are some database i use broker too.

How do we configure the broker?  Please follow these steps.

My Database info :

Primary database devdb12
Standby database  devdb12_dg

Step 1   Configuring the broker parameters

Set initialization parameter DG_BROKER_START to a value of TRUE on both databases so that Data Guard Broker background process (DMON) would start automatically whenever a Data Guard configuration was successfully implemented.

ALTER SYSTEM SET dg_broker_start = TRUE;








Broker creates two data file to store the information. It creates on the default directory$ORACLE_HOME/dbs.  We can also change the directory by using the below command.

alter system set dg_broker_config_file1 = '/data01/dbArch/' scope=both;
alter system set dg_broker_config_file2 = '/data01/dbArch/' scope=both;

Step 2   Configuring the listener.

During actions like a swichtover and a failover the Dataguard Broker interface will stop and start the instances. The Dataguard Broker Interface needs static registration of the databases in the listener.ora files. The required format is db_unique_name_DGMGRL[.db_domain].

The below entry should be in primary database listener.ora file. Listener should be up and running.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 99.999.99.999)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = devdb12)
      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)
      (SID_NAME = devdb12)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = devdb12_dgmgrl)
      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)
      (SID_NAME = devdb12)
    )
)

The below entry should be in standby database listener.ora file. Listener should be up and running.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 99.999.99.999)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = devdb12_dg)
      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)
      (SID_NAME = devdb12_dg)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = devdb12_dg_dgmgrl)
      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)
      (SID_NAME = devdb12_dg)
    )
)

Step 3   Create the broker for primary database. This command can be executed only on primary database.  Use the CREATE CONFIGURATION command to initialize a Data Guard Broker configuration named devdb12_broker, and confirm its creation with the SHOW CONFIGURATION command:

create configuration 'devdb12_broker' as
primary database is devdb12
connect identifier is devdb12;








show configuration













Step 4 Add the broker for standby database. This command can be excuted only on primary database. If we have three standby then, we need to add broker config for three times on the primary database.

add database devdb12_dg as
 connect identifier is devdb12_dg
 maintained as physical;







show configuration












Step 5  Enabling the broker

Issue the ENABLE CONFIGURATION command to activate the configuration and then confirm the successful activation of the primary and standby databases via the SHOW DATABASE command:

enable configuration;






show database devdb12
show database devdb12_dg

























Show configuration














Now you are done with Broker setup! :-)

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more