今天遇到一个奇怪的问题，3个logic standby 中有一个delay 查看任何日志都没有发现错误，有一个transaction在apply一个SQL，这条SQL没有任何问题，SQL所涉及到的表也没有任何问题
当时的apply情况如下：
SID USERNAME MACHINE EVENT PARAM W WT SQL ST LT LOGON_TIME
—— ———- ———————- —————————— ——————– —- —- —————————- — —— ———-
3222 oracle@db-15 (P000) LogMiner: wakeup event for rea 1/0/0 0 718 / A 1587 1588
3279 oracle@db-15 (P003) LogMiner: wakeup event for pre 1/0/0 0 #### / A 1587 1588
3238 oracle@db-15 (P008) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3237 oracle@db-15 (P011) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3276 oracle@db-15 (P002) LogMiner: wakeup event for pre 1/0/0 0 #### / A 1587 1588
3250 oracle@db-15 (P005) rdbms ipc message 500/0/0 0 #### /05dcutzfrnzmw A 1587 1588
3254 oracle@db-15 (P007) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3256 oracle@db-15 (P010) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3257 oracle@db-15 (P012) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3258 oracle@db-15 (P015) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3262 oracle@db-15 (P014) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3263 oracle@db-15 (P013) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3267 oracle@db-15 (P017) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3269 oracle@db-15 (P016) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3274 oracle@db-15 (P009) rdbms ipc message 500/0/0 0 #### /404qaurwrsnva A 1587 1588
3275 oracle@db-15 (P004) LogMiner: wakeup event for pre 1/0/0 0 #### / A 1587 1588
3246 oracle@db-15 (P006) latch: cache buffers chains 63287313528/124/0 -1 #### bmgkxmt7cybyp/bbjphxukw15ap K 1587 1588
3277 oracle@db-15 (P001) LogMiner: wakeup event for bui 1/0/0 0 814 21wwpjmcv56ds/ A 1587 1588
具体SQL为：
select “PRODUCT_DESCRIPTION”,”SALE_SKILL” from “PROD_DATA2″.”PRO
DUCT” p where(:1=”ID”)for update 我们看到这条SQL带有 for update操作
尝试kill掉这个进程 hang 死:
SQL> alter system kill session ‘3272,4’;
alter system kill session ‘3272,4’
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
尝试kill掉spid
[oracle@db-15 ~]$ kill -9 8823
-bash: kill: (8823) – No such process
尝试手动停止sql apply:
SQL>alter database stop logical standby apply;
依然hang死
所有的进程都在等待这个回话结束but,it’s for update
尝试强制abort这个transaction:
SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
SQL>alter database start logical standby apply immediate;
done.3个小时的delay很快被追上
Stopping SQL Apply on a Logical Standby Database
To stop SQL Apply, issue the following statement on the logical standby database:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. Thus, this command may not stop the SQL Apply processes immediately.
If you want to stop SQL Apply immediately, issue the following statement:
SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
下面做个测试
maybe 仅是个人猜测：备库由PL/SQLDEV 发起的for update查询，但是DBA 没有关闭这个进程：
开启一个新的session:
SQL> update product set id=100000 where id=56;
出现了等待 。也许由于某个DBA 对这个逻辑备库开启了表的for update，但是忘掉了commit 这个操作 it just a guess !
