Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders. Many times the source of confusion lies in the similarity of this error number to
ORA-12154
.
This aim of this document is to show a method for diagnosis and resolution to a scenario in which an
ORA-12514
is raised during the connection to a database.
Problem
On an attempt a connection to the database through SQL*Plus the following error is raised.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| [oracle@ora2 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 22 10:36:38 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect ejenkinson@testdb Enter password: ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor SQL> |
Above the error
ORA-12514
was received when attempting to connect to the database testdb
. Before speculating on what could be the cause of the error, you should first look up the error using either oerr
or Google.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| [oracle@ora2 ~]$ oerr ora 12514 12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor" // *Cause: The listener received a request to establish a connection to a // database or other service. The connect descriptor received by the listener // specified a service name for a service (usually a database service) // that either has not yet dynamically registered with the listener or has // not been statically configured for the listener. This may be a temporary // condition such as after the listener has started, but before the database // instance has registered with the listener. // *Action: // - Wait a moment and try to connect a second time. // - Check which services are currently known by the listener by executing: // lsnrctl services <listener name> // - Check that the SERVICE_NAME parameter in the connect descriptor of the // net service name used specifies a service known by the listener. // - If an easy connect naming connect identifier was used, check that // the service name specified is a service known by the listener. // - Check for an event in the listener.log file. [oracle@ora2 ~]$ |
Diagnosis
According to the description above the listener does not have a databases named
testdb
registered. Unlike the error ORA-12154
the connection attempt did make it to the listener. A quick check of the services the listener is listening for can be found using lsnrctl services
.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| [oracle@ora2 ~]$ lsnrctl services LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-JUL-2010 10:52:17 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) Services Summary... Service "testdb" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "testdbXDB" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: ora2.localdomain, pid: 25010> (ADDRESS=(PROTOCOL=tcp)(HOST=ora2.localdomain)(PORT=42911)) The command completed successfully [oracle@ora2 ~]$ |
The output shows that the database
testdb
is registered with the listener. The next step is verify that the SERVICE_NAME
parameter in the connect descriptor in TNSNAMES
is correct.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| [oracle@ora2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1 /network/admin/tnsnames.ora # Generated by Oracle configuration tools. TESTDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora2.localdomain)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = estdb) ) ) [oracle@ora2 ~]$ |
Above it seen that the
SERVICE_NAME
has a typo.
No comments:
Post a Comment