Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DBMS_* packages under the user SYS are INVALID

Posted on 2007-11-29
14
Medium Priority
?
3,688 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
13 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
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…
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

581 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