Dataguard and DG Broker Setup Issues

This article comprises of various issues encountered by us while setting up Dataguard and DG Broker on 11gR2 RAC setup.

1)Connection to RMAN Auxiliary database gives ORA-1031

rman target sys/oracle123@tintin_dgmgrl auxiliary  sys/oracle123@snowy_dgmgrl

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Feb 15 10:52:20 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TINTIN (DBID=2175947715)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

To resolve this ensure that you are able to connect to database using sqlplus

sqlplus sys/oracle123@snowy_dgmgrl as sysdba

If this gives error then

a)Ensure password file exists for auxiliary database and remote_login_passwordfile=exclusive
b)In case of RAC ensure that tnsnames.ora entry is correct. It should look like below

snowy_dgmgrl =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev02.eglbp.corp.yahoo.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev01.eglbp.corp.yahoo.com)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = snowy_DGMGRL)))
)

We are not using load_balance and failover in this tns entry as only one instance is up while using rman duplication

2) RMAN duplication failing with following error

RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-29760: instance_number parameter not specified

This issue happens when you are trying to duplicate the database on same host using rman with spfile option.Even though we have set db_unique_name to say snowy, but instance name is still being set as tintin1/tintin2. Set init.ora parameters for auxiliary database and clone without using spfile option

run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
duplicate target database for standby from active database DORECOVER;
}

3)Switchover using DG Broker fails to bring up old primary database

DGMGRL> switchover to snowy;
Performing switchover NOW, please wait...
New primary database "snowy" is opening...
Operation requires shutdown of instance "tintin1" on database "tintin"
Shutting down instance "tintin1"...
ORACLE instance shut down.
Operation requires startup of instance "tintin1" on database "tintin"
Starting instance "tintin1"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

You can take a look at alert log for new primary database or drcsid.log under trace directory. In this case alert log was reporting following errors

Fatal NI connect error 12514, connecting to:
(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbdev01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=oradbdev02)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=tintin_DGB)(CID=(PROGRAM=oracle)(HOST=oradbdev02)(USER=oracle)))))

It’s reporting service_name as tintin_DGB but we have specified service name as tintin_dgmgrl.

{db_unique_name}_DGB.{db_domain}: This Service is used by the DMON-Processes to communicate between each other

{db_unique_name}_XPT.{db_domain}: This Service is used for Log Transport Services and FAL (corresponding Initialization Parameters are set once a Configuration is enabled) – Oracle 10.x only

As per My Oracle Support doc 745201.1, we can use DGConnectIdentifier property to override the _DGB service. But in our environment, it was already set. Even StaticConnectIdentifier parameter was showing correct value

DGMGRL> show instance tintin1 StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.66.80.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=tintin_DGMGRL)(INSTANCE_NAME=tintin1)(SERVER=DEDICATED)))'

To workaround this , we registered tintin_DGB service in listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = tintin_DGMGRL)
          (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
          (SID_NAME = tintin1)
    )
    (SID_DESC =
             (GLOBAL_DBNAME = snowy_DGMGRL)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = snowy2)
   )
	(SID_DESC =
             (GLOBAL_DBNAME = tintin_DGB)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = tintin1)
   )
  )

After making changes on both nodes and restarting listener we were able to perform switchover/switchback without any errors.