Cannot drop Jobs because SYS.DBMS_INTERNAL_LOGSTDBY error


ORA-04063: package body “SYS.DBMS_INTERNAL_LOGSTDBY” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_INTERNAL_LOGSTDBY”
ORA-06512: at “SYS.DBMS_IJOB”, line 522
ORA-06512: at “SYS.DBMS_JOB”, line 171
ORA-06512: at line 2

I check this package, i got this error “PL/SQL: ORA-00942: table or view does not exist” but i can’t find what table because this package body wrapped. So i want to find what table that in use in this package. Here the sql to find what table that used in SYS.DBMS_INTERNAL_LOGSTDBY (you must run in 2 server to find what table doesn’t exists) :

SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
where object_name = ‘DBMS_INTERNAL_LOGSTDBY’;

OWNER OBJECT_TYPE        OBJECT_NAME                                 OBJECT_ID  STATUS
SYS        PACKAGE                   DBMS_INTERNAL_LOGSTDBY 4339               VALID
SYS        PACKAGE   BODY    DBMS_INTERNAL_LOGSTDBY 7300             INVALID

———————————————————————————————————–

SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 7300 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = ‘TABLE’
ORDER BY OWNER, OBJECT_NAME;

On my server i have 24 object_name, such as :

AUD$
AUDIT_ACTIONS
COL$
DUAL
FGA_LOG$
IND$
INDPART$
JOB$
OBJ$
TAB$
TS$
USER$
LOGMNR_DICTSTATE$
LOGMNR_LOG$
LOGMNR_SESSION$
LOGMNR_UID$
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$EVENTS
LOGSTDBY$HISTORY
LOGSTDBY$PARAMETERS
LOGSTDBY$SCN
LOGSTDBY$SKIP
LOGSTDBY$SKIP_SUPPORT
LOGSTDBY$SKIP_TRANSACTION
Now, try to find what table doesn’t exist, copy the script to create table from another server which have the table.  After there are no table miss, compile DBMS_INTERNAL_LOGSTDBY again.

Advertisements

5 Responses

  1. Thank you for this information. I had the exact same problem, root cause slightly different.

    I cloned a database and skipped some tablespaces intentionally for space reason. On of the tablespaces contained audit information saved in the AUD$ table, which was not present and thus caused the DBMS_INTERNAL_LOGSTDBY to become invalid.

    Oracle Support did not know how to solve the issue, either.

    Once again, thank you for your post and keep up the good work.

  2. I would like to thank you so much, really i waste long time to solve the issue and Oracle metalink not provide me any solution. Thanks again.

  3. Thanks for the information, it’s really helpful.
    Sorted my problem.

  4. You are absolutely brilliant this helped me to solve my issue. I dropped the streams objects which caused SYS objects to become invalid which is very strange. Oracle did nothing when I opened a SR just ran catproc, catalog, ultrp so many times nothing resolved you note did it. Adios

  5. Great post thanks again oracle is clueless on the error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: