Monday, November 30, 2009

Invalid Objects on SYS and ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

Some Objects invalid on SYS, then used catalog.sql, catproc.sql, that made error:
SELECT dbms_registry_sys.time_stamp('PATCH_BGN') AS timestamp FROM DUAL
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
BEGIN dbms_registry.check_server_instance; END;
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 1
Some Objects invalid, we can recreate with scripts at ORACLE_HOME/rdbms/admin PATH
Example Object invalid:
DBMS_STATS_INTERNAL PACKAGE BODY ,DBMS_STATS PACKAGE BODY
We can recreate dbms_stats_internal & dbms_stats:
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb
But Our Problem, we found many objects invalid on SYS, after used "catalog.sql", "catproc.sql"
SQL> startup migrate -- "backup database" before & if use RAC, should -- alter system set cluster_database=false scope=spfile -- before
SQL> spool /tmp/catalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> spool off
SQL> spool /tmp/catproc.log
SQL> @?/rdbms/admin/catproc.sql
SQL> spool off
Below Error after used catproc.sql:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
Checked in spool file.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY DBMS_REGISTRY:

95/17 PL/SQL: Item ignored
95/21 PLS-00302: component 'REGISTRY$' must be declared
Checked objects invalid(Found many objects invalid):
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME
/
What should we do ?
- recover database until time (if have backup & if necessary)
- check inconsistencies in the data dictionary(metalink 136697.1) hcheck package, then verify the reported inconsistency data dictionary + fix(manual)... and then run "catalog.sql", "catproc.sql" and "utlrp.sql" again.
Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hout as described in Note:101468.1
3. Create package hcheck in SYS schema.
4. execute hcheck.full
5. verify the reported inconsistency
Then:
SQL> startup migrate
SQL> set serverout on
SQL> set echo on
SQL> spool report.txt
SQL> exec hcheck.full
SQL> spool off
Verifed(metalink 456468.1) the reported inconsistency data dictionary and fixed(manual), then:
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
No Error about DBMS_REGISTRY package.

Actually, we should solve a real problem, before use catalog/catproc.
The SYS.DBMS_REGISTRY problem, that made database couldn't do something (expdmp/exp, run utlrp.sql or ...)

By the way, Thank You Oracle Support for hcheck package.

No comments: