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.