11gR2使用oradebug直接读取x$内存表

在11g这个版本中,我们可以使用oradebug中自带的direct_access命令去直接获取X$开头的内存表的数据。那么我们的数据库hang住了,这招还可以使用吗?一直没做过这方面的测试,今天测试了一把,的确是可以的。那以后我们数据库hang住了之后,我们仍然可以使用直读x$表来进行信息的查询。

[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 28 15:00:14 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

SQL> oradebug setorapid 2
Oracle pid: 2, Unix process pid: 20175, image: oracle@11g.localdomain (PMON)
SQL> oradebug suspend;
Statement processed.
SQL> select 'oradebug poke 0x'||addr||' 1 0x01' from v$latch_children where name='shared pool';

'ORADEBUGPOKE0X'||ADDR||'10X01'
---------------------------------------
oradebug poke 0x0000000060107780 1 0x01
oradebug poke 0x00000000601076E0 1 0x01
oradebug poke 0x0000000060107640 1 0x01
oradebug poke 0x00000000601075A0 1 0x01
oradebug poke 0x0000000060107500 1 0x01
oradebug poke 0x0000000060107460 1 0x01
oradebug poke 0x00000000601073C0 1 0x01

7 rows selected.

SQL> oradebug poke 0x0000000060107780 1 0x01
oradebug poke 0x00000000601076E0 1 0x01
oradebug poke 0x0000000060107640 1 0x01
oradebug poke 0x00000000601075A0 1 0x01
oradebug poke 0x0000000060107500 1 0x01
oradebug poke 0x0000000060107460 1 0x01
oradebug poke 0x00000000601073C0 1 0x01BEFORE: [060107780, 060107784) = 00000000
AFTER:  [060107780, 060107784) = 00000001
SQL> BEFORE: [0601076E0, 0601076E4) = 00000000
AFTER:  [0601076E0, 0601076E4) = 00000001
SQL> BEFORE: [060107640, 060107644) = 00000000
AFTER:  [060107640, 060107644) = 00000001
SQL> BEFORE: [0601075A0, 0601075A4) = 00000000
AFTER:  [0601075A0, 0601075A4) = 00000001
SQL> BEFORE: [060107500, 060107504) = 00000000
AFTER:  [060107500, 060107504) = 00000001
SQL> BEFORE: [060107460, 060107464) = 00000000
AFTER:  [060107460, 060107464) = 00000001
SQL> BEFORE: [0601073C0, 0601073C4) = 00000000
AFTER:  [0601073C0, 0601073C4) = 00000001

通过上面的一系列命令,我把PMON完全hang死住了,然后任何的会话都无法再连接上。连接上数据库的session也不能做任何查询。对于这种情况,我们一般采用prelim的方式还是可以进行登陆的。

[oracle@11g ~]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 28 17:33:30 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug direct_access set content_type = 'text/plain';
Statement processed.
SQL> oradebug direct_access select ksusepnm FROM x$ksuse 
ORA-15655: Fixed table "X$KSUSE" cannot be accessed safely in prelim connection.

这里我们可以看到我们使用direct_access去访问内存表的时候报错了,出于安全考虑oracle它无法在prelim的连接下面访问x$这样的内存表。但是在已经连接上的session上面,我们是可以使用direct_access查询的。

SQL> oradebug direct_access select ksusepnm FROM x$ksuse
KSUSEPNM        = oracle@11g.localdomain (J001)
KSUSEPNM        = oracle@11g.localdomain (PMON)
KSUSEPNM        = oracle@11g.localdomain (PSP0)
KSUSEPNM        = oracle@11g.localdomain (VKTM)
分享到: 更多