36、lock_2（锁的兼容性、系统夯住解决办法）
oracle数据库的锁
1、TX
行锁（事务锁）
2、TM
表锁（DML锁）
查询tm、tx锁：
select * from v$lock_type a where a.TYPE in ('TM','TX');
有两个参数：1、ID1：在哪个对象上持有的锁；2、ID2：表上的锁
开始一个事务，没有提交：
[oracle@db11g ~]$ sqlplus u3/u3
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 27 01:01:29 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc t1
Name Null? Type
--------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
SQL> delete from t1;
1 row deleted.
SQL> select sid from v$mystat where rownum=1;
SID
----------
932
查询会话（会话SID：932）的锁：
select * from v$lock l where l.type in ('TM','TX');
ID2 = 0：表示表锁
查询是在哪个对象上持有着锁：
select * from dba_objects o where o.object_id=87413;
持有模式LMODE类型参考下图：
LMODE为6：表示以x方式持有表（被这个事务所阻塞）
事务xid有三部分组成，段号，事务表槽位号，第几次被覆盖：
| 段号，事务表槽位号 | 第几次被覆盖 |
|---|---|
| ID1 | ID2 |
说明：sid为9、18、814的会话，都被ID1:524300 和ID2：10472所决定的事务锁阻塞了
根据ID1计算出事务：
SELECT trunc(262163/power(2,16)) XIDUSN,
bitand(262163,to_number('ffff','xxxx')) XIDSLOT,
t1.id2
FROM v$lock t1
WHERE t1.BLOCK > 0;
查询事务：
select * from v$transaction;
根据SES_ADDR查找，事务所对应的会话的sid：
select * from v$session s where s.saddr='00000000BCA86940';
可以用另一种方式来查看谁被谁阻塞了：
select 'This Session' || ' ' || a.sid || ',' || a.serial# ||
'is blocked by ' || a.BLOCKING_SESSION
from v$session a
where a.BLOCKING_SESSION is not null;
结果：
oracle数据库的锁
一个会话修改表里的一个数据行时，在数据行上加上了一个行锁（RX锁）
另外一个会话想修改相同的行的时候，被锁住；表现为：1号会话所对应的事务，锁住了2号会话所对应的事务（表现为TX锁）
我们在一个表的一个数据行上加了一个行锁，那么Oracle在这个表上加上一个RX锁(这个锁是TM锁)
修改数据行时，加行锁，会在表上加表锁
rx和rx是互相兼容的
共享锁（S锁）：就是在表上加了一个共享锁之后，这时候任何人不能修改这个表里的任何数据，X锁也是一样
锁的兼容性
1、TX：事务锁（作用在行上）
对于事务锁来讲，事务和事务是阻塞的；比如事务1要修改一行数据，事务2也要修改同一行数据，这时候事务1（TX1）阻塞事务2（TX2）；
对于TX锁，只有排他锁，是互斥的（前提条件是修改相同的数据行的时候）
2、TM：表锁（作用在表上）
| 表上操作类型 | 表上锁类型 |
|---|---|
| insert、delete、update | SX锁(原来叫RX锁) |
| for update | SS锁(原来叫RS锁) |
| ddl | X锁 |
| create index | s锁 |
SQL> select * from t3 where id = 1 for update; -- 会在t3表上加ss锁
3、对某个表做了DDL，会导致在表上加上X锁
4、也可以使用lock命令（lock table ... in ...）在表上加上锁
锁的兼容性：
锁的兼容性实验
1、首先在会话上执行回滚操作，将之前所有的锁都释放：
SQL> rollback;
Rollback complete.
2、在表上做insert操作，这时候会在表上加上一个SX锁：
SQL> insert into t1 values(3,'11');
1 row created.
3、查询表锁
select * from v$lock where type='TM';
看到sid为会话27，根据id1:87413 知道是表t1(刚才查过了),根据LMODE是3可以知道是sx锁。
4、在会话2上执行for update
SQL> select sid from v$mystat where rownum=1;
SID
----------
28
SQL> select * from t1;
ID NAME
---------- --------------------
3 11
SQL> select * from t1 where ID=3 for update;
ID NAME
---------- --------------------
3 11
我们知道访问一个表使用for update会在一个表上加ss锁。
我们查看一下：
5、查询表锁
select * from v$lock where type='TM';
可以看到LMODE是3，查看LMODE表可以看到是SX锁，和预想的不太一样（LMODE应该是2），实际上SS和SX是一样的，都是在行上加一个行锁
6、在会话3上执行删除表。可以看到资源正忙，因为这个表正在被其他会话修改（SS和x是不兼容的）。
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
注意：11g新特性。可以设置超时时间：
7、查询参数：ddl_lock_timeout
SQL> show parameter time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
awr_snapshot_time_offset integer 0
control_file_record_keep_time integer 7
cursor_space_for_time boolean FALSE
ddl_lock_timeout integer 0
distributed_lock_timeout integer 60
log_checkpoint_timeout integer 1800
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
parallel_min_time_threshold string AUTO
resumable_timeout integer 0
timed_os_statistics integer 0
timed_statistics boolean TRUE
8、设置会话超时时间为100秒
SQL> alter session set ddl_lock_timeout = 100;
Session altered.
假设要在一个表上加锁，然后给100秒的超时时间
9、再次执行删除表t1的操作
SQL> drop table t1;
10、查询表锁
select * from v$lock where type='TM';
可以看到sid 会话id为18的会话，LMODE为6，查看锁类型为x。
11、在会话4，手动的加RX锁，可以加上（之前的是SX，现在加上一个SS，SX和SS是兼容的，可以加上）
SQL> lock table t1 in row exclusive mode;
Table(s) Locked.
12、使用会话5手动加上一个X锁（排它锁），加不上（X和SX是不兼容的）
SQL> lock table t1 in exclusive mode;
13、会话4转换锁（也就是说，一开始会话4已经持有一个锁了，然后想转成另外一个锁（RX锁转换为X锁），也就是一个转换者队列）
SQL> lock table t1 in row exclusive mode;
Table(s) Locked.
SQL> lock table t1 in exclusive mode;
Table(s) Locked.
14、现在会话将锁转换为X锁，会被前面的SX锁阻塞住
SQL> lock table t1 in exclusive mode;
15、将前面的SX锁释放掉，oracle会优先考虑转换者队列先进去，即使它排在队列的后面
SQL> rollback;
Rollback complete.
将SX锁释放掉之后，会话4（转换者队列）的X锁优先进入了，会话5的X锁还锁着
系统夯住了怎么办
系统夯住了，一般有两种情况：
1、所有的会话都在等一个资源（会话占用同一个资源），无法获取到资源
2、所有的会话（或者少数会话）被一个会话锁住了
夯住了表现为：登录不进去了，或者登陆进去什么都干不了了，执行什么都挂在那
紧急处理：我们都是将数据库重启
解决夯住的办法：
hang：
1、都在等待一个资源
2、被另一个会话锁住
夯住的解决办法：
1、sqlplus登陆，查询
2、hanganalyze、sysstate dump
模拟一个系统夯住的情况
1、四个会话都执行删除t1表，都被阻塞住了
delete from t1;
2、查到进程号，直接用操作系统杀死进程
查看谁被谁阻塞了：
select 'This Session' || ' ' || a.sid || ',' || a.serial# ||
' is blocked by ' || a.BLOCKING_SESSION
from v$session a
where a.BLOCKING_SESSION is not null;
查询结果：
三个会话都被17阻塞住了
解决被夯住的办法：
办法1：（kill -9）
1、查询会话sid为17的会话进程
select spid from v$process a,v$session b where a.addr=b.paddr and b.sid=17;
结果：
2、杀掉进程号为18136的进程
[root@db11g ~]# ps -ef | grep 18136
oracle 18136 18135 0 Apr26 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 18558 18538 0 00:10 pts/6 00:00:00 grep 18136
[root@db11g ~]# kill -9 18136
办法2：（oradebug）
1、使用oradebug
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
SETORAPNAME <orapname> Set Oracle process name to debug
SHORT_STACK Get abridged OS stack
CURRENT_SQL Get current SQL
DUMP <dump_name> <lvl> [addr] Invoke named dump
PDUMP [interval=<interval>] Invoke named dump periodically
[ndumps=<count>] <dump_name> <lvl> [addr]
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE <address> <type> <count> Print/dump an address with type info
SETVAR <p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
SETTRACEFILEID <identifier name> Set tracefile identifier
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return output
SETINST <instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double quotes
DMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len> <self|exist|all|target> Watch a region of memory
DELETE <local|global|target> watchpoint <id> Delete a watchpoint
SHOW <local|global|target> watchpoints Show watchpoints
DIRECT_ACCESS <set/enable/disable command | select query> Fixed table access
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
CALL [-t count] <func> [arg1]...[argn] Invoke function with arguments
CORE Dump core without crashing process
PROCSTAT Dump process statistics
2、oradebug setmypid：以当前会话登陆做数据分析
SQL> oradebug setmypid
Statement processed.
3、oradebug unlimit不限制文件的大小
SQL> oradebug unlimit
Statement processed.
oradebug unlimit含义：一个数据库实例，以当前的会话登陆之后，做一个debug，然后会把数据库当前登录的所有会话dump出来，dump出来，文件可能有点大，oradebug unlimit就是在dump的时候不要限制文件的大小，要多大有多大，dump出来的文件很大也不要管它
4、查看dumplist，dump可以看下列的信息：
SQL> oradebug dumplist
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
SQLNET_SERVER_TRACE
CROSSIC
CONTEXTAREA
HANGDIAG_HEADER
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
REALFREEDUMP
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
KSTDUMPALLPROCS_CLUSTER
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE
HANGANALYZE_PROC
HNGDET_MEM_USAGE_DUMP
DEAD_CLEANUP_STATE
HANGANALYZE_GLOBAL
GES_STATE
CGS
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
GIPC
MMAN_ALLOC_MEMORY
MMAN_CREATE_DEF_REQUEST
MMAN_CREATE_IMM_REQUEST
MMAN_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
GLOBAL_BUFFER_DUMP
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
KCB_WORKING_SET_DUMP
KCBS_ADV_INT_DUMP
KCBI_DUMP_FREELIST
SCN_AUTO_ROLLOVER_TS_OVERRIDE
REDOLOGS
ARCHIVE_ERROR
LOGHIST
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_READ_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_CORRUPT_REPEAT
KRB_CORRUPT_OFFSET
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRB_OVERWRITE_ACTION
KRB_CORRUPT_SPHEADER_INTERVAL
KRB_CORRUPT_SPHEADER_REPEAT
KRB_CORRUPT_SPBITMAP_INTERVAL
KRB_CORRUPT_SPBITMAP_REPEAT
KRB_CORRUPT_SPBAD_INTERVAL
KRB_CORRUPT_SPBAD_REPEAT
KRB_UNUSED_OPTION
KRBMRSR_LIMIT
KRBMROR_LIMIT
KRBABR_TRACE
KRDRSBF
KRC_TRACE
KRA_OPTIONS
KRA_TRACE
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
KTPR_DEBUG
DUMP_TEMP
DROP_SEGMENTS
TEST_SPACEBG
TREEDUMP
LONGF_CREATE
KDLIDMP
ROW_CACHE
LIBRARY_CACHE
LIBRARY_CACHE_OBJECT
CURSORDUMP
CURSORTRACE
CURSOR_STATS
XS_SESSION_STATE
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
KXFRHASHMAP
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
ASM_EVENREAD
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
ASHDUMPSECONDS
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST
5、以级别3做一个夯分析
oradebug dump hanganalyze 3
SQL> oradebug dump hanganalyze 3
Statement processed.
6、查看dump出来的文件名、所在位置
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18596.trc
7、查看文件内容
[oracle@db11g ~]$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18596.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18596.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: db11g
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 18596, image: oracle@db11g (TNS V1-V3)
*** 2017-04-27 00:14:52.575
*** SESSION ID:(927.2395) 2017-04-27 00:14:52.575
*** CLIENT ID:() 2017-04-27 00:14:52.575
*** SERVICE NAME:(SYS$USERS) 2017-04-27 00:14:52.575
*** MODULE NAME:(sqlplus@db11g (TNS V1-V3)) 2017-04-27 00:14:52.575
*** ACTION NAME:() 2017-04-27 00:14:52.575
......
......
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): orcl.orcl
oradebug_node_dump_level: 3
os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [ 00:27:07 ]
NOTE: scheduling delay has not been sampled for 0.385974 secs
0.000000 secs from [ 00:27:03 - 00:27:08 ], 5 sec avg
0.000000 secs from [ 00:26:07 - 00:27:08 ], 1 min avg
0.000000 secs from [ 00:22:08 - 00:27:08 ], 5 min avg
vktm time drift history
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 1 Signature Hash: 0x163c4cba
[b] Chain 2 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 2 Signature Hash: 0x163c4cba
[c] Chain 3 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 3 Signature Hash: 0x163c4cba
Chain：意思就是，假设有8个会话，其中前四个会话被一个会话阻塞住了，第五个会话正在等待资源，最后三个会话被另外一个会话阻塞住了，这里就表现为三个Chain
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 18357
process id: 34, oracle@db11g (TNS V1-V3)
session id: 8
session serial #: 643
}
is waiting for 'enq: TM - contention' with wait info:
{
p1: 'name|mode'=0x544d0006
p2: 'object #'=0x15575 -- 等待的事务
p3: 'table/partition'=0x0
time in wait: 21 min 35 sec
timeout after: never
wait id: 30
blocking: 0 sessions
wait history:
* time between current wait and wait #1: 0.000143 sec
1. event: 'enq: TM - contention'
time waited: 3.481145 sec
wait id: 29 p1: 'name|mode'=0x544d0003
p2: 'object #'=0x15575
p3: 'table/partition'=0x0
* time between wait #1 and #2: 0.000387 sec
2. event: 'SQL*Net message from client'
time waited: 0.927711 sec
wait id: 28 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000054 sec
3. event: 'SQL*Net message to client'
time waited: 0.000019 sec
wait id: 27 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 18136
process id: 22, oracle@db11g (TNS V1-V3)
session id: 17
session serial #: 2915
}
which is waiting for 'SQL*Net message from client' with wait info: -- 17一直在闲着，等待客户端发送命令
{
p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
time in wait: 20 min 57 sec
timeout after: never
wait id: 52
blocking: 3 sessions
wait history:
* time between current wait and wait #1: 0.000021 sec
1. event: 'SQL*Net message to client'
time waited: 0.000016 sec
wait id: 51 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000756 sec
2. event: 'SQL*Net message from client'
time waited: 37.989932 sec
wait id: 50 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000012 sec
3. event: 'SQL*Net message to client'
time waited: 0.000007 sec
wait id: 49 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 1 Signature Hash: 0x163c4cba
-------------------------------------------------------------------------------
===============================================================================
Intersecting chains:
-------------------------------------------------------------------------------
Chain 2:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 18003
process id: 30, oracle@db11g (TNS V1-V3)
session id: 27
session serial #: 1701
}
is waiting for 'enq: TM - contention' with wait info:
{
p1: 'name|mode'=0x544d0006
p2: 'object #'=0x15575
p3: 'table/partition'=0x0
time in wait: 21 min 35 sec
timeout after: never
wait id: 74
blocking: 0 sessions
wait history:
* time between current wait and wait #1: 0.000076 sec
1. event: 'enq: TM - contention'
time waited: 15.762422 sec
wait id: 73 p1: 'name|mode'=0x544d0003
p2: 'object #'=0x15575
p3: 'table/partition'=0x0
* time between wait #1 and #2: 0.005536 sec
2. event: 'SQL*Net message from client'
time waited: 18 min 15 sec
wait id: 72 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000003 sec
3. event: 'SQL*Net message to client'
time waited: 0.000012 sec
wait id: 71 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by 'instance: 1, os id: 18136, session id: 17',
which is a member of 'Chain 1'.
Chain 2 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 2 Signature Hash: 0x163c4cba
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Chain 3:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 18092
process id: 36, oracle@db11g (TNS V1-V3)
session id: 28
session serial #: 2001
}
is waiting for 'enq: TM - contention' with wait info:
{
p1: 'name|mode'=0x544d0006
p2: 'object #'=0x15575
p3: 'table/partition'=0x0
time in wait: 21 min 35 sec
timeout after: never
wait id: 36
blocking: 0 sessions
wait history:
* time between current wait and wait #1: 0.000086 sec
1. event: 'enq: TM - contention'
time waited: 13.212338 sec
wait id: 35 p1: 'name|mode'=0x544d0003
p2: 'object #'=0x15575
p3: 'table/partition'=0x0
* time between wait #1 and #2: 0.000420 sec
2. event: 'SQL*Net message from client'
time waited: 18 min 8 sec
wait id: 34 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000019 sec
3. event: 'SQL*Net message to client'
time waited: 0.000014 sec
wait id: 33 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by 'instance: 1, os id: 18136, session id: 17',
which is a member of 'Chain 1'.
Chain 3 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 3 Signature Hash: 0x163c4cba
-------------------------------------------------------------------------------
===============================================================================
Extra information that will be dumped at higher levels:
[level 4] : 1 node dumps -- [LEAF] [LEAF_NW]
[level 5] : 3 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[7]/1/8/643/0xbcac09e0/18357/NLEAF/[16][27][26]
[16]/1/17/2915/0xbcaa5200/18136/LEAF/
[26]/1/27/1701/0xbca86940/18003/NLEAF/[16][27]
[27]/1/28/2001/0xbca83860/18092/NLEAF/[16]
*** 2017-04-27 00:27:07.698
===============================================================================
END OF HANG ANALYSIS
===============================================================================
*** 2017-04-27 00:27:07.700
===============================================================================
HANG ANALYSIS DUMPS: oradebug_node_dump_level: 3
===============================================================================
State of LOCAL nodes([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[7]/1/8/643/0xbcac09e0/18357/NLEAF/[16][27][26]
[16]/1/17/2915/0xbcaa5200/18136/LEAF/
[26]/1/27/1701/0xbca86940/18003/NLEAF/[16][27]
[27]/1/28/2001/0xbca83860/18092/NLEAF/[16]
No processes qualify for dumping.
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================
*** 2017-04-27 00:27:07.701
Oradebug command 'dump hanganalyze 3' console output: <none>
*** 2017-04-27 00:27:51.527
Processing Oradebug command 'tracefile_name'
*** 2017-04-27 00:27:51.527
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18596.trc
办法3：如果sqlplus登陆不进去的时候，使用：sqlplus -prelim / as sysdba
我们知道当一个数据库hang住时,最头痛的问题是无法登陆数据，也就无法进行故障的处理,因此很多人只能通过重启
操作系统来讲解决问题，其实从Oracle 10g开始,Oracle提供了prelim的登陆方式，如下:
sqlplus -prelim / as sysdba -- 这样登录，不登录数据库，但是产生一个进程
然后也是用oradebug：
oradebug setospid
oradebug unlimit
oradebug dump systemstate 10
还有就是，如果不愿意输oradebug的话，直接使用下面的命令（前提是必须登录数据库）：
SQL> alter session set events 'immediate trace name systemstate level 10';
SQL> alter session set events 'immediate trace name hanganalyze level 3';
如果不登录数据库，会报如下错误：
SQL> alter session set events 'immediate trace name hanganalyze level 3';
alter session set events 'immediate trace name hanganalyze level 3'
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
oradebug可以不需要登录数据库：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump hanganalyze 3
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19819.trc
办法4：如果使用sqlplus -prelim / as sysdba还是登录不了，使用gdb
在aix平台下，我们使用dbx
linux下，使用gdb
1、使用gdb，我们要dump，必须找一个会话：
[root@db11g ~]# ps -ef | grep LOCAL
oracle 19256 19255 0 03:03 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19306 19305 0 03:03 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19353 19352 0 03:03 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19401 19400 0 03:03 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19449 1 0 03:04 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 19451 1 0 03:04 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 19819 19818 0 03:46 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19894 19893 0 03:55 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 19918 19898 0 03:56 pts/1 00:00:00 grep LOCAL
[root@db11g ~]# date
Thu Apr 27 03:56:37 CST 2017
2、我们在使用gdb之前，先给gdb加上一个s位权限，这样执行这个命令，就会获得这个命令的相关权限（或者配oracle的sudu权限，它能够执行gdb）
[root@db11g ~]# cd /usr/bin
[root@db11g bin]# ll gdb
-rwxr-xr-x 1 root root 4493600 Apr 24 2013 gdb
[root@db11g bin]# chmod +s gdb
[root@db11g bin]# ll gdb
-rwsr-sr-x 1 root root 4493600 Apr 24 2013 gdb
或者配oracle的sudu：
[root@db11g bin]# oracle sudu gdb
3、通过一个会话，dump整个数据库
[oracle@db11g ~]$ gdb $ORACLE_HOME/BIN/ORACLE 19894 -- 通过19894，对整个数据库做一个dump
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-60.el6_4.1)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>…
/u01/app/oracle/product/11.2.0/db_1/BIN/ORACLE: No such file or directory.
Attaching to process 19894
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/bin/oracle…(no debugging symbols found)...done.
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libodm11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libodm11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libcell11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libcell11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libskgxp11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libskgxp11.so
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libclsra11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libclsra11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libdbcfg11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libdbcfg11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libhasgen11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libhasgen11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libocr11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libocr11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libocrb11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libocrb11.so
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libocrutl11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libocrutl11.so
Reading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib64/libnuma.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnuma.so.1
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /u01/app/oracle/product/11.2.0/db_1/lib/libnque11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0/db_1/lib/libnque11.so
0x0000003eab60e740 in __read_nocancel () from /lib64/libpthread.so.0
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.132.el6.x86_64 libaio-0.3.107-10.el6.x86_64 numactl-2.0.7-8.el6.x86_64
(gdb) print ksudss(10) -- 以级别10做一个systemstate的dump
$1 = 0
(gdb) detach -- dump完了之后，结束dump
Detaching from program: /u01/app/oracle/product/11.2.0/db_1/bin/oracle, process 19894
(gdb)
4、查看dump出来的文件
[oracle@db11g ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@db11g trace]$ ll *19894*
-rw-r----- 1 oracle oinstall 1098104 Apr 27 04:12 orcl_ora_19894.trc
-rw-r----- 1 oracle oinstall 158001 Apr 27 04:12 orcl_ora_19894.trm
[oracle@db11g trace]$ vi orcl_ora_19894.trc
发现这个文件很难看懂，使用ass109.awk工具将dump出来的文件格式化一下
将文件复制到/u01/app/oracle/diag/rdbms/oracl/oracl/trace目录下
[oracle@oracle trace]$ pwd
/u01/app/oracle/diag/rdbms/oracl/oracl/trace
[oracle@oracle trace]$ ls ass109.awk
ass109.awk
[oracle@oracle trace]$ awk -f ass109.awk oracl_ora_19894.trc --进行格式化
查看文件内容：
40: 0: waiting for 'Streams AQ: qmn slave idle wait'
41: 9: waited for 'Streams AQ: waiting for time management or cleanup tasks'
42: 0: waiting for 'SQL*Net message from client'
44: 0: waiting for 'rdbms ipc message'
45: 0: waiting for 'rdbms ipc message'
47: 0: waiting for 'enq: TX - row lock contention'[Enqueue TX-00080011-000028F5]
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.
Resource Holder State
Enqueue TX-00080011-000028F5 42: 0: waiting for 'SQL*Net message from client' --42号持有这个事务
Object Names
~~~~~~~~~~~~
Enqueue TX-00080011-000028F5
33961 Lines Processed.
[oracle@oracle trace]$ vi oracl_ora_19894.trc
PROCESS 42:
----------------------------------------
SO: 0x94711480, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x94711480, name=process, file=ksu.h LINE:12721, pg=0
(process) Oracle pid:42, ser:2, calls cur/top: (nil)/0x70dde2c8
flags : (0x0) -
flags2: (0x1000), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 9
last post received-location: ksq.h LINE:2015 ID:ksqrcl
last process to post me: 0x94712538 13 0
last post sent: 0 0 9
last post sent-location: ksq.h LINE:2015 ID:ksqrcl
last process posted by me: 0x94716818 32 0
(latch info) wait_event=0 bits=0x0
Process Group: DEFAULT, pseudo proc: 0x943696a8
O/S info: user: oracle, term: UNKNOWN, ospid: 2989
OSD pid info: Unix process pid: 2989, image: oracle@oracle (TNS V1-V3)
/pid:42 --查找pid为42的操作系统的pid
查看操作系统层面是否有这个2989的进程呢:：
[oracle@oracle trace]$ ps -ef |grep 2989
oracle 2989 2988 0 14:27 ? 00:00:01 oracleoracl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 5788 5479 0 20:46 pts/4 00:00:01 gdb /u01/app/oracle/product/11.2.0/db_1/bin/oracle 2989
oracle 5873 5564 0 20:59 pts/5 00:00:00 grep 2989
查看这个进程到底是不是2989：
select spid from v$process a,v$session b where a.addr=b.paddr and b.sid=42;
最后kill -9将进程杀死：
[oracle@db11g ~]$ kill -9 2989
=======================================================================================================================
修改一个数据行，在数据行上加上了一个行锁
另外一个会话想修改相同的行的时候，被锁住，表现为
1号会话所对应的事务锁住了2号会话所对应的事务
TX锁
我们在一个表的一个数据行上加了一个行锁，那么Oracle在这个表上加上一个RX锁?
SELECT trunc(t1.ID1 / power(2, 16)) XIDUSN,
bitand(t1.id1, to_number('ffff', 'xxxx')) XIDSLOT,
t1.id2
FROM v$lock t1
WHERE t1.BLOCK > 0
select 'This Session' || ' ' || a.sid || ',' || a.serial# ||
' is blocked by ' || a.BLOCKING_SESSION
from v$session a
where a.BLOCKING_SESSION is not null;
SQL> oradebug help
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump HANGANALYZE 3
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/jinshang/jinshang/trace/jinshang_ora_5018.trc
SQL>
SQL> oradebug dumplist
SQL> oradebug dump SYSTEMSTATE 10
Statement processed.
SQL> alter session set events 'immediate trace name systemstate level 10';
Session altered.
SQL> alter session set events 'immediate trace name hanganalyze level 3';
Session altered.
SQL>
对于rac
-
ALTER SESSION SET EVENTS ‘immediate trace name HANGANALYZE level
’; -
使用oradebug 命令
ORADEBUG setmypid
ORADEBUG setinst all
ORADEBUG -g def hanganalyze—针对rac的用法
对于单实例，我们通常进行如下操作即可:
oradebug setmypid
oradebug hanganalyze 3
其次在做hang分析的时候,建议同时做一个systemstate dump或针对个别的process进行processstate dump,如下：
---systemstate dump
oradebug setmypid
oradebug unlimit
oradebug dump systemstate level 2;
oradebug close_trace
oradebug tracefile_name
---processstate dump
oradebug setospid xxxx
oradebug dump processstate level 3;
oradebug close_trace
oradebug tracefile_name
我们知道当一个数据库hang住时,最头痛的问题是无法登陆数据，也就无法进行故障的处理,因此很多人只能通过重启
操作系统来讲解决问题，其实从Oracle 10g开始,Oracle提供了prelim的登陆方式，如下:
sqlplus -prelim / as sysdba
oradebug setospid
oradebug unlimit
oradebug dump systemstate 10
退一万步讲，即使我们无法通过SQLPLUS登陆数据库，仍然还是可以从操作系统层面入手的，
即通过操作系统的命令来
对进程进行dump,例如aix环境中可以使用dbx命令来dump processstate，
如下：
dbx -a PID (where PID = any oracle shadow process)
—通过ps -ef|grep xxx查看
dbx() print ksudss(10)
…return value printed here
dbx() detach
[oracle@rac11g trace]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 11 11:10:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
[root@rac11g ~]# cd /usr
[root@rac11g usr]# cd bin
[root@rac11g bin]# ll gdb
-rwxr-xr-x 1 root root 4216824 Jun 29 2012 gdb
[root@rac11g bin]# chmod +s gdb
gdb gdbserver gdbtui
[root@rac11g bin]# chmod +s gdb
[root@rac11g bin]# ll gdb
-rwsr-sr-x 1 root root 4216824 Jun 29 2012 gdb
[root@rac11g bin]#
[oracle@rac11g ~]$ ps -ef|grep LOCAL
oracle 5421 5420 0 11:12 ? 00:00:00 oraclejinshang (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
gdb $ORACLE_HOME/bin/oracle 5421
(gdb) print ksudss(10)
$1 = 0
(gdb) detach
Detaching from program: /u01/app/oracle/product/11.2.0/db_1/bin/oracle, process 5421
(gdb)
[oracle@rac11g trace]$ awk -f ass109.awk jinshang_ora_5421.trc
[oracle@rac11g trace]$ vi jinshang_ora_5421.trc
SQL> alter database add supplemental log data;
除了通过print ksudss(10)进行systemstate dump，还可以进行下面的dump
print ksdhng(3,1,0) 相当于oradebug hanganalyze 3
print ksudps(10) 相当于oradebug dump processstate 10
print curdmp() 相当于oradebug call curdmp（也就是oradebug dump cursordump)
print ksdtrc(4) 相当于oradebug dump events 4（这里参数表示level,1–session,2–process,4–system)
print ksdsel(10046,12) –相当于为attach的进程设置10046事件level 12
print skdxipc() –相当于oradebug ipc
print skdxprst() –相当于oradebug procstat
[root@rac11g ~]# strace -p 5818 -o 5815.trc
ddl_lock_timeout
alter session set ddl_lock_timeout=5;
LOCK TABLE … IN lockmode MODE [NOWAIT | WAIT integer]
SQL> oradebug help
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump HANGANALYZE 3
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/jinshang/jinshang/trace/jinshang_ora_5018.trc
SQL>
SQL> oradebug dumplist
SQL> oradebug dump SYSTEMSTATE 10
Statement processed.
SQL> alter session set events 'immediate trace name systemstate level 10';
Session altered.
SQL> alter session set events 'immediate trace name hanganalyze level 3';
Session altered.
SQL>
对于rac
- ALTER SESSION SET EVENTS ‘immediate trace name HANGANALYZE level
’; - 使用oradebug 命令
ORADEBUG setmypid
ORADEBUG setinst all
ORADEBUG -g def hanganalyze—针对rac的用法
对于单实例，我们通常进行如下操作即可:
oradebug setmypid
oradebug hanganalyze 3
其次在做hang分析的时候,建议同时做一个systemstate dump或针对个别的process进行processstate dump,如下：
---systemstate dump
oradebug setmypid
oradebug unlimit
oradebug dump systemstate level 2;
oradebug close_trace
oradebug tracefile_name
---processstate dump
oradebug setospid xxxx
oradebug dump processstate level 3;
oradebug close_trace
oradebug tracefile_name
我们知道当一个数据库hang住时,最头痛的问题是无法登陆数据，也就无法进行故障的处理,因此很多人只能通过重启
操作系统来讲解决问题，其实从Oracle 10g开始,Oracle提供了prelim的登陆方式，如下:
sqlplus -prelim / as sysdba
oradebug setospid
oradebug unlimit
oradebug dump systemstate 10
退一万步讲，即使我们无法通过SQLPLUS登陆数据库，仍然还是可以从操作系统层面入手的，
即通过操作系统的命令来
对进程进行dump,例如aix环境中可以使用dbx命令来dump processstate，
如下：
dbx -a PID (where PID = any oracle shadow process)
—通过ps -ef|grep xxx查看
dbx() print ksudss(10)
…return value printed here
dbx() detach
[oracle@rac11g trace]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 11 11:10:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
[root@rac11g ~]# cd /usr
[root@rac11g usr]# cd bin
[root@rac11g bin]# ll gdb
-rwxr-xr-x 1 root root 4216824 Jun 29 2012 gdb
[root@rac11g bin]# chmod +s gdb
gdb gdbserver gdbtui
[root@rac11g bin]# chmod +s gdb
[root@rac11g bin]# ll gdb
-rwsr-sr-x 1 root root 4216824 Jun 29 2012 gdb
[root@rac11g bin]#
[oracle@rac11g ~]$ ps -ef|grep LOCAL
oracle 5421 5420 0 11:12 ? 00:00:00 oraclejinshang (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
gdb $ORACLE_HOME/bin/oracle 5421
(gdb) print ksudss(10)
$1 = 0
(gdb) detach
Detaching from program: /u01/app/oracle/product/11.2.0/db_1/bin/oracle, process 5421
(gdb)
[oracle@rac11g trace]$ awk -f ass109.awk jinshang_ora_5421.trc
[oracle@rac11g trace]$ vi jinshang_ora_5421.trc
SQL> alter database add supplemental log data;
除了通过print ksudss(10)进行systemstate dump，还可以进行下面的dump
print ksdhng(3,1,0) 相当于oradebug hanganalyze 3
print ksudps(10) 相当于oradebug dump processstate 10
print curdmp() 相当于oradebug call curdmp（也就是oradebug dump cursordump)
print ksdtrc(4) 相当于oradebug dump events 4（这里参数表示level,1–session,2–process,4–system)
print ksdsel(10046,12) –相当于为attach的进程设置10046事件level 12
print skdxipc() –相当于oradebug ipc
print skdxprst() –相当于oradebug procstat
[root@rac11g ~]# strace -p 5818 -o 5815.trc
