DBMS_* packages under the user SYS are INVALID

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.
kzsk31Asked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Then you may need to open a Service Request with Oracle.
0
 
Jinesh KamdarCommented:
Try running the catalog.sql
0
 
kzsk31Author Commented:
Thx Jinesh_kamdar,
however, what are the risks of running this.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
rbrookerCommented:
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
 
kzsk31Author Commented:
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
 
rbrookerCommented:
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
 
sdstuberCommented:
after @catalog.sql  did your run catproc?

../rdbms/admin/catproc.sql
0
 
kzsk31Author Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
rbrookerCommented:
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
 
kzsk31Author Commented:
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
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
 
kzsk31Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.