We are working on pre-upgrade steps and noticed that
“Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components
are showing as INVALID.
SQL> select comp_id, comp_name,version, statusfrom
dba_registry;
COMP_ID
COMP_NAME VERSION STATUS
---------- ----------------------------------------
------------------------------ --------------------------------------------
ORDIM Oracle
Multimedia 11.2.0.4.0 VALID
XDB Oracle
XML Database 11.2.0.4.0 VALID
EXF Oracle
Expression Filter 11.2.0.4.0 VALID
RUL Oracle
Rules Manager 11.2.0.4.0 VALID
OWM Oracle
Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle
Database Catalog Views 11.2.0.4.0 INVALID
CATPROC Oracle
Database Packages and Types
11.2.0.4.0 INVALID
JAVAVM JServer
JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle
XDK 11.2.0.4.0 VALID
CATJAVA Oracle
Database Java Packages 11.2.0.4.0 VALID
I see that CATPROC, CATALOG is INVALID in the registry and followed below
steps to validate.
Step1: Validate
‘packages and types’ by running catalog and catproc scripts
$sqlplus
"/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off
Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if
database version is lower than 10g.
This should resolve the issue. If CATPROC, CATALOG is still invalid in the
registry then follow next step
Step2:-
Run below script as SYSDBA until it returns 'CATPROC can be validated now'.
This script recompiles all dependent objects.
Note that you need to replace CATPROC with CATALOG in below
script and execute to recompile the objects related to CATALOG.
REM ***************
REM CHECKVALID.SQL
REM ***************
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;
/
Validate CATPROC. CATALOG by executing
following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;
Now I can see that all Database components are objects are VALID.
SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID
COMP_NAME VERSION STATUS
---------- ----------------------------------------
------------------------------ --------------------------------------------
ORDIM Oracle
Multimedia 11.2.0.4.0 VALID
XDB Oracle
XML Database 11.2.0.4.0 VALID
EXF Oracle
Expression Filter 11.2.0.4.0 VALID
RUL Oracle
Rules Manager 11.2.0.4.0 VALID
OWM Oracle
Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle
Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle
Database Packages and Types
11.2.0.4.0 VALID
JAVAVM JServer
JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle
XDK 11.2.0.4.0 VALID
