my recent reads..

Atomic Accidents: A History of Nuclear Meltdowns and Disasters; From the Ozark Mountains to Fukushima
Power Sources and Supplies: World Class Designs
Red Storm Rising
Locked On
Analog Circuits Cookbook
The Teeth Of The Tiger
Sharpe's Gold
Without Remorse
Practical Oscillator Handbook
Red Rabbit

Sunday, June 24, 2007

Check LOCAL_LISTENER if you run RAC!

Had a case recently with a 10gR2 RAC install. Everything seemed to have been setup to spec, but we were seeing clients occasionally getting ORA-12545 errors and failing to connect, and things getting even worse during failover testing.

After investigating and solving this, it was painfuly obvious how easy the configuration issue can sneak into a RAC install, which prompts me to blog about it now. Bottom line: if you are installing RAC or are responsible for managing a RAC database, I strongly suggest you swivel over right now and 'show parameter LISTENER'!

So back to our case.... looking at a sqlnet client trace, it was apparent that the client was being redirected to the server hostname, not one of the RAC virtual addresses. Two problems:
  1. the client couldn't resolve the server hostname since it wasn't in DNS or the client hosts file, and
  2. the client shouldn't be connecting to the server hostname anyway!

The client tnsnames.ora and the servers' tnsnames.ora and listener.ora files had all been checked and were setup with only references the the RAC virtual addresses, so where was the reference to the 'physical' hostname coming from? Well the answer is the LOCAL_LISTENER server parameter.

You would think however that if your client connection descriptor (taken from tnsnames.ora for example) only referenced virtual addresses, you would be safe, right? Not the case, and having a solid understanding of how the listener works is critical to knowing why.

The problem stems from the way that in 10g instances automatically register with the listener, and it is very easy to fall into this trap if you haven't paid very close attention to section "9 Understanding the Oracle Real Application Clusters Installed Configuration" in the platform-specific cluster installation guides.

If you have a DEDICATED server config, then the LOCAL_LISTENER parameter is used for the instance registration with the listener. If you are using a default listener on port of 1521, then DBCA will not automatically set the LOCAL_LISTENER. Section "9.8 Configuring the Listener File (listener.ora)" describes how to manually set a correct LOCAL_LISTENER value, but if you haven't done that, it will default to a connection string that refers to the physical host address (not virtual address).

But you might think "connecting to the physcial host address can't be too bad, can it?". Well yes, there are two problems you can see:
  1. Clients may not be able to resolve the host address if you don't have that in DNS, and more importantly
  2. In a failover situation, clients will not follow the virtual IP.

Even then, you might think this would be a very rare problem, because client's tnsnames or other naming is always telling them to connect to the virtual address anyway.

Again, not so. It can be very common for the client to get a connection to the physical host address even if the tnsnames tells them to connect to the virtual address, because of RAC workload management and listener redirects.

Lets take an example of a RAC service called SVC with two instances SVC1 and SVC2 running on host1 and host2 (with virtual addresses host1_vip and host2_vip). The client tnsnames would look something like this:
SVC = 
(ADDRESS = (PROTOCOL = TCP)(HOST = host1_vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2_vip)(PORT = 1521))

The diagram shows how a connection is handled through the listener in the case where the LOCAL_LISTENER is not set correctly. The flows go like this:
  1. The client selects a virtual host from the address list and attempts a connection, in this case to the listener on host1_vip.
  2. The listener selects a preferred instance to handle the session for the service SVC. If the local SVC1 instance is down, or if it thinks instance SVC2 is better able to service the request it sends a listener redirect to the client. This redirect will be to the physical hostname (host2)
  3. If the client is not able to resolve "host2", you will see an ORA-12545 at this point. If it can resolve the address, then the client establishes a connection to the listener on the host2 address. If SVC2 is running, you should now have a good connection to the database. However, consider now what happens if host2 fails. CRS will ensure that the host2_vip will shift over to host1, but the client is connected to host2 address and you will get into a TCP timout situation. Maybe your client will eventually detect the dead connection and attempt to reconnect (using VIPs), but at best the user application will have stuttered for a significant period of time (depending on your tcp and sqlnet settings).

