27

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.

alex
  • 6,818
  • 9
  • 52
  • 103
  • 1
    Please provide content of your sqlnet.ora file and the relevant part of your tnsnames.ora. – Christian13467 Aug 06 '09 at 08:32
  • sqlnet.ora is NAMES.DIRECTORY_PATH= (TNSNAMES ) tnsnames.ora: PNEWS10S.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SID = pnews10s) ) ) –  Aug 06 '09 at 09:17

7 Answers7

29

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"

Rohit Verma
  • 457
  • 2
  • 13
John McG
  • 608
  • 4
  • 10
7

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

mahesh
  • 71
  • 1
  • 1
  • 1
    Manesh, this does not add anything to the highest score of this post. If you consider it helpful, please upvote it, and delete this answer. – Tomer Shetah Oct 08 '20 at 16:39
  • @TomerShetah yes, it adds value indeed. The highest valued answer didn't work for me but this one did it (export instead of set). – Paco Abato May 28 '21 at 08:56
  • 1
    As a new Oracle user, the distinction this answer makes between "database name" (top comment) and "service name" was valuable to me. – matmat Jul 14 '21 at 19:05
3

Are you trying a local connection (e.g. "sqlplus u/p") or a network connection (e.g. "sqlplus u/p@pnews10s.world")? 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".

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • 1
    I had a similar problem. The connection string MUST look like: sqlplus MyUsername/MyPassword@//MyHostname@PortNum/MyServiceName. Please see full post at http://stackoverflow.com/a/29423960/1822379 – Alan Thompson Apr 02 '15 at 23:10
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.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • excuse my ignorance - what do you mean by: left the wrong line feed codes in. –  Aug 06 '09 at 08:42
  • Text lines ends with line feed and return codes like \r \n. Different OS have different standards how this is done. So from time to time you could encounter a text file that has different (wrong) way of doing this. E.g. if you download linux text file to you windows machine. – pero Aug 06 '09 at 08:46
  • I've only edited the file using vi in linux, so would presume the file text line ends are intact –  Aug 06 '09 at 09:15
2

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.

MichaelN
  • 1,734
  • 11
  • 6
1

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.

Annie C
  • 764
  • 2
  • 12
  • 31
1

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.

Akshay
  • 11
  • 1