ORA-12514 during switchover using Data Guard Broker

I’ve seen, on several occasions, questions being asked about data guard broker being unable to automatically start the databases during a switchover, failing with ORA-12514 - TNS:listener does not currently know of service requested in connect descriptor?

This is most likely because the special service db_unique_name_DGMGRL has not been registered properly with the listener.

This is one of the requirements when configuring Data Guard broker.

From the Oracle documentation (Reference Oracle® Data Guard Broker 10g Release 2 (10.2)):

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:

LISTENER = (DESCRIPTION =
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
   (PO1RT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
   (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
   (ORACLE_HOME=oracle_home)))

I think the main reason for overlooking this prerequisite is because nothing about this is mentioned in Oracle 10g Data Guard Concepts and Administration Guide, the chapters for standby databases configuration.

So once the initial configuration is set up and works fine, the listener prerequisites for setting up Data Guard Broker are probably overlooked.

Of course, this applies only if Data Guard Broker is configured manually. If one uses OEM Grid Control to set up and configure the Data Guard Configuration, OEM will make the necessary changes.

Another point I want to make here is the importance of practicing various switchover and failover scenarios. It is not only that you will gain more experience and fill more comfortable doing the same thing in real situation, but you also will learn about any hidden configuration wholes that you may have overlooked or missed in your configuration steps.

Here is an example of this problem and its solution:

For this example I used two Oracle 10.2.0.1 databases, dg1db running as a primary database and dg2db running as a physical standby database.

[oracle@dg1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***
Connected.
DGMGRL> show configuration

Configuration
  Name:                dg-test
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    dg1db - Primary database
    dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

Now lets try the switchover. As you can see the role transition was done successfully, but the database startup that should happened at the end failed with ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

DGMGRL> switchover to dg2db;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "dg1db"
You must start instance "dg1db" manually
Operation requires startup of instance "dg2db" on database "dg2db"
You must start instance "dg2db" manually
Switchover succeeded, new primary is "dg2db"
DGMGRL>

After starting the databases manually, I checked the status of the Data Guard configuration and it was SUCCESS.

DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL>

Now I will make the necessary changes to the listeners and try the switchover in the opposite direction. After the change listener.ora should look like (pay attention to GLOBAL_NAME=dg1db_DGMGRL.localdomain).

[oracle@dg1 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = dg1db_DGMGRL.localdomain)
    (ORACLE_HOME = /u01/app/oracle/product/ora10g)
    (SID_NAME= dg1db)
  )
)

LISTENER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
)

[oracle@dg1 admin]$ lsnrctl reload
[oracle@dg1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2009 17:02:35

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2009 08:08:17
Uptime                    0 days 8 hr. 54 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/ora10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=1521)))
Services Summary...
Service "dg1db.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGB.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGMGRL.localdomain" has 1 instance(s).
Instance "dg1db", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1db_XPT.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
The command completed successfully

I did the same thing with the second listener.
Now the switchover will complete without any problems.
DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL> switchover to dg1db
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg2db" on database "dg2db"
Starting instance "dg2db"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg1db"
DGMGRL>

DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Primary database
  dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

DGMGRL>

Cheers,
Mihajlo Tekic