重現Oracle資料庫Hang住的情況

What is the Oracle Diagnostic Methodology (ODM)? [ID 312789.1]

ODM TEST:

查詢語句：

select to_number(addr,'xxxxxxxxxxxxxxxx') from v$latch_parent where name='process allocation';
select name from v$latch_parent where name like '%library%';
select name from v$latch_children where name like '%library%';
select name from v$latch_parent where name like '%process%';

session A:

[oracle@resoft ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 22:52:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select to_number(addr,'xxxxxxxxxxxxxxxx') from v$latch_parent where name='process allocation';

TO_NUMBER(ADDR,'XXXXXXXXXXXXXXXX')
----------------------------------
                         536896888

SQL> select pid,program from v$process;

       PID PROGRAM
---------- ------------------------------------------------
         1 PSEUDO
         2 oracle@resoft (PMON)
         3 oracle@resoft (PSP0)
         4 oracle@resoft (VKTM)
         5 oracle@resoft (GEN0)
         6 oracle@resoft (DIAG)
         7 oracle@resoft (DBRM)
         8 oracle@resoft (DIA0)
         9 oracle@resoft (MMAN)
        10 oracle@resoft (DBW0)
        11 oracle@resoft (LGWR)

       PID PROGRAM
---------- ------------------------------------------------
        12 oracle@resoft (CKPT)
        13 oracle@resoft (SMON)
        14 oracle@resoft (RECO)
        15 oracle@resoft (MMON)
        16 oracle@resoft (MMNL)
        17 oracle@resoft (D000)
        18 oracle@resoft (S000)
        19 oracle@resoft (TNS V1-V3)
        20 oracle@resoft (QMNC)
        21 oracle@resoft (Q000)
        22 oracle@resoft (Q001)

       PID PROGRAM
---------- ------------------------------------------------
        23 oracle@resoft (SMCO)
        24 oracle@resoft (VKRM)
        25 oracle@resoft (CJQ0)
        26 oracle@resoft (W000)
        27 oracle@resoft (TNS V1-V3)
        28 oracle@resoft (W001)

SQL> oradebug setorapid 2;
Oracle pid: 2, Unix process pid: 2770, image: oracle@resoft (PMON)

SQL> oradebug suspend;
Statement processed.

此時仍可以登入成功

[oracle@resoft ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:01:31 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

session A:
SQL> oradebug call kslgetl 536896888 1;
Function returned 1

此時，已經不能使用任何使用者登入。

[oracle@resoft ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:03:37 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

…

釋放的方法：

重新串連一個session B:

[oracle@resoft ~]$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:14:39 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

[oracle@resoft ~]$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:19:47 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

關閉所有session,包括串連的shell ，關閉shell終端串連或者命令視窗即可。

重新串連到資料庫

[oracle@resoft ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:23:45 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  845348864 bytes
Fixed Size                  1348216 bytes
Variable Size             595594632 bytes
Database Buffers          243269632 bytes
Redo Buffers                5136384 bytes
Database mounted.
Database opened.

資料庫hang住時資訊收集：

[oracle@resoft ~]$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 12 23:29:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_4889.trc
接下來的事情就是分析，折騰資料庫了…