-
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:
-
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
Last edited by dbasan; 11-02-2005 at 03:18 PM.
"What is past is PROLOGUE"
-
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
-
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
-
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.
Last edited by dbasan; 11-02-2005 at 04:26 PM.
"What is past is PROLOGUE"
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|