Oracle 9i 升级中的bug-- Sys.Cdc_alter_ctable_before ORA-04020 deadlock detected while trying to lock obj...

在将数据库从9.2.0.6 升级到 9.2.0.8 的过程中，执行utlrp.sql 脚本时,遇到了Oracle 的一个bug。 Oracle 的说法是：

Oracle Server - Enterprise Edition - Version: 9.2.0.8 and later[Release: 9.2 and later ]
Information in this document applies to any platform. This is cause by internal bug 3017048 fixed in 10.1.0.2.

Oracle 9.2以后的版本都有这个bug，直到10.1.0.2 中才fixed.

错误描述：

Researching the issue on ora-4020 and SYS.CDC_CREATE_CTABLE_BEFORE lead to BUG 3228083 which was
experiencing similar problems on the same object. This bug was closed as a duplicate of bug 3017048.

Oracle Server - Enterprise Edition - Version: 9.2.0.1
This problem can occur on any platform.
Checked for relevance on 13-Aug-2009.

Running catalog.sql fails with and ORA-4020
ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE

.
Verified the issue by the created trace file which shows the following:

ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
39039ccd8 3892cf7f0 38b77a680 X 3892cf7f0 38b470910 X
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
------------- WAITING LOCK -------------
SO: 38b77a680, type: 51, owner: 38aaf3cc0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=38b77a680 handle=39039ccd8 request=X
call pin=0 session pin=0
htl=38b77a6f0[38b470980,38b449af8] htb=38b449af8
user=3892cf7f0 session=3892d0d10 count=0 flags=[00] savepoint=5860866
LIBRARY OBJECT HANDLE: handle=39039ccd8
name=SYS.CDC_ALTER_CTABLE_BEFORE
hash=bae60924 timestamp=03-07-2006 10:29:15
namespace=TRGR flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-00ff-00ff lock=X pin=X latch#=3
lwt=39039cd08[38b77a6a0,38b77a6a0] ltm=39039cd18[39039cd18,39039cd18]
pwt=39039cd38[39039cd38,39039cd38] ptm=39039cdc8[39039cdc8,39039cdc8]
ref=39039cce8[39039cce8, 39039cce8] lnd=39039cde0[39039cde0,39039cde0]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
38b470910 3892cf7f0 3892d0d10 2 X [00]
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
38b77a680 3892cf7f0 3892d0d10 0 X
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
38b472560 3892cf7f0 3892d0d10 0 2 X 00ff
LIBRARY OBJECT: object=3923ed1a8
type=TRGR flags=EXS/LOC/BCM/ALT[0025] pflags=NST [101] status=INVL load=0

This is cause by internal bug 3017048 fixed in 10.1.0.2.

Internal BUG:3017048 - Ora-4020, Functional Index Locking During Invalidation Causing Self-Deadlock

Researching the issue on ora-4020 and SYS.CDC_CREATE_CTABLE_BEFORE lead to BUG 3228083 which was
experiencing similar problems on the same object. This bug was closed as a duplicate of bug 3017048.

Internal BUG:3228083 - Appsst10g:R8:Utlrcmp Error: Ora-04045: Sys.Cdc_Create_Ctable_Before

.

Set the following in the INIT.ORA then restart the database:

_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0

Then rerun CATALOG.SQL.


After creating and running these scripts and the database is ok then restart the database with the parameters taken out of the init.ora.


As an Alternative, if your application is not using CDC, we can disable these triggers as follows:

SQL> conn / as sysdba
SQL> ALTER TRIGGER sys.cdc_alter_ctable_before DISABLE;
SQL> ALTER TRIGGER sys.cdc_create_ctable_after DISABLE;
SQL> ALTER TRIGGER sys.cdc_create_ctable_before DISABLE;
SQL> ALTER TRIGGER sys.cdc_drop_ctable_before DISABLE;

Thiswill alsoprevent the deadlock from occurring.

资料二

Oracle Server - Enterprise Edition - Version: 9.2.0.8 and later[Release: 9.2 and later ]
Information in this document applies to any platform.

Catproc.sql fails with the following error:

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_STANDARD
ORA-04021: timeout occurred while waiting to lock object SYS.CDC_ALTER_CTABLE_BEFORE

Trying tocreate a database from a database that already exists.

1. Modify init.ora to contain:

_system_trig_enabled=FALSE
job_queue_processes=0
aq_tm_processes=0

Save init.ora

(The following are to be completed from sqlplus as the SYS user)

2. Issue a shutdown immediate:
SQL> shutdown immediate

3. SQL>startup pfile='<insert full path of init.ora here>'

4. SQL>@catalog.sql

5. SQL>@catproc.sql

6. Check for invalids from dba_objects:

SQL> select owner, object_name from dba_objects where status='INVALID';

7. Run utlrp.sql:
SQL>@utlrp.sql

8. Check for invalids again.

SQL> select owner, object_name from dba_objects where status='INVALID';

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

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请

最后

以上就是怕黑月饼为你收集整理的Oracle 9i 升级中的bug-- Sys.Cdc_alter_ctable_before ORA-04020 deadlock detected while trying to lock obj...的全部内容，希望文章能够帮你解决Oracle 9i 升级中的bug-- Sys.Cdc_alter_ctable_before ORA-04020 deadlock detected while trying to lock obj...所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。