Whats wrong with these tns files ? ORA-12514

Printable View

11-02-2005, 02:59 PM
roadwarriorDBA

Whats wrong with these tns files ? ORA-12514

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:
11-02-2005, 03:14 PM
dbasan

Quote:

Originally Posted by 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)
)
)

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
11-02-2005, 04:12 PM
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
11-02-2005, 04:18 PM
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
11-02-2005, 04:20 PM
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.
11-02-2005, 04:35 PM
roadwarriorDBA

Quote:

Originally Posted by 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.

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

However I cannot connect locally if I reference the tnsnames entry.
11-04-2005, 12:26 AM
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.