Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
2,127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 37754668
check this forum...

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

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

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 37754680
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
ID: 37756689
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:7Souls
ID: 37757996
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
ID: 37758876
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
ID: 37761595
please post the name of the invalid object ? owner of the object also should help.

Thanks
0
 

Author Comment

by:7Souls
ID: 37765615
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Suggested Courses

664 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