In previous posts I have explored various reasons why a client process might not be able to connect to an Oracle database. In “Help! I can’t connect to my database” I presented an overview of the process used by sqlnet to convert a connection request to a network connection descriptor and deliver that request to a listener capable of servicing the request. I followed that with two posts going into more depth on the types of problems that can prevent the request from reaching the host server. Now I would like to look at the next possible problem in the sequence, in particular “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.
For this demonstration I will generate the error then go through the standard analysis and solution. After that, I will explore some of the interesting factors that flow from this.
The Error
The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.
C:\>sqlplus scott/tiger@vlnxora1 SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 18:47:26 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name: C:\>
Fig. 1
Of course the very first thing anyone should do when confronted with an Oracle error is to check the official description. There are many sources on the web, but I like to start with ‘oerr’ utility on the server ..
[oracle@vmlnx01 admin]$ 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 // - 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.
Fig. 2
The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. So how do we know exactly what service was “requested in connect descriptor”?
First, do a sanity check by looking at the tnsnames.ora file.
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
vlnxora1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fubar)
)
)
C:\>
Fig. 3
In Fig. 3, line 8 we see that we should be requesting SERVICE_NAME = fubar. In our discussion of ORA-12154 I described how we might not be using the tnsnames.ora we thought we were. At this point we know what SERVICE_NAME we should be using. We can use tnsping to confirm this.
C:\>tnsping vlnxora1 TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-MAR-2 011 19:02:39 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fubar))) OK (0 msec) C:\>
Fig. 4
Fig. 4, line 13 shows that we are requesting a connection to service ‘fubar’. Don’t be fooled by the good return code. As we saw in “tnsping – what it is, what it isn’t“, tnsping only goes as far as confirming there is a listener at the specified ip address and port. It says nothing about any services the listener knows about. The presence of SERVCICE_NAME in the feedback is simply the result of showing the entire connect descriptor.
Now that we know what service name was actually requested, we need to check what the listener knows about. Examining the listener configuration file, listener.ora, could give some clues but it is not the whole story. In fact, the listener can be started without any listener.ora file at all. The only sure way to tell what the listener knows about is to ask it directly, with the lsnrctl command:
[oracle@vmlnx01 ~]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-MAR-2011 19:11:49 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 15-MAR-2011 18:45:24 Uptime 0 days 0 hr. 26 min. 25 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Services Summary... Service "vlnxora1" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@vmlnx01 ~]$
Fig. 5
This listener knows about three service names (vlnxora1, vlnxora1XDB, and vlnxora1_XPT), all associated with the instance ‘vlnxora1’. (The XDB and XTP services are for special use – we can ignore them for general connection problems.) The vast majority of the time, that’s all the debugging we need to do. We know that we should be requesting a connection to service ‘vlnxora1’ instead of ‘fubar’. Since that request comes from the client, we have to fix tnsnames.ora
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
vlnxora1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
C:\>sqlplus scott/tiger@vlnxora1
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 19:14:41 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Fig. 6
We see in Fig. 6, line 12 that we have made the necessary correction in tnsnames.ora. The subsequent connection request is successful (fig. 6, line 18), thus validating our analysis and corrective action.
Registering the service with the listener
So how did the listener come to know about service “vlnxora1” in the first place? There are two methods by which a service is registered with the listener – “static” and “dynamic”. We’ll discuss each in turn.
Static registration
Static registration is accomplished by configuring the SID_LIST section of the listener.ora file.
[oracle@vmlnx01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /ora00/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME=myfubardb)
(ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
(SID_NAME = fubar)
)
(SID_DESC =
(GLOBAL_DBNAME=vlnxora1)
(ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
(SID_NAME = vlnxora1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01.vmdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@vmlnx01 admin]$
Fig. 7
In Fig. 7 we see three SIDs listed: PLSExtProc, fubar, and vlnxora1 (lines 7,14, and 19). Checking the status of the listener, we get
[oracle@vmlnx01 admin]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:27:25 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlnx01.vmdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 18:25:56 Uptime 0 days 0 hr. 1 min. 28 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora00/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "myfubardb" has 1 instance(s). Instance "fubar", status UNKNOWN, has 1 handler(s) for this service... Service "vlnxora1" has 2 instance(s). Instance "vlnxora1", status UNKNOWN, has 1 handler(s) for this service... Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@vmlnx01 admin]$
Fig. 8
Notice the entry for service “myfubardb” (fig. 8, line 25) maps back to “(GLOBAL_DBNAME=myfubardb)” (fig 7, line 12) and is related to instance “fubar”, which maps to fig 7, line 14). Further, notice that its status is UNKNOWN. This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora. It is unknown because the listener doesn’t make a check to see if there is an instance named “fubar” broadcasting a service name of “myfubardb”. The listener is just saying “if you ask for a connection to “myfubardb”, I’ll see what I can do to service it.” In fact, I have no database named “fubar” or “myfubardb”.
Notice also that service “vlnxora1” has two instances, one unknown, and one READY. Like myfubardb, the UNKNOWN vlnxora1 comes from listener.ora (fig. 7, line 19); the READY instance comes from the database having registered with the listener (dynamic registration).
Again, for our current discussion, we can ignore the services vlnxora1XDB and vlnxora1_XTP. These have special internal uses for Oracle.
For the remainder of the discussion, I am going to completely remove listener.ora, then restart the listener so that it has no static registrations and is running with all default values:
[oracle@vmlnx01 admin]$ rm listener.ora [oracle@vmlnx01 admin]$ ls -l listener.ora ls: listener.ora: No such file or directory [oracle@vmlnx01 admin]$ lsnrctl stop LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:43 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully [oracle@vmlnx01 admin]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:49 Copyright (c) 1991, 2007, Oracle. All rights reserved. Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.4.0 - Production Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 18:44:49 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) The listener supports no services The command completed successfully [oracle@vmlnx01 admin]$
Fig. 9
With no static registration the listener will start with all default values and support no services until a pmon process registers itself.
Dynamic registration
Dynamic registration is accomplished when the pmon process of the database instance contacts the listener and requests registration. This occurs at instance startup, and every few minutes during the life of the instance.
There are three initialization parms that affect what service name(s) pmon will register with the listener:
You should look up each one in the Reference Manual and read the descriptions. (Click on the links) Notice particularly in the description of SERVICE_NAMES the following:
If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.
There is another interaction that is not spelled out in the Reference Manual, but mentioned in the Net Services Administrator’s Guide:
The service name defaults to the global database name, a name comprising the database name (DB_NAME parameter) and domain name (DB_DOMAIN parameter)
Since neither DB_DOMAIN nor SERVICE_NAMES are required parameters, let’s start with an instance with neither of those set, then start observing how service names get constructed with various settings. For each iteration I will do the following:
1) alter an initialization parm
2) bounce the database (some of the parms require it. To keep things clean and consistent, I’ll do it for all of them)
3) restart the listener, to flush the old registrations
4) force a new registration
5) show the listener status, with the results of the new registration
6) show the values of all three parms, for comparison
[oracle@vmlnx01 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 16 19:17:57 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system set service_names='' scope=spfile; System altered. SQL> alter system set db_domain='' scope=spfile; System altered. SQL> @doit SQL> startup force ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1267068 bytes Variable Size 138414724 bytes Database Buffers 142606336 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SQL> !lsnrctl stop LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:18:52 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully SQL> !lsnrctl start LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:19:01 Copyright (c) 1991, 2007, Oracle. All rights reserved. Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.4.0 - Production Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 19:19:01 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) The listener supports no services The command completed successfully SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:19:01 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 19:19:01 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Services Summary... Service "vlnxora1" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string vlnxora1 SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string SQL>
Fig. 10
In Fig. 10, at lines 91,93 and 95, we see three service names. All three are associated with the instance “vlnxora1” and derived their name from the initialization parameter “db_name”.
Next, we add a simple service name. While normal practice might be to make it the same as the db_name, I’ll make it different so that we can trace it to the end result. Remember, it is quite acceptable to have multiple service_names, which we will get to in a moment.
SQL> alter system set service_names='edstevens' scope=spfile; System altered. SQL> @doit ---- snip repetitive commands and output ---- SQL> !lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:22:06 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 19:22:06 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Services Summary... Service "edstevens" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string vlnxora1 SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string edstevens SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string SQL>
Fig. 11
Now we see that all of the service names derived from db_name are still in place, but we have also added one derived from service_names (Fig. 11, line 27)
Next we set db_domain
SQL> alter system set db_domain='acme.com' scope=spfile; System altered. SQL> @doit ---- snip repetitive commands and output ---- SQL> !lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:24:01 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 19:24:01 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Services Summary... Service "edstevens.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string vlnxora1 SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string edstevens SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string acme.com SQL>
Fig. 12
Notice that all service names – those derived from db_name as well as the one derived from service_names – have the value of db_domain appended to them.
Next we add a second service name, this one qualified with a second domain name. Not something you’d normally do, but useful for demonstrating the interaction of the parameters
SQL> alter system set service_names='edstevens,wiley.coyote.com' scope=spfile; System altered. SQL> @doit ---- snip repetitive commands and output ---- SQL> !lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:27:07 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 16-MAR-2011 19:27:07 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521))) Services Summary... Service "edstevens.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1XDB.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "vlnxora1_XPT.acme.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... Service "wiley.coyote.com" has 1 instance(s). Instance "vlnxora1", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string vlnxora1 SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string edstevens,wiley.coyote.com SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string acme.com SQL>
Fig. 13
As before, all of the unqualified service names derived from db_name and service_names have been qualified with the value of db_domain. But notice that we have a new service name (Fig. 13, line 35) from the second value supplied from service_names. Since this was fully qualified in the service_names initialization parm, the value of db_domain was not applied.
Conclusion
We have explored the relationship between the connect descriptor issued by the client and the services supported by the listener, as well as the factors that control what services the listener supports. In the concluding post in this series, I will discuss how the database locates the listener in order to register its services – the LOCAL_LISTENER initialization parameter.
Film at eleven …
[...] [...]
Pingback by ORA-12154: TNS:could not resolve the connect identifier specified - dBforums — May 23, 2012 @ 7:49 am |
Thanks for the clear explanation. The testing/examples were great.
Comment by Joe — June 10, 2012 @ 4:37 pm |
Ed,
Thanks for the clear explanation on how to statically register an instance. I wish I had found that earlier. But, in any regard, I think I followed what you are saying, and I still can’t get my instance to show up in the lsnrctl status. Can you think of anything that I can do to troubleshoot? I posted this question on OTN, and it had my listener.ora.
https://forums.oracle.com/forums/thread.jspa?messageID=10397221
I think I need this to do a rman duplication. I want to clone another database to this one, so I need a “remote” connection to the not-yet-created database.
Any suggestions you can think of to try would be very helpful.
Thanks,
Stephen
Comment by Stephen aka DigitalEagle — June 14, 2012 @ 3:25 pm |
Stephen,
Check my reply on OTN. You mis-spelled the header for the SID_LIST section of your listener.ora file. You have “SID_LIST_LISTNER =”, should be “SID_LIST_LISTENER”. Missing the first “E” in LISTENER.
Comment by Ed Stevens — June 14, 2012 @ 6:56 pm |
SQL> @doit
SP2-0310: unable to open file “doit.sql”
Comment by ejvyas — August 21, 2012 @ 10:46 am |
Is there a question here? Preferably one that relates to ora-12514, which is the subject of this line of discussion.
Comment by Ed Stevens — August 22, 2012 @ 5:31 pm |
What is @doit? Do you mean listener reload?
Comment by ejvyas — August 22, 2012 @ 5:33 pm
In sqlplus, the ‘@’ means “read this file (default extension of .sql) and execute the statements contained in it.
So “@doit” is telling sqlplus to execute the file ‘doit.sql’. What was actually in ‘doit.sql’ is clearly shown in the lines that follow it in the examples.
Comment by Ed Stevens — August 22, 2012 @ 6:04 pm
[...] http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ [...]
Pingback by ORA-12514: TNS:listener does not currently know of service « dbtechtalk — October 16, 2012 @ 6:43 pm |
Thanks for the detailed guide. It was very helpful.
Comment by Nimesh — November 10, 2012 @ 12:56 am |
Hi,
Thanks for great explanation.
Is it possible to provide doit.sql file?
Thanks,
Adam
Comment by Adam — November 15, 2012 @ 11:53 am |
Adam – “doit.sql” is just the name I use for all my one-off, throw-away, demo scripts. In this case, it’s all right there in the post. The first line of the script is “set echo on”, so the file is nothing more than the commands you see after “@doit”.
Comment by Ed Stevens — November 15, 2012 @ 8:25 pm |
Reblogged this on Bass.||.Ball.||.Code [::-1] and commented:
Best help I’ve found so far in setting up Oracle 11g.. Plus of course my lucky shorts o_O?! Oracle need to change this.. Talk about baptism by fire!
Comment by samuelolungati — November 27, 2012 @ 7:53 am |
Good explanation. Great job …
Comment by Ramesh — December 1, 2012 @ 2:42 pm |
Thanks Ed,
this was very clear and very helpful (I fixed the issue, but need to spend more time reading your blogs related to Oracle)
thanks,
Dave
Comment by Dave — January 10, 2013 @ 11:32 am |
excellent and clear explanation of connection issues … much clearer and easier to follow than the oracle docs ;+), thanks
Comment by norm — January 15, 2013 @ 10:33 am |
Thanks a lot for the clear explanation with very good and easier follow example.
Thanks,
Comment by David — February 5, 2013 @ 4:21 pm |
Excellent Documentation to resolve the listener issues.
–Arock
Comment by Arockiaraj Manuvel — April 30, 2013 @ 5:21 pm |
Fantastic article. Thanks!
Comment by bah — May 13, 2013 @ 12:04 pm |