Bug 18223021 : DISK FILE OPERATIONS I/O ON HP-UX.

We have been dealing with a strange ASM  behavior over the past few months across all ASM instances in our multiple Oracle 11gR2 (11.2.0.2) Cluster envs on HPUX 11.31 OS. Even a simple and typical ASM task like, adding new disk to ASM disk group, disk group mount/unmount and querying for CANDIDATE asm disks was taking min of 20 minutes, and sometimes infinity time. This behavior caused a lot of performance degradation across all database instance in the cluster, and most of the databases suffered with 'Disk file operations i/o' wait with other consequences.

I must say, Oracle support indeed made a few unsuccessful attempts by suggesting increasing the ASM instance SGA, enabling async i/o on OS, reducing the number of disks etc to address the issue. Finally, they have logged a BUG '18223021' for our issue and we are yet to receive the fix.

Below are a few consequences of this behavior we are confronting:

• Oracle 10g database instances running on the node where a new ASM disk being added to the existing disk group had suffered with control file locking and database hung issues
• We ensure the disk group to which a new disk is being added is not mounted on non relative ASM instances (in other nodes)
• When adding disk takes infinite time on one of the ASM instances, the only way was to shutdown the instance with dismount the ASM disk group subsequently to speed-up and complete the procedure
• Most databases are suffering with 'Disk File operations I/O'

If you have MOS access, you may refer the BUG for more details.

Stay tuned for the fix and solution...

SELECT FOR UPDATE OF" Hangs in 11.2.0.1

While going through a bug list on metalink I have come across of a ML note: "SELECT FOR UPDATE OF" Hangs in 11.2.0.1 [ID 1081727.1] and thought to blog about it which could be useful for the people who are on 11g or planning to go to 11g. Below is the extract from the note:

Applies to:  Version: 11.2.0.1 - Release: 11.2 applies to any platform. 

Symptoms "SELECT FOR UPDATE OF" Hangs in 11.2.0.1.


SQL:
SELECT table1.rowid
FROM table1 JOIN table2 t2 ON table1.id = t2.id
FOR UPDATE OF table1.text1,table1.text2;

Cause
Bug 9278351 "SELECT FOR UPDATE OF" HANGS IN 11.2.0.1
Base Bug 9294110 LEFT OUTER JOIN WITH FOR UPDATE CLAUSE HANGS FOREVER

The 10046 trace shows high 'cursor: pin S wait on X':
Solution
  Apply Patch 9294110.


happy reading

Jaffar

Oracle bugs - Part 3(GETTING "CONNECTION NOT ACTIVE" IN EVMD.LOG )

I am back again with Part 3 of Oracle bugs series. Although this bug won't bug you much, nevertheless, the frequent appearance of the following message in the evmd.log (under the cluster home) may raise some alarms (doubts) in your mind about your network stability:

2009-11-29 15:34:04.821: [ COMMCRS][1]clscsendx: (6000000000397610) Physical connection (6000000000b09c40) not active
2009-11-29 18:17:41.748: [ COMMCRS][1]clscreceive: (6000000000397910) Connection not active
2009-11-29 18:17:41.748: [ COMMCRS][1]clscreceive: (6000000000397910) Connection not active
2010-01-23 06:51:22.669: [ COMMCRS][1]clscsendx: (6000000000398150) Physical connection (60000000009ff860) not active
2010-01-23 09:43:06.210: [ COMMCRS][1]clscsendx: (6000000000398150) Physical connection (60000000009ff860) not active
2010-01-23 12:52:23.196: [ COMMCRS][1]clscsendx: (60000000003982d0) Physical connection (60000000009ff860) not active

Those warnings are flagged CLSC_TRACE_ALWAYS as indicated in Bug 3251584.
They concerns connections between the evmd daemons and other processes that are disconnected from the point of view of the evmd, i.e. the evmd detect the remote connections are not active anymore, but the remote side has not disconnect the link in a proper way. Hence the warning message. They may not be removed as indicated in Bug 3251584.

Those evmd warning messages can be safely ignored as they are informative only.

Happy reading,

Jaffar

Oracle Bugs - Part 2 (ASM instance on RAC crashed due to ORA-600 [kfclUpdateBuffers20])

Here comes the Part 2 story of my encounter with various Oracle bugs in the recent past.

