CATPROC Oracle Database Packages and Types INVALID after upgrade from 12.1.0.2.0 to 19.3.0.0
Scenario:
We have upgraded our db from 12.1.0.2 to 19.3 and SYS objects got INVALID.
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types INVALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0
Solution :
1) Below script will tell us which PLSQL code needs to be validated/compiled first for the CATPROC to get validated too. Please apply its recommendation and then repeat it until no more recommendations are there.
sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/
2) If the above query returns 'CATPROC can be validated now', then please execute the following command to validate the catproc registry component:
$ sqlplus / as sysdba
exec dbms_registry_sys.validate_catproc;
I have followed 1st step,
SQL> declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND 2 (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_D 3 ATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/ 4 5 6 7 8 9 10 11 12 13 14
Please compile Invalid object DBMS_AUTO_INDEX_INTERNAL Object_id 904209
PL/SQL procedure successfully completed.
SQL> alter package DBMS_AUTO_INDEX_INTERNAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY DBMS_AUTO_INDEX_INTERNAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7256/5 PL/SQL: SQL Statement ignored
7319/14 PLS-00231: function 'DBMS_AUTO_INDEX_INTERNAL.AUTO_INDEX_ALLOW'
may not be used in SQL
7319/39 PL/SQL: ORA-00904: : invalid identifier
7403/9 PL/SQL: Statement ignored
7403/13 PLS-00364: loop index variable 'CUR' use is invalid
7407/9 PL/SQL: Statement ignored
7407/43 PLS-00364: loop index variable 'CUR' use is invalid
7410/9 PL/SQL: Statement ignored
7410/44 PLS-00364: loop index variable 'CUR' use is invalid
Still this package is INVALID, so followed below steps :
1. connect to the database as sysdba:
sqlplus "/ as sysdba"
2. shutdown immediate
3. startup upgrade
4. spool catalog.log
@$ORACLE_HOME/rdbms/admin/catalog.sql
spool off
5. spool catproc.log
@$ORACLE_HOME/rdbms/admin/catproc.sql
spool off
6. spool utlrp.log
@?/rdbms/admin/utlrp
spool off
7. shutdown immediate
8. startup
9.
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types VALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0
Hope this will help you :)
