Wednesday, January 14, 2009

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

14 comments:

Fayyaz said...

Nice article. I have wasted my 5 hours to find out why it is giving error for Listener. I found my solution now.....
Thanks man.

MT said...

Hi Appoo,

Thanks for reading.

I am glad this article has helped you out to find the solution you were looking for.

--Mihajlo

Rajeshkumar Govindarajan said...

useful and information articles

Anonymous said...

how do you determine what to specify in the GLOBAL_DBNAME.

How do i find out the GLOBAL_DBNAME

Freight Broker Training said...

I would like to thank the author of this blog for posting such a wonderful blog. You blog is very informative. Thanks a lot!

Anonymous said...

I know this is very old, but in case you're still looking....

Where does this line in your listener.ora come from?

Service "dg1db_XPT.localdomain" has 1 instance(s).

I have the _DGMGRL service, but am having startup errors, and DG seems to use this _XPT extension for something. Ideas? -Todd

Anonymous said...

first show this parameter

sql> show parameter db_unique_name
Name type value
-----------------------------------
db_unique_name string stand

Sql> show parameter db_domain

Name type value
---------------------------------
db_domain string


Then

GLOBAL_DBNAME =stand_DGMGRL


if you set db_domain

Sql> show parameter db_domain

Name type value
---------------------------------
db_domain string mydomain.com


Then

GLOBAL_DBNAME =stand_DGMGRL.mydomain.com


do this for both primary ,standby databse

Anonymous said...

Nice post

Anonymous said...

HI, I' HAVE A SIMILAR PROBLEM, THE SWITCHOVER WORKS FINE, BUT DON´T GET UP THE NEW STANDBY DATABASE, AND NOT DISPLAY ANY ERROR... BUT SAYS:
You must start instance "meta4conti" manually
MY LISTENE CONFIGURATION II'S FINE. I DONT KNOW WHATS IS THE PROBLEM. THANKS FOR YOUR TIME!

Mathew said...

If primary and standby are on the same server, what will be the configuration of listener.ora and tnsnames.ora for Dataguard Broker configuration?

Anonymous said...

Thanks for this post. From my experience I don't think this is mentioned in 11g docs either. In 11g though, it appears to be _DGB instead of _DGMGRL.

MT said...

First I want to thank everyone for reading this blog.

@tinky2jed, in 11.2 you don't necessarily need _DGMGRL service, however you still need to statically register with the listener so the broker can restart the instance. Instead of _DGMGRL you can statically register any service, however in that case you'll have to specify StaticConnectIdentifier instance specific property in your DG configuration. (I'll write I small update some of the next days)


DGB service seems to be used by the broker to check the heartbeat between the nodes. (http://bit.ly/15KchtQ).


As far as the documentation goes, it still covers the topic in the prerequisite section of “Oracle Data Guard Installation” Chapter(http://bit.ly/18DABDp)

Alex said...

Hello.
I strugled entire days with this issue, I tried everything posted here and in many other blogs.

Finally, I found the solution on Metalink note for 11gR2:

"Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL (Doc ID 308943.1)"

It's not too clear but at the end, it says that there is a new parameter called "StaticConnectIdentifier" which you have to set propery.

I've removed the domain for the node and it started to work!

Thanks,
Alex.

Maziz said...

Thanks work perfect . i wonder why it is not mention on oracle document .