【正文】 l Mon Jun 11 16:54:38 2007Error: unexpected error (6) from the Cluster Service (LCK0)Mon Jun 11 16:54:38 2007Errors in file /u01/app/oracle/admin/rac/bdump/:ORA29702: error occurred in Cluster Group Service operationMon Jun 11 16:54:38 2007Errors in file /u01/app/oracle/admin/rac/bdump/:ORA29702: error occurred in Cluster Group Service operationMon Jun 11 16:54:38 2007Errors in file /u01/app/oracle/admin/rac/bdump/:ORA29702: error occurred in Cluster Grou。 現(xiàn)象：其中某一結(jié)點(diǎn)操作系統(tǒng)重啟。Aggregation EnabledAny query that displays HASH GROUP BY in the execution plan could hit this bug.The HASH GROUP BY feature is enabled by default in .l SolutionPatch 4604970 can be downloaded from Metalink for most platforms .Problem is resolved in latest Patchset release .Workaround:Increase the memory used by hash group byOrDisable the use of hash group by eg: set optimizer_features_enable to orset _gby_hash_aggregation_enabled to FALSE. Linux + Oracle 10g RAC的平臺(tái)上，發(fā)生節(jié)點(diǎn)重啟故障216。具體內(nèi)容參見(jiàn)metalink：Wrong Results Possible on When New HASH GROUP BY Feature is Used（Doc ID: Note:）l CauseBug 4604970 Wrong Results With 39。 其它這個(gè)問(wèn)題已經(jīng)多次出現(xiàn)，與很多Oracle 售前人員交流過(guò)，沒(méi)有定論。如問(wèn)題仍然存在，使用下面語(yǔ)句：SQL Analyze table pute statistics for all indexes。 原因：未知，懷疑為Oracle Bug216。 現(xiàn)象：查詢(xún)語(yǔ)句結(jié)果集返回不全，只返回一條或其中幾條。則語(yǔ)句就是：SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF 查詢(xún)委托返回記錄不對(duì)216。 解決方法：打補(bǔ)丁4518443 文件里加入：SUBSCRIBE_FOR_NODE_DOWN_EVENT_listener_name=OFF其中，listener_name 是數(shù)據(jù)庫(kù)的監(jiān)聽(tīng)器的名稱(chēng)。檢查是否真因?yàn)榇薆UG造成此現(xiàn)象：$ ps ef | grep tnslsnrora10g 8909 1 0 Sep 15 ? 902:44 /u05/10GHOME/DBHOME/bin/tnslsnr sales inheritora10g 22685 8909 0 14:19:23 ? 0:00 /u05/10GHOME/DBHOME/bin/tnslsnr sales inherit正常情況只有一個(gè)監(jiān)聽(tīng)器，而此BUG則會(huì)出現(xiàn)兩個(gè)監(jiān)聽(tīng)器。 原因：10201 版本上的一個(gè)BUG：4518443。若是用9207 版本的SQLPLUS，則會(huì)出現(xiàn)：沒(méi)反應(yīng)，HANG住。 環(huán)境:10201，AIX53(據(jù)ORACLE稱(chēng)，在任何操作系統(tǒng)版本都有此問(wèn)題)216。tracecontrolfiledatabaseinternal SQL 備份當(dāng)前的最新的控制文件 SQL 216。groupdrop altergroupdrop 刪除中間過(guò)渡用的日志組5 alterlogfile。systemlogfile。systemlogfile。system 216。size/oracle/oradata/39。3logfiledatabase1024M。)(39。groupadd altersize/oracle/oradata/39。1logfiledatabase 操作系統(tǒng)下刪除原日志組3中的文件 216。3。logfiledatabase2。logfiledatabase1。logfiledatabase 216。switch alterswitch 切換當(dāng)前日志到新的日志組 alter) size 1024M。alter database add logfile group 5 (39。/oracle/oradata/39。 216。kjfcrfg: DRM window size = 04096 (min lognb = 15) *** 20070519 15:28:Reconfiguration started (old inc 0, new inc 16)Synchronization timeout interval: 900 secList of nodes: 0 1*** 20070519 15:28: Global Resource Directory frozennode 0release 10 2 0 2node 1release 10 2 0 2 number of mastership buckets = 128 domain attach called for domid 0 * kjbdomalc: domain 0 invalid = TRUE * kjbdomatt: first attach for domain 0 asby init, 0/0/x1 asby returns, 0/0/x1/false * Domain maps before reconfiguration: * DOMAIN 0 (valid 0): 0 * End of domain mappings * Domain maps after reputation: * DOMAIN 0 (valid 0): 0 1 * End of domain mappings Dead inst Join inst 0 1 Exist inst Active Sendback Threshold = 50 % Communication channels reestablished sent syncr inc 16 lvl 1 to 0 (16,5/0/0) sent synca inc 16 lvl 1 (16,5/0/0) received all domreplay () sent master 1 () sent syncr inc 16 lvl 2 to 0 (16,7/0/0) sent synca inc 16 lvl 2 (16,7/0/0) Master broadcasted resource hash value bitmaps* kjfcrfg: domain 0 valid, valid_ver = 16 Nonlocal Process blocks cleaned out Set master node info sent syncr inc 16 lvl 3 to 0 (16,13/0/0) sent synca inc 16 lvl 3 (16,13/0/0) Submitted all remoteenqueue requestskjfcrfg: Number of mesgs sent to node 1 = 0 sent syncr inc 16 lvl 4 to 0 (16,15/0/0) sent synca inc 16 lvl 4 (16,15/0/0) Dwncvts replayed, VALBLKs dubious sent syncr inc 16 lvl 5 to 0 (16,18/0/0) sent synca inc 16 lvl 5 (16,18/0/0) All grantable enqueues granted sent syncr inc 16 lvl 6 to 0 (16,20/0/0) sent synca inc 16 lvl 6 (16,20/0/0) *** 20070519 15:28: Submitted all GCS cache requests sent syncr inc 16 lvl 7 to 0 (16,22/0/0) sent synca inc 16 lvl 7 (16,22/0/0) Post SMON to start 1st pass IR Fix write in gcs resources sent syncr inc 16 lvl 8 to 0 (16,24/0/0) sent synca inc 16 lvl 8 (16,24/0/0) *** 20070519 15:28:Reconfiguration plete* domain 0 valid?: 1 *** 20070519 15:28:kjxgrtmc2: mounting member 0 thread 1Begin DRM(32) sent syncr inc 16 lvl 9 to 0 (16,0/31/0) sent synca inc 16 lvl 9 (16,0/31/0) ... sent syncr inc 16 lvl 39 to 0 (16,0/36/0) sent synca inc 16 lvl 39 (16,0/36/0) *** 20070519 15:28: sent syncr inc 16 lvl 40 to 0 (16,0/38/0) sent synca inc 16 lvl 40 (16,0/38/0) End DRM(32) 日志文件太小引起的切換過(guò)于頻繁假設(shè)現(xiàn)有三個(gè)日志組，每個(gè)組內(nèi)有一個(gè)成員，每個(gè)成員的大小為512MB，現(xiàn)在想把此三個(gè)日志組的成員大小都改為1G。4． shutdown 當(dāng)前數(shù)據(jù)庫(kù)并重起數(shù)據(jù)庫(kù)SQL Startup216。2． 修改備份出來(lái)的數(shù)據(jù)庫(kù)參數(shù)文件，在文件末尾添加*._gc_affinity_time=0*._gc_undo_affinity=false3． shutdown 當(dāng)前數(shù)據(jù)庫(kù)，已剛才備份修改后的數(shù)據(jù)庫(kù)參數(shù)文件啟動(dòng)到nomountSQL Shutdown immediate (兩臺(tái)機(jī))SQL Startup pfile=’/home/oracle/’ nomount。_gc_affinity_time=0 defines the frequency in minutes to check if remastering is needed._gc_undo_affinity=FALSE defines the number of times a node must access an object for it to be a DRM candidate216。再者，從Oracle的trace文件里可以看出在兩次交易故障發(fā)生時(shí)，oracle rac的DRM 正在做大量的數(shù)據(jù)同步信息。根據(jù)Oracle 工程師的反饋，可以對(duì)DRM 做一些處理。216。事后，HP工程師在主機(jī)層面查看了相關(guān)的日志信息，并未發(fā)現(xiàn)什么異常的情況，同時(shí)表示無(wú)法在現(xiàn)有資源下監(jiān)控?cái)?shù)據(jù)庫(kù)數(shù)據(jù)線網(wǎng)絡(luò)的流量。在操作系統(tǒng)層面，節(jié)點(diǎn)1的cpu利用率為0，節(jié)點(diǎn)2的cpu利用率為20。 現(xiàn)象：在 20070521 14:42:00 左右，管理員發(fā)現(xiàn)報(bào)盤(pán)系統(tǒng)hang住，AS隊(duì)列上有大量積壓，但未登陸到oracle系統(tǒng)查看等待事件，也未作其他的數(shù)據(jù)庫(kù)方面的診斷處理。 關(guān)于DRM216。216。, cascade = true, degree = 7)。, estimate_percent = 20, method_opt = 39。 基于Schema的統(tǒng)計(jì)信息收集begin (ownname = 39。具體聯(lián)系開(kāi)發(fā)人員。對(duì)抓出來(lái)的語(yǔ)句進(jìn)行分析，如果懷疑是統(tǒng)計(jì)信息不正確引起的全表掃瞄，可以先進(jìn)行統(tǒng)計(jì)信息的收集，方法如下。物理內(nèi)存(RAM)交換分區(qū)(Swap Space)Between 1024 MB and 2048 MB2 倍 RAMBetween 2049 MB and 8192 MBMore