ogg进程stop无效，kill假死分析
一、问题背景
ogg延迟过高，经过分析和定位是某些表缺乏主键和索引，走全表扫描，导致ogg的单个事务执行较慢，Time Since Chkpt延迟一直无法降下来给出解决方案是选择合适的列创建索引，在创建索引时，报ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
（前提：stop 复制进程或者 kill复制进程）
一般方案：
1、alter session set ddl_timeout=60;不加online
2、加 online参数在线创建
以上两者在kill ogg复制进程的时候一般都很会失败
二、问题解决
分析堵塞原因：
SQL> select SID,INST_ID,SERIAL#,USERNAME,PROGRAM,EVENT,BLOCKING_SESSION,STATUS from gv$session where USERNAME in ('GOLDENGATE','VERIDATA','C##GGADMIN') GROUP BY SID,INST_ID,SERIAL#,USERNAME,PROGRAM,EVENT,BLOCKING_SESSION,STATUS;
SID INST_ID SERIAL# USERNAME PROGRAM EVENT BLOCKING_SESSION STATUS
---------- ---------- ---------- -------------------- ------------------------------ ------------------------------ ---------------- ------------------------
1415 1 1 GOLDENGATE oracle@hmb (AS05) rdbms ipc message WAITING
2833 1 1 GOLDENGATE oracle@hmb (AS07) gc current grant 2 -way WAITING SHORT TIME
2607 1 1 GOLDENGATE oracle@hmb (AS06) rdbms ipc message WAITING
18 1 3 GOLDENGATE oracle@hmb (AS03) rdbms ipc message WAITING
14125 1 5 GOLDENGATE sqlplus@hmb (AS02) enq:TX - row lock contention 2833 WAITING
145 1 3 GOLDENGATE oracle@hmb (AS04) rdbms ipc message WAITING
6 rows selected.
SQL> select SQL_SID from gv$session where sid=14125;
SQL_SID
---------------
wwwttxxx1725s
SQL>select SQL_TEXT from v$sql where SQL_SID='wwwttxxx1725s';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------
create index oggindex6 on DEMO.DEMOTEST1(ID,FIRSTNUMBER) online;
SQL> select SQL_SID from gv$session where sid=2833;
SQL_SID
---------------
fnps101qm03f6
SQL>select SQL_TEXT from v$sql where SQL_SID='fnps101qm03f6';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------
DELETE /*+restrict_all_ref_cons */ FROM "DEMO"."DEMOTEST1" WHERE ........
DELETE /*+restrict_all_ref_cons */ FROM "DEMO"."DEMOTEST1" WHERE ........
DELETE /*+restrict_all_ref_cons */ FROM "DEMO"."DEMOTEST1" WHERE ........(ogg复制进程做的业务sql)
kill会话：alter system kill session 'SID, SERIAL#,@INST_ID';
SQL> alter system kill session '2833, 40039,@1';
online创建索引成功
