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

[Former Member]

Hello All,

I am getting ORA 12154 error and DC to DR log shipping is not happening. To summarize the set up, It's an HP-UX High availability 2 node cluster and Dataguard is configured.

Two days ago, an offline database back up was initiated in Prod unintentionally, after which The mentioned error is generating. The log recovery at DR is also affected due to this.

Kindly let me know if any logs that can be shared for more analysis.

Any suggestion to fix this error is appreciated.

Regards,

Ameya Bobhate

[janos_mucsi-besze · Accepted solution]

Hi Ameya,

Check if "tnsping <SID>" works from primary (HA) to standby and vica versa.

Sometimes a trailing 'space' in SQL NET configuration (listener.ora,tnsnames.ora) can cause this issue. You may try to check the listener.ora and tnsnames.ora with an expert text editor like notepad++.

Or you may to correct and configuration via netca and netmgr tools.

Regards,

János

[former_member188883]

Hi Ameya,

The error message implies

A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured

Please check tnsnames.ora and sqlnet.ora are perfectly maintained without any missing syntax.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hello Deepak,

I did check the entries and found them unchanged. I took an offline database backup, where DR was not brought down and this error has occurred. I am not sure what approach should I take before correcting this error. TNSNAMES.Ora entries are unchanged.

Regards,

Ameya Bobhate

[former_member188883]

Hi Ameya,

To begin the troubleshooting

Execute lnsrctl services on DR database

The error which you get means that it cannot resolve the connect string given in log_archive_dest_2, have you set NAMES.DEFAULT_DOMAIN in your sqlnet.ora ?

1) ensure that you are checking the correct tnsnames.ora, should be in $ORACLE_HOME/network/admin unless TNS_ADMIN is defined.

2) try suffixing the tns_alias with a domain name in log_archive_dest_2 and tnsnames.ora.

3) you can directly specify the connect string instead of alias in the parameter log_archive_dest_2, something like below ( if this works then you can confirm that the the issue is with tnsnames.ora)

alter system set log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR ASYNC NOAFFIRM' scope=both;

Hope this helps.

Regards,

Deepak Kori

[former_member188883]

Hi Ameya,

Additionally refer thread http://scn.sap.com/thread/3151285

Similar issue is discussed and resolved.

Regards,

Deepak Kori

[Former Member]

Hello Deepak; Janos,

I restarted the DC and DR database. I am personally sceptical about restart but I went ahead anyway and now DC-DR log shipping is fine. DR is recovering too.

Not sure why the issue occurred, so I'll monitor for the time being.

Thank you for your generous support.

Regards,

Ameya