topic Re: ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specifie in Administration and Deployment

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified.

ibrwa — Fri, 25 Mar 2022 10:27:49 GMT

Dear SAS community,

I'm trying to connect to Oracle with Libname statement but I got an error. I have done lot of research and checks but I'm blocked. I try to run the following libname statement:

libname ORGORA Oracle user=ORG password="ORG" schema=ORG_SYS path="ORG";

I have the following error:

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified.
ERROR: Error in the LIBNAME statement.

Oracle and SAS are installed in the same server. I have made a look in the tnsnames.ora and I can find the path correctly defined:

ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORG)
)
)

I then check that in the server I can ping the path ORG which works fine also:

[oracle@xxxxx ~]$ tnsping ORG
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-MAR-2022 11:22:48
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORG)))
OK (0 msec)

I have also check in SQL developer, and I can connect without any issues. Please see the attachment. I have restarted the database, restarted the listener but I'm blocked.

Can you please help me ?

Thank you very much in advance.

Re: ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specifie

Sajid01 — Sat, 26 Mar 2022 22:58:21 GMT

Hello
Successful connection with SQL developer means you have the proper credentials.
Try using the following libname statements

libname ORGORA Oracle user=ORG password=ORG path=ORG;

Re: ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specifie

Patrick — Sun, 27 Mar 2022 00:43:56 GMT

Have you already seen this discussion?
https://communities.sas.com/t5/SAS-Programming/ORACLE-connection-error/td-p/201064

Re: ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specifie

ibrwa — Fri, 01 Apr 2022 07:24:45 GMT

Hello @Patrick

Thank you for your reply. I have solved the issue. So what happens? When I configured the Oracle client for SAS access to Oracle I relied on this tutoriel: https://blogs.sas.com/content/sgf/2017/04/25/how-to-configure-oracle-client-for-successful-access-to-oracle-databases-from-sas-viya/

The tuto explains this: To configure the Oracle client, create a file called tnsnames.ora, for example, in the /etc/ directory. Paste the following lines with the appropriate connection parameters of your Oracle DB into the tnsnames.ora file. (Replace"your_tnsname", "your_oracle_host", "your_oracle_port"and"your_oracle_db_service_name"with parameters according to your Oracle DB implementation)

However I think that it is not a good practice. Why ? The tnsnames.ora used by Oracle software can be updated if you create a new service name like I did. So if you create a separate tnsnames.ora in /etc you need to update it each time when the original tnsnames.ora is modified.

I my situation I forgot to update the file /etc/tnsnames.ora. Thus, my new service names ORG is not present in /etc/tnsnames.ora then I got the issue. It is better to use TNS_ADMIN variable directly used in Oracle which is: $ORACLE_HOME/admin/network then you I don't need to each time update the file /etc/tnsnames.ora.

But finally yes I have solved my issue.

Thank you for your help.

Re: ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specifie

Patrick — Fri, 01 Apr 2022 07:45:34 GMT

@ibrwa Thank you for sharing with the SAS community. Also feel free to add a comment to the referenced blog if you think there is a better or alternative way how to do this. I'm sure this will be helpful to others facing similar challenges in the future.