How to connect SQLPlus with tnsnames.ora

Question

I want to connect with my Oracle database without installing Oracle Client.

I downloaded:

• "Instant Client Package - Basic" and
• "Instant Client Package - SQL*Plus"

Then I created folder on C:\Oracle\instantclient, where I extracted all packages.

I have been set system environment like:

Path - C:\Oracle\instantclient NSL_LANG - with properly key ORACLE_HOME - C:\Oracle\instantclient ORACLE_SID - C:\Oracle\instantclient TNS_ADMIN - C:\Oracle\instantclient

Then I created tnsnames.ora file with configuration in C:\Oracle\instantclient

and when I puted a command to cmd:

sqlplus user/password @HOST

I have a message like:

ERROR: ORA-12560: TNS:protocol adapter error

but when I tried like:

sqlplus user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=address to host)(Port=1521))(CONNECT_DATA=(SID=address to SID)))

everything works properly. Why SQL have a problem with recognize tnsnames.ora file?

Answer 1

Your command should be:

sqlplus user/password@HOST

with no space between the password and @HOST part.

With the space it treats the @HOST as a script to execute once you've logged in, and it tries to connect locally, which produced that TNS error. (As you don't log in the HOST isn't ever evaluated to establish if it exists, so it's effectively noise at this point).

C:\>sqlplus -l -s x/y @HOST
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

With the space removed it looks for HOST as a TNS alias:

C:\>sqlplus -l -s x/y@HOST
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

For me that still gets an error since I don't have HOST in my tnsnames.ora, but it's a different error and you can see it's at least trying to use it as a TNS alias. If you have it defined properly it will be able to connect to your database.

Answer 2

NEVER edit $ORACLE_HOME/network/admin/tnsname.ora manually, use "netmr" to create Local Name. Don't know why it's same but do not work.

Answer 3

This commando works fine for me:

sqlplus /nolog connect username/password@hostname:port/SERVICENAME