|
|
|
Step to follow for validating Catalog and Catproc in the Registry
|
By kiran kature
Oct 20, 2008
|
 digg!
 Print
 Email to Friend
Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.
|
|
|
Step to follow for validating Catalog and Catproc in the Registry
How to determine what is keeping Catalog and Catproc invalid in the Registry,
even though you cannot find any invalid SYS objects on the database?
SQL> select comp_id, status, version from dba_registry;
COMP_ID STATUS VERSION
------------------------------ ----------- ------------------------------
CATALOG INVALID 10.2.0.4.0
CATPROC INVALID 10.2.0.4.0
|
Now check the Objects in SYS schema.
SQL> select object_name, object_type from dba_objects where owner = 'SYS' and status = 'INVALID';
no rows selected
|
Solution:
1. Run this SQL script until it returns CATPROC can be validated.
If any invalid objects are listed, they must be dropped or made valid to
continue.
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. Then run the utlrp.sql script.
3. Now check the Registry to ensure a valid status for Catalog and Catproc
SQL> select comp_id, status, version from dba_registry;
COMP_ID STATUS VERSION
------------------------------ ----------- ------------------------------
CATALOG VALID 10.2.0.4.0
CATPROC VALID 10.2.0.4.0
|
|
Comments/Reviews on this article: |
RareDBA DBA
Mar 02, 2009 |
Hello All,
We were trying to upgrade the oracle 10.2.0.3 to 10.2.0.4 but failed stating that INVALID CATPROC found.
I tried to run the catalog.sql and catproc.sql in upgrade mode and then utlrq.sql but all went in vain
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 10.2.0.3.0 VALID
Oracle Database Packages and Types 10.2.0.3.0 INVALID
Oracle XML Database 10.2.0.3.0 VALID
I have tried lot more fix but all failed.
the below objects are found invalid in dba_objects.
SQL> select object_name,object_type,owner from dba_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE OWNER
-------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------------------
DBMS_STREAMS_ADM PACKAGE BODY SYS
BINARY_DOUBLE TYPE SYS
UTL_NLA PACKAGE BODY SYS
UTL_NLA PACKAGE SYS
UTL_NLA_ARRAY_FLT TYPE SYS
UTL_NLA_ARRAY_DBL TYPE SYS
UROWID TYPE SYS
BINARY_FLOAT TYPE SYS
QTI_RECYCLE PROCEDURE SYS
UTL_NLA_ARRAY_DBL SYNONYM PUBLIC
UTL_NLA SYNONYM PUBLIC
OBJECT_NAME OBJECT_TYPE OWNER
-------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------------------
UTL_NLA_ARRAY_FLT SYNONYM PUBLIC
REMOVE_MSGUSREAD PROCEDURE BB_BB60
REMOVE_MSGMAIN_BY_ARCHMAIN PROCEDURE BB_BB60
REMOVE_CONFMAIN_BY_GROUPS PROCEDURE BB_BB60
REMOVE_CONFMAIN_BY_CRSMAIN PROCEDURE BB_BB60
REMOVE_ARCHMAIN_BY_ID PROCEDURE BB_BB60
REMOVE_ARCHMAIN_BY_FORUMMAIN PROCEDURE BB_BB60
COURSE_ARCHIVE_MAIN_CP PROCEDURE BB_BB60
GROUP_ARCHIVE_MAIN_CP PROCEDURE BB_BB60
SAVE_CU_PROC PROCEDURE BB_BB60
21 rows selected.
Also tried your fix but failed.
SQL> @test.sql
Please compile Invalid object UTL_NLA
Object_id 11949
PL/SQL procedure successfully completed.
SQL> alter package utl_nla compile;
alter package utl_nla compile
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.UTL_NLA_ARRAY_DBL
ORA-21700: object does not exist or is marked for delete
SQL>
Please suggesst me |
Joan Brittingham
Nov 04, 2008 |
I cannot find the table: Registry with cid, date-loading etc in it under sys in 10G. Am I missing something? |
Joan Brittingham
Nov 04, 2008 |
I see now that the script should have REGISTRY$ as the table. |
|
About author:
I subbmitted some article which are important for the dba point of view.
|
|
|
