Implementing Virtual IP addresses with Oracle 10g/RAC for E-Business Suite 11i

Oracle 10g Cluster Ready Services enables databases to use a Virtual IP address to configure the listener on. This feature is to assure that oracle clients quickly failover when a node fails.

When a client connects to a tns-alias, it uses a TCP connection to an IP address, defined in the tnsnames.ora file. When using RAC, you define multiple addresses in your tns-alias, to be able to fail over when an IP address, listener or instance is unavailable. However, TCP timeouts can differ from platform to platform or implementation to implementation. This makes it difficult to predict the failover time. In theory, the failover time can add up to two minutes (default TCP timeout). In Oracle 10g, when a node fails, the Virtual IP address is taken over by (one of) the remaining RAC node(s).

The client can now very quickly (i.e. a couple of seconds to less than a second) determine that the listener does not respond (the IP address does, but there is no listener), so it will fail over to the alternative address as configured in the tnsnames.ora.

When I first read about this feature, I was really looking forward to implement this into an E-Business Suite on RAC environment. However, unfortunately this feature wasn’t supported in Oracle E-Business Suite 11i at the time. Until ADX.F (patch 3453499) came out October 2005 (I guess it was around that time).

Great was my surprise finding the following lines in the README.txt file of the patch:

…
  [4] Support Virtual IP in listener.ora for AutoConfig
      When registering a node within the Applications Topology,
      11i.ADX.F will use a virtual IP if it is present and applicable.
      This allows machines such as the database to be configured
      to use a virtual IP for controlling traffic to the host.
…

Isn’t that promising? – Yes it is, however I find it rather enigmatic.

When registering a node within the Application Topology ADX.F will use a virtual IP when it is available… OK. But how do I implement this feature? Just by installing the patch? So I logged an iTAR to find out how to implement this, but without success. Until the end of last year (2005) Oracle Support could not tell me how to implement Virtual IP for Oracle E-Business Suite. Not even today can I find documentation for it on Metalink, but that can be because I am not looking hard enough. In this article, I will explain how I got it to work.

DISCLAIMER: I describe in this document a way how to implement Virtual IP addresses for the database listener in an Oracle E-Business Suite 11i environment. Since no information was begotten from Oracle at the time of writing this document, I cannot state that this is a supported way of implementing this Oracle 10g/RAC feature. Also, I explain in this document to change some configurations directly in the database. I am only partly making use of pre-defined procedures for this, but I am also using direct DML statements to make changes to the database in order to make sure that old data is removed from the database. Neither I nor AMIS Services can be held responsible for any damage that will be done performing this outline on any environment. This procedure should always be tested first on a Test environment.

Note: Oracle 10g Release 1 cannot failover applications (as 10g release 2 is supposed to do), therefore it is impossible to failover the listener to the remaining node, unless you create some really fancy scripting yourself.

Architecture

In this example, I am using a basic single-node implementation of Oracle E-Business Suite. I have 2 nodes installed with Oracle RDBMS 10g with Real Application Clusters and Automatic Storage Management, and on the same machines I have the application server installed with a shared APPL_TOP on a shared file system.

 

Node	Hostname	Instance name	Interconnect	VIP Alias
Node 1	db01	PROD1	db01rac	db01vip
Node 2	db02	PROD2	db02rac	db02vip

 

Prerequisites

The first thing to do is to implement Oracle E-Business Suite 11i. After this, the database needs to be upgraded to Oracle 10g, migrated to RAC and optionally to ASM, which is what I did. I followed Oracle Metalink Note 312731.1 to implement all of this. Note 312731.1 (Configuring Oracle Applications Release 11i with 10g Real Application Clusters and Automatic Storage Management) points you to all necessary documentation and procedures to implement Oracle 10g Real Application Clusters for your Oracle E-Business Suite 11i environment.

Migration to VIP

In order to use VIP, you have to change the RDBMS environment so that it will let the listener use the Virtual IP address. To accomplish this, you need to change several autoconfig parameters for the Database Tier:

• Local Host Name    <= I changed db01 to db01vip and db02 to db02vip
• Virtual IP alias        <= I changed db01 to db01vip and db02 to db02vip
• Private Node Name    <= I changed db01 to db01rac and db02 to db02rac

To do this, log on to the Oracle Applications Manager as sysadmin

