java.lang.NoClassDefFoundError: javax/jms/Connection

Anand called me today and informed me that Workflow Notification Mailer was refusing to start. The logs showed this error:

java.lang.NoClassDefFoundError: javax/jms/Connection

at oracle.apps.fnd.wf.bes.ConnectionManager$1.run(ConnectionManager.java:112)

at oracle.apps.fnd.wf.bes.Utilities$1.run(Utilities.java:558)

at java.lang.Thread.run(Thread.java:479)

We found Metalink Note 38266.1 which says that AF_CLASSPATH needs to have appsborg2.zip and not appsborg.zip. Anand checked this variable and found that appsborg.zip was present. He corrected this variable in the context file and added it in adovars.env. After this he restarted the services. Mailer came up without errors on restarting

X: symbol lookup error: X: undefined symbol: BuiltinRegisterFpeFunctions

Ever since 11g was certified with 11i, I have wanted to do a test install in a lab environment. After a flurry of upgrades to 10g, I now have a little free time. First I downloaded Oracle Enterprise Linux 5 from edelivery.oracle.com. We followed metalink note 316806.1 to determine the pre-requisites for this OS. They were given as:

Oracle Enterprise Linux 5*

Red Hat Enterprise Linux 5* (base and Advanced Platform)

Update 1 or higher of Oracle Enterprise Linux (OEL) 5/Red Hat Enterprise Linux (RHEL) 5 is required.

The following packages are not part of the OEL 5 or RHEL 5 distribution media and must be installed manually:

• compat-libstdc++-egcs-1.1.2-1¹
• compat-libcwait-2.1-1¹
• compat-oracle-el5-1.0-5¹
• openmotif21-2.1.30-11.EL5¹
• binutils-2.15²

The following packages must be installed from the OEL 5 or RHEL 5 distribution media:

• xorg-x11-deprecated-libs-devel-6.8.2-1.EL.13.37
• xorg-x11-deprecated-libs-6.8.2-1.EL.13.37
• xorg-x11-devel-6.8.2-1.EL.13.36
• xorg-x11-libs-6.8.2-1.EL.13.36
• compat-libgcc-296-2.96-138
• compat-libstdc++-33-3.2.3-61
• compat-db-4.2.52-5.1
  

Note :

¹: Download from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/ (for both OEL 5 and RHEL 5)
²: GNU linker (ld) version 2.15 is required for relinking the modules in Advanced Planning & Scheduling (MSC, MSO, MSR) - download binutils-2.15 from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/ (for both OEL 5 and RHEL 5)

We had great trouble locating these rpms:

• xorg-x11-deprecated-libs-devel-6.8.2-1.EL.13.37
• xorg-x11-deprecated-libs-6.8.2-1.EL.13.37
• xorg-x11-devel-6.8.2-1.EL.13.36
• xorg-x11-libs-6.8.2-1.EL.13.36

The above rpms are not present in Oracle Enterprise Linux 5 DVD. Finally we found them in Oracle Enterprise Linux 4 CDs. However there were numerous errors about dependencies and conflicts. Since these are deprecated, we decided to skip these libraries.

After booting, X would not come up. It was showing the above error.

I tried to retract my steps and found that I had installed this package:
xorg-x11-libs-6.8.2-1.EL.13.36

I removed this package:

[root@rel12 ~]# rpm -q xorg-x11-libs
xorg-x11-libs-6.8.2-1.EL.33.0.1
[root@rel12 ~]# rpm -e xorg-x11-libs-6.8.2-1.EL.33.0.1

After this I tested by typing startx in text mode in a putty session.

[root@rel12 ~]# startx
xauth: creating new authority file /root/.serverauth.15918


X Window System Version 7.1.1
Release Date: 12 May 2006
X Protocol Version 11, Revision 0, Release 7.1.1
Build Operating System: Linux 2.6.22.14-72.fc6 i686 Red Hat, Inc.
Current Operating System: Linux rel12.ps.ge.com 2.6.18-92.el5 #1 SMP Fri May 23 22:17:30 EDT 2008 i686
Build Date: 23 May 2008
Build ID: xorg-x11-server 1.1.1-48.41.0.1.el5
Before reporting problems, check http://wiki.x.org
to make sure that you have the latest version.
Module Loader present
Markers: (--) probed, (**) from config file, (==) default setting,
(++) from command line, (!!) notice, (II) informational,
(WW) warning, (EE) error, (NI) not implemented, (??) unknown.
(==) Log file: "/var/log/Xorg.0.log", Time: Wed Jul 30 15:23:37 2008
(==) Using config file: "/etc/X11/xorg.conf"

