10.2.0.4 RAC 碰到Bug - ORA-04068，ORA-04065，ORA-06508

tolywang

就是这个procedure ,  今天deploy 新的东西，一些procedure 或 table都修改了 ，并且编译了
所有的procedure,  查询这个SQL 发现还是这个出现问题的procedure , 其他procedure都没有。

tolywang

所有开发人员都有debuge any procedure 的权限 。


Debug 权限会否导致procedure 改变属性或导致ORA-04068 ?

tolywang

重新创建这个procedure 的  bak ，名称为  sp_wip_transfer_bak  ， 查询下面的SQL :


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;



还是原来的 sp_wip_transfer ,   _bak 的没有被查询出来 。

不清楚修改名字能不能解决问题 。 或者重新drop 然后创建 。

tolywang

从上面SQL中查出的字段中  D_TYPE = 5 表示 SYNONYM ,   和下面的metalink 文档  ID 835792.1  非常像 。

但是不懂其中的这句话的意思 ：   Already at version 10.2.0.4 which includes fix for Bug 6136074   



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











ORA-4068 Errors for Valid Objects [ID 835792.1]   

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

  修改时间 01-JUN-2009     类型 PROBLEM     状态 MODERATED   

In this Document
  Symptoms
  Cause
  Solution
  References



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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4
This problem can occur on any platform.

Symptoms
-- Problem Statement:
Receiving error ORA-04068: Existing state of packages has been discarded errors even though the packages are valid.

Already at version 10.2.0.4 which includes fix for Bug 6136074

We can see timestamp discrepancies exist for valid objects.

set pagesize 10000
column d_name format a20
column p_name format a20
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;

Example output:
SP_CALCULAR_CUSTO_MEDIO 5 82287 CEQ_SP_CALCULAR_CUSTO_MEDIO 09-ABR-2009 10:22:35 20-MAI-2009 19:59:00 *DIFFER*

SP_ATUALIZA_ESTOQUE 5 82278 CNV_SP_ATUALIZA_ESTOQUE 09-ABR-2009 10:47:14 21-MAI-2009 01:27:43 *DIFFER*



Cause
The objects from the query are not PL/SQL objects but synonyms. (D_TYPE=5)

In this case, we have synonyms which reference parent objects that have different timestamp values.

Solution
-- To implement the solution, please execute the following steps::
Manually drop and recreate the synonyms from list provided from query above and then recheck query for discrepencies.
References
BUG:6136074 - ORA-4068 ORA-4065 ORA-06508 ERRORS SIGNALED FOR VALID OBJECTS
NOTE:456338.1 - ORA-4068 ORA-4065 ORA-06508 Errors Signalled For Valid Objects

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


相关的



--------------------------------------------------------------------------------
产品
--------------------------------------------------------------------------------

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
错误
--------------------------------------------------------------------------------
4068; ERROR; ORA-4068

tolywang

更新一下。


按照最后的文档所说， 删除这个procedure的同义词，然后重新建立，运行Oracle提供的Check SQL,
没有 timestamp value 不一致的记录了 。


希望不要再出现了 。


具体为什么会有不一致 timestamp value ，还不清楚 。

andyxu

晕，我查了一下，库中有1931个。倒了。。。。

再请问一下，owner为public，object_name类似javax/media/jai/widget/JaiI18N、oracle/AQ/AQOracleStructData等这种的，需不需要rebuild synonyms？如果需要，该怎么做呢？谢谢。

inthirties2

辛苦了，lz

inthirties2

搞定没有

tolywang

原帖由 inthirties2 于 2010-2-22 11:06 发表
搞定没有

刪除不一致timstamp 的synonym ， 重新建立后， 查詢oracle提供的sql ， 沒有發現有 不同 timestamp 的objects.  
基本解決 。  不知道还会不会出现，　只有将这个ＳＱＬ　列为维护项目，　每周Ｃｈｅｃｋ一次　。　　

tolywang

原帖由 Yong Huang 于 2010-2-23 01:11 发表
> Debug 权限会否导致procedure 改变属性或导致ORA-04068 ?

That's not possible.

>  Already at version 10.2.0.4 which includes fix for Bug 6136074

The customer's database is already at version 10.2.0.4 (which should have included the fix for the bug) and yet it still has this problem.

> 具体为什么会有不一致 timestamp value ，还不清楚 。

It's an Oracle code bug. Somewhere they should invalidate some dependent objects and they did not.

> 再请问一下，owner为public，object_name类似javax/media/jai/widget/JaiI18N、
> oracle/AQ/AQOracleStructData等这种的，需不需要rebuild synonyms？如果需要，该怎么做呢？

We have these Java class objects shown in the query too. But since we don't use Java, we never had any problem. I don't know if you need to rebuild the synonyms. If I were you, do nothing. Wait and see.

Yong Huang

　　



Ｙｏｎｇ　Ｈｕａｎｇ，　ｔｈａｎｋ　ｙｏｕ　ｖｅｒｙ　ｍｕｃｈ　．　
关于 Java class objects　（ｄｉｆｆｅｒｅｎｔ　ｔｉｍｅｓｔａｍｐ）　， 我们会先放在一边，看看会有什么问题 。


还有一个小问题 ，  ＂The customer's database is already at version 10.2.0.4 (which should have included the fix for the bug) and yet it still has this problem.＂。     在10.2.0.4 库中， 通过Oracle提供的那个SQL 查询到了 dependency problems 的一些Objects ,    不过版本是 10.2.0.2 ，  是否你自己或其他DBA 也在10.2.0.4 版本中碰到过 ORA-04068，ORA-04065，ORA-06508 错误 ？