重现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
接下来的事情就是分析，折腾数据库了…