An ASM instance on one of the node of 8 node RAC environment crashed due to 'ORA-00600: internal error code, arguments: [kfclUpdateBuffers20]' error and all the active instances on this node crashed too with 'communication failure with ASM instance'.

 The cause is due to an intermittent buffer conversion problem in ASM internal structures,  LMS b/g process has terminated the ASM instance with an ORA-600 kfclUpdateBuffers20 error.


Following bugs have been registered against this behavior:

Bug 6489250 INTERMITTENT ORA-600 [KFCLUPDATEBUFFERS20] WITH ASM
Bug:6605106 AN ASM NULL CONVERTED BUFFER MAY BE REFERENCED
 

Details:
ORA-600 [kfclUpdateBuffers20] can occur in ASM as a null converted
buffer may be accessed. Other errors could also occur.

To fix this bug, either apply one-off patch which suits your environment version or apply 10.2.0.5 or upgrade to 11g.

Happy oracle bugs reading.

Jaffar

Oracle bugs - Part 1 (an Instance hung on 'WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK')

Since couple of months, I have been dealing with various Oracle bugs on our 8 node RAC running on HPUX environment with Oracle V10.2.0.4. That really keeps my life busy and given enough nightmares. I just thought of sharing my experience dealing these bugs in a three part series. In the first part of the series, I am going to discuss a situation where an RAC database with two instances hung scenario and how we handled the situation.

An RAC database with two instances was hung with the 'WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!'  error message in the alert.log file. Initially, neither I was able to connect to the database via sqlplus nor able to shutdown the database using SRVCTL STOP DATABASE command. I had to connect one of the instance using the 'sqlplus -prelim' option and before I perform instance abort, I have collected the hanganalyze (level 3) and systemstate (level 266) dumps using the oradebug utility. Upon investigating the hanganlyze tracefile, it shows the following contention:

63CA629C:BE596F7A 300 215 10005 2 KSL WAIT END [enq: SQ - contention] 1397817350/0x53510006 144/0x90 0/0x0 time=2929720
63CA62C9:BE596F7B 300 215 10005 1 KSL WAIT BEG [enq: SQ - contention] 1397817350/0x53510006 144/0x90 0/0x0
 
Further analysis shows a dead lock between two process which were either directly or indirectly blocking all other sessions in the database. Digging further more with the trace file reveals the real problem as you can see in the subsequent lines:

Deadlock between process 33 (J000) running DBMS_STATS_SCH and process 45.
Process 33 is waiting for 'cursor: pin S wait on X' mutex idn=57bc44f9 in shared mode while holding library cache lock handle=c00000010e9123c0 in exclusive mode

Mutex c0000001074df688(490, 0) idn 57bc44f9 oper GET_SHRD --> waiting
LIBRARY OBJECT LOCK: lock=c000000101141390 handle=c00000010e9123c0 mode=X

Process 45 is waiting for waiting for 'library cache lock' on SYS.C_COBJ#  handle address=c00000010e9123c0 in shared mode while holding mutex idn 57bc44f9 in mode EXCL.

Mutex c0000001074df688(490, 0) idn 57bc44f9 oper EXCL
LIBRARY OBJECT LOCK: lock=c000000108b0b208 handle=c00000010e9123c0 request=S



As usual, an Oracle support informed me that its a bug 6011045 and the behavior is due to the following reasons:





Abstract:            DBMS_STATS causes deadlock between 'cursor: pin S wait on X' and 'library cache lock'
Fixed-Releases:     B1
Tags:               DBMSPKG DEADLOCK OPS
Details:          
 Gathering statistics (With DBMS_STATS) on bootstrap objects (like ind$) can cause  a deadlock on sessions waiting on between 'cursor: pin S wait on X'  and 'library cache lock' in RAC.  In some instances sessions waiting on 'DFS lock handle' and 'row cache lock'   are also part of the deadlock path.


Yes, we had a job scheduled daily at might night to collect the database statistics (dbms_stats.gather_database_stats). Though it was running smoothly more than a year now, on any unlucky day, you gonna face this sort of troubles. My initial impression was that this bug is fixed in v.10.2.0.4. However, the Oracle support informed me that the bug is not fixed in 10.2.0.4, either I  need to upgrade  to 11g or request for a one-off patch for my platform.

In part 2, I am going to discuss a situation where one of the RAC database instance crashed due to 'ORA-07445 error'.

Happy reading

Jaffar