ORA-12514 during switchover using Data Guard Broker (Update)

This is just a short update for an earlier post about getting ORA-12514 while performing switchover using DataGuard broker.

There was a comment on whether or not _DGMGRL static service is still required when performing a switchover in 11.2 and onwards.

In order for the broker to be able to successfully start an instance during a switchover operation, static service needs to be registered with the listener. Starting from 11.2.0.1 this service doesn’t have to be "<db_unique_name>_DGMGRL.<db_domain>". Oracle introduced a new instance-level property, StaticConnectIdentifier. As a value, this property accepts a valid net service name (defined in tnsnames.ora) or full connection identifier. Therefore, starting from 11.2.0.1 you have flexibility to use any service, which still needs to be statically registered with the listener.

Here is an example:

There are two databases db112a (primary) and db112b(standby) hosted on hosta and hostb respectively.

Below is the content of the tnsnames.ora. It is identical for both hosts:

DB112A=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB112A)
    )
  )

DB112B=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB112B)
    )
  )

listener.ora on HOSTA has the following content:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db112a)
      (ORACLE_HOME = /oracle/product/11.2/dbms)
      (SID_NAME = db112a)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

listener.ora for HOSTB has db112b service statically registered:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db112b)
      (ORACLE_HOME = /oracle/product/11.2/dbms)
      (SID_NAME = db112b)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Now the only thing left would be to set the StaticConnectIdentifier property for each of the databases.

DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - db112

  Protection Mode: MaxPerformance
  Databases:
    dg112a - Primary database
    dg112b - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> edit database db112a set property staticConnectidentifier='db112a'; 
DGMGRL> edit database db112b set property staticConnectidentifier='db112b'; 

And you should be all set to perform a switchover:

DGMGRL> switchover to dg112b
Performing switchover NOW, please wait...
New primary database "dg112b" is opening...
Operation requires shutdown of instance "dg112a" on database "dg112a"
Shutting down instance "dg112a"...
ORACLE instance shut down.
Operation requires startup of instance "dg112a" on database "dg112a"
Starting instance "dg112a"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg112b"

DGMGRL> show configuration

Configuration - db112

  Protection Mode: MaxPerformance
  Databases:
    dg112b - Primary database
    dg112a - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Should you not want to deal with staticConnectidentifier you still need to have "<db_unique_name>_DGMGRL.<db_domain>" statically registered in order for the broker to be able to start the instance.

Useful resources:

11g -  Oracle Data Guard Broker 11g Release 2 (11.2) - Oracle Data Guard Installation

12c-  Oracle Data Guard Broker 12c Release 1 (12.1) - Oracle Data Guard Installation

MOS Note # 1305019.1 - 11.2 Data Guard Physical Standby Switchover Best Practices using the Broker

Hope this helps.