On many occasions, people will email or call me to get help with perceived RAC connectivity issues for various client applications – Toad and other Quest products included. Now some times they may have setups that we’ve not pre-tested, so they have a potential bug – or at least another regression testing scenario for us. But many times, it’s simply a SQL*Net configuration issue. While it’s easy to draw a simple diagram of what RAC is, it’s almost an art form to write RAC “tnsnames.ora” files that work as intended. So I figured a quick refresher on RAC and SQL*Net configuration files might be useful.
Let’s start by drawing a picture of the basic concept of an Oracle Real Application Cluster (a.k.a. RAC). RAC is essentially a group of database servers and Oracle instances running as a collective group so as to appear as a single server and database to the outside world. Thus to all the end users, web servers and application servers out there – there is really just the RAC server and database. The fact that it’s a group performing as a cluster is really transparent and unimportant – except the correct tnsnsmes.ora files J
So what should the tnsnames.ora file look like? Let’s show an example where the RAC database has just two servers and instances: RAC1 and RAC2.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1-vip)(PORT=1521))
(ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2-vip)(PORT=1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = RAC)
(INSTANCE_NAME = RAC1)
)
)
RAC2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2)(PORT=1521))
)
(CONNECT_DATA =
(SERVICE_NAME = RAC)
(INSTANCE_NAME = RAC2)
)
)
So let’s use Toad as an example – how should we connect to the database? Generally speaking, probably 98% of the time we should simply specify the RAC instance/service name and let Oracle load balance us to whichever node has capacity. Only when we need to debug PL/SQL should Toad users (because of an Oracle limitation/requirement) specify connecting to a specific node – such as RAC1. That’s because the Oracle debugger API needs to create two sessions on the same node so one can watch the other. If we tried to debug using RAC, it might load balance the two sessions onto different nodes – and then we’d get a debugger scoping error. But for all other times we should simply use RAC to connect – period.
Finally, look at the RAC entries for the TCP host addresses. Why do they say node-vip instead of just node as with the RAC1 and RAC2 corresponding entries? What is the VIP portion? Let’s look at my PC’s Windows hosts file:
192.168.100.104 linux_rac1
192.168.100.105 linux_rac1-vip
192.168.200.104 linux_rac1-priv
192.168.100.106 linux_rac2
192.168.100.107 linux_rac2-vip
192.168.200.106 linux_rac2-priv
Why are there three IP addresses per database server? Look back to our RAC architecture diagram above. The RAC instances require two distinct TCP/IP addresses, one for the public network (e.g. linux_rac1) and one for the private network (linux-rac1-priv). So what exactly are the seemingly extra entries for linux_rac1-vip and linux_rac2-vip, and why do we use them as our addresses for the RAC entry in our tnsnames.ora file?
When the DBA sets up the cluster nodes, one of the steps involved creating virtual IP addresses. That’s what these entries point to. A virtual IP address is the path by which users should access the node, and whenever a node crashes – Oracle can dynamically reassign the virtual IP address to point to one of the other nodes in the cluster. That way we have transparent application failover (TAF) – because all the network access is virtualized and dynamic. Hence nodes can come and go, or be physically relocated – and to the database nothing has changed. Only the Oracle listeners on the database servers need be aware of this dynamic addressing scheme.