[20190211]rac下解鎖應用出現的阻塞.txt

--//別人問的問題,rac環境下應用程式出現阻塞,一般的指令碼已經不適用.因為可能例項1上的連線阻塞例項2的連線.

--//google找到如下連結:http://www.pythian.com/blog/oracle-rac-and-gv-views-a-second-look/

--//我自己修改如下,原連結寫的還是有一些問題:

$ cat viewlockrac1.sql

/* Formatted on 2019/2/11 11:50:46 (QP5 v5.269.14213.34769) */

column WAITER_LOCK_TYPE format a20

column WAITER_MODE_REQ format a20

column kill_command format a60

column EQ_NAME format a30

column REQ_REASON format a50

/* Formatted on 2019/2/11 15:41:07 (QP5 v5.269.14213.34769) */

SELECT gvh.inst_id Locking_Inst

,gvh.sid Locking_Sid

,gvs.serial# Locking_Serial

,gvs.status Status

,gvs.module Module

,gvw.inst_id Waiting_Inst

,gvw.sid Waiter_Sid

,CURSOR

(

SELECT a.eq_name, a.req_reason

FROM V$ENQUEUE_STATISTICS a

WHERE gvh.TYPE = a.eq_type

)

Waiter_Lock_Type

,DECODE

(

gvh.TYPE

,'MR', 'Media_recovery'

,'RT', 'Redo_thread'

,'UN', 'User_name'

,'TX', 'Transaction'

,'TM', 'Dml'

,'UL', 'PLSQL User_lock'

,'DX', 'Distrted_Transaxion'

,'CF', 'Control_file'

,'IS', 'Instance_state'

,'FS', 'File_set'

,'IR', 'Instance_recovery'

,'ST', 'Diskspace Transaction'

,'IV', 'Libcache_invalidation'

,'LS', 'LogStaartORswitch'

,'RW', 'Row_wait'

,'SQ', 'Sequence_no'

,'TE', 'Extend_table'

,'TT', 'Temp_table'

,'Nothing-'

)

Waiter_Lock_Type

,DECODE

(

gvw.request

,0, 'None'

,1, 'NoLock'

,2, 'Row-Share'

,3, 'Row-Exclusive'

,4, 'Share-Table'

,5, 'Share-Row-Exclusive'

,6, 'Exclusive'

,'Nothing-'

)

Waiter_Mode_Req

, 'alter system kill session '

|| ''''

|| gvs.sid

|| ','

|| gvs.serial#

|| ',@'

|| gvs.inst_id

|| ''' immediate ;'

"Kill_Command"

FROM gv$lock gvh, gv$lock gvw, gv$session gvs

WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2

FROM gv$lock

WHERE request = 0

INTERSECT

SELECT id1, id2

FROM gv$lock

WHERE lmode = 0)

AND gvh.id1 = gvw.id1

AND gvh.id2 = gvw.id2

AND gvh.request = 0

AND gvw.lmode = 0

AND gvh.sid = gvs.sid

AND gvh.inst_id = gvs.inst_id;

--//測試看看:

xxxx> @ viewlockrac1

LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS MODULE WAITING_INST WAITER_SID WAITER_LOCK_TYPE WAITER_LOCK_TYPE WAITER_MODE_REQ Kill_Command

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

1 1695 41793 INACTIVE SQL*Plus 2 1657 CURSOR STATEMENT : 8 Transaction Exclusive alter system kill session '1695,41793,@1' immediate ;

CURSOR STATEMENT : 8

EQ_NAME REQ_REASON

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

Transaction contention

Transaction row lock contention

Transaction allocate ITL entry

Transaction index contention

xxxxx> alter system kill session '1695,41793,@1' immediate ;

System altered.

--//實際上一般出現這樣的情況主要都是應用程式設計不合理的問題,我們生產系統也是一樣.我自己生產系統實際應用的指令碼如下:

--//有1次半夜上班起來解鎖,純粹垃圾軟體.沒有辦法寫了自動解鎖的程式碼:

--//實際上執行這樣的程式碼還是要小心再小心(純粹沒有辦法),很無奈...

--//1.假設你要做一個應用程式升級,dml也許要執行很長時間,如果這個時候出現阻塞,把這個dml kill是不對的.

--//2.還有1種情況就是連續阻塞,就是使用者發現應用掛起,他會直接kill,然後在開啟新的應用程式,這樣會出現連續阻塞的情況.這樣要kill許多會話才行.

--//以下指令碼就不合適.必須手工介入解決問題.

$ cat find_lock.sql

SET PAGESIZE 0

SET ECHO OFF HEADING OFF FEED OFF PAGESIZE 0 VERIFY OFF

WHENEVER SQLERROR EXIT FAILURE;

SET SERVEROUTPUT ON;

BEGIN

FOR cursor_x

IN (SELECT DISTINCT

BLOCKER_INSTANCE_ID

,BLOCKER_SID

,BLOCKER_SESS_SERIAL#

, 'alter system kill session '''

|| BLOCKER_SID

|| ','

|| BLOCKER_SESS_SERIAL#

|| ',@'

|| BLOCKER_INSTANCE_ID

|| ''' immediate'

c80

FROM GV$SESSION_BLOCKERS)

LOOP

EXECUTE IMMEDIATE cursor_x.c80;

DBMS_OUTPUT.put_line

(

SYSDATE

|| ' BLOCKER_INSTANCE_ID BLOCKER_SID BLOCKER_SESS_SERIAL# = '

|| cursor_x.BLOCKER_INSTANCE_ID

|| ' '

|| cursor_x.BLOCKER_SID

|| ' '

|| cursor_x.BLOCKER_SESS_SERIAL#

);

END LOOP;

END;

/

SET SERVEROUTPUT OFF;

QUIT

$ cat unlock.sh

#! /bin/bash

# PATH=$PATH:$HOME/bin

export PATH

unset USERNAME

# add by install oracle

# umask 022

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1

export ORACLE_SID=xxxx1

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH24:MI:SS.FF"

export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORA_CRS_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export TEMP=/tmp

export TMPDIR=/tmp

sqlplus -S sys/XXXX as sysdba @/home/oracle/bin/find_lock.sql >> /home/oracle/bin/killsession.txt

echo "OK!"

--//建立crontab如下:

# cat /etc/cron.d/ntp

*/5 * * * * oracle /home/oracle/bin/unlock.sh > /dev/null 2>&1

--//每5分鐘呼叫1次.順便看看放假執行幾次:

# grep "2019-02-0[456789]" killsession.txt |wc

33 297 2924

# grep "2019-02-10" killsession.txt |wc

10 90 890

# grep "2019-01-" killsession.txt |wc

220 1980 19502

[20190211]rac下解鎖應用出現的阻塞.txt

相關文章