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重编译失效对象
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)
上一篇: 冬季小心暖气病 屋内保暖的注意事项
下一篇: mysql常见函数及其用例