Now consider what should happen, with a correctly configured LOCAL_LISTENER.
  1. The client selects a virtual host from the address list and attempts a connection, in this case to the listener on host1_vip.
  2. The listener selects a preferred instance to handle the session for the service SVC. If the local SVC1 instance is down, or if it thinks instance SVC2 is better able to service the request it sends a listener redirect to the client. This redirect will be to the virtual hostname (host2_vip)
  3. We get a good connection established to SCV2 on host2_vip. Again, consider now what happens if host2 fails.
  4. CRS will ensure that the host2_vip will shift over to host1, and the client connection follows this VIP. There will be a slight interruption to communication (depending on how quickly the VIP take-over occurs, and also other factors such if you are connecting through a NAT router, the NATting tables will need to update). Depending on how you have configured FAILOVER_MODE (session, select), you should find that very soon your database connection is alive and you can continue working.

Fortuntely there is an easy fix: update the LOCAL_LISTENER parameter to reference the virtual address.

Oracle have a couple of notes on the issue (342419.1, 333159.1) and how to setup LOCAL_LISTENER. Note however, a the time of writing this (and I'm trying to get it fixed), note 342419.1 does not exactly describe the fix correctly.

It mentions to set the LOCAL_LISTENER using the command like this (where server tnsnames.ora has an instance-connect string using VIP address called 'LISTENER_LXDB0036' ):
Alter system set LOCAL_LISTENER= 'LISTENER_LXDB0036' scope=both;

However, this sets a server parameter that would be picked up on all instances. So there are actually two choices:
  1. change the tnsnames.ora file on each server to have a different, instance-specific definition of 'LISTENER_LXDB0036' , or
  2. I think the preferred way: set a SID-specific parameter by adding the SID parameter to the alter system command (assuming the instance name is SVC1 in this case):
    Alter system set LOCAL_LISTENER= 'LISTENER_LXDB0036' scope=both SID='SVC1';

Note that the listener registration is is only a problem if the database was created with a lister on default port. Haven't seen it myself, but apparently if you use DBCA to create a database with a non-default listener, then the LOCAL_LISTENER entry is explicitly set with correct reference to VIP.

So the message for today. If you are installing RAC or are responsible for managing a RAC database, I strongly suggest you swivel over right now and 'show parameter LISTENER'. Make sure you don't have an HA problem lurking in your system just waiting to bite you and the very worst moment!

Postscript: I submitted a request for change, and note 342419.1 has now been updated to reflect a more correct solution as of 25-Jun-2007.

Postscript 2: This issue is apparently addressed in 11g. Thanks for the report on this karlarao.
Postscript 2b: As Mark Twain would have said, "The reports of a solution are greatly exaggerated" - apparently 11gR2 still does not set a FQDN by default. Time to crack out a test...


Anonymous said...

This isn't a general solution, though.

If the listener works as you describe, how would RAC handle the following situation:

1. Server has physical IP of <IP1>.
2. Client A has virtual IP for the server as <IP2>.
3. Client B has virtual IP for the server as <IP3>.

Now what? Can't set LOCAL_LISTENER to <IP2>, because then client B can't do the failover properly, since it needs <IP3>.

Unknown said...

Not quite, Mr Ed. LOCAL_LISTENER is just affecting the instance registration with the listener. The fact that the cluster will always listening on IP2 and IP3 is forced in the listener.ora configuration. So client B won't have a problem ... unless - as I was trying to describe in the post - client B gets a listener redirect and LOCAL_LISTENER has been incorrectly set

Anonymous said...

I guess I don't understand.

In my example, <IP2> and <IP3> refer to the same RAC node (and both clients have other nodes in their failover lists, to refer to the other RAC nodes).

Let's say LOCAL_LISTENER = <IP2>. Then client B gets redirected to <IP2>, but <IP2> isn't accessible to client B (since it's only valid for the subnet that client A is on).

Do you see what I mean, where different subnets have different virtual IPs for the same exact box? For example, when they map the box outside of their subnet to an address in their subnet, and client A and client B are on different subnets.

Unknown said...

oh, I see. Like you have clients on different vlans acessing the sames db server?

I haven't tried that case, but I suspect that automatic registration would create a potential issue with listener redirects to inappropriate addresses. Probably requires static configuration for the listener(s) to make sure its all OK.

Anonymous said...

An additional note: be sure to fully qualify your DNS entries for the server-vip addresses in the host section of the local_listener connect strings.

If you only have the HOST section set to (HOST=server-vip), not fully qualified, then clients connecting to the database that are load balanced from one instance to another, may fail to connect if the client does not have a default domain of

When a client connects to instance 1 and instance 1 load balances the client, moving them to instance 2, the client is given the local_listener connect string for instance 2. If it doesn't include the fully qualified domain, the client will only know to go to server-vip and fail to connect, not knowing to go to instead.

Thanks for your article Paul. It was very helpful!


Unknown said...

Thanks SC, good point. Just goes to show how attention to every detail is so important to ensure you have an optimally performing cluster.

Average Joe said...

I am having an issue with Oracle10g
on a RHEL 5.0 cluster. The setup is active/passive and no RAC is involved, but we are using the Linux cluster manager.

My issue is with the virtual IP and the listener. It seems that if I have the actual host name specified in the listener.ora file and the virtual ip specified in the tnsnames.ora file, all is well. The database service gets registered just fine and remote connections work.

If I change both the listener.ora and tnsnames.ora files to point to the virtual IP address, the only service that gets registered is the Plsextproc. My database never gets registered and I get ORA-12514.

When I change back to the 1st case where the host name and not virtual IP is specified in the listener.ora once again, after a recycle of the listener, all is ok again.

Any ideas?

Unknown said...

Hi Rockie. Think I follow you. To get the VIP to work, suggest you look again at changing the LOCAL_LISTENER parameter rather than listener.ora.

Also check out this post, it may help: Are Virtual IPs required for Data Guard?

Unknown said...

Explanation about LOCAL_LISTENER is really awesome. I have doubt in this point.
I am not sure byt I am guessing failover can be easily made out through REMOTE_LISTENER parameter. Because if we had set parameter REMOTE_LISTENER in tnsnames.ora, issue will not arise. Though if LOCAL_LISTENER is not set properly, it tries to look for REMOTE_LISTENER where we would have given vips of both nodes, so it automatically fail over to concern node. However there should be a strong reason for Oracle to come up with both REMOTE and LOCAL listener. If any body knows please clarify me.

Best Regards,

Unknown said...

Hi Niranjan,

My understanding is that REMOTE_LISTENER is primarily for server-side load balancing and failover. If used, it should specify the addresses of all the listeners in the cluster so the servers can negotiate workload.

But it is really a server-side concern.

The LOCAL_LISTENER issue I described can still affect the client, and is quite independent of REMOTE_LISTENER behaviour.

Paradoxically;-) .. it is a matter of point of view: LOCAL and REMOTE are named from the server's perspective!

Anonymous said...

Hi Paul,

Just an update.

This is already fixed in 11g. I've recently installed 2node 11g RAC on Windows Server 2k3 64bit, then when I checked the tnsnames.ora not only the entry for remote listener is created but also for the local listener and they are all using the VIPs.

"lsnrctl services" confirmed that they are using the VIPs.

"show parameter remote"
"show parameter listener"
confirmed that they are now properly populated.

- Karl Arao

Unknown said...

Thanks for the update Karl, that's great.

Anonymous said...

Thanks too for sharing this info.. good stuff.. :)

- Karl Arao

Unknown said...

Excellent description of the problem and resolved a 2 day issue we were having with the listener re-direct to another RAC node, keep on blogging!

Unknown said...

Great to hear, thanks Paul.

(that's another Paul folks, not me going split-personality on you;-)

Anonymous said...

Just FYI, Oracle 11gR2 *still* has this set incorrectly.

The LOCAL_LISTENER parameter for each node will have the short hostname instead of the fully qualified domain name (FQDN). You will still need to go to each node and set the LOCAL_LISTENER parameter otherwise on remote systems you will continue to get ORA-12545 errors.

Unknown said...

@anonymous Thanks for the update. That's disappointing news about 11gR2. Time to do some more testing...

Anonymous said...

Cool story as for me. It would be great to read more concerning that topic. The only thing it would also be great to see here is a few pictures of any gizmos.
Kate Trider
Cell phone blocker