Oracle Listener issue

Last month one of my colleagues asked me help him troubleshooting a weird issue for oracle listener whatever on data guard with primary and physical standby at that moment he was deploying oracle data guard. As known as the oracle listener configuration has commonly included three number of parameters, host info (hosname or ip address), port number and service name (offered by static listener).

I checked the oracle listener configuration file on his environment and discovered that listener name is DBNAME (not default LISTENER) but he used the default LISTENER to check the status of listener such as “lsnrctl status listener”. By the way the host and port number are no problem.

Next he typed “lsnrctl status DBNAME” but oracle never shown the service name for his DBNAME. Then I went to check the parameter db_unique_name is NULL on that session window but which has been set to DBNAME in the spfile hence I suggest that he should go to reboot oracle instance firstly in order to make db_unique_name take effect meanwhile he should also check the parameter service_names if it’s DBNAME or not.

Followd my advice he reboot oracle instance and checked service_name that is right. Nevertheless the listener has still existed the problem because oracle always reported no listener when using a user to connect to database by tnsname. Suddenly I found out the parameter local_listener has been set a strange value “DBNAME”, generally speaking, if the listener port number is not 1521 (oracle default) we should add the entire listener entry to here otherwise it should be NULL thus he change it to be NULL. Afterwards using the previous method to test oracle still reported NO LISTENER.

I have to continue to troubleshoot and then ask him about oracle architecture he told me the ip address is a float ip (oracle primary database has been set HA via HA software located in the server, in other words, another server has played the role as secondary and its instance has been shutdown if the first server’s instance is opened), which makes me realized that we need to set the parameter remote_listener like this “dbnamevip:1521” so that the HA float ip has been used by listener. After adjusting it and reload listener now his oracle listener is really taking effect of course using TNSNAME to connect to oracle database is ok. Next he adjusted the same listener parameter configuration on the data guard physical standby server.

The parameter remote_listener is very important for oracle rac and oracle HA, happy to learn this little knowledge.

Hope to help you as well …