oracle open hang 等待cursor: pin S wait on X---惜分飞

客户19.3数据库无法在open过程hang住

---

分析alert日志

 

2022-10-18T15:04:57.374918+08:00

db_recovery_file_dest_size of 102400 MB is 9.58% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

2022-10-18T15:09:55.535116+08:00

ORCLPDB(4):>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=40

ORCLPDB(4):System State dumped to trace file /data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_31225.trc

2022-10-18T15:19:33.374783+08:00

ORCLPDB(4):Undo initialization recovery: err:1013 start: 1911760 end: 2790176 diff: 878416 ms (878.4 seconds)

Pdb ORCLPDB hit error 1013 during open read write (1) and will be closed.

这里比较明显,cdb本身open正常,但是其中的ORCLPDB这个pdb无法open,从而显示hang的情况.查询数据库会话情况

SQL> select event,sql_id from v$session where wait_class#<>6;

 

EVENT                                SQL_ID

---------------------------------------------------------------- -------------

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

 

EVENT                                SQL_ID

---------------------------------------------------------------- -------------

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

row cache lock                           8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

SQL*Net message to client                    1dhc13tspcmys

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

 

EVENT                                SQL_ID

---------------------------------------------------------------- -------------

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

cursor: pin S wait on X                      8vyjutx6hg3wh

 

33 rows selected.

 

SQL> select sql_text from v$sql where sql_id='8vyjutx6hg3wh';

 

SQL_TEXT

--------------------------------------------------------------------------------

update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undo

sqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

 

SQL> col machine for a30

SQL> /

 

   INST_ID    SID PADDR        SQL_ID        EVENT              MACHINE  PROGRAM

---------- ---------- ---------------- ------------- ------------------------------ ------------ ------------------------

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

 

   INST_ID    SID PADDR        SQL_ID        EVENT              MACHINE  PROGRAM

---------- ---------- ---------------- ------------- ------------------------------ ------------ -----------------------

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   4517 00000001907FEC50 8vyjutx6hg3wh row cache lock         xifenfei     oracle@xifenfei (P000)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

 

   INST_ID    SID PADDR        SQL_ID        EVENT              MACHINE  PROGRAM

---------- ---------- ---------------- ------------- ------------------------------ ------------ -----------------------

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

     1   5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X        xifenfei     oracle@xifenfei (P002)

 

31 rows selected.

 

SQL> l

  1  select b.INST_ID,b.sid,b.paddr,b.sql_id,b.event,b.MACHINE,b.PROGRAM from gv$session a,gv$session b

  2* where a.event='cursor: pin S wait on X'  and a.FINAL_BLOCKING_INSTANCE=b.INST_ID and

  3* a.FINAL_BLOCKING_SESSION=b.sid

SQL>

通过上述分析,可以确认是在open pdb的过程中被cursor: pin S wait on X等待事件阻塞,而被阻塞的sql是update /*+ rule */ undo$ set …………，基于这样的情况.大概率可以确认是由于bug导致.通过查询mos,确认和Bug 30931981 – Open Reset Logs Hangs With ‘row cache lock’ and ‘cursor: pin s wait for x’ Waits (Doc ID 30931981.8)类似.

---

不过由于客户的版本是19.3,没有对应的小patch发布.通过对相关恢复事务和恢复方式进行处理,在没有对数据库版本进行任何调整的情况下,顺利打开数据库以最快的速度恢复业务