故事发生平台:
oracle 11.2.0.2 +OGG 11.1.1.1 on windows 2008 64bit
前情提要
用户想删除源库某个需要复制的用户,且源库中配置了并开启了ogg 的ddl复制(为啥这里强调,因为在后面的解决方法中,正是因为某个开启的功能,导致这种错误结果的出现),从而 oracle alert 日志 中以每秒超过100条告警deadlock的速度出现如下报错:
MonMay 20 18:37:52 2013
ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.
ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.
ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.
MonMay 20 18:38:02 2013
ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.
prod_ora_6108.trc :
*** 2013-05-20 16:52:10.475
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0007000d-00072770 34 392 X 34 392 S
session 392: DID 0001-0022-00000399 session 392: DID 0001-0022-00000399
Rows waited on:
Session 392: no row
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=gxvb2kk0wumpb) -----
INSERT INTO "OGG" . "GGS_STICK" (PROPERTY, VALUE) VALUES ('lv_version', :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
00000002F8C0F160 1132 package body OGG.DDLREPLICATION
00000002F9D16BA0 409 anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
process: 0x00000002F63421C8, call: 0x00000002F2EC4788, xact: 0x00000002F224D2C0, curses: 0x00000002F44EA940, usrses: 0x00000002F44EA940
in_exception_handler: no
电视剧中插播”广告“,用户通过pl/sql dev 工具提示:
各位看官看出什么蛛丝马迹(此zsmj非某dota战队的主力),请把视线拉回报错图片上来,结合上述报错:
00000002F8C0F160 1132 package body OGG.DDLREPLICATION
00000002F9D16BA0 409 anonymous block
是不是有所启发?反派人物出场了对吧,ogg的ddl复制产生的lock,还tmd是 anonymous匿名的,ddlreplication 之前配置了什么,启用了什么?
既然你能看到这篇文章,多数人应该是奔着ogg来的,OGG在开ddl复制的最后一步是启用捕捉ddl操作的trigger
GGSCI>@DDL_ENABLE.SQL
#]more ddl_enable.sql 会发现:
....此处省略若干字....
@params
- - enable DDL trigger
alter trigger sys.&ddl_trigger_name enable;
...
现在故事发展到这里,是不是看到几个很醒目的字:sys.ddl_trigger_name,来之与params.sql中定义的define ddl_trigger_name='GGS_DDL_TRIGGER_BEFORE'
那咱就看看这个反派角色到底干了啥?
通过select dbms_metadata.get_ddl('TRIGGER','SYS','GGS_DDL_TRIGGER_BEFORE') from dual;得到的ddl语句中有如下提示:
IF (upper(ora_dict_obj_name) = upper ('GGS_MARKER') OR
upper(ora_dict_obj_name) = upper ('GGS_MARKER_SEQ') OR
upper(ora_dict_obj_name) = upper ('GGS_DDL_TRIGGER_BEFORE') OR
upper(ora_dict_obj_name) = upper ('GGS_DDL_SEQ') OR
upper(ora_dict_obj_name) = upper ('GGS_DDL_HIST') OR
upper(ora_dict_obj_name) = upper ('GGS_DDL_HIST_ALT') OR
upper(ora_dict_obj_name) = upper ('GGS_SETUP')) AND
upper(ora_dict_obj_owner) = upper ('OGG') AND
(upper(ora_sysevent) = upper ('DROP') OR
upper(ora_sysevent) = upper ('RENAME'))
THEN
disallowDDL := 1;
raise_application_error (-20782,
'Cannot DROP object used in Oracle GoldenGate r
eplication while trigger is enabled. ' ||
'Consult Oracle GoldenGate documentation and/or
call Oracle GoldenGate Technical Support if you wish to do so.');
END IF;
在drop用户的时候,跟提示在红色字体部分由冲突,但是O记原厂老张同志说他以前实验中能删除,无解。