Backtrace:
0: X(xf86SigHandler+0x81) [0x80bee71]
1: [0x257420]
2: X(IgnoreClient+0x22) [0x81a39c2]
3: X(ClientSleep+0x39) [0x808cb89]
4: X [0x8089e18]
5: X(OpenFont+0x197) [0x808a0c7]
6: X(SetDefaultFont+0x57) [0x808a127]
7: X(main+0x3ae) [0x806f9de]
8: /lib/libc.so.6(__libc_start_main+0xdc) [0x713dec]
9: X(FontFileCompleteXLFD+0x1ed) [0x806edb1]

Fatal server error:
Caught signal 11. Server aborting

XIO: fatal IO error 104 (Connection reset by peer) on X server ":0.0"
after 0 requests (0 known processed) with 0 events remaining.


Eventhough the above errors appeared, the previous error about undefined symbol had disappeared. I rebooted the server. X came up normally without issues.

XP desktop is sideways / upside down

Vamsi pressed some keys on his laptop and the desktop started showing sideways. After googling for a solution, we found this link. The solution is to

Press Ctrl + Alt + up arrow key

Keep in mind that you should be logged in and not on the username password dialog when you do this.

How to load an ISO image without a drive

Previously I was using a utility called daemon-tools, which was freeware. However daemon-tools now has toolbar and in older versions some extra bloatware. I found this post which gives the steps to use Microsoft Virtual CD software:

Once you download winxpvirtualcdcontrolpanel_21.exe and execute it will create 3 files:

readme.txt

VCdControlTool.exe

VCdRom.sys

Open the program VCdControlTool.exe:

Follow these steps to get your file mounted.

1.) Click Driver Control
2.) Click Install Driver
3.) [it should open to the folder where the files are, if it doesn’t find the folder you extracted the files to] Select VCdRom.sys and hit open.
4.) Hit Start below Install Driver [which should be grey now].
5.) Hit OK
6.) Hit Add Drive
7.) Hit Mount
8.) Find the file you are going to mount [.ISO or other].
9.) Select the file, hit open, hit ok.
10.) If if mounted hit ok, if not eject and try again.
11.) Open My Computer, the drive should be Z, right click to explore and find the setup.exe file [might be another file].

I have never had the cd auto start so you will have to do the last step most likely. This is a little bit of work but it’s worth passing the spyware and needing to reboot.

This is handy for loading an iso image if you don't have a DVD-ROM drive or even a blu-ray drive.

The limitation of this utility are that it works with .iso files only.  I discovered another alternative called VirtualCloneDrive from slysoft which is free of cost.  You can download it from here.  It works with the following file types:

.dvd

.ccd

.img

.iso

.udf

.bin

Shutting down manager due to excessive heap growth

Anand informed us today about an error message in concurrent manager logs:

w1234567.mgr:Shutting down manager due to excessive heap growth:
w1234568.mgr: Orginal Top of Heap: 5708512
w1234679.mgr: Current Top of Heap: 35748576

Excessive heap growth is a common reason for a Java application performance problem.
Heap growth may be caused by your application having a memory leak or of the garbage
collection not doing its job. Memory leaks in the Java language are a significant contributor to
garbage collection bottlenecks. If you do not manage garbage collection, it can have a
significant negative impact on application performance, especially when running on
symmetric multiprocessing (SMP) server machines. The Java Virtual Machine (JVM)
uses concurrent (asynchronous) garbage collection. This type of garbage collection results in
shorter pause times and enables application threads to continue processing requests during
the garbage collection cycle.
Some symptoms of heap growth include:

• Poorly performing application that may have a leak
• Heavy page faulting in the storage pool where your WebSphere application runs (Java does not tolerate heavy paging)
  

You can use several tools to evaluate a possible heap growth problem:

• The Dump Java Virtual Machine (DMPJVM) command
• Start Service Tools (SST)
• Work with System Status (WRKSYSSTS) to investigate faulting and the wait-to-ineligible transitions.
• IBM's iDoctor for iSeries Heap Analyzer

