Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, March 3, 2008

ORA-04063: PACKAGE BODY "SYS.DBMS_UTILITY" HAS ERRORS

At 7 AM on 29th February, I got an SOS call for issues on an instance. After talking to the DBAs, I checked the alert log file:

Doing block recovery for file 1325 block 84015
Block recovery from logseq 2834, block 424302 to scn 10103409529653
Fri Feb 29 06:05:45 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2834 Reading mem 0
Mem# 0: /justanexample/redo1/log1a.dbf
Mem# 1: /justanexample/redo1/log1b.dbf
Block recovery completed at rba 2834.424312.16, scn 2352.1646449464
Doing block recovery for file 453 block 55097
Block recovery from logseq 2834, block 396285 to scn 10103409529653
Fri Feb 29 06:05:45 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2834 Reading mem 0
Mem# 0: /justanexample/redo1/log1a.dbf
Mem# 1: /justanexample/redo1/log1b.dbf
Block recovery completed at rba 2834.424312.16, scn 2352.1646449464
Doing block recovery for file 1418 block 35982
Block recovery from logseq 2834, block 420640 to scn 10103409529653
Fri Feb 29 06:05:45 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2834 Reading mem 0
Mem# 0: /justanexample/redo1/log1a.dbf
Mem# 1: /justanexample/redo1/log1b.dbf
Block recovery completed at rba 2834.424312.16, scn 2352.1646449464
Fri Feb 29 06:05:49 2008
Errors in file /justanexample/oracle/10.2.0/admin/justanexample_justanexample/bdump/justanexample_j000_9484.trc:
ORA-12012: error on auto execute of job 3404
ORA-04063: package body "SYS.DBMS_UTILITY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_UTILITY"
ORA-06512: at "SYS.DBMS_IREFRESH", line 62
ORA-06512: at "SYS.DBMS_REFRESH", line 15
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Fri Feb 29 06:05:49 2008
Errors in file /justanexample/oracle/10.2.0/admin/justanexample_justanexample/bdump/justanexample_j001_16186.trc:
ORA-12012: error on auto execute of job 4155
ORA-04063: package body "SYS.DBMS_UTILITY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_UTILITY"
ORA-06512: at "APPS.WF_EVENT", line 1160
ORA-06512: at "APPS.WF_BES_CLEANUP", line 482
ORA-06512: at line 1

When I checked dba_registry, I found that Oracle packages and types had become invalid.

I asked the DBAs to follow Metalink Note 457861.1:

SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

After running catalog and catproc, which invalidated all dependencies the no. of invalid objects reached 160,000. utlrp.sql took 10 hours to complete with 24 parallel threads. Even after this 16 objects in sys were invalid. These were types:

TYPE AQ$_DESCRIPTOR
TYPE AQ$_DESCRIPTOR
TYPE AQ$_EVENT_MESSAGE
TYPE AQ$_REG_INFO
TYPE AQ$_REG_INFO
TYPE AQ$_REG_INFO_LIST
TYPE AQ$_REG_INFO_LIST
TYPE AQ$_SRVNTFN_MESSAGE
TYPE AQ$_SRVNTFN_MESSAGE
TYPE AQ$_SRVNTFN_MESSAGE
TYPE DBMS_XPLAN_TYPE
TYPE DBMS_XPLAN_TYPE_TABLE
TYPE LCR$_ROW_LIST
TYPE LCR$_ROW_UNIT TYPE MSG_PROP_T
TYPE XMLGENFORMATTYPE

We had to reset these types manually with the command:
Alter type owner.typename reset;

We are still investigating the root cause for this issue.

2 comments:

Anonymous said...

//
Hide Original Post
At 7 AM on 29th February, I got an SOS call
//

its ok vikram.. you get such calls once in a leap year :))))

just kidding.. keep rocking !!!

K. Riding said...

I found this solution because I encountered nearly the same issue. I used the "alter type ... reset" command and it seemed to resolve my problem.

HOWEVER, I went to apply a patch bundle afterwards and received a bunch of errors. Tracking them down led me to the following Oracle bug.

---------------------------------
Bug 4421376 Dump (kgldpo) after ALTER TYPE .. RESET

Description
"ALTER TYPE .. RESET" SQL is accepted as user SQL when it should
signal an error since it is not supported. If the statement is
accepted then it can result in dictionary corruption which can
lead to subsequent problems such as a dump.

With the fix in place attempts to execute the command result in an ORA-922.

Workaround
Do not execute an "ALTER TYPE .. RESET" command.
---------------------------------

So, while the fix resolved my original issue - I am in a much worse place now. I do not recommend this fix to anyone.