Getting error ORA-12154: TNS:could not resolve the connect identifier specified

[Kaustubh Kane]

Hi All,

Precondition:
Primary database is up
Standby database is down

Issue:
When I a trying to connect to Standby database from the Primary database I am getting the following error:
ORA-12154: TNS:could not resolve the connect identifier specified


But I do not get any error when Primary and Standby databases are up. (Primary is up and running and Standby is mounted as Standby)

Could you please help me understand the issue?

Thanks and regards,
Kaustubh Kane.

[Steven E. Protter]

Shalom,

This is a database connectivity issue.

Either there is a real networking problem preventing contact with the actual system or the problem resides in db configuration.

tnsnames.ora
sqlnet.ora
listner.ora

tnsping is the tool of choice for checking and correcting these configuration.

This is a generic error and unfortunately it could be caused by an OS bug (patching) or an Oracle bug. Check metalink.oracle.com for more info.

SEP

[Jonathan Fife]

That error could indicate a number of things -- DNS issues, tns configuration issues, listener issues...Is the standby on a remote server?

What exactly are you trying to do when you get the message?

[Ben Dehner]

This error occurs when the Oracle client cannot resolve the connection string to a database. But it is really odd that this occurs only when the standby database is down. I would typically expect ORA-12514, "listener could not resolve SERVICE_NAME ...". But there are a couple of things to check.

Insure that the connection string is defined the tnsnames.ora file. Also insure that, in the sqlnet.ora file, that the NAMES.DIRECTORY_PATH directive includes TNSNAMES.

Also watch for the NAMES.DEFAULT_DOMAIN directive in the sqlnet.ora. If this is defined, it will be appended to any unqualified connection string. E.g., if

NAMES.DEFAULT_DOMAIN = us.mycompany.com

then "tnsping mydb" (or sql*plus or what have you) will actually try to resolve the connection string "mydb.us.mycompany.com", so that this fully qualified service name must be in the tnsnames.ora file. Again, I'm not sure why the operational status of the standby database would make a difference.

[whiteknight]

Hi,

I attached the TNS troubleshooting guide to see what is a probable cause.

Hope this help you to narrow down the issue.

WK

[Joelmel Roche]

Hi,

Perform these steps:

1. Verify that a tnsnames.ora file exists.
2. Verify that there are not multiple copies of the tnsnames.ora file.
3. In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor.
4. Verify that there are no duplicate copies of the sqlnet.ora file.
5. If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string.
6. If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out.
7. If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name.
8. Activate client tracing and repeat the operation.