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,862 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
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 51

Accepted Solution

by:
HainKurt earned 500 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

911 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

19 Experts available now in Live!

Get 1:1 Help Now