Dataguard broker configuration



Existing  Version : 10g
Primary database  : primedb
Standby database  : standb
ssh and use of spfile on both nodes


setup tnsnames.ora and listener.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 listener.ora on primedb : Additional entry (GLOBAL_DBNAME = primedb_DGMGRL)

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRIMDB_DGMGRL)
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)    
      (SID_NAME = PRIMEDB)
    )

  )

 listener.ora on standdb : Additional entry (GLOBAL_DBNAME = standdb_DGMGRL)

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STANDB_DGMGRL)
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)    
      (SID_NAME = STANDB)
    )

  )
tnsnames.ora on primedb,standdb and observer node : 
Two entries primedb and standdb, 
one entry with (SERVICE_NAME = primedb_DGMGRL) and 
one entry with (SERVICE_NAME = standdb_DGMGRL)

Both primary and standby
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;   -- Creates broker configuration files  automatically

SQL>SHOW PARAMETERS DG_BROKER_CONFIG

Primary
~~~~~~~~~~~
dgmgrl
DGMGRL> connect sys@primedb
Password: <Enter Password>

 DGMGRL> create configuration prime-stand-config AS PRIMARY DATABASE IS primedb  CONNECT IDENTIFIER IS primedb;
DGMGRL> ADD DATABASE standdb AS CONNECT IDENTIFIER IS standdb MAINTAINED AS PHYSICAL;
DGMGRL> show database verbose standdb;
DGMGRL> show database verbose primedb;
DGMGRL> enable configuration;
DGMGRL> show configuration;
DGMGRL> enable database standdb;

 DGMGRL> show database verbose standdb;


Enable fast start failover
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Ensure standby redologs are configured on both primary and standby :  SELECT TYPE,MEMBER FROM V$LOGFILE;

Enable Flashback Database enabled on both primary and standby : UNDO_RETENTION=3600,  UNDO_MANAGEMENT='AUTO', ALTER DATABASE FLASHBACK ON

 dgmgrl
DGMGRL> EDIT DATABASE primedb SET PROPERTY 'LogXptMode'='SYNC';
DGMGRL> EDIT DATABASE standdb SET PROPERTY 'LogXptMode'='SYNC';

 DGMGRL> EDIT DATABASE primedb SET PROPERTY FastStartFailoverTarget='standdb' ;
DGMGRL> EDIT DATABASE standdb SET PROPERTY FastStartFailoverTarget='primedb' ;

 DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY ; -- only if required
dgmgrl
DGMGRL> connect sys/oracle@primedb;

 DGMGRL> show configuration verbose;

 DGMGRL> show database standdb;

 DGMGRL> ENABLE FAST_START FAILOVER;
Now start the observer : DGMGRL>connect sys/oracle@primedb;   DGMGRL> START OBSERVER;
Verify configuration and databases health :SUCCESS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DGMGRL> show configuration verbose;
DGMGRL> show database primedb;
DGMGRL> show database standdb;

Perform switchover :
~~~~~~~~~~~~~~~~~~~~

 DGMGRL> SWITCHOVER TO standb;

 DGMGRL> show configuration verbose;
DGMGRL> show database primedb;
DGMGRL> show database standdb;


e.g RAC
dgmgrl> show configuration;

 Configuration - prime-stand-config
Protection Mode: MaxPerformance
Databases:
PRIMEDB - Primary database 
STANDB  - Physical standby database 
Fast-Start Failover: DISABLED 
Configuration Status: SUCCESS’

dgmgrl>show database 'PRIMEDB';

 User should be able to show database configuration as 
Database - PRIMEDB 
Role:            PRIMARY 
Intended State:  TRANSPORT-ON 
Instance(s): PRIMEDB1 PRIMEDB2 
Database Status: SUCCESS’

dgmgrl>show database 'STANDB';

 Database - STANDB 
Role:            PHYSICAL STANDBY 
Intended State:  APPLY-ON 
Transport Lag:   0 seconds (computed 7 seconds ago) 
Apply Lag:       15 minutes 39 seconds (computed 7 seconds ago) 
Apply Rate:      22.77 MByte/s 
Real Time Query: OFF 
Instance(s): STANDB1 (apply instance) STANDB2

Database Status: SUCCESS’