Howto repair INVALID “Oracle Database Packages and Type” in dba_registry – DBUA upgrade error

I have resolved this error.
Before running the CATUPGRD.SQL , describe the PLAN_TABLE in the SYS schema level.
If you have OTHER_XML column in PLAN_TABLE means , this error wont come.
Otherwise, this error will come when u run the catupgrd.sql for 10.2.0.4.0 patch.

Customers upgrading to 10.2.0.4 or 11.1.0.7 will encounter the bug only and only if they are upgrading a pre-10g (e.g., 9i or 8) db where admin/utlxplan.sql was explicitly run by/as SYS to create a (user) plan table in the SYS schema. I believe this is very rare as DBA won’t use the SYS schema to do explain plan of user/application queries.

Customers upgrading to 10.2.0.2, 10.2.0.3, or 11.1.0.6 won’t encounter this bug during upgrade because for those releases SQL Performance Analyzer exists in the format of a one-off, which is not run as part of upgrade.
dbms_sqlpa is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column. This is why we get an error if SYS.plan_table does not have this column. The other_xml column was introduced in 10.2.0.1 and added to the temporary global plan table plan_table$. plan_table$ was introduced in 10.1 as a replacement to table plan_table which is used to store the result of explain plan command. plan_table has become a public synonym to plan_table$.

Workaround is:
————————-

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

This will resolve your issue.

OR

you can check your registry :

SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;

COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID

8 rows selected

To repair and validate “Oracle Database Packages and Types” component you should :

1. log as SYSDBA

$ sqlplus ” / as sysdba”

2. shutdown running database and startup database in upgrade mode

SQL> shutdown immediate;

For 10.x version – SQL>startup upgrade;

For startup migrate;

3. run this two scripts (i recommend to spool output into file, so you can see complete output after all)

SQL> spool /tmp/cat_repair.txt

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

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

catalog.sql will recreate Oracle database Catalog Views component and catproc.sql will recreate Oracle database Packages and Types component

4. Recompile all invalid objects

SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

5. Shutdown database and startup in open mode

SQL> shutdown immediate;

SQL> startup;

Check your registry again :

SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;

COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID

8 rows selected