Solved

DBMS_* packages under the user SYS are INVALID

Posted on 2007-11-29
14
3,591 Views
Last Modified: 2013-12-19
Hello,
I have done a database refresh to refresh a user schema with production data.
This is a process I run often, however I encountered errors right away - in the import process when refreshing the user schema and saw these errors in the log:

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00046: using FILESIZE value from export file of 1999998976
. importing USERDB's objects into USERDB_MOD
IMP-00017: following statement failed with ORACLE error 4068:
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>'USERDB', export_db_nam"
 "e=>'USERPP', inst_scn=>'3326473809');"
 "COMMIT; END;"
IMP-00003: ORACLE error 4068 encountered
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_LOGREP_IMP" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
IMP-00017: following statement failed with ORACLE error 2304:


Please note: the error ORA-04063: package body "SYS.DBMS_LOGREP_IMP" has errors

I went into TOAD and noted that there are many DBMS_* packages are INVALID, not about 20 or so.

I did run the following script to recompile objects that belong to SYS:
connect / as sysdba
set echo off
set feedback off
set pagesize 999
set linesize 99
@$ORACLE_HOME/rdbms/admin/utlrp.sql

However, this did not resolve the problem.
Can anyone help me figure out how to resolve this problem.

Thank you very much.
0
Comment
Question by:kzsk31
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20377200
Try running the catalog.sql
0
 

Author Comment

by:kzsk31
ID: 20377333
Thx Jinesh_kamdar,
however, what are the risks of running this.
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 20377746
this will rebuild your database catalog.

you can try : @?/rdbms/admin/utlrp.sql

this will not recreate your catalog, but will compile all invalid objects.
there is also a dbms package that will do this, utl_recomp

good luck :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kzsk31
ID: 20377778
thx rbrooker

I did run @?/rdbms/admin/utlrp.sql and that did not resolve the problem completely.

So I will do the rebuild of the db catalog, my concern was would I be blowing away data?
So just to be safe, I think I will create a full backup and then run the catalog.sql
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 20377862
in my data warehouse there are 23 objects that are not compiled including views / packages owned by sys.  i have recompiled them until there is nothing left, but they come back.  they do not affect the operation of the database that i can see.

maybe you are in the same boat...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20378015
after @catalog.sql  did your run catproc?

../rdbms/admin/catproc.sql
0
 

Author Comment

by:kzsk31
ID: 20378234
Thx Sdstruber,
NO I have not run either yet.
I am still figuring out all the steps that would be necessary before I do this.
thx.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20382610
The scripts: "catalog.sql" and "catproc.sql" *DO NOT* destroy any data!  They just rebuild views and other objects that are part of Oracle's internal data dictionary.

We have also noticed some invalid objects in the SYS schema frequently in our Oracle10.1 production database.  i have recompiled them, but they seem to go invalid again shortly later.  As far as I can tell, they are not causing problems.   Here is the list of invalid SYS objects from our database:

Packages:
DBMS_SQLTUNE
DBMS_SWRF_REPORT_INTERNAL

Views:
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_FILESTATXS
DBA_HIST_LATCH
DBA_HIST_LATCH_MISSES_SUMMARY
DBA_HIST_OSSTAT
DBA_HIST_PARAMETER
DBA_HIST_ROWCACHE_SUMMARY
DBA_HIST_SEG_STAT
DBA_HIST_SERVICE_STAT
DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SGASTAT
DBA_HIST_SQLBIND
DBA_HIST_SQLSTAT
DBA_HIST_SYSSTAT
DBA_HIST_SYSTEM_EVENT
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_TABLESPACE_STAT
DBA_HIST_WAITSTAT
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 20392068
those would be the same objects that are invalid in my 10.1.0.5 db.  i have 2 others, but they are custom ones developed here.
0
 

Author Comment

by:kzsk31
ID: 20427523
Here are the steps I followed:
updated init.ora to include _system_trig_enabled=false
Executed the following:
../rdbms/admin/catalog.sql
../rdbms/admin/catproc.sql
../rdbms/admin/utlrp.sql

When running catproc.sql - noted lots of errors in the alert log:
hu Dec  6 20:28:45 2007
Errors in file /u2/app/oracle/admin/test/udump/test_ora_5924.trc:
ORA-00600: internal error code, arguments: [17012], [0x4463D8E50], [], [], [], [
], [], []

Checked thru Toad and noted that all the DBMS_REP* packages were invalid.
So ran ../rdbms/admin/catrep.sql
Changed the inti.ora - _system_trig_enabled=true
And shtudown/restrated the system.

Still there are packages that are invalid and they are NOT  the ones posted by MarkGeer.

0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 20428739
Then you may need to open a Service Request with Oracle.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20919843
Forced accept.

Computer101
Community Support Moderator
0
 

Author Comment

by:kzsk31
ID: 20929830
HI,
Just want to provide an update, I recreated the database using DBCA and then re-imported all the custom schemas with data.  And that's how I got around the problem.
Thx for all the help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
set item in form 7 19
Oracle Nested table uses ? 2 60
PL/SQl Expanding the WHERE statement in query 3 55
oracle sql developer + get hour from datetime field 2 26
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question