Myth and Misconception during Oracle EBusiness Suite Cloning/Refresh

As a Oracle Apps DBA, most of us are very familiar with the Oracle EBusiness Suite cloning/refresh process. Though there may be a few changes in perspective every one follow as per the client requirement. 

Overall; in simple terms we can divide the Cloning/Refresh process of an EBusiness Suite instance into following few steps at a high level:

1) Prepare the Source environment

2) Backup and copy the File system to Target Servers

3) Extract the backups and restore on Target server

4) Configure the Target environment.

One of the Myth that many of the Oracle Apps DBAs are unaware is that, whenever we run ADPRECLONE utility on the source environment, we have to be very careful while entering the apps password. 

Most may be thinking, what will happen at the max if I give wrong apps password, it might fail and I can re-run it again. But the fact is, it will make more damage than we expect/know.

It actually shuts down the database if we pass wrong apps password during adpreclone.pl dbTier on the db node. It will cause outage to your environment and thereafter escalations, etc. Just be careful!!!

Now lets look at it on apps node (Middle Tier). Someone might ask you what will happen if you pass wrong password to adpreclone.pl appsTier on the middle Tier. 

On the Middle Tier, even if we give wrong apps password, it will still continue without any issues.

JDK upgrade in Oracle EBS 12.1.3

Aim : 

The aim of this post is to detail the steps of JDK upgrade in EBS 12.1.3.

Steps:

1) Download and install the following patches:
Patch 16545472:R12.OAM.B
Patch 17309237:R12.TXK.B
Patch 16496713:R12.POS.B

2) Download latest version of JDK that we are planning to upgrade to. (In our case, plan is to upgrade to JDK 1.7.0_271.

We can download JDK 1.7.0_271 using the patch#31311341 from Oracle Support site.

3) Stop all application services using adstpall.sh apps/apps

4) Copy the JDK to below location:

cd $IAS_ORACLE_HOME
mv jdk jdk_07082020
mv jdk1.7.0_271 jdk

cd $IAS_ORACLE_HOME/appsutil
mv jdk jdk_07082020
mv jdk1.7.0_271 jdk

5) Rename JRE 1.4.2 as it would not be required:

cd $IAS_ORACLE_HOME/jre
mv 1.4.2 1.4.2_old 

6) Copy the albony font file:

cp $FND_TOP/resource/font_file_name.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts

7) Download and Apply Patch 12848228 and 5659594

Apply Patch 12848228 and Patch 5659594 to your 10.1.2.3 Oracle Home using Opatch.

8) Copy JDK to 10.1.2 Home

cd $ORACLE_HOME
mv jdk jdk_old
mv jdk1.7.0_2 jdk 

9) Rename JRE 1.4.2 under 10.1.2 Home

mv $ORACLE_HOME/jdk64 $ORACLE_HOME/jdk64_old /* if there is such */
mv $ORACLE_HOME/jre/1.4.2 $ORACLE_HOME/jre/1.4.2_old
mv $ORACLE_HOME/jre/1.4.2 $ORACLE_HOME/jre/1.4.2_old

10) Apply Below Patches to 10.1.2 Home:

Patch 16271876 - (apply this patch first)
Patch 17907988 - (apply this patch befor the Forms patch 17653437)
Patch 17653437 - Forms Patch
Patch 17645157 - Reports Patch
Patch 16241466  - Proc Patch

11) Relink the forms and reports

$INST_TOP/ora/10.1.2
Souce the env file

cd $ORACLE_HOME/forms/lib32
make -f ins_forms.mk sharedlib install

cd $ORACLE_HOME/reports/lib
make -f ins_reports.mk install

Run ADAdmin and select the Forms and Reports regeneration.

12) Start all Application services using adstrtal.sh apps/apps

JRE Upgrade in Oracle EBS 12.1.3

In order to perform JRE upgrade in Oracle EBS R12.1.3, follow the below steps:

1) Apply the Interoperability Patch # 21624242.

2) Download the JRE Plugin from the below URL:

https://www.oracle.com/technetwork/java/javase/downloads/index.html

Make sure to download 32-bit version of 

3) Rename the JRE plugin File

Rename the downloaded JRE Native Plugin file to j2se<jversion>.exe

jre-8u202-windows-i586.exe or jre-8u202-windows-x64.exe would be renamed j2se18202.exe

4) Place the Renamed JRE Plugin on the Web Application Tier
Move the j2se<jversion>.exe file to the web application tier and place it in the following directory:

mv j2se18202.exe  $COMMON_TOP/webapps/oacore/util/jinitiator/

5) Upgrade and Configuration

Run the $FND_TOP/bin/txkSetPlugin.sh script against the web node of the application tier.

