Oracle TNS: net service name is incorrecly specified

Question

Help! I'm a newbie to Oracle who's trying to access a 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 am able to successfully tnsping my connection, but am not getting much further.

Any advice or help would be greatly appreciated.

Answer 1

Try setting the Oracle SID

set ORACLE_SID=database name

Answer 2

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.

Answer 3

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").

Answer 4

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.