In a RAC configuration we traditional have a VIP associated to the public address for every node in the cluster. Since this is the only public network to the database server, all traffic is directed through this VIP.
Note: Multiple networks in the cluster are not officially supported for Oracle 10.2 RAC. So this entire venture of multiple networks was at our risk.
We had a requirement to isolate application traffic received from the application tier from the regular public/user/third party application traffic. For this an additional public IP was configured for each server in the cluster and an additional VIP was defined on this new public IP. Since the intention was to use this new VIP only for the application tier, we called it the middle tier (mt) VIP. So this note discuss the process followed and the outcome
Steps to add the new VIP
There are two methods of adding a VIP to CSR from the command prompt as discussed below or by creating a ‘cap’ file that contains the profile definitions. This is a 3 node cluster, the nodes are ebspocdb1, ebspocdb2, ebspocdb3
1. Create an user VIP using the following command as user ‘oracle’.
/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb1-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb1 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=188.8.131.52,on=255.255.255.0
/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb2-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb2 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=184.108.40.206,on=255.255.255.0
/app/oracle/product/crs/bin/crs_profile -create ora.ebspocdb3-mt.vip -t application -d ‘Mid Tier application VIP’ -a /app/oracle/product/crs/bin/usrvip -h ebspocdb3 -p favored -o as=1,ap=1,ra=0,oi=eth3,ov=220.127.116.11,on=255.255.255.0
2. Once the profile has been created, register the resource with CRS..
3. This resource should be owned by ‘root’. As user ‘root’ change the ownership of the resource and then give ‘oracle’ user execute privilege for this resource. You can execute these commands from any node in the cluster.
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb1-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb1-mt.vip -u user:oracle:r-x
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb2-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb2-mt.vip -u user:oracle:r-x
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb3-mt.vip -o root
/app/oracle/product/crs/bin/crs_setperm ora.ebspocdb3-mt.vip -u user:oracle:r-x
4. Start the resource as user ‘oracle’
/app/oracle/product/crs/bin/crs_start -c ebspocdb1 ora.ebspocdb1-mt.vip
/app/oracle/product/crs/bin/crs_start -c ebspocdb2 ora.ebspocdb2-mt.vip
/app/oracle/product/crs/bin/crs_start -c ebspocdb3 ora.ebspocdb3-mt.vip
5. Verify if VIP has been configured and started
[oracle@ebspocdb1 ~]$ crsstat | grep .vip
ora.ebspocdb1.vip ONLINE ONLINE on ebspocdb1
ora.ebspocdb1-mt.vip ONLINE ONLINE on ebspocdb1
ora.ebspocdb2.vip ONLINE ONLINE on ebspocdb2
ora.ebspocdb2-mt.vip ONLINE ONLINE on ebspocdb2
ora.ebspocdb3.vip ONLINE ONLINE on ebspocdb3
ora.ebspocdb3-mt.vip ONLINE ONLINE on ebspocdb3
NOTE: if you need to modify this resource for any reason you could use the crs_profile update command. However you have to first stop the resource using crs_stop command and then execute the update command. For example..
crs_profile -update ora.ebspocdb1-mt.vip -t application -a /app/oracle/product/crs/bin/usrvip -h ebspocdb1 -p favored -o oi=eth3,ov=18.104.22.168,on=255.255.255.0
After executing the update command, repeat steps 2 thru 4 to implement this change.
Create second database listener
Create a second listener on the new mt.vip using netca on a different port. Select the new mt VIP as the network. This will also add the new listener to CRS. After configuring the listener verify the status.
[oracle@ebspocdb1 ~]$ crsstat | grep .lsnr
ora.ebspocdb1.LISTENERMT_EBSPOCDB1.lsnr ONLINE ONLINE on ebspocdb1
ora.ebspocdb1.LISTENER_EBSPOCDB1.lsnr ONLINE ONLINE on ebspocdb1
ora.ebspocdb2.LISTENERMT_EBSPOCDB2.lsnr ONLINE ONLINE on ebspocdb2
ora.ebspocdb2.LISTENER_EBSPOCDB2.lsnr ONLINE ONLINE on ebspocdb2
ora.ebspocdb3.LISTENERMT_EBSPOCDB3.lsnr ONLINE ONLINE on ebspocdb3
ora.ebspocdb3.LISTENER_EBSPOCDB3.lsnr ONLINE ONLINE on ebspocdb3
Update the LOCAL_LISTENER and REMOTE_LISTENER definitions in the tnsnames.ora file.
Update the LOCAL_LISTENER and REMOTE_LISTENER definitions in the tnsnames.ora file to include the new LISTENERMT_EBSPOCDB1 listener definitions. Once this is complete and the listener is recycled, the database services are dynamically registered with the listener.
While the default VIP was defined in the DNS server, the private VIP was only visible to the app tier. On a load test, we noticed that not all connections was established with success. Actually every other connection failed.
The intention of having a dedicated VIP was to isolate connections from the app tier to the database tier using this private VIP. However once the LOCAL_LISTENER parameter is configured, database services are registered with both listeners, what’s wrong with that? When the app tier makes a connection request to the database tier using the new VIP, and when the database tier had to return the session handle back to the requestor, it could not pin it back to the same listener that received the request and almost every other time the session handle was routed to the other listener causing session death.
What would have been nice, is if the services could be mapped to a specific listener, which would allow sessions to be pinned back to the service to the listener to the requestor.
Its obvious why Oracle does not support multiple public networks for the database tier, since VIPs are assigned to public NIC, additional VIP did not help in this isolation.