How come sqlplus not connecting?

Question

My goal is to connect to an Oracle 9i instance from my OS X machine. I've followed the setup instructions here and got through them with no errors (eventually). However, I'm finding that sqlplus is unable to connect:

[ ethan@gir ~ ]$ sqlplus xxx/yyy@zzz

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 17 10:13:08 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Looooong wait...

ERROR:
ORA-12170: TNS:Connect timeout occurred

Enter user-name: xxx
Enter password: 
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

My tnsnames.ora file...

zzz =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = dbhost)
        (PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = zzz)
  )
)

Maybe there's an env variable that needs to be set?

---

UPDATE

Able to ping DB host machine no problem.

Tried...

sqlplus xxx/yyy@//dbhost/zzz

Got...

ERROR:
ORA-12170: TNS:Connect timeout occurred

Tried using SID instead of SERVICE_NAME in tnsnames.ora. Did not seem to change the result. Reverted back to SERVICE_NAME.

---

Last couple entries in sqlnet.log...

***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for MacOS X Server: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for MacOS X Server: Version 10.2.0.4.0 - Production
  Time: 17-APR-2009 10:33:06
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: Message 12535 not found; No message file for product=network, facility=TNS
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Message 505 not found; No message file for product=network, facility=TNS
    nt secondary err code: 60
    nt OS err code: 0


***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for MacOS X Server: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for MacOS X Server: Version 10.2.0.4.0 - Production
  Time: 17-APR-2009 11:24:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: Message 12535 not found; No message file for product=network, facility=TNS
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Message 505 not found; No message file for product=network, facility=TNS
    nt secondary err code: 60
    nt OS err code: 0

---

PARTIAL ANSWER

Thanks everyone for your answers. They were helpful. I found that there was a DNS issue. I was able to ping by hostname, so thought that should work fine. I also tried I.P. address. Turned out that I needed the internal "10.1.x.x" I.P. address for it to work on this OS X machine (but hostname is fine on Windows).

At this point, I can connect with...

sqlplus xxx/yyy@//INTERNAL_IP/zzz

However, with those values entered into tnsnames.ora, this still doesn't work...

sqlplus xxx/yyy@zzz

...

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

---

I searched for a sample tnsnames.ora file that was close to what I needed and copied the contents into my file. Changed the params and now everything works. Not sure why mine wasn't working.

Answer 1

Since you are using a 10g client, it's advisable to use Easy Connect syntax instead:

export TWO_TASK=//dbhost/zzz
sqlplus xxx/yyy

, or just this:

sqlplus 'xxx/yyy@//dnhost/zzz'

Also check your ORACLE_HOME points to the right folder: tnsnames.ora is searched for in $ORACLE_HOME/network/admin/tnsnames.ora

Answer 2

Your brackets seem correct.

Try using the SID:

The following is an example of a tnsnames.ora file:

IDENTIFIER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = userid.myhosteddb.net)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = odb))
  )

Read about SID here.

Answer 3

There is probably a sqlnet.log file being generated in your working directory. This may help you or if you post its contents it might give us more information.

In your example, you're trying two different things. On the command line you used "xxx/yyy@zzz". It looks like this is finding the "zzz" entry in tnsnames.ora successfully, but the timeout indicates that it is getting no response whatsoever from the server. Can you ping dbhost successfully?

On your second try you just entered "xxx" for the username; which makes sense if you are no accustomed to SQLPlus, but as you can see there is no point where it prompts you for the database name. So in this case it was trying to connect to "xxx/yyy" without a service name, leading to the second error. This just means you don't have a default service name set up. So this error comes from incomplete input. (You would enter "xxx@zzz" for the username to specify the service name at this prompt. You can actually enter the whole connect string "xxx/yyy@zzz" at the username prompt, if you don't mind the password being visible.)

Answer 4

Have you tried using telnet to get to the open port to make sure a firewall isn't blocking you? may be worth a try telnet port-num host

Answer 5

Did you set up your environment with the oraenv script?

Have you tried tnsping?

$ tnsping $ORACLE_SID

Perhaps it will help to compare the output on a machine that does connect to the output on a machine that fails to connect. At least that's what I do just before contacting our DBA.

You can get a bit more detail on what the error with the oerr command:

$ oerr ora 12170
12170, 00000, "TNS:Connect timeout occurred"
// *Cause:  The server shut down because connection establishment or
// communication with a client failed to complete within the allotted time
// interval. This may be a result of network or system delays; or this may
// indicate that a malicious client is trying to cause a Denial of Service
// attack on the server.
// *Action: If the error occurred because of a slow network or system,
// reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
// SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
// If a malicious client is suspected, use the address in sqlnet.log to
// identify the source and restrict access. Note that logged addresses may
// not be reliable as they can be forged (e.g. in TCP/IP).

Answer 6

You can use

sqlplus user/password@servicename_host

if you cannot connect you can use

sqlplus user/password@(DESCRIPTION=((ADDRESS=(PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = zzz)))

if you are using linux or unix OS you need to use quotes else the () are interpreted by the shell

e.g

sqlplus user/password@'(DESCRIPTION=((ADDRESS=(PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = zzz)))'