客户的一套数据看12.2.0.1 客户反映连接不上数据库,处理不了更新操作,后处理问题
发现2节点的cpu使用率100%,并且都被crsctl.bin process 占用。
查看数据库等待事件,存在大量的library cache lock
blocking_session为3626,并且等待事件是crs call completion
之前有一个客户遇到类似的问题,是3个节点的第三节点,这次是两个节点的第2节点,不知道是否有巧合,都是instance_number最大的节点。
下面是抛出的堆栈信息:
Short stack dump:
ksedsts <- ksdxfstk <- ksdxcb <- sspuser <- __sighandler
<- __connect <- sgipcnClscIpcConnect <- sgipcnConnect <- gipcmodNetworkProcessConnect <- gipcmodNetworkProcessEndpoint
<- gipcmodNetworkNonBlockComplete <- sgipcnCompletion <- sgipcwWaitHelper <- sgipcwWait <- gipcWaitOsd
<- gipcInternalWait <- gipcWaitF <- prom_waitforconnect <- prom_connect_ext <- prom_connect
<- proac_con_init <- proac_init <- proa_init <- procr_init_ext2 <- procr_init_ext
<- clse_get_auth_loc <- clscrsconGipcConnect <- clscrsconInvokeCmd <- clscrscmd_doAPI_internal <- clscrscmd_get_crsd_version
<- clscrscmd_doAPI3 <- clscrscmd_doAPI2 <- clscrs_stat2 <- clsrapii_stat_reg_attrs <- clsrapii_resattr_by_filter
<- clsrapii_resattr <- clsr_get_db <- clsr_get_db_pwfile <- kjha_get_db_pwfile <- kzsrfpf
<- kzsrlav <- kzsrGetPWFileFormat <- kzsrUpdateUserInfo <- kziaUpdateAcctStat <- kzia_password_lifecycle
<- kziavua <- kpolnb <- kpoauth <- opiodr <- ttcpip
<- opitsk <- opiino <- opiodr <- opidrv <- sou2o
<- opimai_real <- ssthrdmain <- main
-------------------------------------------------------------------------------
Process diagnostic dump actual duration=5.902000 sec
(max dump time=30.000000 sec)
............
SO: 0x4729e9db0, type: 4, owner: 0x4b2163b68, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
proc=0x4b2163b68, name=session, file=ksu.h LINE:15737, pg=0, conuid=0
(session) sid: 3626 ser: 10708 trans: 0x41f900068, creator: 0x4b2163b68
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
0000-0000-000000000002-028B-0000727D DID: , short-term DID:
txn branch: (nil)
edition#: 0 user#/name: 123/ZHDD_MEM
oct: 0, prv: 0, sql: (nil), psql: 0x27ec55258
stats: 0x1c2f3ebe0, PX stats: 0x1101de44
service name: ZDMEMDB
client details:
O/S info: user: grid, term: , ospid: 95791
machine: zd-hdd-db2 program: oracle@zd-hdd-db2 (TNS V1-V3)
application name: xxxxx (TNS V1-V3), hash value=3351328086
Current Wait Stack:
0: waiting for 'CRS call completion'
clsrrestype=0xe, kjha_action=0x6, =0x0
wait_id=9 seq_num=10 snap_id=1
wait times: snap=14 min 56 sec, exc=14 min 56 sec, total=14 min 56 sec
wait times: max=infinite, heur=14 min 56 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0x5a0
There are 3128 sessions blocked by this session.
Dumping one waiter:
inst: 2, sid: 5592, ser: 37199
wait event: 'library cache lock'
p1: 'handle address'=0x2bfb1dbb0
p2: 'lock address'=0x26d7a70d0
p3: '100*mode+namespace'=0x4f0002
row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0
min_blocked_time: 890 secs, waiter_cache_ver: 41547
Wait State:
fixed_waits=0 flags=0x22 boundary=(nil)/-1
Session Wait History:
elapsed time of 0.004458 sec since current wait
0: waited for 'library cache lock'
handle address=0x2bfb1dbb0, lock address=0x129c375c0, 100*mode+namespace=0x4f0003
wait_id=8 seq_num=9 snap_id=1
wait times: snap=0.000622 sec, exc=0.000622 sec, total=0.000622 sec
wait times: max=15 min 0 sec
wait counts: calls=1 os=1
occurred after 0.009263 sec of elapsed time
1: waited for 'PGA memory operation'
=0x10000, =0x1, =0x0
wait_id=7 seq_num=8 snap_id=1
wait times: snap=0.000008 sec, exc=0.000008 sec, total=0.000008 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.001909 sec of elapsed time
2: waited for 'PGA memory operation'
=0x20000, =0x1, =0x0
wait_id=6 seq_num=7 snap_id=1
wait times: snap=0.000009 sec, exc=0.000009 sec, total=0.000009 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.001348 sec of elapsed time
3: waited for 'library cache load lock'
object address=0x2bfb1dbb0, lock address=0x129c37938, 100*mask+namespace=0x4f0003
wait_id=5 seq_num=6 snap_id=1
wait times: snap=0.048842 sec, exc=0.048842 sec, total=0.048842 sec
wait times: max=infinite
wait counts: calls=1 os=1
occurred after 0.000214 sec of elapsed time
......
8: waited for 'PGA memory operation'
=0x10000, =0x1, =0x0
wait_id=0 seq_num=1 snap_id=1
wait times: snap=0.000011 sec, exc=0.000011 sec, total=0.000011 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
.......
4520B78E0 00000000 00000000 [........]
Enqueue blocker waiting on 'CRS call completion'
后开sr,给出的建议是先,先设置如下参数,绕过底层crs就不会在crs call completion,问sr设置有什么不好的地方,回答是go ahead。
KNOWLEDGE
-----------------
Keywords: CRS call completion
- Bug 20581546 : LONG WAIT ON CRS CALL COMPLETION
- Bug 20188979 : RCA FOR CRSD LOCK CAUSING SESSIONS TO ASM TO LOCK-UP
WORKAROUND:
-----------
if setting SQL> alter system set "_notify_crs"=FALSE scope=spfile; skips the
notification to CRS of the change and thus avoids the wait.
restart database;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-2150354/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-2150354/