Oracle Hang Analyze

Oracle Hang Analyze

很多情况下，Oracle hang导致sqlplus无法连接，从而无法获得Oracle系统和进程状态，可使用sqlplus -prelim选项，在Oracle挂起时依然能使用sqlplus，从而能获得数据库状态，并且可以强制关闭数据库。

跟踪当前会话信息

   oradebug setmypid  --跟踪当前会话
   oradebug setospid  --跟踪系统进程
   oradebug setorapid  --跟踪ORACLE进程
   oradebug unlimit   --取消trace文件大小限制     
   oradebug tracefile_name  --查看trace文件名及位置

单实例：

sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 266
--等一分钟后再次执行hanganalyze分析
oradebug hanganalyze 3
oradebug dump systemstate 266
oradebug tracefile_name

备注：
如果是系统的进程ID，可以使用oradebug setospid id.

如果是根据Oracle ID，可以使用oradebug setorapid id 来追踪。

v$process 下的pid 是Oracle 的ID(orapid)。spid 是系统的ID(ospid)。

获取orapid、ospid

select sid,a.serial#,b.pid orapid,b.spid ospid,b.pname,b.tracefile from v$session a ,v$process b  where sid = 45 and a.paddr = b.addr;

rac环境：

sqlplus -prelim / as sysdba
oradebug setorapname reco
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
--等一分钟后再次执行hanganalyze分析
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug tracefile_name;

场景模拟：
1、使用两个场景做dml操作，会话1创建测试表，在其中一行更新数据不提交。
2、在会话2对表做更新操作
3、在会话3生成trace文件

单实例环境：

session 1

SQL> conn admin/oracle
Connected.
SQL> create table tb_hang(id number,remark varchar2(20));

Table created.

SQL> insert into tb_hang values(1,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> select USERENV('sid') from dual;

USERENV('SID')
--------------
           197

SQL>  update tb_hang set remark='hang1' where id=1;
session1不提交

session 2

SQL> select USERENV('sid') from dual;           

USERENV('SID')
--------------
            67
            
SQL> update tb_hang set remark='hang2' where id=1;
此时session2被hang住

session 3

SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4527.trc
SQL> select USERENV('sid') from dual; 

USERENV('SID')
--------------
           133

生成trace文件

vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4527.trc

被阻塞的会话2（在等待）
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 4456
              process id: 25, oracle@sdw1 (TNS V1-V3)
              session id: 67
        session serial #: 20573
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x30003
                      p3: 'sequence'=0x7b8
            time in wait: 50.200560 sec
           timeout after: never
                 wait id: 42
                blocking: 0 sessions
             current sql: update tb_hang set remark='hang2' where id=1
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpw
wait()+160<-ksliwat()+2022<-kslwaitctx()+163<-ksqcmi()+2848<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit
1()+336<-kdddgb()+8364<-kdusru()+461<-updrowFastPath()+1209<-qerupFetch()+2366<-updaul()+1321<-updThreePhaseExe()+318<-updex
e()+418<-opiexe()+10378<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+26

会话1，正在阻塞其他会话（未提交）
and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 4355
              process id: 19, oracle@sdw1 (TNS V1-V3)
              session id: 197
        session serial #: 5601
    }
    which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x62657100
                      p2: '#bytes'=0x1
            time in wait: 1 min 6 sec
           timeout after: never
                 wait id: 48
                blocking: 1 session
             current sql: <none>
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-read()+14<-ntpfprd()+1
17<-nsbasic_brc()+376<-nsbrecv()+69<-nioqrc()+495<-opikndf2()+978<-opitsk()+831<-opiino()+969<-opiodr()+917<-opidrv()+570<-s
ou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245