• Click on “Site Map”
• In the next page, click on “AutoConfig”
• You will see a list of at least 4 files. The first two are for the database; the second pair is for Applications.

• Click on the Pencil Icon to edit the parameters for the first file in the list. This should be the first database server.
• Click on the “Local” tab
• Change the values of “Local Host Name” and “Virtual IP alias” to the VIP alias
• Change the value of “Private Node Name” to your RAC interconnect alias. This needs to be done, because otherwise OAM will change the value for you into the value of the VIP alias.

• Save the configuration.

• Repeat this for the second database server configuration file.

The reason to change both “Local Host Name” and “Virtual IP alias” is to assure that both the listener and the tnsnames.ora files are affected by running autoconfig. If you change only “Virtual IP alias”, the listener will be configured to listen on the VIP address, but autoconfig is using “Local Host Name” to define the aliases for tnsnames.ora.

After entering the changes, save the configuration.

For the Application Tier you need to change the following parameter:

• Data Server Host    <= I changed db01 to db01vip and db02 to db02vip

To do this, go back to the AutoConfig page

• You will see the list of 4 files again. The first two are for the database, the second pair is for Applications.

• Click on the Pencil Icon to edit the parameters for the third file in the list. This should be the first Applications server.
• Click on the “System” tab
• Expand the “oa_db_server” key
• Change the value of “Data Server Host” to the VIP alias

• Save the configuration

• Repeat the above steps for the fourth file in the list (the second Applications Server).

 

Always check whether the changes you made through Oracle Applications Manager have been propagated to the actual files. Oracle Applications Manager sometimes reports that files have been saved and changes have been applied and that it is only needed to run autoconfig, but I have encountered various (and even with the latest patch sets!) occasions where Oracle Applications Manager reported that files had been saved, but no changes had been made to the actual files. Running autoconfig does not result in any changes to the environment, in this case.

Clean The Environment

Also, before running autoconfig, I recommend (and this is a personal recommendation – see disclaimer above!) to clean up the configuration in the database. Autoconfig stores the configuration parameters for all of the nodes in the environment to the database, and uses the values from the database to dynamically create the listener.ora and tnsnames.ora files. Cleaning up the tables that hold all of this information, makes sure that no redundant information is kept after running autoconfig, and thus no redundant (and possibly wrong and/or obsolete) information ends up in your configuration files (listener.ora/tnsnames.ora)

I always do this in the following way:

• First, connect to the database as apps.
• In sql*plus, run the following command:

	sql> execute FND_CONC_CLONE.SETUP_CLEAN;

