7

I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/23488394/how-to-install-sql-plus-client-in-linux

My connection string is correct, but I get the error

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

I tried resolving this issue by attempting to looking at my tnsnames.ora, but it did not exist and the path connected to my $ORACLE_HOME did not have the subfolders "/network/admin/". In order to resolve this, I created both folders and created a tnsnames.ora file in the new admin/ containing my connection string. Unfortunately, I still get the same error.

The path to my new tnsnames.ora file being: /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora

For reference my connection string is structured the following way:

(DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

And my command I am putting into my terminal is: sqlplus "userName/myPassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))"

What should I do to resolve this error?

2
  • does tnsping AARCER1 work? Or sqlplus userName/myPassword@AARCER1?
    – Philᵀᴹ
    Mar 19, 2015 at 18:09
  • I think that it is a good habit to set TNS_ADMIN env variable in your profile and point it to your folder with tnsnames.ora. Especially helpful when using more than one client on one machine.
    – elfcheg
    Mar 20, 2015 at 11:21

2 Answers 2

7

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file.

Your entry in tnsnames.ora file should be like this:

AARCER1 = (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

Then you can shorten your command to sqlplus userName/myPassword@AARCER1 - that is the purpose of tnsnames.ora.

In order to use tnsnames.ora you must also have file sqlnet.ora. There you need an entry like this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

However, default value is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect), so actually you can skip it.

Set environment variable TNS_ADMIN to location of these files, i.e.

export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
1

You can also avoid the tnsnames.ora file by using the EZ Connect method.

sqlplus scott/[email protected]:1524/<service name goes here>

You can get your service name by running lsnrctl status command.

1
  • I successfully tested my scripts using the above method. But when I transfer the scripts to another server, the connections fails (12154). Any idea where should I check?
    – aaa
    Jun 15, 2021 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.