Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DBMS_* packages under the user SYS are INVALID

Posted on 2007-11-29
14
Medium Priority
?
3,660 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

916 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