Monday, February 09, 2009

DEMO HRMS 9.0 : ORA-00600

If you are installing, working, with Peoplesoft HRMS9.0, Peopletools 8.49 on Oracle database 10gR2 (currently on 10.2.0.4), you could easily hit an ORA-600 error.

Go to the front end application, menu :
"Manager Self Service / Job and Personal Information / Request Job Change"
You'll receive the following pop-up error message :


And the alert.log of the database :
[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ tail -f alert_DMOHRMS9.log
Starting background process QMNC
QMNC started with pid=13, OS id=6767
Sun Feb 8 17:38:24 2009
Completed: ALTER DATABASE OPEN
Sun Feb 8 17:38:32 2009
Starting background process CJQ0
CJQ0 started with pid=19, OS id=6830
Sun Feb 8 17:45:08 2009
Errors in file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc:ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

The content of the file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc :
[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ more /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
/apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/10.2.0
System name: Linux
Node name: orion.phoenix-nga
Release: 2.6.9-67.0.0.0.1.ELsmp
Version: #1 SMP Sun Nov 18 00:23:42 EST 2007
Machine: i686
Instance name: DMOHRMS9
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 6865, image: oracle@orion.phoenix-nga
*** ACTION NAME:() 2009-02-08 17:45:08.957
*** MODULE NAME:(PSAPPSRV@orion.phoenix-nga (TNS V1-V3)) 2009-02-08 17:45:08.957 *** SERVICE NAME:(DMOHRMS9) 2009-02-08 17:45:08.957
*** SESSION ID:(145.39) 2009-02-08 17:45:08.957
*** 2009-02-08 17:45:08.956
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT ' ' ,C.MANAGER_ID ,COUNT(*) FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2
.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_R CD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTI D AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX (A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 W HERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID
----- Call Stack Trace -----
[...]

If we are running the same query onto the database within SQL*Plus, same error, even to get the explain plan :
SQL> explain plan for
2 SELECT ' ' ,C.MANAGER_ID ,COUNT(*)
3 FROM PS_JOB A , PS_DEPT_TBL C
4 WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
5 AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) 6 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) 7 AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) 8 9 10 11 12 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID 13 14 15 16 17 18 ;
FROM PS_JOB A , PS_DEPT_TBL C
* ERROR at line 3: ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

