代码改变世界

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

2019-06-25 23:17  潇湘隐者  阅读(...)  评论(... 编辑 收藏

使用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"

 

clip_image001

 

 

如下所示,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> 
 
SQLselect * from v$option where parameter = 'Oracle Label Security';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- --------------
Oracle Label Security                                            FALSE
 
SQL> 

 

 

clip_image002

 

 
 
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

 

 

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

 

 

 

 

参考资料:

 

http://www.itpux.com/thread-568-1-1.html

 

ORA-07445 [zllcini] or ORA-04045 in a Database with OLS Set to FALSE (文档 ID 233110.1)