Whats wrong with these tns files ? ORA-12514

[roadwarriorDBA]

I cannot connect after upgrade to 10.2
TNS:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.codc182.rqinet.com)
)
)


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PLSExtProc)
)
)

Listener:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oradata/orabinaries/10.2.0/db)
(PROGRAM = extproc)
)
)

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl.codc182.rqinet.com
SQL>


It looks exactly like other boxes yet the listener says it does not handle 'orcl'


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)


[oracle@codc182 admin]$ lsnrctl service

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-NOV-2005 11:58:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=codc182.rqinet.com)(PORT=1521)))
Services Summary...
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


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 2 11:58:43 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

[dbasan]

I cannot connect after upgrade to 10.2
TNS:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.codc182.rqinet.com)
)
)

Is your SID orcl.codc182.rqinet.com?

Correct this to the exact way you have set in initfile (SERVICE_NAME = SID) and then connect.

Always use ip address in HOST to avoid confusion

[roadwarriorDBA]

The database says its

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl.codc182.rqinet.com
SQL>

I can sqlplus / as sysdba

I can also sqlplus user/password if I leave the @tnsname out it works fine.

I just wont get the service going and connect @orcl

[roadwarriorDBA]

TNSPing works fine. I dont know whats wrong with the listener.

tnsping orcl

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.codc182.rqinet.com)))
OK (0 msec)
[oracle@codc182 admin]$ sqlplus scott/tiger@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 2 13:17:40 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

[dbasan]

The name orcl.codc182.rqinet.com comes the way you have defined in the initfile.

When you are connecting locally you don't need the service name, you can connect trouble free.

When you do that from remote client/server you have troubles.

In your case the tnsping has identified the host and the the Listener was unable to connect to the database as it cannot recognize the SID thereafter.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Says to set the service_name right (to SID) in TNS file and connect.

[roadwarriorDBA]

The name orcl.codc182.rqinet.com comes the way you have defined in the initfile.

When you are connecting locally you don't need the service name, you can connect trouble free.

When you do that from remote client/server you have troubles.

In your case the tnsping has identified the host and the the Listener was unable to connect to the database as it cannot recognize the SID thereafter.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Says to set the service_name right (to SID) in TNS file and connect.

The database says thats the service name via show parameter service.

However I cannot connect locally if I reference the tnsnames entry.

[roadwarriorDBA]

There was a local listener defined in the pfile and it needed to be in the tnsnames. I dont really understand this local listener concept. I had added the local listener back in before but the tns alias is case sensitive until the database auto registers with the listener which takes a couple minutes.