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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2215
  • Last Modified:

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?

Thanks,
7Souls
0
7Souls
Asked:
7Souls
  • 4
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
check this forum...

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

I guess dropping/re-creating tablespace will solve the issue...
0
 
HainKurtSr. System AnalystCommented:
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
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

"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
 
dbmechanicCommented:
please post the name of the invalid object ? owner of the object also should help.

Thanks
0
 
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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now