Tuesday, April 29, 2008

Recompiling Oracle invalid objects or Brainfucking part 2

Finally I cloned the production database (RHEL 4, Oracle 10g 10.2.0.3 64 bit edition) to the test one (Fedora Core 8, Oracle 10g 10.2.0.1 updated to 10.2.0.3). All seemed working: I connected to the database using SQLPlus as sysdba and executed some simple SELECT-queries successfully. But as usual there appeared a new trouble :) - when I tried to connect as any user except sysdba, I got the following messages:

Enter password:
ERROR:
ORA-06553: PLS-801: internal error [56319]


ERROR:
ORA-06553: PLS-801: internal error [56319]


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

The point is that some objects may have an invalid status after a database upgrade, cloning (especially between 32 and 64 bit editions) etc.

As a remedy for this trouble, you should :

1) Shutdown the database (shutdown)
2) Start up the database in upgrade mode (startup upgrade)
3) Execute utlirp.sql script from $ORACLE_HOME/rdbms/admin directory (@?/rdbms/admin/utlirp 0). You should pass the integer parameter to this script - just pass a zero (0). If you are curious enough, look inside the script for the description.
4) As the prompt will say - shutdown the database again
5) Start up the database in a normal mode (startup)
6) Execute utlrp.sql script (@?/rdbms/admin/utlrp)

Game Over :)

No comments: