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

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

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.

Get Oracle Certifications for all Exams
Free Online Exams.com