The txkSetPlugin.sh script updates the JRE version information in your AutoConfig context file and runs AutoConfig to incorporate the new values throughout your application. This script must be run using the following command:

 txkSetPlugin.sh 18202

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

Issue :

Planning ODS Load Concurrent Program fails with below error in the log file:

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

Cause:

The issue is caused by the following setup:
The indexes with MSC might have corrupted.

Solution :

Please follow following steps to drop and recreate the indexes to fix the issue:

Note:
Please changes the APPS/APPS in step 3 with apps user and password.

1. Run the following SQL to generate all the 'drop' statements needed to drop all the indexes on the relevant MSC_ tables:

SELECT 'drop index msc.'||index_name||';'
FROM (
SELECT ods.table_name ,
  ai.index_name ,
  SUBSTR(ods.table_name,5)
  || '__'
  || TO_CHAR(mip.instance_id) partition_name
FROM msc_ods_table_v ods ,
  msc_inst_partitions mip ,
  all_indexes ai
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
MINUS
SELECT ods.table_name ,
  aip.index_name ,
  aip.partition_name
FROM all_ind_partitions aip ,
  all_indexes ai ,
  msc_ods_table_v ods
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
AND ai.index_name = aip.index_name
AND ai.owner = aip.index_owner);

2. Copy the resulting DROP statements to a notepad.

3. Execute the following SQL to generate all the 'adjava' commands to add the indexes back to the tables.

Note: In line 2, substitute the correct password for MSC and APPS (the second occurrence). In line 3, substitute the correct server name, port, and SID name. 

SELECT 'adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp '
|| 'MSC MSC APPS APPS '
|| 'thin ebs.example.com:1521:EBSDB index '
|| '$MSC_TOP/patch/115/xdf/'||table_name||'.xdf '
|| '$FND_TOP/patch/115/xdf/xsl '
|| 'logfile=$HOME/adjava_XDF.log'
|| 'changedb=y"' as xdf_command, table_name
FROM (
SELECT ods.table_name ,
  ai.index_name ,
  SUBSTR(ods.table_name,5)
  || '__'
  || TO_CHAR(mip.instance_id) partition_name
FROM msc_ods_table_v ods ,
  msc_inst_partitions mip ,
  all_indexes ai
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
MINUS
SELECT ods.table_name ,
  aip.index_name ,
  aip.partition_name
FROM all_ind_partitions aip ,
  all_indexes ai ,
  msc_ods_table_v ods
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
AND ai.index_name = aip.index_name
AND ai.owner = aip.index_owner);

4. Copy the results to a notepad

5. Execute the DROP statements.

6. Execute the ADJAVA statements in Application Node.

7. Retest the collections.
 

Ref : 

Planning ODS Load Errors With ORA-14098: Index Mismatch For Tables In ALTER TABLE EXCHANGE PARTITION (Doc ID 2251732.1)   

How to Rollback / Revert the autoconfig changes in EBS

When ever we run autoconfig, the autoconfig log files are stored under the following locations.

On Database Tier:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/<MMDDhhmm>

On Middle Tier:
$APPL_TOP/admin/$CONTEXT_NAME/log/<MMDDhhmm>


At the same time, all the backup configuration files from each autoconfig session are saved under following directories:

On Database Tier:
$ORACLE_HOME/appsutil/out/$CONTEXT_NAME/<MMDDhhmm>

On Middle Tier:
$APPL_TOP/admin/$CONTEXT_NAME/out/<MMDDhhmm>

There is also a script called restore.sh / restore.cmd under these locations along with backup configuration files.

We can run restore.sh (unix) or restore.cmd (windows) to revert back the autoconfig changes.

DMZ/External URL redirecting to Internal SSO login page

Issue :
In EBS 12.2, External URL pointing to SSO even though external node not registered with OID. Instead of pointing to SSO URL, external login URL should redirect to AppsLocalLogin.jsp

Cause: 
In EBS 12.2 and Later, intergration of SSO/OID/OAM/OIM can be done at site level only and not at server level. We can have a separate OID/OAM for external users and register the external node with the OID/OAM intended for external users.


Fix:
In all cases, since integration is done at site level, by default the external URL also redirects to SSO URL. To avoid that, update the s_login_page context variable on external node to point to http://hostname:port/OA_HTML/AppsLocalLogin.jsp.

In EBS 12.2 with DMZ, internal URL is redirecting to external and vice versa.

Issue : In EBS 12.2 with DMZ, internal URL is redirecting to external and vice versa.

Cause : When autoconfig has been run on internal, external and internal nodes; entries of external node are added in mod_wl_ohs.conf which is causing the URL to redirect while login.

Fix : Make sure the mod_wl_ohs.conf does not have entries of the external node in internal application tier and vice versa. Take a backup and remove them manually (if any).
And restart the services and check.