In theory this should be enough to clean up your environment parameters. In practice, some information remains in the tables, or some tables are not cleaned at all (I have never actually digged into this to find out what fnd_conc_clone.setup_clean actually does and which tables it hits, but I know that truncating the following tables (all of them in the applsys scheme) do clean up the environment a little more thoroughly, if not completely:

• FND_APPS_SYSTEM
• FND_APP_SERVERS
• FND_SYSTEM_SERVER_MAP
• FND_DATABASES
• FND_DATABASE_INSTANCES
• FND_DATABASE_SERVICES
• FND_DB_SERVICE_MEMBERS
• FND_DATABASE_ASSIGNMENTS
• FND_TNS_LISTENERS
• FND_TNS_LISTENER_PORTS
• FND_TNS_ALIASES
• FND_TNS_ALIAS_ADDRESSES
• FND_TNS_ALIAS_SETS
• FND_TNS_ALIAS_SET_USAGE
• FND_APPL_TOPS
• FND_ORACLE_HOMES
• FND_TNS_ALIAS_DESCRIPTIONS
• FND_TNS_ALIAS_ADDRESS_LISTS
• FND_DB_INSTANCE_PARAMS
• FND_SIDS
• FND_NODES

Note: It might be a little counter-intuitive to see that you have to manually truncate these tables, especially in an environment like Oracle E-Business Suite. I have no idea whether this is a supported way of dealing with the situation; I guess not. However, I have been told that several Oracle consultants use this methodology to clean up environments. I know for sure that it works, because I have done it this way successfully various times and it should not do any damage to your environment. If you are uncertain about it, there is always the possibility of copying the affected tables (export, or CTAS) before starting this procedure.

Note: All of these tables will be refilled with the appropriate information when running autoconfig.

AutoConfig

Now that we have cleaned the environment in the database, we need to run a specific batch of autoconfig routines. Since there is no information in the database, it needs to be filled. We do this by running autoconfig on all tiers sequentially, starting with the database tiers. After having completed this run of autoconfig, you will find out that the information in the configuration files on all four nodes – for example: tnsnames.ora – is different. You might wonder how this is possible. Well, here is why:

By running autoconfig on the first database server (db01), the information for the first node is entered into a set of clean tables. Therefore, db01 cannot retrieve any data concerning the other nodes from the database, simply because the data is not available.

When you run autoconfig on the second database server, this node will recognize the information from db01 and will add its own information into the database, resulting in a set of tns files that “know” both db01 and db02. When we want to configure db01 so that it also knows about db02, we have to run autoconfig again on db01.

In order to get all information about all nodes propagated to all nodes, you will have to run autoconfig in the following order, using the procedure outlined below:

1. Run autoconfig for the first database tier

2. Run autoconfig for the second database tier

3. Run autoconfig for the first application tier

4. Run autoconfig for the second application tier

5. shutdown the database listeners

6. startup the database listeners (now on the vip alias)

The database resolves the values of local_listener and remote_listener when the database is mounted. The resolution of these values is kept in memory for the time the database is up and running, so the new values for local_listener and remote_listener need to be effectuated for the database. You can do this in one of the following ways:

     Option 1 Perform the following statements in the database (connect / as sysdba):

a. sql> show parameter listener

b. Note down the value of the parameters local_listener and remote_listener and perform the following statements with the identical values you noted down. This forces the database to refresh the information:

c. sql> alter system set local_listener=<DBname>_LOCAL;

d. sql> alter system set remote_listener=<DBname>_REMOTE;

Note 1: You can verify the registration of the database to both of the listeners by issuing “srvctl services <LISTENER_NAME>” on the first database tier only. This should give you the output that for service <DBName> there are two instances. See Step 11, bullet b for additional information when you don’t get the expected result.

Note 2: Because the first database did not know about the second instance at the time of running autoconfig, its remote_listener only resolves to the first database tier. The second database tier knew from the database that there was another instance, so automatically this other instance was added to the <DBName>_REMOTE alias in tnsnames.ora. Therefore, only the second instance registers against both listeners, and not the first. After running autoconfig on the first DB tier again (step 11) this should be resolved.

     Option 2 Bounce the instances

7. Check whether you can connect to the database as apps, using the applmgr user, from both application servers.

Source the application environment file (APPS<CONTEXT_NAME>.env)

Sqlplus apps/<password for apps> should connect you to the database successfully.

8. Run autoconfig for the first application tier again

9. Run autoconfig for the second database tier again

10. Run autoconfig for the first database tier again

Note 1: Since the remote_listener has changed for this DB tier, it is necessary to perform the “alter system set remote_listener=<DBName>_remote” again for the instance running on this tier. You will now see that this instance also registers against the listener on Node 2

Note 2: It is possible that in certain conditions your instance is unable to register against the remote listener properly. You will find that the instance(s) only register against the local listeners. If this is the case, check your sqlnet.ora whether node_checking is enabled. If it is, make sure that your nodenames and all of the vip nodenames are included in the invited nodes list.

• When you successfully complete this procedure you have yourself an environment with Oracle 10g/RAC that uses the Virtual IP addresses for the listeners.
  

  Summary

   

  I described in this article how to implement the Virtual IP addresses of Oracle 10g Real Application Clusters in an E-Business Suite 11i environment using autoconfig.
  Now that we have done all of the steps, we have database listeners that are tied to the virtual IP addresses that come with Oracle 10g RAC. When either the node or the interface of either node in the cluster fails, the IP address is taken over by the remaining node in the cluster. When clients connect to this IP address, they are immediately failed over to the other virtual IP address, because they are able to rapidly determine that there is no listener available on that IP address. When the interface or the node comes back online, Oracle Cluster Ready Services takes care of the restoration of the Virtual IP address to the original recovered node, and the listener can be started again.

  I am open to discussion on this topic, because no information has been provided by Oracle other than the statements in the README.txt of the ADX.F patchset. Maybe there are people working at Oracle that do know whether this is the way to do this. If someone has any other ideas, please post a reply.