Can't connect thru SQLPLUS at command prompt (ORA-12514 TNS Listener Error)

when I say "client" I mean the machine you are running sqlplus on (which may also be an application server but it doesn't matter for the purposes of this problem)

when I say "server" I mean the machine the database is running on

Hello sdstuber, both the listener.ora and tnsnames.ora files are from the CDC1HCM03DB Oracle 10g Server as that is where we are running sqlplus from at the command prompt on the oracle "server" itself.
Ken

You should not need to change your hosts file. From your post above, I think the hosts file is fine. The listener does not appear to recognise the sid supplied.

Have you checked that the listener has something liek this instance registered? Do that with C:\lsnrctl services

The output should show hcm2prod in the list and show status as Ready. If not then let us know.

R.

here's the output from "lsnrctl services":
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\install>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-JAN-2009 10:58
:54

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CDC1HCM03DB)(PORT=1521))
)
Services Summary...
Service "HCM2PROD.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:447 refused:0 state:ready
         LOCAL SERVER
Service "HCM2PRODXDB.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: CDC1HCM03DB, pid: 2352>
         (ADDRESS=(PROTOCOL=tcp)(HOST=CDC1HCM03DB)(PORT=1040))
Service "HCM2PROD_XPT.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:447 refused:0 state:ready
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

C:\Documents and Settings\install>

Sounds like you need to modify your sqlnet.ora file in the location where the tnsnames.ora is located.

Please show the contents of the sqlnet.ora

R.

here is the sqlnet.ora file in the same directory as tnsnames.ora. I noticed there isn't a PID_AGENT file for this HCM2PROD instance. Could that be a problem?

# sqlnet.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

try adding this line to your sqlnet.ora

NAMES.DEFAULT_DOMAIN = world

the other change to make here is comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)

#SQLNET.AUTHENTICATION_SERVICES= (NTS)

You should have success now.
R.

the database has already registered itself dynamically.  
The lsnrctl output above shows registration is not the problem.

Plus, it's 10.2.0.4  the days of forcing static registration are long past.
It's simply not necessary anymore.

ok guys...I edited my sqlnet.ora file as recommended and still get the same Oracle error regarding the TNS as shown below. Any other thoughts or suggestions? (I've also pasted the newly edited sqlnet.ora file below for reference). Please respond ASAP as i have production people getting restless on me.

Thx!
Ken

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\install>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 30 12:33:36 2009

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

Enter user-name: system@hcm2prod
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SQLNET.ORA:
# sqlnet.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = world

instead of sqlplus....

try "tnsping hcm2prod"

regardless of whether it succeeds or not
it should tell you HOW it's trying to resolve the name

your listener configuration doesn't matter (yet) with that error as your client isn't getting far enough to begin talking to the listener

wait a sec...

that's a different error than you originally posted.
back the changes out one at a time until you get the original error.

From your listing dated
01.29.2009 at 07:00PM
it is apparantly that (in oposite to MR. Stuber statement !!!)
one of the services HCM2PROD is registered, but the other is not registered:
HCM2PROD
I suggest you immediatelly to do static registration as mentioned in
my posting 01.29.2009 at 09:49PM EET.

I will suggest the following:

Remove the NAMES.DEFAULT_DOMAIN = world entry and retry.

Restart the listener

Check lsnrctl services

If it shows Service "HCM2PROD.WORLD" has 1 instance(s), then try connecting as Enter user-name: system@hcm2prod.world

I hope this is clear

R.

Is hcm1prod instance up? If not, that will explain why it is not registered with the listener.

R.

how did I say anything opposite?

I didn't post the output of the listener services.  I simply went with what I saw.

Service "HCM2PROD.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:447 refused:0 state:ready
         LOCAL SERVER

what do you expect it to see?

and, if there is a problem with dynamic registration,
wouldn't it be better to resolve the problem than try to force a work around?
I know you've posted many times that you can't get dynamic registration to work
I have no idea why you have so many failures with it,  based on some of the other
problems you've posted perhaps your servers are very screwed up.

however, before we try to fix your systems (that would be a different question anyway)
lets work on this one.

"one of the services HCM2PROD is registered, but the other is not registered:
HCM2PROD"

what did you mean by that?  what's the difference between HCM2PROD and HCM2PROD?
I assume it's a typo, but what did you mean to type?

hey guys...i appreciate all your help. I neglected to say that "hcm1prod" is a disabled instance that is not running...just "hcm2prod" is running. I apologize for that. I did go ahead and register the instance in the listener.ora and i also did a tnsping hcm2prod. It points to my sqlnet.ora file. I'm pasting the output below with the different errors along with the code from all 3 configuration files (sqlnet.ora, tnsnames.ora, and listener.ora). Thanks guys, I appreciate everyone's input. Let me know your thoughts after seeing these three different errors below.

TNSPING:
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\install>tnsping hcm2prod

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 30-JAN-2
009 13:27:08

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name


HCM2PROD.WORLD:
C:\Documents and Settings\install>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 30 13:29:06 2009

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

Enter user-name: system@hcm2prod.world
Enter password:
ERROR:
ORA-12170: TNS:Connect timeout occurred


HCM2PROD:
C:\Documents and Settings\install>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 30 13:31:27 2009

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

Enter user-name: system@hcm2prod
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

LSNRCTL SERVICES:
C:\Documents and Settings\install>lsnrctl services
 
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 30-JAN-2009 13:35
:07
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CDC1HCM03DB)(PORT=1521))
)
Services Summary...
Service "HCM2PROD" has 1 instance(s).
  Instance "HCM2PROD", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
Service "HCM2PROD.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:16 refused:0 state:ready
         LOCAL SERVER
Service "HCM2PRODXDB.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: CDC1HCM03DB, pid: 2376>
         (ADDRESS=(PROTOCOL=tcp)(HOST=CDC1HCM03DB)(PORT=1040))
Service "HCM2PROD_XPT.WORLD" has 1 instance(s).
  Instance "hcm2prod", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:16 refused:0 state:ready
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
 
 
SQLNET.ORA:
# sqlnet.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
 
# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.
 
# SQLNET.AUTHENTICATION_SERVICES= (NTS)
 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
 
NAMES.DEFAULT_DOMAIN = world
 
 
TNSNAMES.ORA:
# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
 
HCM1PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDC1HCM03DB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HCM1PROD)
    )
  )
 
HCM2PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDC1HCM03DB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HCM2PROD)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
 
 
LISTENER.ORA:
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = HCM2PROD)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = CDC1HCM03DB)(PORT = 1521))
    )
  )
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:

Also make a comprehensive search of Firewalls.
Windows XP has a default one, in many cases shops
installs a second one (Zone Alarm, Trend Micro)
which is hidden and unknown but .. works and
prevents connections.

Check how many NIC cards has your server.
If they work correctly.
Are all network parameter set correctly.
Do your net recently experience changes in DNS matter,
default gateways and other network parameters?