This could be another post ATG RUP6 bug.

ORA-600 and ORA-7445 errors after upgrade to Apps 11.5.10.2 and DB 10.2.0.3

After our recent upgrade from 11.5.9/9.2.0.8/Solaris 8 to 11.5.10.2/10.2.0.3/Solaris 10, we have regular occurances of ORA-600 and ORA-7445 errors:

ORA-600 errors:

ORA-00600: internal error code, arguments: [12209], [111], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [139], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [153], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [154], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [156], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [157], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [158], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [162], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [172], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [190], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [193], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [197], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [198], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [217], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [219], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [230], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [292], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [497], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [498], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [71], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [12209], [95], [], [], [], [], [], []

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

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

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

ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [17281], [1001], [0x3F6A0D310], [], [], [], [], []

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

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [64], [64], [65], [], [], [], []

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [7], [0], [8], [], [], [], []

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

ORA-00600: internal error code, arguments: [kglhdunp2_2], [0x437813380], [37], [0x47B749B28], [0x437813470], [0x437A1C828], [1000], [2]

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

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

ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []

ORA-7445 errors:

ORA-07445: exception encountered: core dump [peplmufc_Unregister_From_Cleanup()+152] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF713EB1D8] [] []

ORA-07445: exception encountered: core dump [peplmufc_Unregister_From_Cleanup()+152] [SIGSEGV] [Invalid permissions for mapped object] [0xFFFFFFFF56D9ABB8] [] []

ORA-07445: exception encountered: core dump [rnmobj1()+3320] [SIGSEGV] [Address not mapped to object] [0x000000134] [] []

ORA-07445: exception encountered: core dump [sdfind_rtn_hdr()+908] [SIGBUS] [Object specific hardware error] [0xFFFFFFFF6240A03C] [] []

Some of these errors are causing business critical problems. We have severity 1 SRs open with Oracle to solve these issues. Will update once I learn more.

We disabled automatic SGA on Oracle's suggestion and the errors have disappeared. It is described in more detail in this post.

Relaying denied

In one of our environments, the mail server was changed. Previously the SMTP server was configured as the Exchange Server. It was changed to Sendmail. However sending mail to any external domain was giving this error:

Relaying denied

A google search revealed a page about Controlling SMTP Relaying. However, none of the steps applied to us. Mail was working fine from other servers created with the very same OS image.

A simple smtp commandline test is to

First connect to the Apache/Forms tier with telnet

telnet apache.justanexample.com

Once you are logged in

telnet sendmail.justanexample.com 25

MAIL FROM:vikramdas@justanexample.com
RCPT TO:mailuser@justanexample.com

Relaying Denied.

However if I connected to a different server instead of connecting to apache.justanexample.com:

telnet qaserver.justanexample.com

telnet sendmail.justanexample.com 25

MAIL FROM:vikramdas@justanexample.com
RCPT TO:mailuser@justanexample.com

Recipient OK.

I am still investigating what is causing Relaying Denied error. As a workaround, we changed the configuration and pointed SMTP server field in workflow mailer configuration to a different sendmail server. We verified in the same way as described above. This worked. However I am still trying to find out what was causing the Relaying denied message from the previous server. I'll update this post once I find out.

Forbidden HTTP-403 in Apache access_log

This morning, I got a call from the DBAs. A new Apache/Forms node was added to an instance. During sanity checks, whenever this node was accessed through this navigation:

System Administration -> PL/SQL ping

the browser showed HTTP-403 and the apache access log showed:

192.168.7.81 - - [20/Jul/2008:01:50:53 -0400] "GET /pls/apps11i/OracleSSWA.Execu
te?E=%7B!38FC0AD8B864E929E00AF2CBA3052B695A3C266C98FB2A33&P= HTTP/1.1" 403 241

192.168.7.81 - - [20/Jul/2008:02:44:18 -0400] "GET /pls/apps11i/fnd_icx_launch.l
aunch?resp_app=FND&resp_key=PS_EOPS_DBA&secgrp_key=STANDARD&start_func=FND_FNDRS
RUN&other_params= HTTP/1.1" 403 244

I checked /etc/resolv.conf and found that the domain was different from the other nodes:

cat /etc/resolv.conf
domain prod.justanexample.com

