JDE OCI Error ora-03120

[kk_jde]

All,

We are on XE, HP-UX, Oracle 10g with Citrix thin clients. We recently upgraded our Oracle database from 8i to 10g (10.2.0.3)...in order to accomodate this we also upgraded JDE service pack from SP22_G to SP23_W.

I have Oracle client 10g installed all the citrix servers. This is generated through the PD7333 where processing is all local... I made sure that JDE is using the 10g client and correct jdboci10.dll driver. However, I am still getting an intermittent error on jde.log reported, F41021 update failure and causing inventory to be left.

Here is an example of the error log, failure on next number table F00022.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++
1744/3624 Tue Nov 18 10:39:02 2008 dbperfrq.c386
OCI0000178 - Unable to execute - UPDATE PRODDTA.F00022 SET UKOBNM=:BND1,UKUKID=:BND2 WHERE ROWID = :LASTROWID

1744/3624 Tue Nov 18 10:39:02 2008 dbperfrq.c393
OCI0000179 - Error - ORA-03120: two-task conversion routine: integer overflow

1744/3624 Tue Nov 18 10:39:02 2008 jdb_drvm.c986
JDB9900401 - Failed to execute db request

1744/3624 Tue Nov 18 10:39:02 2008 jdb_exet.c2593
JDB3600011 - Failed to perform Update for F00022
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I dont think the number is that big...right now the next number for the cardex table is 63,245,306. Any ideas why?

Also the update to F41021 table is also failing with same error...Please help... Thanks,

Arvi

 

[cmanderson]

Did you or someone else upgrade the database from 8i to 10g? I'm asking because if it was you, perhaps you might have noticed something peculiar during the database upgrade. If not, you could ask to check the OraInstall logs on the database server. Was the same user id used for both 8.x and 10.x databases? If not, check the environment variables for the language environment - it is possible the language environment doesn't match between the client and the server.

The error you're experiencing can be caused by a number of things, from faulty hardware to something as simple as a failed relink of the Oracle home when the 10.2.0.3 patch was applied after the initial 10g R2 install. It is possible to relink the Oracle Home. This used to be a quite common exercise, not so common now, but it is documented for each supported platform.

Because you're running HP-UX, which is a platform where you quite often experience these types of issues (I've seen it on HP-UX more than AIX or Solaris, Linux is probably a close second.)

Alse check Metalink note 1012295.102 for common issues and resolutions for this problem.

 

[kk_jde]

Charles,

Thanks for your response. Our DBA did the upgrade... I did check with him about relinking the Oracle Home, but his response was that he didnt have to do that because the entire binary files were replaced. DBA said he didnt have any issues during the upgrade process. But the rebuild stats process took for ever, almost 36 hours for 800GB database. I have access to the DB HP-UX server, how can I check to make sure it is relinked properly? What else can I have our team check around for?

 

[kk_jde]

I couldnt find this 1012295.102 note on the metalink. What would be the search option to get this file? Please let me know...Thanks.

 

[cmanderson]

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4879065727281715536:::14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,1012295.102,1,1,1,helvetica

I was referring to Metalink (now "My Oracle Support") for Oracle Database and Middleware support (http://metalink2.oracle.com ), not Metalink3 (where you go now for JDE support).

Also check out Note:416857.1.

Notice your ORA-03120 is listed in the symptom and a recommendation is to relink your Oracle home. Or you could relink your DBA. ;-)

 

[cmanderson]

[ QUOTE ]
his response was that he didnt have to do that because the entire binary files were replaced.

[/ QUOTE ]


Bless his heart.

This smells of an environmental problem.


http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#sthref277


OK, this could be possible if an existing, known working Oracle Database 10.2 Home from an HP-UX box was cloned to the server. This is slightly rare, but not necessarily unusual. It could also be possible if a known working and tested Oracle Home was used for the upgrade of your JDE database instance from 8.1 to 10.2.

However, when you install Oracle Database, the Oracle Home is linked, and when a patch is applied it is relinked. Someone please prove me wrong, but there is really no way around that which I know of...as a matter of fact if you don't have the prerequisites all in order before the Oracle install, you can, and generally will run into issues (for instance, HP ITRC Forum Link.)


"Relinking Oracle manually is suggested under these circumstances:

- An OS upgrade has occurred.
- A change has been made to the OS system libraries. This can occur during the application of an OS patch.
- A new install failed during the relinking phase.
- Individual Oracle executables core dump during initial startup.
- An individual Oracle patch has been applied (However, explicit relink instructions are usually either included in the README or integrated into the patch install script)"


I've seen a very seasoned Senior Oracle DBA run into this problem, a DBA that I very much respected and who was trusted wiith a >2 Terabyte JDE production instance for a Fortune 500 company, so it can happen even to the best of them. She upgraded and patched on a known working HP-UX server and ended up with some zero byte binaries like sqlplus, which you can't do without for an Enterprise Server!, ultimately having to copy them from another box just to get the server up and running again (the business was breathing down her neck...)


There is usually an install log saved in the temporary directory (which could be /tmp or wherever the DBA might have had TEMP environment variable pointed prior to the insall.) If in /tmp, pretty much any user on the box can navigate through there unless the oracle users default file permissions were restricting world from reading them.



<font color="red"> THIS POST IS DEDICATED TO DAVE HELSLEY.....<evil laugh></font>