StarOffice 8 and E-Business Suite

We are currently evaluating whether Microsoft Office can be replaced with StarOffice 8. For this one of the steps is to define how E-Business Suite will be affected. The first thing which comes to mind is export to excel functionality. E-Business Suite has this functionality in XML publisher, Discoverer and Web ADI. I have logged an SR to find out whether Oracle certifies this officially. Will update more when I find out.

DB Sesions from JVM are very very high

For past few months, there was an issue of too much swapping on a shared box in our environment. After the high SGAs were size down, the problem was still at large. One of the experts monitored and saw that JVM sessions were very high on OS level. He inquired about any known issues after RUP6. Metalink Note 459353.1 which describes post ATG RUP 5/6 issues, shows a bug 6841295 which has a similar behavior. It says:

After RUP6 Patch 5903765 is applied there are a large number of idle JDBC connections owned by the APPLSYSPUB user. These connections build up and cause performance issues.

AppsLocalLogin.jsp is leaking connections when the initial login is not successful.

Note: Although the title of the bug and patch suggests that this issue appears after RUP6 + JDK 1.6 the problem will manifest after RUP6 and does not require JDK 1.6 to be triggered

There are lots of idle database connections coming from the APPLSYSPUB user.

Use this script to identify if you are experiencing this problem:
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%';


These connection leaks are NOT reported in ""AOL/J Database connection pool status" page.

Last SQL executed shows :

BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;
or
BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;

The fix can be obtained via Patch 6841295 "DB SESSIONS FROM JVMS ARE VERY VERY HIGH AFTER ATG RUP6 + JDK 1.6 UPGRADE"

This patch is password protected - please contact Oracle Support

We executed the SQL:

SQL> select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%'; 2 3 4 5

SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
6712

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
363139


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140226

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472095


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
471800

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140221

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140219


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1133010

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472089

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472094


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140216

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472082


16 rows selected.

That matches roughly with:

$ top -b 5000 -s size | grep applmgr
25809 applmgr 73 59 0 1140M 356M sleep 39:01 0.00% java
15528 applmgr 65 59 0 361M 187M sleep 28:49 0.00% java
15548 applmgr 62 59 0 1051M 275M sleep 22:02 0.00% java
15770 applmgr 62 59 0 1051M 269M sleep 21:59 0.00% java
15538 applmgr 62 59 0 1051M 283M sleep 21:23 0.00% java
19372 applmgr 62 59 0 1136M 345M sleep 12:28 0.00% java
19435 applmgr 62 59 0 1140M 338M sleep 11:53 0.00% java
19409 applmgr 62 59 0 1112M 306M sleep 9:39 0.00% java
19395 applmgr 62 59 0 1111M 315M sleep 9:31 0.00% java
19399 applmgr 62 59 0 1111M 313M sleep 9:29 0.00% java
19414 applmgr 62 59 0 1111M 304M sleep 9:11 0.00% java
19421 applmgr 60 59 0 1144M 343M sleep 8:59 0.00% java
19452 applmgr 61 59 0 1114M 307M sleep 8:55 0.00% java
19384 applmgr 59 59 0 1114M 307M sleep 8:40 0.00% java

Strangely this instance was not on RUP6 but on RUP4. Maybe this issue occurs on RUP4 too. Since this patch is password protected, I have logged an SR with Oracle to get the password. We'll be applying this in one of the affected instances and check if it solves the issue. Will update this post once it is done.

ARHLSTG1.SQL and recyclebin

During the application of a merged functional patch which had FIN_PF.G, PJ_PF.M and 300 other patches, worker running arhlstg1.sql failed:

FAILED: file arhlstg1.sql on worker 4.

Examining the worker log showed this error:

Start time for file is: Thu Jul 03 2008 16:33:09