In other nodes it was cat /etc/resolv.conf
domain justanexample.com

What this means is that whenver the server is trying to identify itself it identifies itself with the new domain name instead of the old one. After adding the line

Allow apps11i.prod.justanexample.com

in httpd_pls.conf and bouncing Apache, the PL/SQL ping succeeded and the error HTTP-403 did not appear.

I asked the DBAs to add the line
Allow apps11i.prod.justanexample.com

in these four .conf files in $IAS_ORACLE_HOME/Apache/Apache/conf/ :

httpd_pls.conf
apps.conf
oprocmgr.conf
trusted.conf

In shared APPL_TOP instances:

cd $IAS_CONFIG_HOME/Apache/Apache/conf

grep apps11i.prod.justanexample.com *.conf  

The above grep command should return these 4 files:

httpd_pls.conf
apps.conf
oprocmgr.conf
trusted.conf

Once this is in place, if you bounce apache and forms, it will start working.

ksh: vncserver not found

On starting vncserver the following error appeared:

ksh: vncserver not found

which vncserver returned:
/usr/local/bin/vncserver

Even starting it with the fully qualified path gave the same not found error

Since vncserver is a perl script, I even tried:

perl /usr/local/bin/vncserver

Same error.

Finally I opened the vncserver file in vi and saw that the first line read:
#!/usr/local/bin/perl

However perl is installed in /usr/bin/perl

$ which perl
/usr/bin/perl
$

So I edited the path in the first line of the vncserver file
from /usr/local/bin/perl
to /usr/bin/perl

After this vncserver started without issues

_XSERVTransSocketCreateListener: failed to bind listener

On a newly built Solaris 10 server whenever vncserver was started it errored out with these errors:

_XSERVTransSocketCreateListener: failed to bind listener
_XSERVTransSocketUNIXCreateListener:
...SocketCreateListener() failed
_XSERVTransMakeAllCOTSServerListeners: failed to createlistener for local

Fatal server error:
Failed to establish all listening sockets

I checked the permissions of vncserver file and /tmp/.X11-unix/

$ ls -ld /tmp/.X11-unix/
drwxrwxr_x 2 root root 235 Jul 19 19:59 /tmp/.X11-unix/
$

The others group doesn't have permission

# chmod 777 /tmp/.X11-unix/
$ ls -ld /tmp/.X11-unix/
drwxrwxrwx 2 root root 235 Jul 19 19:59 /tmp/.X11-unix/

That fixed the issue.

Correct permissions on /var/tmp

/var/tmp is the default Solaris swap mount. If you notice it has an extra t in its permissions:

$ ls -ld /var/tmp
drwxrwxrwt 16 root sys 8192 Jul 17 17:54 /var/tmp
$

If you ever see /var/tmp without this t

drwxrwxrw 16 root sys 8192 Jul 17 17:54 /var/tmp

These are the permissions and ownership /var/tmp should have:

sudo chown root:root /var/tmp
sudo chmod 1777 /var/tmp

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []

After the upgrade from 11.5.9 / 9.2.0.8 to 11.5.10.2/10.2.0.3 we are getting errors in DB alert logs:

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []

A search through ORA-600 lookup tool turns up this note:

Subject:	ORA-600 [kcbz_check_objd_typ_3]
	Doc ID:	Note:406787.1	Type:	REFERENCE
	Last Revision Date:	10-JUL-2008	Status:	PUBLISHED

Note: For additional ORA-600 related information please read Note 146580.1

PURPOSE:
 This article represents a partially published OERI note.

 It has been published because the ORA-600 error has been
 reported in at least one confirmed bug.

 Therefore, the SUGGESTIONS section of this article may help
 in terms of identifying the cause of the error.

 This specific ORA-600 error may be considered for full publication
 at a later date. If/when fully published, additional information
 will be available here on the nature of this error.

SUGGESTIONS:

 If the Known Issues section below does not help in terms of identifying
 a solution, please submit the trace files and alert.log to Oracle
 Support Services for further analysis.

 Known Issues:
 Bug# 6405339   See Note 6405339.8
     Corruption / OERI[kcbz_check_objd_typ_3] / OERI:5400 from PQ
     Fixed: 10.2.0.5, 11.1.0.7, 11.2

 Bug# 5689412   See Note 5689412.8
     ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery
     Fixed:

 Bug# 4592596   See Note 4592596.8
     Corruption (ORA-1410) from multi-table insert with direct load
     Fixed: 10.2.0.4, 11.1.0.6

 Bug# 4430244 +  See Note 4430244.8
     Segment advisor can load blocks of dropped objects into buffer cache (KCB OERI errors)
     Fixed: 10.2.0.4, 11.1.0.6

