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

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?

7SoulsData Analyst/Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
check this forum...

I guess dropping/re-creating tablespace will solve the issue...
HainKurtSr. System AnalystCommented:
also check this bug

This is a bug in and and a patch has been released which can be downloaded from metalink.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
7SoulsData Analyst/Database AdministratorAuthor Commented:
Thanks, HainKurt

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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

7SoulsData Analyst/Database AdministratorAuthor Commented:
Thanks, HainKurt

I just applied the CPU patch to my 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.

7SoulsData Analyst/Database AdministratorAuthor Commented:
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.


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.


PL/SQL procedure successfully completed.




Do you know what I should do next?

please post the name of the invalid object ? owner of the object also should help.

7SoulsData Analyst/Database AdministratorAuthor Commented:
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
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?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.