Oracle Database Discussions

Hi, all.

I have a 2 node RAC database on windows 2003 Enterprise Edition SP1.

Yesterday, I found a number of "GES: Potential blocker " messages
in the alert log file of node 2 instance.

● The following is from alert_rac2.log.
-----------------------------------------------------------------------------------------------------------

Thu Aug 16 13:25:44 2007
GES: Potential blocker (pid=5868) on resource HW-00000004-02400029;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\bdump\rac2_mmon_5944.trc and DIAG trace file
Thu Aug 16 13:34:06 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_6088.trc and DIAG trace file
Thu Aug 16 13:52:05 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_2368.trc and DIAG trace file
Thu Aug 16 14:09:08 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_956.trc and DIAG trace file
Thu Aug 16 14:27:28 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_3556.trc and DIAG trace file
Thu Aug 16 14:47:03 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5552.trc and DIAG trace file
Thu Aug 16 15:05:46 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_1140.trc and DIAG trace file
Thu Aug 16 15:23:20 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_4824.trc and DIAG trace file
Thu Aug 16 15:45:33 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_1156.trc and DIAG trace file
Thu Aug 16 16:30:55 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5896.trc and DIAG trace file
Thu Aug 16 16:47:44 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5996.trc and DIAG trace file

...
...
...

Fri Aug 17 14:57:49 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_3840.trc and DIAG trace file
Fri Aug 17 15:15:14 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_4260.trc and DIAG trace file
Fri Aug 17 15:34:26 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_3724.trc and DIAG trace file
Fri Aug 17 16:16:58 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_6036.trc and DIAG trace file
Fri Aug 17 17:09:56 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_912.trc and DIAG trace file
Fri Aug 17 17:31:38 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5148.trc and DIAG trace file
Fri Aug 17 18:08:55 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_4816.trc and DIAG trace file
Fri Aug 17 18:29:28 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_4356.trc and DIAG trace file
Fri Aug 17 19:03:40 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_4140.trc and DIAG trace file
Fri Aug 17 19:26:06 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_3708.trc and DIAG trace file
Fri Aug 17 19:58:43 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5584.trc and DIAG trace file
Fri Aug 17 21:23:04 2007
GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;
enqueue info in file d:\oracle\product\10.2.0\admin\rac\udump\rac2_ora_5904.trc and DIAG trace file

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

●I issued the following script to find out LOCKS.
-----------------------------------------------------------------------------------------------------------

SELECT DECODE(G.INST_ID,1,'RAC1',2,'RAC2') INSTANCE,
S.SID,
G.TYPE,
S.USERNAME,
S.SERIAL#,
S.PROCESS,
DECODE(LMODE,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/ROW', 6,'Exclusive') LMODE,
DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share', 5,'S/ROW',6,'Exclusive')REQUEST,
DECODE(REQUEST,0,'BLOCKER','WAITER') STATE
FROM GV$GLOBAL_BLOCKED_LOCKS G,
GV$SESSION S
WHERE G.SID = S.SID
AND G.INST_ID = S.INST_ID
ORDER BY STATE

-----------------------------------------------------------------------------------------------------------
INSTANCE SID TYPE USERNAME SERIAL# PROCESS LMODE REQUEST STATE
RAC2 544 US 1 5868 Exclusive None BLOCKER
RAC2 544 HW 1 5868 Exclusive None BLOCKER
RAC2 392 US NMSUSER 3 1234 None Exclusive WAITER
RAC2 393 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 394 US NMSUSER 28 1234 None Exclusive WAITER
RAC2 407 US 125 1256 None Exclusive WAITER
RAC2 408 US SYSMAN 2790 1234 None Exclusive WAITER
RAC2 425 US SYS 3136 1904 None Exclusive WAITER
RAC2 431 US NMSUSER 22 1234 None Exclusive WAITER
RAC2 438 US NMSUSER 22 1234 None Exclusive WAITER
RAC2 439 US NMSUSER 9 1234 None Exclusive WAITER
RAC2 457 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 458 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 459 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 460 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 461 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 462 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 463 US NMSUSER 3 1234 None Exclusive WAITER
RAC2 464 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 465 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 466 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 467 US NMSUSER 7 1234 None Exclusive WAITER
RAC2 469 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 470 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 471 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 472 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 473 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 474 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 475 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 476 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 477 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 478 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 479 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 480 US NMSUSER 2 1234 None Exclusive WAITER
RAC2 481 US NMSUSER 5 1234 None Exclusive WAITER
RAC2 482 US NMSUSER 10 1234 None Exclusive WAITER
RAC2 493 US NMSUSER 5 1234 None Exclusive WAITER
RAC2 494 US NMSUSER 5 1234 None Exclusive WAITER
RAC2 496 US NMSUSER 5 1234 None Exclusive WAITER
RAC2 503 US NMSUSER 16777 1234 None Exclusive WAITER
RAC2 529 US DBSNMP 6 4612:4232 None Exclusive WAITER
RAC2 541 HW 1 5944 None Share WAITER
RAC2 380 US SYSMAN 643 1234 None Exclusive WAITER
RAC1 544 US 1 2380 None Exclusive WAITER
RAC2 362 US NMSUSER 2 1234 None Exclusive WAITER


--------------------------------------------------------------------------------------------------------
● As you see in aboves, SID 544 (smon) is blocker, and the lock types are
"HW", AND "US". All other application sessions are waiter, and the lock type is
"US".

I have got the messages of alert log (node2) like followings.
-->GES: Potential blocker (pid=5868) on resource HW-0000000D-00000000;
-->GES: Potential blocker (pid=5868) on resource US-0000000D-00000000;

--------------------------------------------------------------------------------------------------------
● The following is from v$session(sid 544).

sid--> 544
program --> oracle.exe(smon)
type--> background
blocking_session_status --> unknown
event --> gc_buffer_busy
p1text --> file#
p1 --> 9 (▲ file number 9 belongs to the undo tablespace of node 2.)
p2text --> block#
p2--> 41
p3text --> id#
p3 --> 65577
wait_class --> Cluster
state--> waiting

--------------------------------------------------------------------------------------------------------
● In summary, smon of node 2 is blocking all application sessions.
SMON of node 2 is waiting for something to be finished in undo tablespace.
(lock type --> HW, US)


● I restarted the instance of node 2 , and everything is fine until now.
No locks are in the v$lock view.

Please let me hear your advice and your experience about why smon is waiting
for something in the undo tablespace of node 2, and as a result, why smon is
blocking all application sessions.

Is there any problem in I/O sub systems? or other oracle configuration problems?

Thanks in advance.
Best Regards.