Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CATPROC gets invalidated

Re: CATPROC gets invalidated

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Mon, 15 May 2006 19:25:09 +0800
Message-ID: <015701c67812$3aebbc70$6401a8c0@porgand>


To get some more information, you could use logminer to filter out updates to obj$ table for dbms_standard's corresponding row in there.

Tanel.

> Hi all,
>
> This is very weird... Third time in the last few days catproc got
> invalidated. This is on 10.1.0.4 database on AIX that was upgraded to
> 10.2.0.2 and than downgraded back to 10.1.0.4 (almost a month ago).
> it's a DW environment.
>
> Last occurrence's description:
> The issue appears in different forms one of which is trying to run
> DBMS_STATS:
> ORA-04068: existing state of packages has been discarded
> ORA-04065: not executed, altered or dropped stored procedure
> "SYS.DBMS_STANDARD"
> ORA-06508: PL/SQL: could not find program unit being called
> ORA-06512: at "SYS.DBMS_STATS", line 12210
> ORA-06512: at line 2
>
> I checked invalid objects DBMS_STATS and DBMS_STANDARD packages are
> VALID. There are some invalid objects though. For example,
> DBMS_SQLTUNE and DBA_HIST_* views. CATPROC component in
> DBA_SERVER_REGISTRY is shown as invalid. This time I was able to fix
> it with simple utlrp.sql.
>
> Two time before we couldn't run utlrp.sql because DBMS_STANDARD was
> actually invalid and I could see it! So first time we went to startup
> migrate and catrelod.sql. Second time I just complied DBMS_STANDARD
> manually and than was able to run utlrp.sql happily.
>
> After the second issue I enabled auditing for all DDL using AUDIT ANY
> PRIVILEGE (and excluded than SELECT/UPDATE/DELETE/INSERT). However, I
> couldn't see any DDL on sys objects in audit trail.
>
> So how to catch what causes packages' invalidation and in the last
> case what the heck I couldn't run DBMS_STATS if it was valid as well
> as DBMS_STANDARD (which was in the error text)?
>
> I can only add that another similar database in the same environment
> showed similar behavior just couple days ago. It's gotta be something
> similar and I believe caused by some user action. (This environment
> setup in very un-secure way and I wouldn't be surprised that some
> users/developers have actually access to DBA privileges but this is
> another story).
>
> Thanks in advance for any hints.
>
> --
> Best regards,
> Alex Gorbachev
>
> http://oracloid.blogspot.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 15 2006 - 06:25:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US