mysql事务挂起,OGG长事务挂起的处理方案

客户的OGG进程产生告警，发现某个抽取进程虽然是RUNNING状态，但由由于抽取进程延时较大，想尝试重启进程，但正常关闭报下面错误：

Sending STOP request to EXTRACT E_CRM ...

There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT E_CRM, FORCESTOP command.

Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 936072, SCN 3261.2175124252 (14008063476508), RBA 23880208

Redo Thread 2, Redo Log Sequence Number 959952, SCN 3262.51756501 (14010235076053), RBA 29624336.

由于是生产系统，不敢轻易强制关闭进程， 所以先排查那些事务必在跑，用下面的语句查出来。

SELECT s.sid,s.serial#,s.status,s.username,t.start_time,s.WAIT_TIME, s.osuser, s.sql_id, s.program

FROM gv$session s,gv$transaction t

WHERE s.INST_ID=t.INST_ID and s.saddr=t.ses_addr

order by t.start_time desc;

SID SERIAL# STATUS USERNAME START_TIME WAIT_TIME

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

OSUSER SQL_ID PROGRAM

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

168 13155 INACTIVE BOSS_SOS 06/21/16 18:17:57 0

weblogic JDBC Thin Client

3716 52969 ACTIVE BOSS_SOS 06/21/16 18:17:57 0

weblogic dqad3w3jbn16b JDBC Thin Client

2789 20237 INACTIVE BOSS_SOS 06/21/16 18:17:57 0

weblogic JDBC Thin Client

2153 50043 INACTIVE BOSS_SOS 06/21/16 18:17:57 0

weblogic JDBC Thin Client

3850 51307 ACTIVE BOSS_DML 06/21/16 18:17:51 0

root buq03vxw18vm5 cyload@billserv2 (TNS V1-V3)

2715 29803 INACTIVE BOSS_SOS 06/21/16 18:17:43 0

weblogic JDBC Thin Client

1154 35189 INACTIVE BOSS_SOS 06/21/16 18:16:26 0

root O2sndiog@iogserv1 (TNS V1-V3)

1573 40217 KILLED BOSS_DML 06/08/16 11:46:08 0

Administrator datkxm9j9y73s plsqldev.exe

8 rows selected.

如确定事务属于异常挂起，可以回滚，可以直接杀掉该会话：

alter system kill session '1573,40217' immediate;

这个时候，抽取进程可以正常重启，问题解决！！！

©版权声明：本文为天凯DBS的原创文章，转载请附上原文出处链接及本声明，否则将追究法律责任。

更多DBA案例请关注访问天凯DBS！