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

[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