Solved

DBMS_* packages under the user SYS are INVALID

Posted on 2007-11-29
14
3,554 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
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
Try running the catalog.sql
0
 

Author Comment

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

Expert Comment

by:rbrooker
Comment Utility
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
 

Author Comment

by:kzsk31
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
after @catalog.sql  did your run catproc?

../rdbms/admin/catproc.sql
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:kzsk31
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

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

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
Community Support Moderator
0
 

Author Comment

by:kzsk31
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now