Some of the indexes of the two tables involved in this query are created in descending order.
The descending indexes have been introduced with the Peopletools 8.48 onwards for some (for me, mysterious) reasons, let's say surely to improve the performances.
But, I remember some months ago a benchmark I did on CRM9 (Peopletools 8.48 - Oracle database 10.2.0.3), the indexes in descending order cause a lot of performances issues. I had to use an hidden parameter to deactivate the descending indexes to have better performance.
/* For reminder, it is not only more than a huge work to recreate all the indexes without that DESCending clause, but that make the Peoplesoft application customized, which is not so good for the support, that's why the hidden parameter was a good workaround to keep the Peoplesoft objects closer to the standard. */
Here again, the descending indexes causes the trouble.
As you could have seen above, it is not performance issue, but transactional error and a database ORA-600.
It seems to hit an "old" Oracle bug (from 8.1.7.x to 9.2.x, bug ref #869177) for which the workaround was the setting of "_IGNORE_DESC_IN_INDEX" to TRUE (FALSE by default).

So, it seems we are still under the same effect, my database is 10.2.0.4. So, let's change the setting :

SQL> conn / as sysdba
Connected.
SQL> alter system set "_ignore_desc_in_index" = true scope=both;

System altered.

SQL>

Now, it is not enough, the indexes have to be rebuilt. Rebuild in the Peoplesoft meaning, DROP + CREATE.
So, open the Application Designer, connect within PS user.
Insert into the project the two records JOB and DEPT_TBL.
Then build the project, check only "Create Index", go to the settings, and be sure you are checking the "Recreate index if it already exists", then build the project, as show below :

Then, run the generated SQL script, that won't modify the record (the tables), just drop and recreate the indexes. Oracle will ignore the DESC option embedded in the commands lines of the indexes creation because of the setting of that hidden parameter "_IGNORE_DESC_IN_INDEX".

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rebuild_indexes

Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index created.


Index altered.
[...]


Finally, go back to the front end application, and retry the previous failing transaction, from now that'll work fine :
Problem solved.

However, here above was only an example on two records (tables), but you could have to rebuild all the indexes for all the records. A lot of time is required for that.

Conclusion : before creating, especially before loading the Peoplesoft objects into the database (before the first datamover load of the database setup), it could be a good idea to set this hidden parameter "_IGNORE_DESC_IN_INDEX" to TRUE.

Caution : from now, you have a difference between the Peoplesoft datamodel (with the DESCending order in the indexes description) and the Oracle dictionary (without the DESCending order), that means every time you'll rebuild the records, the Application Designer will try to rebuild the indexes, that is not the case when objects are same on both sides. This is one of the biggest problem of this solution.
Caution 2 : playing around hidden parameter is supposed to be validated by your Oracle support.

Going further : after raising a SR to the Oracle support, it seems this bug is solved on Oracle 11.1, and a fix will be included in the coming 10.2.0.5 patchset.
Wait and see, or manage an upgrade to 11.1 (after testing, I confirm, it is working fine on 11.1.0.7).

Enjoy,

8 comments:

Nicolás said...

EXCELENT YOUR POST, WE HAVE SAME SYMPTOMS OVER PEOPLE SOFT APPLICATION. I'M FROM ARGENTINA, I'M DBA ORACLE AND I TURN ON TRUE THE HIDDEN PARAMETER. WE HAVE RELEASE 10.2.0.4 ON AIX 5.3 SYSTEM, 64BITS. PLEASE LET US TO KNOW IF REALLY IT FIXED ON 11g.
BEST REGARDS.


NICOLAS(DBA ORACLE - ARGENTINA)

Gasparotto Nicolas said...

I recently upgraded my databases of my labs to Oracle 11.1.0.7, then removed this parameter from spfile (alter system reset "_IGNORE_DESC_IN_INDEX" scope=spfile sid='*') and bounce my db. Finally, the ORA-600 is gone, this case is not reproductible on 11.1.0.7 with standard parameter value.

Gasparotto Nicolas said...

Of course, after removing the parameter from spfile and bouncing the db (I mean your 11g database), you'll need to rebuild the index as well from AppDesigner to be again DESC ordered.

Nicolás said...

I HAVE A QUESTION ABOUT YOUR LAST COMMENTS, WE KEEPING IN MIND THAT THE ORACLE RELEASE IS 10.2.0.4. YOU TELL ME, IF I'M RIGHT ABOUT IT.... FIRST, I SHOULD TO SET THE PARAMETER ON "TRUE" AND RESTART THE DATABASE. THEN, I SHOULD TO VERIFY IF THE "ORA600 ERROR" HAS GONE....
ONLY, IF WE GET THE SAME BEHAVIOR (ORA 600 ERROR) THEN WE TOO SHOULD TO REBUILD ALL INDEXES WITHOUT CLAUSE "desc".
OTHERWISE, IF THE ERROR HAS GONE, WE DON'T NEED TO DO ANYTHING...
I'M APOLOGY BY MY POOR AND BASIC ENGLISH....
I'M HOPE THAT YOU UNDERSTAND ME.
REGARDS.


NICOLAS NANO

Gasparotto Nicolas said...

If you are on 10.2.0.4 and experiencing same issue as the post describe, then you should :
1. change the parameter "_IGNORE_DESC_IN_INDEX" to true (no bounce is required), alter system with scope=both should do the trick
2. rebuild the index through Application Designer
Oracle will ignore the DESC order by himslef because the parameter value is TRUE. Everything is explained in the steps described in the post, at least I hope it is clear.
My previous comment was in case of you are upgrading your db to 11g.

Nicolás said...

THANKS A LOT, FOR YOU EXPLANATION..
WE HAVE SET ON "TRUE" THE VALUE'S PARAMETER.NOW, WE WILL PROCEED WHIT THE TESTS CASES. I HOPE DON'T FIND ANY ORA600'S ERROR....
I'LL LET YOU ABOUT THE RESULTS....
THANKS AGAIN...


NICOLAS

Devesh said...

Excellent post!!

MiN said...

Thanks, this solved the problem faced my company's client.

Kelvin (Peoplesoft Consultant - Malaysia)