ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS

使用orachk工具检查数据库实例的时候，发现报告里面有类似下面这样一些错误（最近有给oracle 10g应用补丁psu 10.2.0.5.180717，不清楚是这个产生的还是其他原因导致），使用脚本检查，发现有很多invalid对象（具体参考[转载]—health check reports problem: dependency$ p_timestamp mismatch for valid objects (文档 id 781959.1)）

 

[w] - p_obj#=37817 d_obj#=38416 

[w] - p_obj#=38014 d_obj#=38605 

[w] - p_obj#=38020 d_obj#=38611 

[w] - p_obj#=38043 d_obj#=38634 

[w] - p_obj#=38061 d_obj#=38652 

[w] - p_obj#=38064 d_obj#=38655 

[w] - p_obj#=38087 d_obj#=38678 

[w] - p_obj#=38090 d_obj#=38681

 

select 

do.obj# d_obj,do.name d_name, do.type# d_type, 

po.obj# p_obj,po.name p_name, 

to_char(p_timestamp,'dd-mon-yyyy hh24:mi:ss') "p_timestamp", 

to_char(po.stime ,'dd-mon-yyyy hh24:mi:ss') "stime", 

decode(sign(po.stime-p_timestamp),0,'same','*differ*') x 

from sys.obj$ do, sys.dependency$ d, sys.obj$ po 

where p_obj#=po.obj#(+) 

and d_obj#=do.obj# 

and do.status=1 /*dependent is valid*/ 

and po.status=1 /*parent is valid*/ 

and po.stime!=p_timestamp /*parent timestamp not match*/ 

order by 2,1;

 

然后在执行编译一些invalid对象时遇到了下面错误ora-04045，如下所示：

 

sql> alter public synonym "sm$version" compile;

alter public synonym "sm$version" compile

*

error at line 1:

ora-00604: error occurred at recursive sql level 1

ora-04045: errors during recompilation/revalidation of lbacsys.lbac_events

ora-04064: not executed, invalidated

ora-04064: not executed, invalidated package body "lbacsys.lbac_events"

ora-06508: pl/sql: could not find program unit being called:

"lbacsys.lbac_events"

ora-06512: at line 2

ora-06508: pl/sql: could not find program unit being called:

"lbacsys.lbac_events"

 

 

 

如下所示，lbacsys下有很多invalid对象，最奇怪的是，执行了上面脚本，会触发ora-04045,似乎还会导致其他会话执行脚本时也触发这个错误：

 

 

 

 

sql> col comp_name for a36;

sql> col version for a30;

sql> col version for a10;

col status for a8;

select comp_id, comp_name, version, status 

from dba_registry 

where comp_name='oracle label security';

comp_id                        comp_name                            version    status

------------------------------ ------------------------------------ ---------- --------

ols                            oracle label security                10.2.0.5.0 valid

sql> 

sql>  select * from v$option where parameter = 'oracle label security';

parameter                                                        value

---------------------------------------------------------------- --------------

oracle label security                                            false

sql> 

 

 

 

tue jun 25 20:15:01 hkt 2019

errors in file /u01/app/oracle/admin/xxx/udump/xxx_ora_11203.trc:

ora-00604: error occurred at recursive sql level 1

ora-04045: errors during recompilation/revalidation of lbacsys.lbac_events

ora-04064: not executed, invalidated 

ora-04064: not executed, invalidated package body "lbacsys.lbac_events"

ora-06508: pl/sql: could not find program unit being called: "lbacsys.lbac_events"

ora-06512: at line 2

ora-06508: pl/sql: could not find program unit being called: "lbacsys.lbac_events"

ora-06512: at line 2

[oracle@xxxxx bdump]$ more  /u01/app/oracle/admin/xxxx/udump/xxx_ora_11203.trc

/u01/app/oracle/admin/epps/udump/epps_ora_11203.trc

oracle database 10g release 10.2.0.5.0 - 64bit production

oracle_home = /u01/app/oracle/product/10.2.0/db_1

system name:    linux

node name:      xxxx

release:        2.6.32-200.13.1.el5uek

version:        #1 smp wed jul 27 21:02:33 edt 2011

machine:        x86_64

instance name: xxx

redo thread mounted by this instance: 1

oracle process number: 48

unix process pid: 11203, image: oracle@xxxx (tns v1-v3)

*** action name:() 2019-06-25 20:15:01.745

*** module name:(sqlplus@xxxx (tns v1-v3)) 2019-06-25 20:15:01.745

*** service name:(sys$users) 2019-06-25 20:15:01.745

*** session id:(113.2150) 2019-06-25 20:15:01.745

error in executing triggers on connect internal

*** 2019-06-25 20:15:01.750

ksedmp: internal or fatal error

ora-00604: error occurred at recursive sql level 1

ora-04045: errors during recompilation/revalidation of lbacsys.lbac_events

ora-04064: not executed, invalidated 

ora-04064: not executed, invalidated package body "lbacsys.lbac_events"

ora-06508: pl/sql: could not find program unit being called: "lbacsys.lbac_events"

ora-06512: at line 2

ora-06508: pl/sql: could not find program unit being called: "lbacsys.lbac_events"

ora-06512: at line 2

 

 

当时参考“ora-04045: errors during recompilation/revalidation of lbacsys.lbac_events”这篇文章做了如下处理，告警日志里面不会出现ora-04045错误了，但是如果我使用

 

上面的语句编译invalid对象。就又会触发这个错误出现。似乎这种方法是指标不治本

 

 

shutdown immediate; 

startup migrate; 

alter view lbacsys.lbac$all_table_policies compile; 

alter package lbacsys.lbac_events compile body; 

shutdown immediate; 

startup;

 

 

最后参考官方文档“ora-07445 [zllcini] or ora-04045 in a database with ols set to false (文档 id 233110.1)“给出解决方案：

 

 

ora-4045 related to lbacsys.lbac_events

 

   ---------------------------------------

 

 

 

   ora-00604: error occurred at recursive sql level 1

 

   ora-04045: errors during recompilation/revalidation of lbacsys.lbac_events

 

   ora-06508: pl/sql: could not find program unit being called

 

   ora-06512: at line 2

 

   ora-06508: pl/sql: could not find program unit being called

 

   ora-06512: at line 2

 

 

solution description

--------------------

 

 

 

 

 

1. install oracle label security with the oracle universal installer

 

2. run $oracle_home/rdbms/admin/catnools.sql script to disable ols

 

3. optionally de-install 'the label security' software

 

 

 

 

 

explanation

-----------

 

the oracle universal installer may install ols components in some cases:

patchset apply

 

 

 

解决方案：

 

 

1:卸载oracle label security

 

@?/rdbms/admin/catnools.sql

 

 

2：运行/tmp/compiling.lst重编译失效对象

 

具体参考[转载]—health check reports problem: dependency$ p_timestamp mismatch for valid objects (文档 id 781959.1)

 

 

3：运行@?/rdbms/admin/catols.sql脚本安装oracle label security

 

 

第三步可以做也可以不做，是具体情况而定。

 

 

 

 

参考资料：

 

 

ora-07445 [zllcini] or ora-04045 in a database with ols set to false (文档 id 233110.1)