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

相关文章

Python UV安装、升级、卸载详细步骤记录

《PythonUV安装、升级、卸载详细步骤记录》:本文主要介绍PythonUV安装、升级、卸载的详细步骤,uv是Astral推出的下一代Python包与项目管理器,主打单一可执行文件、极致性能... 目录安装检查升级设置自动补全卸载UV 命令总结 官方文档详见:https://docs.astral.sh/

苹果macOS 26 Tahoe主题功能大升级:可定制图标/高亮文本/文件夹颜色

《苹果macOS26Tahoe主题功能大升级:可定制图标/高亮文本/文件夹颜色》在整体系统设计方面,macOS26采用了全新的玻璃质感视觉风格,应用于Dock栏、应用图标以及桌面小部件等多个界面... 科技媒体 MACRumors 昨日(6 月 13 日)发布博文,报道称在 macOS 26 Tahoe 中

华为鸿蒙HarmonyOS 5.1官宣7月开启升级! 首批支持名单公布

《华为鸿蒙HarmonyOS5.1官宣7月开启升级!首批支持名单公布》在刚刚结束的华为Pura80系列及全场景新品发布会上,除了众多新品的发布,还有一个消息也点燃了所有鸿蒙用户的期待,那就是Ha... 在今日的华为 Pura 80 系列及全场景新品发布会上,华为宣布鸿蒙 HarmonyOS 5.1 将于 7

Oracle修改端口号之后无法启动的解决方案

《Oracle修改端口号之后无法启动的解决方案》Oracle数据库更改端口后出现监听器无法启动的问题确实较为常见,但并非必然发生,这一问题通常源于​​配置错误或环境冲突​​,而非端口修改本身,以下是系... 目录一、问题根源分析​​​二、保姆级解决方案​​​​步骤1:修正监听器配置文件 (listener.

MySQL的ALTER TABLE命令的使用解读

《MySQL的ALTERTABLE命令的使用解读》:本文主要介绍MySQL的ALTERTABLE命令的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、查看所建表的编China编程码格式2、修改表的编码格式3、修改列队数据类型4、添加列5、修改列的位置5.1、把列

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》:本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock

ubuntu系统使用官方操作命令升级Dify指南

《ubuntu系统使用官方操作命令升级Dify指南》Dify支持自动化执行、日志记录和结果管理,适用于数据处理、模型训练和部署等场景,今天我们就来看看ubuntu系统中使用官方操作命令升级Dify的方... Dify 是一个基于 docker 的工作流管理工具,旨在简化机器学习和数据科学领域的多步骤工作流。

Oracle 通过 ROWID 批量更新表的方法

《Oracle通过ROWID批量更新表的方法》在Oracle数据库中,使用ROWID进行批量更新是一种高效的更新方法,因为它直接定位到物理行位置,避免了通过索引查找的开销,下面给大家介绍Orac... 目录oracle 通过 ROWID 批量更新表ROWID 基本概念性能优化建议性能UoTrFPH优化建议注

PostgreSQL 序列(Sequence) 与 Oracle 序列对比差异分析

《PostgreSQL序列(Sequence)与Oracle序列对比差异分析》PostgreSQL和Oracle都提供了序列(Sequence)功能,但在实现细节和使用方式上存在一些重要差异,... 目录PostgreSQL 序列(Sequence) 与 oracle 序列对比一 基本语法对比1.1 创建序

Oracle数据库常见字段类型大全以及超详细解析

《Oracle数据库常见字段类型大全以及超详细解析》在Oracle数据库中查询特定表的字段个数通常需要使用SQL语句来完成,:本文主要介绍Oracle数据库常见字段类型大全以及超详细解析,文中通过... 目录前言一、字符类型(Character)1、CHAR:定长字符数据类型2、VARCHAR2:变长字符数