Solved

Error on a autoexecute of job I received the "ORA-04063: package body "SYS.PRVT_ADVISOR" has errors" message

Posted on 2012-03-22
7
1,814 Views
Last Modified: 2012-04-09
Hi experts,

While doing a backup on my RMAN catalog. I got a "RMAN-03002: failure of configure command at 03/22/2012 11:56:31 RMAN-20001: target database not found in recovery catalog" errors while looking for a solution I went through the 'alert log' and the only errors that I found which only happened at 10:00 pm every evening was this:

"
ORA-12012: error on auto execute of job 8937
ORA-04063: ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.DBMS_ADVISOR", line 102
ORA-06512: at "SYS.DBMS_SPACE", line 1462
ORA-06512: at "SYS.DBMS_SPACE", line 1566
 has errors"

The first problem went away after I bounced the DB however the problem of the ORA-04063 appears to have been around since the inception of the Database and has caused no operational problems. I've checked the web and most of what I found was that it was bug of some type. So I recompiled the object and I got the error I looked into TOADand scanned the object for the errors and what I found was:

"PL/SQL: ORA-00904: "NUM_DB_REPORTS": invalid identifier
PL/SQL: ORA-00904: "LAST_REPORT_TIME": invalid identifier"

Do any of you know what the error is?

Thanks,
7Souls
0
Comment
Question by:7Souls
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
check this forum...

https://forums.oracle.com/forums/thread.jspa?threadID=356704

I guess dropping/re-creating tablespace will solve the issue...
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
Comment Utility
also check this bug

This is a bug in 10.2.0.1 and 10.2.0.2 and a patch has been released which can be downloaded from metalink.
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=4707226.8
0
 

Author Comment

by:7Souls
Comment Utility
Thanks, HainKurt

I've yet to apply the CPU patch to the 10.2.0.5 on the Windows 2008 64bit server. I'll work on it today and let you know how it goes.

Thanks,
7Souls
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:7Souls
Comment Utility
Thanks, HainKurt

I just applied the CPU patch to my 10.2.0.5 on Windows 2008 but I dont see the bug referenced from your link. See below.

Interim patches (1) :

Patch  12429524     : applied on Fri Mar 23 11:26:39 EDT 2012
Unique Patch ID:  13849672
   Created on 20 Jun 2011, 02:45:06 hrs
   Bugs fixed:
     12429524, 11066597, 9935787, 10236704, 11853331, 9825461, 8411930
     6651220, 7026523, 8882576, 9360157, 10068982, 10173237, 10324526, 8332730
     7516536, 11814891, 9871302, 6055658, 12363485, 10220194, 7592673, 10202758
     9735237, 10052339, 12332704, 8666117, 9145204, 9824435, 11703718, 7280234
     9269536, 10132870, 9094926, 9323583, 10144882, 9860769, 9488247, 10306945
     9532911, 9184754, 11657004, 9656004, 8528171, 10191707, 8943287, 11727013
     10407744, 11930680, 8966823, 10160615, 10637621, 4672767, 5638228
     12328269, 11731166, 9215341, 10159846, 11076894, 10269717, 10091171
     9061785, 8211733, 8412426, 9020537, 9024850, 9150282, 10010310, 7602341
     8865718, 11737047, 11675644, 10111381, 9368502, 8660422, 9032322
     9295217, 10224187, 10080775, 9841679, 8977287, 10054513, 10220046
     9577583, 10352673, 8277300, 8394351, 8350262, 9949948, 9879181, 9469117
     10017048, 8771916, 10155684, 6694396, 10308274, 10312161, 10157402
     8434467, 9668086, 9980062, 6151936, 10091698, 8228796, 9699654, 10114856
     9978573, 10156303, 5575218, 9081686, 10196896, 10080735, 5519322, 6768114
     6904068, 7045446, 7452759, 7612454, 8684595, 8811401, 9109487, 9320130
     9337325, 9362218, 9390484, 9548269, 9770451, 10155844, 9047975, 8663644
     7708267, 6086930, 9539556, 9355794, 8264642, 9495669, 6975402, 9659614
     9387574, 9320786, 9793452, 9024737, 9196440, 8970313, 9308296, 9663844
     10099855, 6402302, 7519406, 8224558, 8487143, 8544696, 8546356, 8664189
     8996060, 9261837, 9577921, 9586637, 9646836, 9683239, 9711859, 9713537
     9714832, 9726739, 9747153, 9772888, 9870614

I'm have to justify the solution given.

Thanks,
7Souls
0
 

Author Comment

by:7Souls
Comment Utility
Hi HainKurt,

I compile invalid objects using the 'utlrprp.sql' and I checked dba_objects for 'invalid' and still wound up with this invalid object (See below) do you know what else that I could be missing because I don't know the name of the dropped tablespace and currently I using all of the tablespaces that are in DBA_Tablespaces.

"OBJECTS WITH ERRORS
-------------------
                  1

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

PRVT_ADVISOR"

Do you know what I should do next?

Thanks,
7Souls
0
 
LVL 1

Expert Comment

by:dbmechanic
Comment Utility
please post the name of the invalid object ? owner of the object also should help.

Thanks
0
 

Author Comment

by:7Souls
Comment Utility
Hi dbmechanic,

The problem that I'm having is I don't know which tablespace that is invalid. The only thing that I remembered doing is a rename on a 'datafile'. So from the view mentioned as a workaround in the link mentioned above.

select distinct(TABLESPACE_NAME),SEGMENT_OWNER from dba_auto_segadv_ctl;

Tablespace name     owner
DEVORADB_CAT      DEVORADB
DSISGTD_CAT      DSISGTD
FUNOLAP_CAT      FUNOLAP
SYSAUX                  SYS

Since this is my RMAN catalog if I try dropping tablespaces to get rid of this how do I retain the backup information that I currently have on my databases?

Thanks,
7Souls
0

Featured Post

Why do Marketing keep bothering you?

Is your marketing department constantly asking for new email signature updates? Are they requesting a different design for every department? Do they need yet another banner added? Don’t let it get you down! There is an easy way to manage all of these requests...

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
A safe way to clean winsxs folder from your windows server 2008 R2 editions
Via a live example, show how to take different types of Oracle backups using RMAN.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

772 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

13 Experts available now in Live!

Get 1:1 Help Now