重现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终端连接或者命令窗口即可。
重新连接到