Tuesday, September 28, 2010

Index on a partitioned table waits on 'row cache lock' in RAC environment

Problem Description
Creating Index on a partitioned table fails with Oracle error ORA-04021. This is a 2-node Oracle RAC environment and in the table there is large number of partitions and sub-partitions. Index creation waits on 'row cache lock'. The same is true while dropping a tablespace. Drop Tablespace hangs on Row Cache Enqueue.

Problem Investigation
From the trace file we get following information.
PROCESS 24:

SO: 0xb9e505908, type: 4, owner: 0xb9f351708, flag: INIT/-/-/0x00
(session) sid: 477 trans: 0xb7ce39798, creator: 0xb9f351708, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0018-00000018, short-term DID: 0001-0018-00000019
txn branch: (nil)
oct: 9, prv: 0, sql: 0xacafc75e8, psql: 0xb0266cc18, user: 55/TA
O/S info: user: oracle, term: pts/1, ospid: 16322, machine: rac2
program: sqlplus@rac2 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'row cache lock' blocking sess=0x(nil) seq=4750 wait_time=0 seconds since wait started=18
cache id=2, mode=0, request=5

Process 24 is executing sql: 0xacafc75e8 which is:
LIBRARY OBJECT HANDLE: handle=acafc75e8 mtx=0xacafc7718(1) cdp=1
name=CREATE INDEX USER_ACTIVITY_IDX ON PROD.USER_ACTIVITY(IMSI, CALLING_IMSI) TABLESPACE HISTORY_IDX_01 LOCAL PARALLEL 32 UNUSABLE

It seems this process is waiting for exclusive enqueue lock mode on dc_segments:
----------------------------------------
SO: 0xafb5989f0, type: 50, owner: 0xb7ce39798, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=0xb9e505908 object=0xb004f3690, request=X
savepoint=0x25a3749
row cache parent object: address=0xb004f3690 cid=2(dc_segments)
hash=2997173a typ=11 transaction=(nil) flags=00000000
own=0xb004f3760[0xb004f3760,0xb004f3760] wat=0xb004f3770[0xafb598a20,0xafb598a20] mode=N
status=-/-/-/-/-/-/-/-/-
request=X release=FALSE flags=2
instance lock id=QC 1cd04d4c 3ab28c03
data=
0000006a 000001da 0039918b 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
----------------------------------------
From the trace file it is not clear who is holding this row cache enqueue.

Cause of the Problem
Index creation waits on 'row cache lock' in RAC environment occurred due to oracle bug.
Oracle named this bug as 'Bug 6321551' in version 10.2.0.2
'Bug 8417354' in version 10.2.0.4
'Bug 6004916' in version 11.0.0.0

Solution of the Problem
The bug is Oracle internal unpublished bug.
- This bug is fixed in Oracle version 10.2.0.5. So apply Oracle 10.2.0.5 patchset if you are running Oracle version 10g.

- If your Oracle database version is 10.2.0.2 or 10.2.0.3 you can apply one-off patch 6004916.

- Another workaround is to create the index with only one node being started (single instance).
i) Stop all RAC instance but one.
ii) Run the index creation script on one node.
iii) After process is completed startup rest of the instances.

No comments:

Post a Comment