Oracle – Data Guard Broker Setup

Data Guard Broker Setup

Note

In this document,
Primary Server: Chennai
Secondary Server: Trichy

Primary DB Unique Name : PROD_DGP
Standby DB Unique Name : PROD_DGS


Prerequisite
  
 1. Dataguard
is configured and redo shipping and redo apply is happening, this document
intended for enable Data Guard Broker for an existing standby environment.
2. Database should be running in spfile.
3. Listener port should be opened in firewall between Primary DB server and Standby DB Server.


Check DG_BROKER_START parameter on Primary and Standby

             On
Primary and Standby
       Login
as Sys
       sqlplus
“/as sysdba”
show parameter
DG_BROKER_START
Set DG_BROKER_START to
true on Primary and Standby
                On
Primary and Standby
       alter
system set DG_BROKER_START = true scope=both;
               
Update listener.ora on
Primary and Standby
On Primary
LISTENER_PROD_DGP =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Chennai)(PORT
= 1537))
   )
SID_LIST_LISTENER_PROD_DGP
=
   (SID_LIST =
     (SID_DESC =
           (GLOBAL_DBNAME = PROD_DGP_DGMGRL)
           (ORACLE_HOME =
/oracle/app/oracle/product/PROD/11204)
           (SID_NAME = PROD)
     )
     (SID_DESC =
              (GLOBAL_DBNAME = PROD_DGP_DGB)
              (ORACLE_HOME =
/oracle/app/oracle/product/PROD/11204)
              (SID_NAME = PROD)
    )
    )
Note: If
db_domain parameter is not null, GLOBAL_DBNAME should be like
db_unique_name_DGMGRL.db_domain
On Standby
LISTENER_PROD_DGS =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Trichy)(PORT
= 1537))
   )
SID_LIST_LISTENER_PROD_DGS
=
   (SID_LIST =
     (SID_DESC =
           (GLOBAL_DBNAME = PROD_DGS_DGMGRL)
           (ORACLE_HOME =
/oracle/app/oracle/product/PROD/11204)
           (SID_NAME = PROD)
     )
     (SID_DESC =
              (GLOBAL_DBNAME = PROD_DGS_DGB)
              (ORACLE_HOME =
/oracle/app/oracle/product/PROD/11204)
              (SID_NAME = PROD)
    )
    )
Note: If
db_domain parameter is not null, GLOBAL_DBNAME should be like
db_unique_name_DGMGRL.db_domain
Sample TNSNAMES.ora
Primary and Standby
PROD_DGS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Trichy)
(PORT = 1537))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD_DGS_DGMGRL)
    )
  )
PROD_DGP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Chennai)
(PORT = 1537))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD_DGMGRL)
    )
  )
Create the data guard
broker configuration
On Primary DB Node
DGMGRL> connect
sys@PROD
Password:
Connected.
DGMGRL> CREATE
CONFIGURATION PROD_DG_CONFIG AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD_DGP;
Configuration “PROD_dg_config”
created with primary database “PROD”
Note: For 12c DB , you may get the below error when you create the
configuration.
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be
added
Solution:
On Primary and Standby DB, Connect as sysdba
alter system set log_Archive_dest_2=”;
DGMGRL> ADD
DATABASE PROD_DGS AS CONNECT IDENTIFIER IS PROD_DGS MAINTAINED AS PHYSICAL;
Database “PROD_dgs”
added
DGMGRL> SHOW
CONFIGURATION;
Configuration – PROD_dg_config
  Protection Mode: MaxPerformance
  Databases:
    PROD    
– Primary database
    PROD_dgs – Physical standby database
Fast-Start Failover:
DISABLED
Configuration Status:
DISABLED
DGMGRL> SHOW
DATABASE PROD_dgs
Database – PROD_dgs
  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    PROD
Database Status:
DISABLED
Enable configuration
On Primary DB Node
DGMGRL> connect
sys@PROD
Password:
Connected.
DGMGRL> ENABLE
CONFIGURATION;
Enabled.
DGMGRL> show
configuration
Configuration – PROD_dg_config
  Protection Mode: MaxPerformance
  Databases:
    PROD    
– Primary database
    PROD_dgs – Physical standby database
Fast-Start Failover:
DISABLED
Configuration Status:
SUCCESS
This step is needed if
you want to set Archival removal policy Standby Database.
[oracle@dbbocoprod13
DTPROD]$ 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 /
Connected as SYSDG.
DGMGRL>  edit database prod_dgs set property
Binding=’mandatory’;
Property
“binding” updated
Database switchover
1.  Make sure redo shipping is happening and redo is getting applied
into the standby.
2.  In the DGMGRL console, input “show configuration”, make sure there
is no error reported and configuration status should be SUCCESS.
3.  Verify no error is reported in alert log.
4.  Make sure DG listener is up and running on primary and standby.
On Primary
DB Node
 dgmgrl sys/*****@PROD_dgs
 DGMGRL for Linux: Version 11.2.0.4.0 – 64bit
Production
 Copyright (c) 2000, 2009, Oracle. All rights
reserved.
 Welcome to DGMGRL, type “help” for
information.
 Connected.
 DGMGRL> switchover to PROD
 Performing switchover NOW, please wait…
 Operation requires a connection to instance
“PROD” on database “PROD”
 Connecting to instance “PROD”…
 Connected.
 New primary database “PROD” is
opening…
 Operation requires startup of instance “PROD”
on database “PROD_dgs”
 Starting instance “PROD”…
 ORACLE instance started.
 Database mounted.

 Switchover succeeded, new primary is “PROD”
  • April 4, 2017 | 12 views
  • Comments