All Things Oracle

Dynamic service registration and ora-12514

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))
    )
  )
2. This must be complemented with the configuration of the local_listener parameter, for which we have two possibilities, a first one in which we place the data explicitly:
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;
The listener_bd alias must exist locally, as shown below (tnsnames.ora):
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!

Did you find this article interesting, did you have any doubts, do you want to suggest a topic to cover, leave me your comments or contact me me right now!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Link to article published in Toad World, about how to overcome the lack of archivelogs to synchronize a standby database in Oracle.
The enq: CF - contention event could be resolved with controlfile maintenance if the controlfile contains a large number of archived log records.
If the Oracle optimizer chooses inefficient execution plans on queries that use MAX or MIN, it is possible that this is bug 5611962.

Need Help?

Fill in these details and I will be in touch as soon as possible.