[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