How to fix invalid database components when you upgrade

How to fix invalid database components when you upgrade

End users got invalid objects error and database eventually crashed. After starting up database, following components are invalidated: AMD (OLAP Catalog), CATPROC (Oracle Database Packages and Types), CONTEXT (Oracle Text) and ODM (Oracle Data Mining).

Attempt 1: use utlrp.sql to compile invalid objects: does not work.

Attempt 2: startup restrict and then run following: (does not work).
SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
SQL>@?/rdbms/admin/utlrp.sql

Attempt 3: startup upgrade and then run following:
SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
SQL>@?/rdbms/admin/utlrp.sql

It worked.

Here is the note from 10gR2 upgrade guide:

Note: The UPGRADE keyword allows you to open a pre-10.2 database. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

Thus the upgrade keyword includes restricted mode. Furthermore, disables system triggers etc. Normally, upgrade key is used during an upgrade session. I believe we need to set _system_trig_enabled to be false under restricted mode before running catalog.sql, catproc.sql.

*._system_trig_enabled = TRUE # set FALSE for upgrades/migrations

Check and validate individual components:

SQL> Rem Indicate CATPROC load complete and check validity
SQL> BEGIN
2 dbms_registry.update_schema_list('CATPROC',
3 dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));
4 dbms_registry.loaded('CATPROC');
5 dbms_registry_sys.validate_catproc;
6 dbms_registry_sys.validate_catalog;
7 END;
8 /