Alter system command hanging on RAC database (Doc ID 2253302.1)

Last updated on APRIL 16, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

"alter system" command is hanging in RAC:


alter system set log_archive_dest_state_3='ENABLE';

---------------------------------------------------------------------------------------------------------------
  ActSes %Thread | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
---------------------------------------------------------------------------------------------------------------
  .66 (66%) | 2 | | | enq: PE - contention | Other



  % Total Total Event Distinct Avg time
  I SID STATE EVENT P1 P2 P3 SEQ# Time Time ms Events ms/Event
--- ------- -------- ----------------------------------- -------------------------- ---------------- ---------------- ---------- ------- ------------ ---------- ----------
  2 2721 WAITING enq: PE - contention name|mode= parno= 0 0= 0 100.00 2900.000 1 2900.000
  0000000050450006



*** 2017-03-26 01:41:30.485
Suspected Hangs in the System
Root Chain Total Hang
Hang Hang Inst Root #hung #hung Hang Hang Resolution
ID Type Status Num Sess Sess Sess Conf Span Action
----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
1 HANG VALID 3 6345 2 2 LOW LOCAL Terminate Process

inst# SessId Ser# OSPID Event
----- ------ ----- --------- -----
3 5589 7 24096 enq: PE - contention
3 6345 1 20905 reliable message

*** 2017-03-26 01:41:30.488
HM: Short Stack of immediate waiter session ID 5589, OSPID 24096 of hang ID 1
Short stack dump:
ksedsts()+380<-ksdxfstk()+52<-ksdxcb()+3592<-sspuser()+140<-__sighndlr()+12<-call_user_handler()+868<-sigacthandler()+92<-_syscall6()+32<-sskgpwwait()+236<-skgpwwait()+176<-ksliwat()+1952<-kslwaitctx()+144<-kjusuc()+6260<-ksipgetctxi()+3852<-ksqcmi()+31152<-ksqgtlctx()+2532<-ksqgelctx()+800<-kspgetpeeq()+332<-kspset0()+1364<-kkyasy()+18812<-kksExecuteCommand()+2380<-opiexe()+15344<-kpoal8()+1872<-opiodr()+1176<-ttcpip()+932<-opitsk()+1728<-opiino()+924<-opiodr()+1176<-opidrv()+1032<-sou2o()+88<-opimai_real()+316<-ssthrdmain()+324<-main()+316<-_start()+380

HM: Short Stack of root session ID 6345, OSPID 20905 of hang ID 1
Short stack dump:
ksedsts()+380<-ksdxfstk()+52<-ksdxcb()+3592<-sspuser()+140<-__sighndlr()+12<-call_user_handler()+868<-sigacthandler()+92<-_syscall6()+32<-sskgpwwait()+236<-skgpwwait()+176<-ksliwat()+1952<-kslwaitctx()+144<-ksrpubwait_ctx()+1116<-kspbcast()+840<-kspset0()+868<-ksksetplaninternal()+948<-jskqRecoverWindow()+1724<-jscrs_select0()+1740<-jscrs_select()+588<-rpiswu2()+604<-kkjcjexe()+868<-ksbcti()+1368<-ksbabs()+1556<-ksbrdp()+1720<-opirip()+1680<-opidrv()+748<-sou2o()+88<-opimai_real()+512<-ssthrdmain()+324<-main()+316<-_start()+380

 

The blocker broadcasted the change and waiting for remote instance(s) to consume. That needs to consumed by remote instance GEN0 process

Process Group: DEFAULT, pseudo proc: 0x50a2158a78
O/S info: user: oracle, term: UNKNOWN, ospid: 17805
OSD pid info: Unix process pid: 17805, image: oracle@ssdb6034 (GEN0)
Short stack dump:
ksedsts()+380<-ksdxfstk()+52<-ksdxcb()+3592<-sspuser()+140<-__sighndlr()+12<-call_user_handler()+868<-sigacthandler()+92<-qetlbr()+280<-qertbFetchByRowID()+2032<-rwsfcd()+116<-qeruaFetch()+412<-qervwFetch()+144<-qerjotRowProc()+336<-qertbFetchByRowID()+2680<-qerjotFetch()+1232<-rwsfcd()+116<-qerhjFetch()+1396<-qersoProcessULS()+216<-qersoFetch()+892<-opifch2()+3048<-opifch()+44<-opiodr()+1176<-rpidrus()+192<-skgmstack()+144<-rpidru()+160<-rpiswu2()+604<-rpidrv()+1496<-rpifch()+72<-kcliprm_action()+1004<-ksbabs()+1348<-ksbrdp()+1720<-opirip()+1680<-opidrv()+748<-sou2o()+88<-opimai_real()+512<-ssthrdmain()+324<-main()+316<-_start()+380
----------------------------------------
SO: 0x4fc829d620, type: 10, owner: 0x5099ca0a90, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
proc=0x5099ca0a90, name=FileOpenBlock, file=ksfd.h LINE:6688 ID:, pg=0
(FOB) 4fc829d620 flags=2560 fib=4ff1974f30 incno=4 pending i/o cnt=0
fname=+SCRM01P_DATA01/scrm01p/datafile/system.465.935583823
fno=1 lblksz=8192 fsiz=512000

GEN0 was going through below stack.

rpifch()+72<-kcliprm_action()+1004<-ksbabs()+1348<-ksbrdp()+1720

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms