CODEWITHSUNDEEP
oracle-database
Simon
Simon

Reputation:

Oracle TNS: net service name is incorrecly specified

I'm trying to access an Oracle database on an old server we inherited for a client.

I'm confident I have the Oracle database and listener started and working, but when trying to access sqlplus or the exp commands, I'm getting the following error:

ORA-12162: TNS:net service name is incorrectly specified

I have edited the tnsnames.ora file to change the host to 127.0.0.1 rather than an external URL and I am able to successfully tnsping my connection, but am not getting much further.

Upvotes: 32

Views: 160970

Answers (8)

shonky linux user
shonky linux user

Reputation: 6428

This error is a bit misleading. In my case I was using the 19C instant client and wanted to connect to any of the predefined database service names defined in tnsnames.ora

I had the tnsnames.ora file in the Instant Client default location $ORACLE_HOME/lib/network/admin but the ORA-12162 error occurred on any sqlplus connection attept.

I'm not sure why tnsnames.ora wasn't working in the default location, but once I set a TNS_ADMIN environment variable pointing to the full path of the network/admin directory it worked correctly.

Upvotes: 0

John McG
John McG

Reputation: 698

Try setting the Oracle SID

//for windows server.

set ORACLE_SID=database name

//for linux server

export ORACLE_SID=database name

Ideally, database name here should be SID(Site Id). These evn variable should go in file ".profile" of user "oracle"

Upvotes: 38

mahesh
mahesh

Reputation: 81

export ORACLE_SID=bvteng worked for me, where bvteng was the service name.

Upvotes: 8

Akshay
Akshay

Reputation: 11

In my case, the problem was that the DSN and the ServiceName was configured as the same in the odbc.ini file.This should not be a problem, but only after changing the DSN name, I was able to connect to the database through isql.

Upvotes: 1

Annie C
Annie C

Reputation: 804

Check the tnsnames.ora file, in my case, took me days to find out there were either tab characters in the connection string or other invisible special characters that were causing it to fail.

Upvotes: 2

Dave Costa
Dave Costa

Reputation: 48111

Are you trying a local connection (e.g. "sqlplus u/p") or a network connection (e.g. "sqlplus u/[email protected]")? Are they both giving you the same error?

The TNSPING by definition is using a network connection. I see some references that indicate you can get the 12612 error when using a local connection. So that is a possible explanation why you are seeing the error from SQLPlus but not TNSPING. If so, try doing a network connection instead.

The local connection not working is probably due to ORACLE_SID not being set correctly as John suggested, but his syntax may not be the right method for whatever shell you are using. Make sure you are using the correct method for the given shell, such as "export ORACLE_SID=name" or "setenv ORACLE_SID name".

Upvotes: 3

MichaelN
MichaelN

Reputation: 1744

Dave Costa has presented you with 2 important question. Are you trying to connect via net8 or locally via extproc? Is the listener on the local machine(127.0.0.1 -- loop back device) setup for extproc connection?

To use the net8 or tcp connection protocol, you need to specify user/pw@tns_alias. To connect locally via extproc you should specify the oracle_sid parameter and then connect via name/pw.

I also notice the tnsalias has the .world domain appended to it, but the sqlnet.ora file does not contain a reference to NAMES.DEFAULT_DOMAIN as being "world".

Also what is the env parameter for TNS_ADMIN? Make sure your tools are looking at the correct tnsnames.ora file. Too many time people modify one tnsnames.ora and the programs/software is looking at another.

Upvotes: 2

Thilo
Thilo

Reputation: 262504

I have edited the tnsnames.ora file to change the host to 127.0.0.1 rather than an external url and am able to successfully tnsping my connection, but am not getting much further.

The last time that happened to me (tnsping works but sqlplus does not, same error message you got), the issue was that someone had copied the tnsnames.ora file from a Windows machine, and left the wrong line feed codes in.

If that is the case, you need to do some dos2unix.

These files are very sensitive to "correct" white-space and tabbing.

Someone should complain to Oracle about that.

Upvotes: 2

Related Questions