DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 4, 2017

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"

No comments: