Symptoms
— Problem Statement:
Setiting logminer to a specific tablespace reports the following error
SQL> BEGIN dbms_logmnr_d.set_tablespace(‘logmnr_tbs’); end;
/
2 BEGIN dbms_logmnr_d.set_tablespace(‘logmnr_tbs’); end;
*
ERROR at line 1:
ORA-04063: package body “SYS.DBMS_LOGMNR_INTERNAL” has errors
ORA-06508: PL/SQL: could not find program unit being called:
“SYS.DBMS_LOGMNR_INTERNAL”
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 131
ORA-06512: at line 1
Cause
Dba_Registry shows an invalid component and there are invalid objects in the sys and system schema
related to logminer.
Output
from the following queries show that Oracle Database Packages and Types
are INVALID and that logminer objects are invalid.
select object_name, owner, object_type from dba_objects where status = ‘INVALID’ and owner in
(‘SYS’,’SYSTEM’);
Solution
— To implement the solution, please execute the following steps::
1. Run this query as sys. It should come back with NO rows.
2. To validate the packages and types by running catupgrd.sql (part of the 10.2.0.3 patch installation)
shutdown immediate
–start the db
STARTUP UPGRADE
–spool to a file
SPOOL patch.log
–run the upgrade script
@?/rdbms/admin/catupgrd.sql
–turn off spooling (review file for errors)
spool off
–shutdown and restart.
shutdown immediate
startup
3. Confirm that all SYS and SYTEM owned objects are valid.
Check that Oracle Database Packages and Types is VALID in dba_registry.
4. Run dbms_logmnr_d.set_tablespace to set the logminer tablespace.