perl adcfgclone.pl appsTier fails with coredump error on AIX

Issue:

On AIX server, perl adcfgclon.pl appsTier is failing with below errors.

$ perl adcfgclone.pl appsTier
Enter the APPS password [APPS]:


First Creating a new context file for the cloned system.
The program is going to ask you for information about the new system:

sh: 758236 Illegal instruction(coredump)
ERROR: context creation not completed successfully.
Please check /tmp/adcfgclone_1515866.err file for errors


$ cat  /tmp/adcfgclone_1515866.err
        stackpointer=2ff207f0
JVMXM004: JVM is performing abort shutdown sequence
JVMDG217: Dump Handler is Processing a Signal - Please Wait.
JVMDG303: JVM Requesting Java core file
JVMDG304: Java core file written to /u01/app/comn/clone/bin/javacore1413304.1369742403.txt
JVMDG215: Dump Handler has Processed Exception Signal 4.


Cause:

The cause of the issue is because the symbolic links were missing from either $IAS_ORACLE_HOME/appsoui/jre/1.3.1 or $COMMON_TOP directory.


Fix:

1. Go to /<COMMON_TOP>/clone/jre. 
2. mv lib lib.old 
3. mv bin bin.old 
4. ln -s jre/bin bin 
5. ln -s jre/lib lib 
6. Repeat the steps in $IAS_ORACLE_HOME/appsoui/jre/1.3.1 
7. Run the perl adcfgclone.pl AppsTier again.

Concurrent Program failed with Error ORA-20003: FAILURE: AppServ Notification for Profile

Issue:

In ASCP, Concurrent Program using utl_http package has failed with below error in the log file:

ORA-20003: FAILURE: AppServ Notification for Profile ID=461Failed Return value: <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

<HTML><HEAD>

<TITLE

Cause:

Pval in sys_params under Demantra Schema (MDSEM in our case) table has wrong value for pname AppServerURL 

Verification:

SQL>  select pname,PVAL

  2  from msdem.sys_params

  3  where PNAME='AppServerURL';

PNAME         PVAL

------------  --------------------------------------------

AppServerURL  http://demuatapp01.xyz.com:7778/demantra_uat

Solution:

SQL> update msdem.sys_params

set pval='http://demuatapp01.xyz.com:7778/demantra_uat11' 

where pname='AppServerURL';

SQL> Commit;

Now restart demantra application services.

Diagnostics not functioning in Oracle Applications after upgrading to 12.1.3

Issue:

After upgrading to R12, when user is following below navigation for diagnostics:

Help -> Diagnostics -> Examine

getting below error:

"Function Not Available for this responsibility. Change responsibilities or contact your system administrator"

Resolution:

Beginning with Release 12.1.3, access to the above Diagnostics submenu items can be controlled by the profile Utilities:Diagnostics or by security functions using Role-Based Access Control (RBAC). Whether or not a submenu item is available is checked on an "as-needed" basis by the system when the user selects the submenu item. If the menu item is not available to the user, the message "Function not available to this responsibility. Change responsibilities or contact your System Administrator."

To fix this error, set the following profile option to Yes at user level for the user to whom this is required.

Utilities:Diagnostics 

After setting this profile to Yes, clear the cache and bounce Apache to reflect the same.

Clear Cache in Oracle Apps R12 from backend (server).

Aim: How to clear cache in Oracle Apps R12 without logging into Applications.

Procedure:  Login to Middletier and use below commands.


$ cd $ADMIN_SCRIPTS_HOME
$ adapcctl.sh stop
$ find $LOG_HOME/ora/10.1.3 -name '*' -type f |xargs rm -f
$ adoacorectl.sh stop
$ adoacorectl.sh start
$ adapcctl.sh start

Autoconfig failed during bisdblrp.sql

Autoconfig failed during bisdblrp.sql

Issue: Found Ora-600 error in alert log while dropping a database link.

Error Message :
ORA-00600: internal error code, arguments: [16500], [kqdDBLinkUpdate], [3], [2], [EDW_APPS_TO_WH.WORLD], [], [], [], [], [], [], []

Current SQL from trace file:
drop database link EDW_APPS_TO_WH.WORLD;

Reason:
There are 2 database links in the database with same name.

SQL> select * from dba_db_links where db_link='EDW_APPS_TO_WH.WORLD';

APPS       EDW_APPS_TO_WH.WORLD    APPS    TEST   24-JUL-12
APPS       EDW_APPS_TO_WH.WORLD    APPS    TEST   25-JUL-12


Solution:
Drop the database links from SYS.LINK$ table using below command:

SQL> delete from sys.link$
 2  where NAME='EDW_APPS_TO_WH.WORLD';