Developer 6i Patchset 18 on Apps should be applied on 806 home

A few hours back, Akhilesh called me for FRM-92100 being faced after upgrading an Apps Database to 10gR2 and applying Developer 6i patchset 18. There were 250 invalid synonyms in the DB after the upgrade which did not belong to APPS. Note 380480.1 advises to fix them using ALTER SYNONYM PUBLIC. COMPILE; and also mentions that Core Apps is not affected due to these invalid synonyms. However ALTER SYNONYM command doesn't work and the correct way is to generate the drop and create synonym commands from the source by using this script. However this had nothing to do with the FRM-92100 problem being faced.

On a hunch, I tried to relink f60webmx and it failed with an undefined symbol error. Akhilesh told me that even though patchset 18 was applied it was reporting the version as 25.2. On checking the version of Developer 6i by running f60gen on command line, we found that it was 25.2 instead of 27.0 which is the version after applying patchset 18. The DBAs who had applied patchset 18 had dutifully followed the readme of patchset 18 (patch 4948577) which states this:

Installation Instructions

1. Set your ORACLE_HOME.
2. If you are going to apply this patch on top of an iAS install:

Set ORACLE_HOME to /6iserver
Prepend /6iserver/bin to the *beginning* of PATH
Prepend /6iserver/lib to the *beginning* of LD_LIBRARY_PATH

3. Copy the files in this patch to your $ORACLE_HOME.
4. Unzip the patch using either WinZip on NT or unzip on Unix.
5. Run the install script contained in the patch:

cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 | tee patch_install_p18.log (ksh)
./patch_install.sh |& tee patch_install_p18.log (csh)

6. Check patch_install_p18.log for errors.
7. Relink Procedure Builder, Forms, Graphics and Reports:

cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install

Reports has both link-time and run-time dependency with libjava.so so you need to append $ORACLE_HOME/network/jre11/lib/sparc/native_threads in $LD_LIBRARY_PATH before linking Reports. The same $LD_LIBRARY_PATH should be used at run-time.

cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install

The ORACLE_HOME was set to IAS_ORACLE_HOME erroneously and patchset 18 was applied on IAS_ORACLE_HOME. This was confirmed by checking for the presence of the directory developer6i_patch18 in IAS_ORACLE_HOME. Akhilesh told me that he had tried to install patchset 18, by setting ORACLE_HOME to 806 home and it complained that patchset 18 was already present. I advised him that, since patchset 18 was applied to IAS_ORACLE_HOME it should be uninstalled when ORACLE_HOME was set to IAS_ORACLE_HOME.

# Run the de-install script contained in the patch:

cd $IAS_ORACLE_HOME/developer6i_patch18
./patch_deinstall.sh 2>&1 | tee patch_deinstall_p18.log (ksh)
./patch_deinstall.sh |& tee patch_deinstall_p18.log (csh)

Once he de-installed it from IAS_ORACLE_HOME, he was able to install it in 806 ORACLE_HOME. We were able to relink f60webmx without any symbol errors after this, and FRM-92100 error disappeared. The Jan 2007 CPU patches for Developer 6i needed to be applied correctly on the newly patched 806 home, as they were also applied incorrectly to IAS_ORACLE_HOME by the DBAs.

This is a very basic thing and you should be very clear about this. Developer 6i in Oracle Apps uses the 806 ORACLE_HOME. The IAS_ORACLE_HOME is used only for Apache Jserv and the 9i Application Server 1.0.2.2.2. Step 6 of the interop note for Dev6i also mentions clearly:

NOTE	All references to ORACLE_HOME refer to the 8.0.6-based Oracle Home.

I guess this is a good example of the adage: "Common sense is not so common ".

I'll repeat again, there are 3 different ORACLE_HOME in Apps 11i:

1. Tools ORACLE_HOME: Oracle 8.0.6 home used by Developer 6i
2. IAS ORACLE HOME: Oracle 8.1.7 home which contains, Apache, Jserv and 9i AS 1.0.2.2.2
3. Database ORACLE_HOME: Oracle 9.2.0 or 10.2.0 home which contains the binaries through which the Apps Database runs.