Metalink Note 466049.1 describes summary of Bugs Containing ORA - 00600[kcbz_check_objd_typ_3]

Purpose

This article is intended to

1. Help the reader to understand ORA-600 [kcbz_check_objd_typ_3] error and how is this caused.

2. List the most common bugs of the aforesaid error and their fixes.

Scope

This is a consolidated article based upon various bugs logged for the error
ORA-600 [kcbz_check_objd_typ_3] and gives the reader an overview about the most frequent bugs which the customers are affected with.

The error ORA-600 [kcbz_check_objd_typ_3] is raised because we see different object id in bufffer cache (BH) and on disk .

Summary of Bugs Containing ORA - 00600[kcbz_check_objd_typ_3]

Bug 5466416---Closed as Duplicate of Unpublished Bug 4430244

Abstract: ORA-600 [KCBZ_CHECK_OBJD_TYP_3] ON INSERT VIA DBMS_STATS OR DBMS_SPACE


Versions affected : 10.2

Fixed releases : 10.2.0.4, 11.0.0.0

Details : Segment advisor code (eg: DBMS_SPACE.OBJECT_GROWTH_TREND)

can load blocks into the cache for Dropped objects as CURRENT leading

to subsequent operations seeing an incorrect (old) version of a block.

This can lead to various internal buffer cache related errors such as

ORA-600 [kcbnew_3] / ORA-600 [kcbz_check_objd_typ_3].

The exact error depends on which code sees the block.

Backportable : yes to 10.2

Symptoms :

1. ORA-600 [kcbnew_3] internal error reported in the alert.log

2. May occur if segment advisor and DROP operation are being run
concurrently on the same object.

Workaround :

sql>alter system flush buffer cache' to flush the buffer in the cache.

Patch Details:

Check Metalink for Patch 4430244 availability.





Bug 5859511 -----> Closed as Duplicate of Unpublished Bug 4592596

Abstract: INTERMITTENT ORA-600 [KCBZ_CHECK_OBJD_TYP_3] FROM PARALLEL SLAVES

Fixed releases : 10.2.0.4, 11.0.0.0

Details:
Corruption can occur using a multi-table insert SQL with
direct load operations. Eg: If the SQL goes parallel.
This can result in subsequent ORA-1410 type errors on selects
from the target table/s.

Symptoms :

1.Select on table fails with ORA-600 [kcbz_check_objd_typ_3] after multi-table insert with direct load was done.

2.Analyze on the table fails with ORA-1410

3.Call Stack :- kcbassertbd3 kcbz_check_objd_typ kcbzib kcbgtcr ktrget kdst_fetch kdstf0010101km
kdsttgr qertbFetch qergiFetch qertqoFetch



Workaround: -
Do not use direct path (APPEND or PQ) in a multi-table insert SQL

Check Metalink for Patch 4592596 availability.

Unpublished Bug 5754708 ----> Closed as Duplicate of Unpublished Bug 4996133

Abstract: ORA-600 [KCBZ_CHECK_OBJD_TYP_3] FROM "SYS.DBMS_STATS"


Symptoms : -

1. Running in RAC mode
2. Reading the overflow segment of an IOT
3. Call Stack :- kdsgnp kafger qerixGetNonKeyCol qerixFetchByLogicalRowid
Or
Call Stack :- kcbassertbd3 kcbz_check_objd_typ kcbzib kcbgtcr ktrget kdsgrp kdsfbr qertbFetchByRowID


Workaround:
Flush the buffer cache (10g onwards)
or
Restart the instance to clear its buffer cache.

Check Metalink for Patch 4996133 availability.





Bug 5348204

Abstract: ORA-600 [KCBZ_CHECK_OBJD_TYP_3] IMPORTING TRANSPORTABLE TABLESPACE FROM 10.1


Fixed In Ver: 10.2.0.1

Symptoms :-

1.Cross Platform tablespace Import using Transportable tablespace option fails with following error message

