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

在将数据库从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.

错误描述：

ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE

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

解决方法， 用spfile 创建pfile, 在pfile里添加如下内容，然后用修改之后的pfile启动数据库，在执行脚本。

_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0

脚本执行完后，在去掉这些参数，正常启动数据库即可。 具体参考下面的2个资料。

资料一

Applies to:

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

Symptoms

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

Cause

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

.

Solution

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.

资料二

Applies to:

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

Symptoms

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

Cause

Trying tocreate a database from a database that already exists.

Solution

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表空间和数据文件的关系，否则拒绝申请