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,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 

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

This will also prevent 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 to create 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';

 

 

 

 

 

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

QQ: 492913789
Email: ahdba@qq.com
Blog: http://www.cndba.cn/dave

网上资源: 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的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/874719

相关文章

将Mybatis升级为Mybatis-Plus的详细过程

《将Mybatis升级为Mybatis-Plus的详细过程》本文详细介绍了在若依管理系统(v3.8.8)中将MyBatis升级为MyBatis-Plus的过程,旨在提升开发效率,通过本文,开发者可实现... 目录说明流程增加依赖修改配置文件注释掉MyBATisConfig里面的Bean代码生成使用IDEA生

Oracle存储过程里操作BLOB的字节数据的办法

《Oracle存储过程里操作BLOB的字节数据的办法》该篇文章介绍了如何在Oracle存储过程中操作BLOB的字节数据,作者研究了如何获取BLOB的字节长度、如何使用DBMS_LOB包进行BLOB操作... 目录一、缘由二、办法2.1 基本操作2.2 DBMS_LOB包2.3 字节级操作与RAW数据类型2.

查看Oracle数据库中UNDO表空间的使用情况(最新推荐)

《查看Oracle数据库中UNDO表空间的使用情况(最新推荐)》Oracle数据库中查看UNDO表空间使用情况的4种方法:DBA_TABLESPACES和DBA_DATA_FILES提供基本信息,V$... 目录1. 通过 DBjavascriptA_TABLESPACES 和 DBA_DATA_FILES

MySQL的隐式锁(Implicit Lock)原理实现

《MySQL的隐式锁(ImplicitLock)原理实现》MySQL的InnoDB存储引擎中隐式锁是一种自动管理的锁,用于保证事务在行级别操作时的数据一致性和安全性,本文主要介绍了MySQL的隐式锁... 目录1. 背景:什么是隐式锁?2. 隐式锁的工作原理3. 隐式锁的类型4. 隐式锁的实现与源代码分析4

MySQL中Next-Key Lock底层原理实现

《MySQL中Next-KeyLock底层原理实现》Next-KeyLock是MySQLInnoDB存储引擎中的一种锁机制,结合记录锁和间隙锁,用于高效并发控制并避免幻读,本文主要介绍了MySQL中... 目录一、Next-Key Lock 的定义与作用二、底层原理三、源代码解析四、总结Next-Key L

前端bug调试的方法技巧及常见错误

《前端bug调试的方法技巧及常见错误》:本文主要介绍编程中常见的报错和Bug,以及调试的重要性,调试的基本流程是通过缩小范围来定位问题,并给出了推测法、删除代码法、console调试和debugg... 目录调试基本流程调试方法排查bug的两大技巧如何看控制台报错前端常见错误取值调用报错资源引入错误解析错误

Oracle登录时忘记用户名或密码该如何解决

《Oracle登录时忘记用户名或密码该如何解决》:本文主要介绍如何在Oracle12c中忘记用户名和密码时找回或重置用户账户信息,文中通过代码介绍的非常详细,对同样遇到这个问题的同学具有一定的参... 目录一、忘记账户:二、忘记密码:三、详细情况情况 1:1.1. 登录到数据库1.2. 查看当前用户信息1.

linux环境openssl、openssh升级流程

《linux环境openssl、openssh升级流程》该文章详细介绍了在Ubuntu22.04系统上升级OpenSSL和OpenSSH的方法,首先,升级OpenSSL的步骤包括下载最新版本、安装编译... 目录一.升级openssl1.官网下载最新版openssl2.安装编译环境3.下载后解压安装4.备份

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

PLsql Oracle 下载安装图文过程详解

《PLsqlOracle下载安装图文过程详解》PL/SQLDeveloper是一款用于开发Oracle数据库的集成开发环境,可以通过官网下载安装配置,并通过配置tnsnames.ora文件及环境变... 目录一、PL/SQL Developer 简介二、PL/SQL Developer 安装及配置详解1.下