adpatch fails- Cannot connect to RAC database

Yesterday I needed to apply a patch to my Oracle E-Business Suite 11.5.10.2 environment.
This environment consist of a 10G release 2 RAC database and two application tiers.

I needed to apply patch 4676589 (11i.ATG_PF.H.RUP4), which is about 275MB in size, zipped.
I defined 4 workers; the patch ran smoothly until I saw 2 workers fail with the following programs:

• fnd_form_custom_action.xdf
• wf_entity_changes.xdf

Taking a look into the logfile of the workers showed me the following:

…
Evaluating symbolic arguments ..
Net8 name-value connect string is:
(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD1)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle))))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD2)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle)))))

DB Address is:
(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))

CONNECT_DATA is:
(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD1)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle)))

SERVICE_NAME/INSTANCE_NAME : [PROD1]

connect_string : [(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD1)))]

..

Done evaluating symbolic arguments.

Time when worker started job: Fri Sep 15 2006 16:20:43

Start time for file is: Fri Sep 15 2006 16:20:43
adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp &un_fnd &pw_fnd &un_apps &pw_apps &jdbc_protocol &jdbc_db_addr sequence &fullpath_fnd_patch/115/xdf_fnd_form_custom_actions.xdf &fullpath_fnd_patch/115/xdf_xsl

Reading product information from file…

Reading language and territory information from file…

Reading language information from applUS.txt …

Temporarily resetting CLASSPATH to:
“/apps/prod/appl/ad/11.5.0/java/adjri.zip:/apps/prod/comn/util/java/1.4/j2sdk1.4.2_04/lib/tools.jar:/apps/prod/comn/util/java/1.4/j2sdk1.4.2_04/lib/dt.jar:/apps/prod/comn/util/java/1.4/j2sdk1.4.2_04/jre/lib/charsets.jar:/apps/prod/comn/util/java/1.4/j2sdk1.4.2_04/jre/lib/rt.jar:/apps/prod/comn/java/appsborg2.zip:/apps/prod/ora/8.0.6/forms60/java:/apps/prod/comn/java”

Calling /apps/prod/comn/util/java/1.4/j2sdk1.4.2_04/bin/java …

Exception occured

Copyright (c) 2003 Oracle Corporation

Redwood Shores, California, USA

XDF(XML Object Description File) Comparison Utility
Version 1
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.

Unable to get the database connection using schema username/passwordIo exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869824)(ERR=12514)(ERROR_STACK=(ERROR=(CODE=12514)(EMFI=4))))

AD Run Java Command is complete.
…

What happened?
The patch utility, or the xdf component, whichever of the two, wanted to establish a jdbc thin connection to the database.
Apparently, the tns-alias defined in my TWO_TASK is used to derive the information to establish this connection. First let’s take a look at this alias:

(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD1)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle))))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD2)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle)))))

This alias is generated by autoconfig.

From this address, the patch utility derives (take a look at the excerpt of the log file shown above) the Database Address.

DB Address is:
(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))

 Next, the procedure derives the CONNECT_DATA:

CONNECT_DATA is:
(CONNECT_DATA=(SERVICE_NAME=PROD)(INSTANCE_NAME=PROD1)(CID=(PROGRAM=)(HOST=host1vip)(USER=oracle)))

Now, suprisingly, I saw the next lines:

SERVICE_NAME/INSTANCE_NAME : [PROD1]

connect_string : [(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD1)))]

Even though there is a difference between SERVICE_NAME and INSTANCE_NAME, Oracle decides to equate them here. This has a history, because one of the requirements of Oracle E-Business Suite is that (at least with a default single instance – non-RAC – database) GLOBAL_DB_NAME=SERVICE_NAME=INSTANCE_NAME. With this in mind, it is not so strange that the value of INSTANCE_NAME it put into SERVICE_NAME. The problem with a RAC database, is that this is destined to fail: At least one of the instances will have a different name. When you use autoconfig to convert your E-Business Database to RAC, SERVICE_NAME will be e.g. PROD and the instances will be called PROD1 and PROD2. No match at all.

The result is shown above: the connection to the database is refused (ORA-12514).

I did some research to bypass the problem. I tried to run the program manually; didn’t work out. Tried several other tricks, but all lead to failures.

Oracle has identified this problem in Metalink Note 375595.1 Loading Date with .xdf files and Can’t Connect to the Database. I am not sure whether or not the note is published/accessable yet.

What does Oracle suggest in this note? Add a new tns-alias e.g. PATCH and set your TWO_TASK to this alias before running adpatch.

for example:

PATCH=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host1vip)(PORT=1521))
(CONNECT_DATA=
(SID=PROD1)
)
)

The Note also warns me to set TWO_TASK to PATCH before running adpatch and to remember to revert it after adpatch is complete.

I have a few remarks on this workaround:

1. Putting this alias in your tnsnames.ora (as was suggested to me by Oracle Support, based on text taken from a bug) is dangerous, in a way. You should consider this as a customization, therefore you should put it into the tns ifile instead. This ensures that after adpatch has run and intiated autoconfig for whatever reason, your PATCH alias isn’t gone. Autoconfig doesn’t hit the ifiles. To lookup the exact name of the ifile, take a look at the bottom line of your tnsnames.ora file. There should be an entry called ifile=….. Create the file accordingly, and put the alias in this file.
2. Again, IMHO, asking a customer to set the TWO_TASK to something else than what is defined in your environment, and “Remember to reverse the change After patching is complete” doesn’t sound like a proper workaround to me. Some dba’s have to manage a lot of things during their daily work, and this is typically something that can easily go wrong at some point. Both setting and reversing the TWO_TASK is easily omitted accidentally, increasing the chances of patch failures, and whatever other things can go wrong when your TWO_TASK variable is accidentally set to a wrong value

I would propose the following (also a workaround, but I think it a better one than the one proposed by Oracle):

Connect to the database as sysdba and add the instance names to the parameter SERVICE_NAMES:

sql> alter system set service_names=’PROD,PROD1,PROD2′;

Give the database some time to register these SERVICE_NAMES to the listeners, but then, whichever value is entered into the SERVICE_NAME of a jdbc connect_string will lead to a successful connection.

This workaround doesn’t require DBAs to remember to set/reset TWO_TASK, the environment on application level remains untouched, and no damaging consequences are encountered after running adpatch/autoconfig.