RAC database with multiple listeners

From: Tyfanie Wineriter <tyfaniew_at_uoregon.edu>
Date: Thu, 21 Jan 2010 09:32:40 -0800
Message-ID: <012701ca9abf$bb6c52f0$3244f8d0$_at_edu>

Good Morning, Oracle-L!  

Quick background: I am running a 10gR2 RAC with 4 nodes and 10 databases in the cluster on Linux.  

Original Problem: Currently, we have users that connect to a database via the database name & VIP. RAC does what it should do & redirects the users to available instances, and this has been fine while we set up RAC & got everything working. Now we would like to start moving users around based on the type of user they are, IE utilize services for the fabulous load-balancing & maneuverability. As is usually the case, we have given access and now need to force them over to using ONLY the new service, and Disallow use of connecting directly to the database.  

First possible solution: We have done some research & thought the best way to do this was to create new listeners in a completely different port range, send out a new TNSNames to everyone, which includes the new service utilizing the new Listener port, and then block off access to the old listener port at the firewall. We do have direct Application access that we would like to continue using the original port.

Example: I have database DB on port 1521, and everyone connected to it on 1521. I now created service DB_SQL and along with it a new listener on port 1591. I want everyone to connect to DB_PUB on port 1591, and block 1521 at the firewall. Inside the firewall, applications would continue using the 1521 port.  

Where I need your help: When trying to do this, I am finding that regardless of which listener the user connects to, it is doing some sort of redirection to either listener, and then half the time the user is unable to connect because it's trying to use the blocked-off listener. IE: When I connect from my desktop to DB_SQL _at_ 1521, I can see in the listener logs that it's accepting connections on the 1591 listener, and vice-versa.  

Info I have found:

  Metalink ID 364855.1 talks about setting the Local_Listener to a VIP and a specific port, but this would cover ALL connections, and I really would like connections on 1521 to stay 1521, and 1591 to stay 1591 so that I can block off 1521.

  Metalink ID 361284.1 says it is random, and I could use CMAN, but to be honest, I can't find a lot of information about CMAN for Oracle 10, nor RAC. Does anyone have any experience with this combination?

  And I have found SCAN for 11gR2, but do you have any idea how silly it is to search for "SCAN" on metalink? Anyway, still looking this up, but not planning on going to 11G for another year, so I'm not sure this is really a good solution.    

Any thoughts or experiences that anyone has had would be very much appreciated. After doing this much research, I'm really wondering if we are chasing the right solution.  

Thanks in Advance..      

~ Tyfanie Wineriter ~  

Database Administrator

University of Oregon

1212 University of Oregon

Eugene, OR 97402-1212

(541) 346-1366

Received on Thu Jan 21 2010 - 11:32:40 CST

Original text of this message