Here, we have couple of puzzles, they are mainly:
1- Why the database services are dynamically registered with non-default listener while the
local_listener definition doesn’t exist in the tnsnames.ora file?
2- If you do some changes in the tnsnames.ora file related to the local_listener, are these
changes dynamically reflected to the database and listener? or do you have to reboot the
database?
If you have time, go through this post to get the answers. If you don’t have time and just need to quickly know the answers of the above puzzles, here they are:
1- This is a special case, when is the database is not synchronized with the local tnsnames.ora file.
2- Yes, we need to reboot the database to refresh the local_listener definition and register the database services.
By the end of this post, I’ll till you a valuable recommendation to avoid the above situations.
Let us start.
(All listeners on this post are not default [i.e. port is not 1521, listener name is not LISTENER])
Case #1: Oracle database (19c, NON-CDB on Windows)
For this database, I found that The LISTENER_ORCL alias is not defined in the tnsnames.ora, but the database services are successfully and dynamically registered with a listener called LSNR19C. Note that the database is not statically registered with this listener.
The question is, how the database is dynamically registered with the above non-default listener while the local_listener definition doesn’t exist in the tnsnames.ora file?
One thing that I noticed:
So, here it is very clear that the correct information of the local listener is captured by the database.
But how this information was captured while the local_listener parameter is not fully identified in the tnsnames.ora file?
I though this may happened while creating the database using the dbca, where it asked about the listener, and whether you want to create a new listener or register with existing one. So, I selected to register with the existing one which was the LSNR19C.
But my expectation is that this should populate both the local_listener (in tnsnames.ora) and the v$listener_network, not only one of them.
One other possibility is that the local_listener definition may be there in the tnsnames.ora, then deleted and the database was not rebooted since that time.
Case #2: Oracle database (12.2, CDB on Windows):
Good, every thing looks perfect. Let us do some changes:
1-I’ll remove(or comment) the local listener definition from the tnsnames.ora and see its impact:
Without restarting any component (Listener or database), there is no impact or change.
2- Stop the listener only ==> Still local_listener and v$listener_network have the same info
3- start the listener ==> After 60 seconds, the database will register its services with the listener.
4- Restart the database (after the above listener restart) ==> database didn’t register its services with the listener !
So, It looks like :
1- v$listener_network is synchronized with the tnsnames.ora at database reboot only.
2- tnsnames.ora changes are not dynamically synchronized with database in all cases.
3- This is the 12.2 behavior and lower, let us see if it is the same also in 19c?.
Case #3: Oracle database (19c, NON-CDB on Windows)
(Note that It is the same database in case #1)
1- I started by ensuring that the local_listener definition is not in the tnasnames.ora.
2- I started both the listener and the database.
==> as you can see the database services are not registered with the listener.
3- I’ll add the local_listener definition to the tnsnames.ora while both the database and listener are running ==> no impact on the database, still the v$listener_network is empty, database services are not registered with the listener
4- Let us restart the listener only ==> still no impact, database services are not registered with the listener, database didn’t feel the tnsnames.ora changes:
==> This proves that tnsnames.ora changes are not dynamically synchronized with the database.
5- Now let us restart the database also ==> database services are now fully registered with the listener.
It may be a good practice to avoid dependance on the tnsnames.ora file for dynamic registration, and define the local_listener directly on the database using a command like:
alter system set local_listener='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT = 1529)))’ scope=both;
Thanks
Ahmed
Ahmed AbdelFattah Blog ! 