sqlplus -s APPS/***** @$AR_TOP/patch/115/sql/arhlstg1.sql &un_ar
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at line 306

A search on metalink revealed bug 6762542, which advises dropping the tables created by arhlstg1.sql

Instead of dropping the tables, we first dropped the synonyms in APPS with the same name as the tables:

DROP SYNONYM APPS.HZ_IMP_PARTIES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSES_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTPTS_SG;
DROP SYNONYM APPS.HZ_IMP_CREDITRTNGS_SG;
DROP SYNONYM APPS.HZ_IMP_CLASSIFICS_SG;
DROP SYNONYM APPS.HZ_IMP_FINREPORTS_SG;
DROP SYNONYM APPS.HZ_IMP_FINNUMBERS_SG;
DROP SYNONYM APPS.HZ_IMP_RELSHIPS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTROLES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSUSES_SG;

Restarted the worker but the error persisted. Then we checked if these tables had any data:

SELECT COUNT(*) FROM AR.HZ_IMP_PARTIES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTPTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CREDITRTNGS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CLASSIFICS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINREPORTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINNUMBERS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_RELSHIPS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTROLES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSUSES_SG

All of them returned zero rows except HZ_IMP_CONTACTPTS_SG

select count(*) from AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [20445], [6892510], [6901726], [], [], [], [], []

Trying to drop table AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

A seach through ORA-600 lookup tool in metalink showed up metalink note 456974.1 which advises:

Symptoms

You encounter this error while running a query against a partitioned table.:

ORA-00600: internal error code, arguments: [20445], [222112], [417107], [], [], [], [], []

If you search your trace file, you may find some of these codes.:

kkdl1ck kkdlack kkmfcbbt kkmfcblo kkmpfcb qcsprfro

Changes

This may be triggered by moving datafiles or making other changes to your database.

Cause

This is caused by unpublished Bug 3738431.

Solution

Unfortunately, there is currently no fix available for this bug.

However, the following workaround should resolve the issue.:

1. purge recyclebin;

2. Bounce the database and retry the statement again.

If above does not help, then try

3. Set init.ora parameter "_recyclebin" = false, bounce the database and drop
the table.

References

@ Bug 3738431 - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [20445], [43290] IN OBJECT EVOLUTION

So we gave the command
SQL> purge recyclebin;
SQL> shutdown immediate;
SQL> startup

Once the database came up, we could do a select coun(*) on the table without triggering ORA-600. So we just resumed the patch, which was able to successfully drop the tables without failing.

Metalink Note 265253.1 describes 10g recyclebin features and methods to disable it:

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.


There is no issues with DROPping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS objects

How to track if your DBUA or catupgrd.sql is progressing

We have standardized on DBUA for all DB upgrades of E-Business Suite instances. While DBUA was on the Upgrading Oracle Server stage, Anand asked me a question. How to know whether DBUA is proceeding well. The progress bar and the DBUA logs tell you only so much. So I checked the contents of $ORACLE_HOME/rdbms/admin/catupgrd.sql which is the upgrade script called by DBUA internally. Here's what it says:

NAME
catupgrd.sql - CATalog UPGraDe to the new release

DESCRIPTION
This script is to be used for upgrading an 8.1.7, 9.0.1, 9.2
or 10.1 database to the new release. This script provides a direct
upgrade path from these releases to the new Oracle release.

The upgrade is partitioned into the following 5 stages:
STAGE 1: call the "i" script for the oldest supported release:
This loads all tables that are necessary
to perform basic DDL commands for the new release
STAGE 2: call utlip.sql to invalidate PL/SQL objects
STAGE 3: Determine the original release and call the
c0x0x0x0.sql for the release. This performs all
necessary dictionary upgrade actions to bring the
database from the original release to new release.
STAGE 4: call the a0x0x0x0.sql for the original release:
This performs all necessary upgrade using
anonymous blocks.
STAGE 5: call cmpdbmig.sql
This calls the upgrade scripts for all of the
components that have been loaded into the database

NOTES

* This script needs to be run in the new release's environment
(after installing the release to which you want to upgrade).
* You must be connected AS SYSDBA to run this script.

This gave me an idea that catupgrd.sql is first invalidating all objects through utlip.sql. Once the catalog is created some objects are recreated. So we can check the count of invalid objects to see if DBUA is proceeding well.

select count(*) from dba_objects
where status='INVALID';

During the Upgrading Oracle Server stage, if you run the above query periodically, you'll notice that the number goes on increasing. This is a good indicator that DBUA is proceeding well. Later on the number will decrease when catalog is created. The number will again increase when the components like Intermedia and Spatial are getting upgraded. Finally during post upgrade step when utlrp.sql is called by DBUA, the number of invalids will start reducing.

ORA-12203 during 11.5.10.2 maintenance pack

Some of the workers were waiting indefinitely during an 11.5.10.2 upgrade as the DB connection could not be made. The worker error log showed:

Start time for file is: Wed Jul 02 2008 06:49:18

sqlplus -s APPS/***** @$PA_TOP/patch/115/sql/paupg007.sql &un_pa &batchsize 4 48
ERROR:
ORA-12203: TNS:unable to connect to destination


Some of the workers failed with the same error

HIDEPW: $FND_TOP/bin/FNDGFU APPS/TIB5C1US 0 Y PROGRAM_NAME=FND_HELP CONTENT_MAP=@FND:admin/import/fndgfu.txt PROGRAM_TAG
=OKS:10 LANGUAGE=US @OKS:help/US/oks11511084417.htm
$FND_TOP/bin/FNDGFU &ui_apps 0 Y PROGRAM_NAME=FND_HELP CONTENT_MAP=@FND:admin/import/fndgfu.txt PROGRAM_TAG=OKS:10 LANGU
AGE=US @OKS:help/US/oks11511084417.htm
APP-FND-01564: ORACLE error 12203 in AFPCOA

Cause: AFPCOA failed due to ORA-12203: TNS:unable to connect to destination.

The SQL statement being executed at the time of the error was: and was executed
from the file .

AD Worker error:
The above program failed with error code 1.
See the AD Worker log file and/or the program log file for details.


We stopped all workers. Bounced DB listener.

update fnd_install_processes
set status='W', control_code='W';

commit;

Restarted adpatch. It went fine after that.

We are still trying to find the root cause of this issue.

Slow running ontjup09.sql during 11.5.10.2 maintenance pack

ontjup09.sql is one of the slow running sqls during 11.5.10.2 upgrade. For this Oracle has given patch 4480056 in Metalink Note 331790.1. However this doesn't solve the issue completely. This is described in Bug 6673695. We faced the same issue during upgrade in an environment in which we merged 4480056 with 3480000 (maintenance pack patch). Even though the ontjup09.sql being called was the latest, the ontjup09.sql took more than 2 hours.

$ adident Header $ONT_TOP/patch/115/sql/ontjup09.sql
$ONT_TOP/patch/115/sql/ontjup09.sql:
$Header ontjup09.sql 115.0.11510.2 2005/07/28 19:45:08 jvicenti noship $

$ adident Header $ONT_TOP/patch/115/sql/ontjup07.sql
$ONT_TOP/patch/115/sql/ontjup07.sql:
$Header ontjup07.sql 115.4.11510.2 2005/07/21 00:54:30 jvicenti ship $

This could be due to the fact that oe_order_history table was 12 GB in size.

Makes me wonder why updates to history table can't be done in advance before the actual downtime. I am logging an SR with Oracle to find out if this can be done for future upgrades.

As indicated by one of the readers, this issue also occurs when you are upgrading to R12.  It has been apparently fixed in R12.0.6.  To people who are upgrading to a lower R12, you should download the SCM patch 7292303 and extract the ontjup07.sql from that patch.  Replace your ontjup07.sql with the one supplied with this patch.