oracle dataguard borker setup and dataguard switchover

Oracle Setup Dataguard Broker

Our setup:
Primary site:
Database: RAC
Nodes: rac1, rac2

Standby site:
Database: RAC_STBY
Nodes: racdr1

Setup listener on primary and standby
add the below entry in listener.ora under grid listener
Primary Node1:

SID_LIST_LISTENER =
 (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = RAC_DGMGRL.localdomain)
                (SID_NAME = RAC1)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

Primary Node2:

SID_LIST_LISTENER =
 (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = RAC_DGMGRL.localdomain)
                (SID_NAME = RAC2)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

Standby:

SID_LIST_LISTENER =
 (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = RAC_STBY_DGMGRL.localdomain)
                (SID_NAME = RAC_STBY1)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

Reload listener on primary and standby

lsnrctl reload

Setup the tnsnames.ora on primary and standby site, please note that we will use host vip as host rather than using scan

Primary node rac1 tnsnames.ora entry:

[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC.localdomain)
    )
  )
RAC_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY.localdomain)
    )
  )
RAC_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC1)
    )
  )
RAC_STBY_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC_STBY1)
    )
  )

RCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RCAT1.localdomain)
    )
  )
[oracle@rac1 admin]$ 

Node rac2 tnsnames.ora entry:

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC.localdomain)
    )
  )
RAC_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY.localdomain)
    )
  )
RAC_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC2)
    )
  )
RAC_STBY_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC_STBY1)
    )
  )
RCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RCAT1.localdomain)
    )
  )

[oracle@rac2 admin]$ 

Standby site tnsnames.ora node racdr1:

[oracle@racdr1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC.localdomain)
    )
  )
RAC_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY.localdomain)
    )
  )
RAC_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC1)
    )
  )
RAC_STBY_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
      (INSTANCE_NAME=RAC_STBY1)
    )
  )

RCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RCAT1.localdomain)
    )
  )

[oracle@racdr1 admin]$ 

After all these setups try tnsping from each node using each tns entry like below. Make sure that it is working from all nodes (Primary and Standby)

tnsping RAC
tnsping RAC_STBY
tnsping RAC_DGMGRL
tnsping RAC_STBY_DGMGRL

Set the parameter on Primary

SQL> alter system set dg_broker_config_file1='+DATA/dr1rac.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+DATA/dr2rac.dat' scope=both sid='*';
System altered.
SQL>
SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*';
System altered.
SQL>
SQL> show parameter dg
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/dr1rac.dat
dg_broker_config_file2               string      +DATA/dr2rac.dat
dg_broker_start                      boolean     TRUE
SQL>

On standby set the parameters as:

SQL> alter system set dg_broker_config_file1='+DATA/dr1RAC_STBY.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+DATA/dr2RAC_STBY.dat' scope=both sid='*';
System altered.
SQL>
SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*';
System altered.
SQL>
SQL> show parameter dg
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +DATA/dr1RAC_STBY.dat
dg_broker_config_file2               string      +DATA/dr2RAC_STBY.dat
dg_broker_start                      boolean     TRUE
SQL>

Log in as oracle and then perform the steps below to setup broker configuration:

[root@rac1 ~]# . ./db_env
[root@rac1 ~]# dgmgrl sys/******@rac
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> 

Create the broker configuration

DGMGRL> create configuration RACDG as primary database is 'RAC' connect identifier is 'RAC';
Configuration "racdg" created with primary database "RAC"
DGMGRL>
DGMGRL> add database 'RAC_STBY' as connect identifier is 'RAC_STBY' maintained as physical;
Database "RAC_STBY" added
DGMGRL>
DGMGRL> enable configuration
Enabled.
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database 'RAC_STBY'
Database - RAC_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    RAC_STBY1
Database Status:
SUCCESS
DGMGRL>
DGMGRL>

And the dataguard broker setup is done.

Switchover to standby database:
Make sure you are connected to standby database using dgmgrl and then do the switchover

[oracle@racdr1 ~]$ dgmgrl sys/********@RAC_STBY
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>
DGMGRL> switchover to 'RAC_STBY'
Performing switchover NOW, please wait...
New primary database "RAC_STBY" is opening...
Operation requires shutdown of instance "RAC1" on database "RAC"
Shutting down instance "RAC1"...
ORACLE instance shut down.
Operation requires startup of instance "RAC1" on database "RAC"
Starting instance "RAC1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "RAC_STBY"
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC_STBY - Primary database
    RAC      - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>