[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