Sometimes I have been consulted about connectivity problems that occur without warning and are accompanied by the error message ORA-12514 TNS:listener does not currently know of service requested in connect descriptor.
There are those who indicate that strangely the communication was perfect shortly before and that they have already checked the configuration files such as listener.ora and tnsnames.ora and that everything looks fine but they still cannot establish new connections.
An additional detail is that all these cases have the common denominator that the port in use is not the usual 1521 but some other. Considering this pre-determined scenario, follow me to see how simple it can be to solve this problem.
Preliminary concepts
The background process PMON is in charge of registering dynamically with the listener the services available in a database, which is known as dynamic registration of services. However, it must be taken into account that by default Oracle databases register their services with the listener assuming that it is attending port 1521, if a port other than this one is used, additional measures are required to allow PMON to know where to register the database services, or it will not be able to do it and we will fall into the ORA-12514 error.
Solution
1. It is not unusual to configure a port other than 1521, there are several reasons for this but mainly it is done as an additional security measure, to prevent attacks that take advantage of the fact that the vast majority leave their listener configured with the default port 1521, hence changing it is highly recommended.
Let’s first look at a modified listener (listener.ora):
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = caliope)(PORT = 15402))
)
)SQL> alter system set local_listener="(address=(protocol=TCP)(host=caliope)(port=15402))";Or a second one, which uses a connection string that can be resolved by querying the tnsnames.ora file.
SQL> alter system set local_listener=listener_bd;LISTENER_BD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = caliope)(PORT = 15402))
)3. With this we only need to verify that the connectivity is indeed operational, for which we consider the following connection string (tnsnames.ora):
BD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = caliope)(PORT = 15402))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bd)
)
)Same as we tested with SQL*Plus:
[oracle@caliope ~]$ sqlplus system/oracle@bd
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Apr 1 15:19:17 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYSTEM@bd >Everything worked perfectly!
Conclusion
What did you think?, it’s not complicated is it, but you would be surprised how many do not follow these considerations and after creating additional listener, notice that new connections are interrupted after stopping the listener using port 1521 and wonder why, if those databases are supposed to use other ports on other listeners; well, now you know, so: let’s fix it!