ORA-39083: Object type TABLE failed to create with error
ORA-600: internal error code, arguments: [kcbz_check_objd_type_3]

2.Call Stack :- kcbz_check_objd_typ kcbzib kcbgtcr ktecgsc ktecgetsh ktecgshx ktsscd_segment ktsscf_segment kdicpsc kdicrws

I am still investigating this one. Will update once I get more information.

Fndfxwho.Sql Fails during TXK or ATG patches

Recently while applying TXK Rollup S and ATG patches, one of the workers running fndfxwho.sql failed:

FAILED: file fndfxwho.sql on worker 8 for product fnd username APPLSYS.

adwork008.log showed:

Start time for file is: Wed Jul 09 2008 17:38:27

sqlplus -s APPS/***** @/gpscd140/erpapp/appl/fnd/11.5.0/patch/115/sql/fndfxwho.s
ql NONE
DECLARE
*
ERROR at line 1:
ORA-20002: [WF_NO_USER] NAME=OPERATOR2 ORIG_SYSTEM=NULL ORIG_SYSTEM_ID=NULL
ORA-06512: at "APPS.WF_LOCAL_SYNCH", line 1890
ORA-06512: at line 154

Metalink Note 372651.1 advises skipping the failed worker and applying ATG RUP5 for the fix. However this error repeated itself when ATG RUP5 was being applied. Metalink Note 374105.1 advises applying ATG RUP6:

Symptoms

On 11.5.10.2, when attempting to apply Patch 4334965 ,
the following error occurs.

ERROR
Fndfxwho.sql fails when installing Patch 4334965

sqlplus -s APPS/*****
@$FND_TOP/11.5.0/patch/115/sql/fndfxwho.sql &un_fnd &pw_fnd
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 36

Cause

In the current SQL statement that is executed a supplementary condition is needed:

Current statement:

select created_by, creation_date, last_updated_by, last_update_date
into createdby, creationdate, lastupdatedby,lastupdatedate
from fnd_user_resp_groups_old
where user_id = who_rec.user_id
and responsibility_id = who_rec.responsibility_id
and responsibility_application_id = who_rec.responsibility_application_id;

New condition:

select created_by, creation_date, last_updated_by, last_update_date
into createdby, creationdate, lastupdatedby,lastupdatedate
from fnd_user_resp_groups_old
where user_id = who_rec.user_id
and responsibility_id = who_rec.responsibility_id
and responsibility_application_id = who_rec.responsibility_application_id
and security_group_id = who_rec.security_group_id;


By adding the supplementary condtition the SQL statement will return just one row, as required by
the business model.

This fix is available in $Header: fndfxwho.sql 115.3 delivered in 11i.ATG_PF.H.delta.6
.

Solution

To implement the solution, please execute the following steps:

1. Obtain 11i.ATG_PF.H.delta.6
2. Review the pre-reqs for this patch and apply to test system

autoconfig fails on adgendbc.sh with ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:


[SETUP PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: $COMMON_TOP/admin/install/$TWO_TASK
adgendbc.sh INSTE8_SETUP 1


AutoConfig is exiting with status 1

When we tried running adgendbc.sh manually, these errors occurred:

Error occurred during initialization of VM
Could not reserve enough space for object heap
UPDATE call failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
Error occurred during initialization of VM
Could not reserve enough space for object heap
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
Error occurred during initialization of VM
Could not reserve enough space for object heap
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Error occurred during initialization of VM
Could not reserve enough space for object heap
DBC generation failed with exit code 1

Based on Metalink Note 394550.1:

Cause

The issue is caused java caching feature enabled.

The following parameter was true in jserv.properties

wrapper.bin.parameters=-DLONG_RUNNING_JVM=true



.

Solution

To implement the solution, please execute the following steps:

1.Take a backup of jserv.properties.
2.Edit the value DLONG_RUNNING_JVM to false.
3.The following should be the change...
wrapper.bin.parameters=-DLONG_RUNNING_JVM=true
to
wrapper.bin.parameters=-DLONG_RUNNING_JVM=false

I modifed jserv.properties and re-ran adgendbc.sh. New errors came up this time:

adgendbc.sh started at Fri Jul 11 14:18:47 EDT 2008


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Fri Jul 11 14:18:54 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : $CONTEXT_NAME
Old Value : $CONTEXT_NAME

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Producti on
With the Partitioning option
JServer Release 9.2.0.5.0 - Production


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
Application server ID already exists for this host - loading..
ADD executed successfully - $FND_SECURE/$CONTEXT_NAME.dbc

Trying to update information ...

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

UPDATE call failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating $FND_SECURE/$TWO_TASK.domainname_$TWO_TASK.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating $FND_SECURE/$CONTEXT_NAME.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Updating Server Security Authentication
java.sql.SQLException: Io exception: Invalid number format for port number
Database connection to jdbc:oracle:thin:@host_name:port_number:database failed
Updating Server Security Authentication failed with exit code 1
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END

We saw various metalink notes which pointed to similar errors. We also upgraded to TXK Rollup S to get rid of the error, but to no avail. The common error during the run of adgendbc.sh was:

APPS.FND_APP_SERVER_PKG", line 357

So I decided to check what is present in line 357:

SQL> select line,text
2 from dba_source
3 where line between 357 and 370
4 and name='FND_APP_SERVER_PKG';

LINE
----------
TEXT
--------------------------------------------------------------------------------
357
select SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN,

358
SUPPORT_DB, PLATFORM_CODE

359
into l_support_cp, l_support_forms, l_support_web, l_support_admin,


LINE
----------
TEXT
--------------------------------------------------------------------------------
360
l_support_db, l_platform

361
from fnd_nodes

362
where server_address = p_address;


Based on the above query I checked for the value of server_address in fnd_nodes and found that two node had the same IP address as per fnd_nodes:

SQL> select node_name,server_address
2 from fnd_nodes;

NODE_NAME SERVER_ADDRESS
------------------------------ ------------------------------
PHYSICALDB 192.168.70.29
VIRTUALDB 192.168.70.29
AUTHENTICATION *
APPTIER 192.168.70.69

I pinged each server and found that the correct IP address of Physical DB was different. So I changed it immediately.

SQL> update fnd_nodes
2 set server_address='192.168.70.31'
3 where node_name='PHYSICALDB';

1 row updated.

SQL> commit;

Commit complete.

After this adgendbc.sh ran without errors.

FNDLIBR segmentation fault on RHEL3 after upgrade to 11.5.10.2

Owen pinged me today. After doing an upgrade on an RHEL3 box, the concurrent managers refused to start up. This was the error logged in the concurrent manager logs:

Starting RCVOLTM Concurrent Manager : 10-JUL-2008 21:11:51
sh: line 71: 19678 Segmentation fault FNDLIBR FND CPMGR "FNDCPMBR sysmgr=\"\" $maxreq $sleep $pmon $quesiz $diag logfile=$logfile $target" STOP
$sysmanager
STOP

Metalink Note 343249.1 describes the same issue. However we were already on 11.5.10.2. And the patches suggested were already present. I searched some more and found Metalink Note 264950.1, which suggests applying 806 interop patch on RHEL boxes:

When GSM is not enabled the concurrent managers start up without issue, if GSM is enabled
and the concurrent managers are started I see the following in the log file:
.
Starting STANDARD Concurrent Manager : 15-DEC-2003 19:28:10
sh: line 71: 7884 Segmentation fault (core dumped) FNDLIBR FND CPMGR
"FNDCPMBR sysmgr=\"\" $maxreq $sleep $pmon $quesiz $diag logfile=$logfile
$target"

Fix

Apply patch 3293983, 8.0.6.3 RHEL3.0 APPLICATIONS INTEROPERABILITY PATCH

3293983 was obsoleted by 3830807. So I asked Owen to apply this patch. However he had already gone through this note and applied this patch. He said that applying the patch had no effect. I had a gut feeling that this was the root cause. So I opened the patch readme of 3293983:

#  You must login to your Linux machine as the Oracle software owner before
#  applying this patch. Your setting for ORACLE_HOME must be the value for the
#  Forms 6i 8.0.6 based ORACLE_HOME and you must have write previlages to this
#  area.
#
#  [1] To apply the patch, unzip the PSE container file:
#        % unzip p3830807_8063_LINUX.zip
#
#  [2] Set your current directory to the directory where the patch
#      is located, eg:
#
#        % cd 3830807
#
#  [3] Add execute permission to the script supplied in this patch
#
#        % chmod u+x patch.sh
#
#  [4] Check the value of LD_LIBRARY_PATH.
#
#      This patch will relink reports60 binaries.
#
#      Reports has both link-time and run-time dependency with libjava.so
#      so you need to include
#         $ORACLE_HOME/network/jre11/lib/linux/native_threads    OR
#         $ORACLE_HOME/network/jre11/lib/i686/native_threads
#      in $LD_LIBRARY_PATH before running patch.sh
#
#      Please check your files under $ORACLE_HOME/network/jre11/lib to see
#      which one of the above is appropriate on your system. The same
#      $LD_LIBRARY_PATH should be used at run-time.
#
#  [5] Run the script supplied by this patch
#
#        % ./patch.sh
#
#  [6] Relink all Applications executables. In AD Administration, choose
#      "Relink Applications programs" from the "Generate Applications Files"
#      menu.
#
#  The installation of the patch is now complete.

I asked owen whether he had relinked all Apps executables, and he said that he had not read the readme of the patch. On realizing the missed step, he relinked all executables through adadmin and restarted the concurrent managers. All the concurrent managers started without issue. The problem was resolved.

FAILED: file icxwtab.odf during adpatch

Recently while patching, the worker running icxwtab.odf failed:

ATTENTION: All workers either have failed or are waiting:

FAILED: file icxwtab.odf on worker 1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

adworker log showed:

Start time for statement below is: Wed Jul 09 2008 17:12:20

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.

AD Worker error:
The index cannot be created as the table has duplicate keys.


Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys because of the error above

As specified in Metalink Note 430673.1:

Symptoms

adpatch fails on script icxwtab.odf with the following errors:

ERROR
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Mon May 07 2007 14:23:44

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.
AD Worker error:
The index cannot be created as the table has duplicate keys.

Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above

SPECIFIC DATA
Ran the suggested query, and here is the output:

TRANSACTION_ID COUNT(*)
-------------------------- -------------
148341124 2
431640607 2
555224577 2
1202811809 2

Cause

These duplicate transactions are there because the concurrent program that deletes temporary
session data (program that removes old entries in ICX_SESSIONS and ICX_TRANSACTIONS) is not
executed on a regular basis. As a result, these tables grow in space and there is the possibility
that the sequences cycle and restart, creating duplicate primary keys.

The following justifies how the issue is related to this specific customer:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

TRANSACTION_ID COUNT(*)
-------------------------- --------------
148341124 2
431640607 2
555224577 2
1202811809 2

This is explained in the following unpublished bug: Bug 5001287 PERFORMANCE PROBLEM WHEN APPROVING POS WITH ICX_TRANSACTIONS

Solution

To implement the solution, please execute the following steps:

1. Run the purge program:
a. The name of the program is "Purge Inactive Sessions" located under the "Apps for the Web Manager" responsibility.
b. The internal name is ICXDLTMP.
c. Also you can find this SQL script under $ICX_TOP/sql (named ICXDLTMP.sql).

2. Rerun the failed worker (icxwtab.odf).

3. Migrate the solution as appropriate to other environments.

4. This program should be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION tables, otherwise they will continue to grow.

Running this sql returned two rows:

SQL> SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
2 3 4 5
SQL> /

TRANSACTION_ID COUNT(*)
-------------- ----------
746007924 2

SQL> desc icx_transactions
Name Null? Type
----------------------------------------- -------- ----------------------------
TRANSACTION_ID NOT NULL NUMBER
SESSION_ID NOT NULL NUMBER
RESPONSIBILITY_APPLICATION_ID NUMBER
RESPONSIBILITY_ID NUMBER
SECURITY_GROUP_ID NUMBER
MENU_ID NUMBER


1 select rowid,transaction_id,session_id
2 from icx_transactions
3* where transaction_id='746007924'
SQL> /

ROWID TRANSACTION_ID SESSION_ID
------------------ -------------- ----------
AAAaZsAGeAAAIsjAAU 746007924 499638533
AAAaZsAAbAAAHb4AAV 746007924 888513258

SQL> delete icx_transactions
2 where rowid='AAAaZsAGeAAAIsjAAU'
3 /

1 row deleted.

SQL> commit;

Commit complete.

Failed worker was restarted through adctrl and it went fine.

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.