"gc buffer busy acquire" occurs due to tablespace read only -> read write in a RAC Database Environment (Doc ID 2221999.1)

Last updated on JANUARY 25, 2017

Applies to:

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

Symptoms

The SQL execution session for the target tablespace continues to wait with "gc buffer busy acquire" and "latch: cache buffers chains" after read only -> read write of the tablespace is done.

The following situation can be confirmed when this problem falls under.

1.  The session that executed SQL after changing the tablespace to read write repeatedly waits in "gc buffer busy acquire" and "latch: cache buffers chains".
If the same SQL is executed from multiple sessions for the RAC instance where wait occurred, both sessions will be in the same wait state.

This is the information of HANG ANALYSIS output to the trace file of dia0.

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
----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
2 HANG VALID 4 17413 2 2 LOW LOCAL Terminate Process

inst# SessId Ser# OSPID Event
----- ------ ----- --------- -----
4 1608 257 59201 latch: cache buffers chains <<<<
4 17413 55785 105526 gc buffer busy acquire <<<<

2.  The P1 column (data file number) and P2 column (block number) of V$SESSION and DBA_HIST_ACTIVE_SESS_HISTORY indicating the data block waiting for "gc buffer busy acquire" in each waiting session are the same. Also, the BLOCKING_SESSION column is NULL.

select sid, serial#, event, p1, p2, p3, BLOCKING_SESSION, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, SQL_ID from v$session;

SID SERIAL# EVENT P1 P2 P3 BLOCKING_SESSION ROW_WAIT_FILE# ROW_WAIT_BLOCK# SQL_ID
---------- ---------- --------------------------- ---------- ---------- ---------- ---------------- -------------- --------------- -------------
25 5 latch: cache buffers chains 2191605712 177 0 4 155 5fadjf5jgc5ht
141 5 gc buffer busy acquire 4 155 1 4 155 5fadjf5jgc5ht
148 13 SQL*Net message to client 1650815232 1 0 4 149 5fadjf5jgc5ht

select count(BLOCKING_SESSION) from gv$session where BLOCKING_SESSION is not;

COUNT(BLOCKING_SESSION)
-----------------------
0

3. There is a buffer cache of scur or xcur and read status when confirming GV$BH for the data block to wait for.

SQL> select INST_ID, STATUS from gv$bh
where file#=6 and block#=15947
and status != 'free';

INST_ID STATUS
---------- ----------
2 scur
2 read

4. If you acquire a systemstate dump on a RAC instance where an event occurs, flag information of "flags: logically_flushed" is displayed in the dump of the buffer of scur or xcur status of the target data block.

Confirmation example of systemstate dump of RAC instance where an event occurs (example of scur)

BH (0x88f95c38) file#: 6 rdba: 0x01803e4b (6/15947) class: 1 ba: 0x88c98000 <<<< P1 is 6 and P2 is 15947
set: 5 pool: 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 730556 objn: 730556 tsn: 5 afn: 6 hint: f
hash: [0x9b9d0e58,0x7ef56a30] lru: [0x88f87ce8,0x88f87b48]
ckptq: [NULL] fileq: [NULL] objq: [0x91cff1c8,0x91cff1c8] objaq: [0x91cff1b8,0x91cff1b8]
use: [0xa0e0c400,0xa0e0c400] wait: [NULL]
st: SCURRENT md: SHR fpin: 'qeilwhrp: qeilbk' tch: 0 le: 0x84fad970 <<<< st: SCURRENT(scur)
flags: logically_flushed <<<< flag
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

* In the case where 3. is xcur, st: XCURRENT is displayed.

Changes

It may occur when all of the following conditions are satisfied.

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