資料庫HANG著

我想準確的來說，應該是連線的太慢了，因為連線太多了。

環境:aix 5.3  兩節點的RAC ，其中B節點的crs監聽沒有啟動。

情況：測試直接訪問的B節點，做測試。

現象：B節點幾乎登不上去，A節點可以連線。

解決：自己連線的時候發現，B節點連不上，A節點可以連線，看B節點的日誌檔案

Tue Sep  6 14:08:12 2011

MMNL absent for 1203 secs; Foregrounds taking over

Tue Sep  6 14:36:23 2011

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=189

System State dumped to trace file /oracle/admin/sptdi/udump/sptdi2_ora_377866.trc

Tue Sep  6 14:38:30 2011

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=118

System State dumped to trace file /oracle/admin/sptdi/bdump/sptdi2_j000_704994.trc

Tue Sep  6 14:42:15 2011

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=22

System State dumped to trace file /oracle/admin/sptdi/bdump/sptdi2_smon_353140.trc

Tue Sep  6 14:43:33 2011

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=128

System State dumped to trace file /oracle/admin/sptdi/bdump/sptdi2_pz98_562688.trc

Tue Sep  6 14:51:35 2011

PMON failed to acquire latch, see PMON dump

Tue Sep  6 14:52:12 2011

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=23

System State dumped to trace file /oracle/admin/sptdi/bdump/sptdi2_reco_488516.trc

Tue Sep  6 14:52:35 2011

PMON failed to acquire latch, see PMON dump

Tue Sep  6 14:53:35 2011

PMON failed to acquire latch, see PMON dump

Tue Sep  6 14:54:35 2011

PMON failed to acquire latch, see PMON dump

Tue Sep  6 14:55:13 2011

MMNL absent for 3965 secs; Foregrounds taking over

第一句沒有找到原因，以後找到原因再補上。下面的報錯是因為‘等待太長時間的鎖’，鎖的原因。可是我又上不了B節點，怎麼查鎖呢？

B點：ps -ef | grep oraclesptdi2 | wc –l 有110個連線，

A點：ps -ef | grep oraclesptdi1 | wc –l  有60個連線，

我就kill -9 幹掉一些B點的連線，然後登上B點，用SELECT RPAD (oracle_username, 10) o_name, session_id SID,

        DECODE (locked_mode,

                0, 'None',

                1, 'Null',

                2, 'Row share',

                3, 'Row Execlusive',

                4, 'Share',

                5, 'Share Row Exclusive',

                6, 'Exclusive'

               ) lock_type,

        object_name, xidusn, xidslot, xidsqn

   FROM v$locked_object, all_objects

WHERE v$locked_object.object_id = all_objects.object_id;查哪些會話補鎖，找到之後， 再kill掉。

然後資料庫回到正常狀態，並把B點監聽啟動。