就OTN上发布的合并声明来看到8月1日，Oracle将完成java.sun.com,developers.sun.com,bigadmin这几个网站合并到OTN的工作，这次迁移将是完整的并且在内容上是无损的。整合后的网站将提供给java开发者，数据库开发者及管理员，系统开发者及管理员一个多样的技术社区。
同时Oracle保证通过重定向技术确保用户原先的网页书签不会失效；java开发者仍可以像以往一样轻松获取java api信息；Oracle暂时不会修改java技术页面内容的构成，开发者目前不用担心不适应(我从metalink到MOS倒是很不适应，所幸现在好了);
docs.sun.com以及原sun旗下的论坛，博客，维基将暂时不做迁移。如果用户有问题可以向社区反馈讨论版反映。另外作为这些网站的原用户可能需要在OTN新注册一个成员账号。
PS:
7月30日，合并似乎提早了，OTN的界面有了极大的变化，就我个人而言似乎还是老的界面比较对眼！
7月13日，11g release 2 的第二个补丁集更新发布了；9i的最终版本为9.2.0.8,10g上10.2.0.5很有可能成为最终版本，我们预期今后(11g,12g)中Patch set数量会有效减少，而patch set update数量可能大幅增加；这样的更新形式可以为Oracle Database提升一定的软件形象。可以猜想11gr2的最终版本号可能是11.2.0.2/3.x。
附该psu的readme note:
Released: July 13, 2010
This document is accurate at the time of release. For any changes and additional information regarding PSU 11.2.0.1.2, see these related documents that are available at My Oracle Support (http://support.oracle.com/):
This document includes the following sections:
Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.
PSU 11.2.0.1.2 includes the fixes listed in Section 5, “Bugs Fixed by This Patch”.
Table 1 describes installation types and security content. For each installation type, it indicates the most recent PSU patch to include new security fixes that are pertinent to that installation type. If there are no security fixes to be applied to an installation type, then “None” is indicated. If a specific PSU is listed, then apply that or any later PSU patch to be current with security fixes.
| Installation Type | Latest PSU with Security Fixes |
|---|---|
| Server homes | PSU 11.2.0.1.2
|
| Client-Only Installations | None |
| Instant Client Installations | None
(The Instant Client installation is not the same as the client-only Installation. For additional information about Instant Client installations, see Oracle Database Concepts.) |
This section includes the following sections:
For a list of platforms that are supported in this Patch Set Update, see My Oracle Support Note 1060989.1 Critical Patch Update July 2010 Patch Availability Document for Oracle Products.
You must use the OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.
For information about OPatch documentation, including any known issues, see My Oracle Support Note 293369.1 OPatch documentation list.
These instructions are for all Oracle Database installations.
Before you install PSU 11.2.0.1.2, perform the following actions to check the environment and to detect and resolve any one-off patch conflicts.
If you are installing the PSU to an environment that has Automatic Storage Management (ASM), note the following:
make, ar, ld, and nm.The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
export PATH=$PATH:/usr/ccs/bin
For an introduction to the PSU one-off patch concepts, see “Patch Set Updates Patch Conflict Resolution” in My Oracle Support Note 854428.1 Patch Set Updates for Oracle Products.
The fastest and easiest way to determine whether you have one-off patches in the Oracle home that conflict with the PSU, and to get the necessary conflict resolution patches, is to use the Patch Recommendations and Patch Plans features on the Patches & Updates tab in My Oracle Support. These features work in conjunction with the My Oracle Support Configuration Manager. Recorded training sessions on these features can be found in Note 603505.1.
However, if you are not using My Oracle Support Patch Plans, follow these steps:
unzip p9654983_11201_<platform>.zip opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9654983
Follow these steps:
unzip p9654983_11201_<platform>.zip cd 9654983 opatch apply
After installing the patch, perform the following actions:
Apply the patch conflict resolution one-off patches that were determined to be needed when you performed the steps in Section 2.3.1.3, “One-off Patch Conflict Detection and Resolution”.
The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.
catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle.sql psu apply SQL> QUIT
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
$ORACLE_HOME/cfgtoollogs/catbundle for any errors:
catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.
These instructions are for a database that is created or upgraded after the installation of PSU 11.2.0.1.2.
You must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” for any new database only if it was created by any of the following methods:
These instructions apply if you need to deinstall the patch.
Follow these steps:
$ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” against the database before deinstalling the PSU.rollback argument as follows.
opatch rollback -id 9654983
Follow these steps:
ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql SQL> QUIT
In a RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.
$ORACLE_HOME/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.Follow these steps for each node in the cluster, one node at a time:
rollback argument as follows.
opatch rollback -id 9654983
If there are errors, refer to Section 3, “Known Issues”.
srvctl start instance
Follow the instructions listed in Section Section 2.4.2, “Patch Post-Deinstallation Instructions for a Non-RAC Environment” only on the node for which the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” were executed during the patch application.
All other instances can be started and accessed as usual while you are executing the deinstallation steps.
For information about OPatch issues, see My Oracle Support Note 293369.1 OPatch documentation list.
For issues documented after the release of this PSU, see My Oracle Support Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues.
Other known issues are as follows.
catbundle.sql script or its rollback script:
ORA-29809: cannot drop an operator with dependent objects ORA-29931: specified association does not exist ORA-29830: operator does not exist ORA-00942: table or view does not exist ORA-00955: name is already used by an existing object ORA-01430: column being added already exists in table ORA-01432: public synonym to be dropped does not exist ORA-01434: private synonym to be dropped does not exist ORA-01435: user does not exist ORA-01917: user or role 'XDB' does not exist ORA-01920: user name '<user-name>' conflicts with another user or role name ORA-01921: role name '<role name>' conflicts with another user or role name ORA-01952: system privileges not granted to 'WKSYS' ORA-02303: cannot drop or replace a type with type or table dependents ORA-02443: Cannot drop constraint - nonexistent constraint ORA-04043: object <object-name> does not exist ORA-29832: cannot drop or replace an indextype with dependent indexes ORA-29844: duplicate operator name specified ORA-14452: attempt to create, alter or drop an index on temporary table already in use ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored. ORA-01927: cannot REVOKE privileges you did not grant
The following documents are references for this patch.
Note 293369.1 OPatch documentation list
Note 360870.1 Impact of Java Security Vulnerabilities on Oracle Products
Note 468959.1 Enterprise Manager Grid Control Known Issues
Note 9352237.8 Bug 9352237 – 11.2.0.1.1 Patch Set Update (PSU)
This patch includes the following bug fixes.
CPU molecules in PSU 11.2.0.1.2:
PSU 11.2.0.1.2 contains the following new CPU molecules:
9676419 – DB-11.2.0.1-MOLECULE-004-CPUJUL2010
9676420 – DB-11.2.0.1-MOLECULE-005-CPUJUL2010
PSU 11.2.0.1.2 contains the following new fixes:
8755082 – ORA-00600: [KCFIS_TRANSLATE4:VOLUME LOOKUP], [2], [WRONG DEVICE NAME], [], [], [
8890026 – ASM PARTNERING CREATES IMBALANCED PARTNERSHIPS
9170608 – STBH:DD BLOCKS PINNED FOR QUERIES THAT DO NOT REQUEST USED SPACE
9363145 – STBH:DB INSTANCES TERMINATED BY ASMB DUE TO ORA-00600 [KFDSKALLOC0]
8330783 – HANGING DB WITH “CACHE BUFFER CHAINS” AND “BUFFER DEADLOCK” WAITS DURING INSERT
8822531 – TAKING AWR SNAP HANGS
8918433 – UNPERSISTED FSFO STATE BITS CAN GET PERSISTED
9363384 – PHYSICAL STANDBY SERVICES NOT STARTED AFTER CONVERT FROM SNAPSHOT
9467635 – BROKER’S METADATA FILE UPGRADE TO 11.2 IS BROKEN
9467727 – GETSTATUS DOC YIELDS INCORRECT RESULT IF DBRESOURCE_ID PROP VALUE IS USED
8774868 – LGSBFSFO: ORA-600 [3020], [3], [138] RAISED IN RECOVERY SLAVE
8822832 – V$ARCHIVE_DEST_STATUS HAS INCORRECT VALUE FOR APPLIED_SEQ#
8872096 – ARCHIVING FORCED DURING CLOSE WHEN NO STANDBY IS PRESENT
9399090 – STBH: CONSTANT/HIGH FREQUENT LOG SWITCHES ON BEEHIVE DATABASE IN THE LAST 3 DAYS
8865718 – RECURSIVE CURSORS CONTAINING “AS OF SNAPSHOT” CLAUSE ARE NOT SHARED
8981059 – HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH
9010222 – APPS ST 11G ORA-00600 [KKSFBC-REPARSE-INFINITE-LOOP]
9067282 – TB:SH:ORA-00600:[KKSFBC-WRONG-KKSCSFLGS] WHILE RUNNING TPC-H
9255542 – ARRAY INSERT TO PARTITIONED TABLE LOOSES ROWS DUE TO CONCURRENT DDL (ORA-14403)
9488887 – FORIEGN KEY VIOLATION WITH ARRAY-INSERT AND ONLINE IDX REBUILD AFTER BUG-9255542
8834425 – ORA-240 IN RVWR PROCESS CAUSING 5MIN TRANSACTIONAL HANG
9210925 – AFTER MANUAL UPGRADE TO 11.1.0.7 PL/SQL CALLS INCORRECT FUNCTION
8505803 – PRE_PAGE_SGA RESULTS IN EXCESSIVE PAGE TABLE SIZE WHEN USING MEMORY_TARGET [AMM]
9165206 – PARTITIONING ORA-600 [KKPOLLS1] / [KKDOILSF1] – DURING PARTITION MAINTANANCE
8875671 – LX64: ORA-600 ARGS [KJPNP_CHK:!MASTER_READY],
9093300 – LOTS OF REPEATED KJXOCDR: DROP DUPLICATE OPEN MESSAGE IN LMD TRACE
8544696 – TABLE GROWTH – BLOCKS ARE NOT REUSED
8650719 – DOWNSTREAM CAPTURE ABORTS WITH ORA-26766
8856478 – RAM SECUREFILE PERF DEGRADATION WITH SF COMPRESSION ON SMALL LOBS DURING ATB MOVE
9272086 – STBH: DATA PUMP WRITER SEEMS TO BE WAITING ON WAIT FOR UNREAD MESSAGE ON BROADCA
8909984 – APPSST GSI 11G: GAPS IN AWR SNAPSHOTS
9068088 – MEDIA RECOVERY WAS HUNG ON STANDBY
9145541 – ORA-600 [25027] / ORA-600 [4097] FOR ACTIVE TX IN A PLUGGED TABLESPACE
9167285 – PKT-BUGOLTP: ORA-07445: [KCRALC()+87]
7519406 – ‘J000’ TRACE FILE REGARDING GATHER_STATS_JOB INTERMITTENTLY SINCE 10.2.0.4
8815639 – [11GR2-LNX-090813] MULTIPLE INSERT CAUSE DATA ALLOCATION ABOVE HHWM
9216806 – HIGH “ENQ: TS – CONTENTION” FOR TEMPORARY SEGMENT WHILE SQLLDR DIRECT PATH LOAD
9242411 – STRESS-BIGBH: LOTS OF OR-3113S IN BIGBH STRESS TEST
9461782 – ORA-7445 [KTSLF_SUMFSG()+54] [SIGSEGV] AND KTSLFSUM_CFS ON CALL STACK
9011088 – [11GR2]ADDING COLUMN TO COMPRESSED TABLE, DATA LOSS OCCURED.
9275072 – APPSST GSI 11G : BUFFER BUSY WAITS INSERTING INTO TABLES
9341448 – APPSST GSI 11G : BUFFER BUSY WAITS AND LATCH: CACHE BUFFERS WAITS WHEN INSERTING
9637033 – ORA-07445[KDR9IR2RST0] INSERT AS SELECT IN A COMPRESSED TABLE WITH > 255 COLUMNS
8664189 – ORA-00600 [KDISS_UNCOMPRESS: BUFFER LENGTH]
9119194 – PSRC: DISTRIBUTED QUERY SLOWER IN 10.2.0.4 COMPARED TO 10.2.0.3
8268775 – PERF: HIGH US ENQUEUE CONTENTION DURING A LOGIN STORM OR SESSION FAILOVER
8803762 – ORA-00600 [KDSGRP1] BLOCK CORRUPTION ON 11G DATABASE UPGRADE
8431487 – INSTANCE CRASH ORA-07445 [KGGHSTFEL()+192] ORA-07445[KGGHSTMAP()+241]
9713537 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-00600
9714832 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-07445
前几日，有客户报一个备用库实例中有多个回话hang住的问题，在hang住前本地有维护人员执行了truncate表的操作。
同事前往客户现场进一步确认了问题，并传回了当时hang状况下的266级systemstate文件。该实例的并发回话数量较少，所以实例状态抓取后总的信息量并不多，这种情形中通过分析systemstate信息往往要好于分析hanganalyze信息。
通过著名的源自于metalink的ass awk脚本可以很快找出各进程的状态，以及重要资源的持有者：
awk -f ass109.awk systemstate.txt
Starting Systemstate 1
…………………………………..
Ass.Awk Version 1.0.9 – Processing systemstate.txt
System State 1
~~~~~~~~~~~~~~~~
1:
2: last wait for ‘pmon timer’
3: waiting for ‘rdbms ipc message’ wait
4: waiting for ‘rdbms ipc message’ wait
5: waiting for ‘latch: cache buffers chains'[Latch 70000048c8c66b0] wait DBW0
6: waiting for ‘latch: cache buffers chains'[Latch 70000048c8c66b0] wait DBW1
7: waiting for ‘latch: cache buffers chains'[Latch 70000048c8c66b0] wait DBW2
8: waiting for ‘rdbms ipc message’ wait
9: waiting for ‘rdbms ipc message’ wait
10: waiting for ‘rdbms ipc message’ wait
11: waiting for ‘enq: RO – fast object reuse'[Enqueue RO-0001000B-00000001] wait
12: waiting for ‘rdbms ipc message’ wait
13: waiting for ‘rdbms ipc message’ wait
14: waiting for ‘rdbms ipc message’ wait
15: waiting for ‘rdbms ipc message’ wait
16: waiting for ‘SQL*Net message from client’ wait
17: waiting for ‘SQL*Net message from client’ wait
18: waiting for ‘SQL*Net message from client’ wait
19: waiting for ‘latch: cache buffers chains'[Latch 70000048c8c66b0] wait
20: waiting for ‘SQL*Net message from client’ wait
21: waiting for ‘SQL*Net message from client’ wait
22: last wait for ‘SQL*Net message from client’ [DEAD]
Cmd: Delete
23: waiting for ‘SQL*Net message from client’ wait
24: waiting for ‘SQL*Net message from client’ wait
25:
26: waiting for ‘SQL*Net message from client’ wait
27: last wait for ‘ksdxexeotherwait’
28: waiting for ‘enq: TX – row lock contention'[Enqueue TX-00120003-00002CC0] wait
Cmd: Update
29: waiting for ‘SQL*Net message from client’ wait
30: waiting for ‘SQL*Net message from client’ wait
31: waiting for ‘SQL*Net message from client’ wait
32: waiting for ‘SQL*Net message from client’ wait
33: waiting for ‘SQL*Net message from client’ wait
34: waiting for ‘Streams AQ: qmn coordinator idle wait’ wait
35: for ‘Streams AQ: waiting for time management or cleanup tasks’ wait
36: waiting for ‘Streams AQ: qmn slave idle wait’ wait
37: waiting for ‘enq: RO – fast object reuse'[Enqueue RO-00010025-00000001] wait
38: waiting for ‘SQL*Net message from client’ wait
39: waiting for ‘SQL*Net message from client’ wait
41: waiting for ‘SQL*Net message from client’ wait
42: last wait for ‘enq: TX – row lock contention’ wait
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of ‘???’ implies that the holder was not found in the
systemstate.
Resource Holder State
Latch 70000048c8c66b0 ??? Blocker
Enqueue RO-0001000B-00000001 10: waiting for ‘rdbms ipc message’
Enqueue RO-0001000B-00000001 11: 11: is waiting for 10: 11:
Enqueue TX-00120003-00002CC0 22: last wait for ‘SQL*Net message from client’
Enqueue RO-00010025-00000001 10: waiting for ‘rdbms ipc message’
Enqueue RO-00010025-00000001 37: 37: is waiting for 10: 37:
Object Names
~~~~~~~~~~~~
Latch 70000048c8c66b0 holding (efd=14) 70000048c8c66b0 Child ca
Enqueue RO-0001000B-00000001
Enqueue TX-00120003-00002CC0
Enqueue RO-00010025-00000001
首先注意到的是RO队列锁，RO即(REUSE OBJECT),该锁用以协调前台进程与后台进程DBWR和CKPT之间的工作，该队列一般只在drop或truncate对象时可见到。可以看到pid为11和37的进程均在等待pid为10的进程，因为pid=10的进程持有着它们锁需要的RO-0001000B-00000001和RO-00010025-00000001，分析dump文件可以发现该持有进程正是CKPT后台进程；
而该检查点进程也处于BUSY的非空闲等待中，其等待事件为’rdbms ipc message’,即它在等待另一个后台进程给它发送信息。这个时候我们来观察其他忙碌的后台进程可以发现，pid为5,6,7的进程均在等待同一个栓’latch: cache buffers chains'[Latch 70000048c8c66b0];这个三个进程均为DBWR进程，此外还有一个DBW3进程处于’rdbms ipc message’等待中，多个DBWR进程是由于设置了db_writer_processes参数；看起来是ckpt进程准备对需要truncate的对象做对象级别的检查点，以保证该对象所有脏块均已写到磁盘上，所以对dbwr进程发出需要写出的message,继而进入’rdbms ipc message’等待直到dbwr进程完成写出任务，但由于dbwr进程长期无法获取某脏块对应的latch: cache buffers chains,故写出工作一直处于pending状态，这样一个hang链就十分清晰了。
我们来分析’latch: cache buffers chains'[Latch 70000048c8c66b0]这个栓，ass分析systemstate dump时将该栓的holder归为’???’,即无法自dump文件中找到该栓的持有者；进一步直接分析dump文件可以发现：
PROCESS 2:
—————————————-
SO: 70000048f529d40, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=2, calls cur/top: 70000047d0e75a0/70000048f8956d0, flag: (e) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 504403177803376304 122 2
last post received-location: kslges
last process to post me: 70000048e533a88 1 6
last post sent: 0 0 148
last post sent-location: ktmpsm
last process posted by me: 70000048e535228 1 22
(latch info) wait_event=0 bits=2
holding (efd=14) 70000048c8c66b0 Child cache buffers chains level=1 child#=61214
Location from where latch is held: kcbgcur: kslbegin:
Context saved from call: 336311247
state=busy(exclusive) (val=0x2000000000000002) holder orapid = 2
waiters [orapid (seconds since: put on list, posted, alive check)]:
5 (480, 1278471465, 0)
6 (480, 1278471465, 0)
19 (480, 1278471465, 0)
7 (216, 1278471465, 0)
waiter count=4
Process Group: DEFAULT, pseudo proc: 70000048e5f91d8
O/S info: user: oracle, term: UNKNOWN, ospid: 74476
OSD pid info: Unix process pid: 74476, image: oracle@DR_570 (PMON)
可以看到pid=2的PMON进程holding 该cache buffers chains子栓，而dbwx进程即waiters 5，6，7；
一般情况下pmon是不会去持有类似于cache buffers chains子栓这样的低级栓的，除非在cleanup失败会话或关闭实例情况下。从ass分析信息来看，当时确实有一个进程处于DEAD状态，即22: last wait for ‘SQL*Net message from client’ [DEAD]，而该会话最后所做的是delete from “LINC”.”MSDB_ACCGL” t操作。
虽然无法证明，但极有可能是22号进程在进行delete过程中发生会话失败，PMON进程尝试清理该进程，并获取了相关栓。但该进程始终没有被杀死，即便使用OS 命令:kill -9 处理该进程后仍可以在systemstate中找到该进程的信息。据同事分析，当时之前曾有一度PMON的CPU使用率达到100%，之后PMON进程进入’pmon timer’空闲等待，且一直没有释放对应子栓，令DBWR进程处于长期无法获得栓资源的进而hang住的状态。
在MOS上搜索PMON+cache buffers chains可以发现几个PMON长期持有该类子栓且从不释放的Bug,但版本为Oracle 8等较老版本，且都是不能reproduceable的case。谨以录之：
Hdr: 4126734 8.1.7.4.0 RDBMS 8.1.7.4.0 BUFFER CACHE PRODID-5 PORTID-59
Abstract: ORACLE PROCESS GOES TO CPU LOOP WHILE HOLDING “CHILD CACHE BUFFERS CHAINS” LATCH*** 01/17/05 07:08 pm ***
TAR:
—-
4249496.999PROBLEM:
——–
1. Clear description of the problem encountered:Oracle process goes to CPU loop while holding “Child cache buffers chains
level=1” latch. This condition causes other processes to wait on
either “latch free” or “Child cache buffers chains” which completely hangthe instance.
When looping Oracle process is killed, PMON also goes to CPU loop
cleaning
up that process. PMON also hold the same “Child cache buffers chains
level=1” latch while instance continues to hang.Hang disappear only after instance is bounced.
2. Pertinent configuration information (MTS/OPS/distributed/etc)
11i Apps Install (version 11.5.9.) running on Oracle 8.1.7.4.0 database.
3. Indication of the frequency and predictability of the problem
Process running pl/sql procedure doing dbms_lob.instr() and
dbms_lob.read()
seem to encounter this problem.4. Sequence of events leading to the problem
5. Technical impact on the customer. Include persistent after effects.
In a bad day, ct encounters 2 occurence of this problem a day causing
loss
of service in peak processing period.DIAGNOSTIC ANALYSIS:
——————–
1. Got 3 systemstate dumps while hang is occuring
2. Got 3 errorstack dumps of PMON while it is looping in CPUWORKAROUND:
———–
bounce instanceRELATED BUGS:
————-
bug 2361194REPRODUCIBILITY:
—————-
– always reproducible on test system at ct site.TEST CASE:
———-
– unable to reproduce on 8.1.7.4.0 test system in-houseSTACK TRACE:
————
PMON stack while looping:
ksedmp ksdxfdmp ksdxcb sspuser _sigreturn kggchk kcbso1 kcbpsod kcbsod
kssxdl kssdch ksudlc kssxdl ksudlp kssxdl ksuxdl ksuxda ksucln ksbrdp
opirip opidrv sou2o main $START$SUPPORTING INFORMATION:
———————–
Customers running Release 11i of the E-Business Suite will be entitled
to complimentary Extended Maintenance Support through July 31, 2005.
We are planning to implement 11gR2 RAC on OCFS2 file system. We are going to have ocr files,voting disk files,database files and flash recovery area files on OCFS2 file system.
Generic Note
————————
ocfs2 is certified for oracle 11gr2 but oracle recommends using asm.
please see this for more information http://download.oracle.com/docs/cd/E11882_01/install.112/e10812/storage.htm#CWLIN262
3.1.3 Supported Storage Options
The following table shows the storage options supported for storing Oracle Clusterware and Oracle RAC files.
Note:
For information about OCFS2, refer to the following Web site:
http://oss.oracle.com/projects/ocfs2/
If you plan to install an Oracle RAC home on a shared OCFS2 location, then you must upgrade OCFS2 to at least version 1.4.1, which supports shared writable mmaps.
For OCFS2 certification status, and for other cluster file system support, refer to the Certify page on My Oracle Support.
Table 3-1 Supported Storage Options for Oracle Clusterware and Oracle RAC
Linux x86-64
11gR2 RAC
RAC for LinuxRAC Technologies Compatibility Matrix (RTCM) for Linux platforms.
http://www.oracle.com/technology/products/database/clustering/certify/tech_generic_linux_new.html
Note 183408.1 Raw Devices and Cluster Filesystems With Real Application Clusters
Note 238278.1 How to find the current OCFS or OCFS2 version for Linux
Note 811306.1 RAC Assurance Support Team: RAC Starter Kit and Best Practices (Linux)
9i 中v$session_wait 是Oracle wait interface的一个主要用户接口，而该动态视图的内容来源于x$ksusecst内部视图：
SQL> select view_definition from v$fixed_view_definition where view_name='GV$SESSION_WAIT'; VIEW_DEFINITION -------------------------------------------------------------------------------- select s.inst_id,s.indx,s.ksussseq,e.kslednam, e.ksledp1,s.ksussp1,s.ksussp1r,e. ksledp2, s.ksussp2,s.ksussp2r,e.ksledp3,s.ksussp3,s.ksussp3r, decode(s.ksusstim, 0,0,-1,-1,-2,-2, decode(round(s.ksusstim/10000),0,-1,round(s.ksusstim/10000))) , s.ksusewtm, decode(s.ksusstim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, ' WAITED SHORT TIME', 'WAITED KNOWN TIME') from x$ksusecst s, x$ksled e where bit and(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0 and s.ksussop c=e.indx SQL> desc x$ksusecst Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(4) //即 v$session中 saddr 会话的起始地址 INDX NUMBER //即 instance_id INST_ID NUMBER //即 sid KSSPAFLG NUMBER KSUSEFLG NUMBER //该session是否仍活着, 1 为 alive KSUSENUM NUMBER //另一个固有编号 KSUSSSEQ NUMBER // 相当于v$session 视图的SERIAL#列 KSUSSOPC NUMBER // 对应x$ksled视图indx列，等待事件列表的一个序列号 KSUSSP1 NUMBER // 即v$session_wait表的p1列 KSUSSP1R RAW(4) // 即v$session_wait表的p1raw KSUSSP2 NUMBER // 即v$session_wait表的p2 KSUSSP2R RAW(4) // 即v$session_wait表的p2raw KSUSSP3 NUMBER // 即v$session_wait表的p3 KSUSSP3R RAW(4) // 即v$session_wait表的p3raw KSUSSTIM NUMBER // 即v$session_wait表的wait_time,但单位为微秒 KSUSEWTM NUMBER // 即v$session_wait表的seconds_in_wait,单位仍为秒
粗略写了一个可以代替v$session_wait视图的查询语句，过滤了可能出现的空闲等待事件，同时细化wait_time列到us级别：
select s.inst_id,
s.indx sid,
s.ksussseq seq#,
e.kslednam event,
e.ksledp1 p1text,
s.ksussp1 p1,
s.ksussp1r p1raw,
e.ksledp2 p2text,
s.ksussp2 p2,
s.ksussp2r p2raw,
e.ksledp3 p3text,
s.ksussp3 p3,
s.ksussp3r p3raw,
s.ksusstim wait_time,
s.ksusewtm seconds_in_wait,
decode(s.ksusstim,
0,
'WAITING',
-2,
'WAITED UNKNOWN TIME',
-1,
'WAITED SHORT TIME',
'WAITED KNOWN TIME') state
from x$ksusecst s, x$ksled e
where bitand(s.ksspaflg, 1) != 0
and bitand(s.ksuseflg, 1) != 0
and s.ksussseq != 0
and s.ksussopc = e.indx
and e.kslednam not in ('pmon timer',
'VKTM Logical Idle Wait',
'VKTM Init Wait for GSGA',
'IORM Scheduler Slave Idle Wait',
'rdbms ipc message',
'i/o slave wait',
'VKRM Idle',
'wait for unread message on broadcast channel',
'wait for unread message on multiple broadcast channels',
'class slave wait',
'KSV master wait',
'PING',
'watchdog main loop',
'DIAG idle wait',
'ges remote message',
'gcs remote message',
'heartbeat monitor sleep',
'SGA: MMAN sleep for component shrink',
'MRP redo arrival',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'simulated log write delay',
'LGWR real time apply sync',
'parallel recovery slave idle wait',
'LogMiner builder: idle',
'LogMiner builder: branch',
'LogMiner preparer: idle',
'LogMiner reader: log (idle)',
'LogMiner reader: redo (idle)',
'LogMiner client: transaction',
'LogMiner: other',
'LogMiner: activate',
'LogMiner: reset',
'LogMiner: find session',
'LogMiner: internal',
'Logical Standby Apply Delay',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery control message reply',
'parallel recovery slave next change',
'PX Deq: Txn Recovery Start',
'PX Deq: Txn Recovery Reply',
'fbar timer',
'smon timer',
'PX Deq: Metadata Update',
'Space Manager: slave idle wait',
'PX Deq: Index Merge Reply',
'PX Deq: Index Merge Execute',
'PX Deq: Index Merge Close',
'PX Deq: kdcph_mai',
'PX Deq: kdcphc_ack',
'shared server idle wait',
'dispatcher timer',
'cmon timer',
'pool server timer',
'JOX Jit Process Sleep',
'jobq slave wait',
'pipe get',
'PX Deque wait',
'PX Idle Wait',
'PX Deq: Join ACK',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Msg Fragment',
'PX Deq: Parse Reply',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'Streams fetch slave: waiting for txns',
'Streams: waiting for messages',
'Streams capture: waiting for archive log',
'single-task message',
'SQL*Net message from client',
'SQL*Net vector message from client',
'SQL*Net vector message from dblink',
'PL/SQL lock timer',
'Streams AQ: emn coordinator idle wait',
'EMON slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams AQ: delete acknowledged messages',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'HS message to agent',
'ASM background timer',
'auto-sqltune: wait graph update',
'WCR: replay client notify',
'WCR: replay clock',
'WCR: replay paused',
'JS external job',
'cell worker idle',
'SQL*Net message to client');
几个月前，在客户的一次演练测试中，一位乙方项目组的经理指出：在RAC环境中使用lsnrctl命令关闭监听，Oracle CRS会自动将该监听重启。客户对他的这个说法十分重视，同时向我咨询，CRS确实会定期对所有资源进行检查，并可能重新启动以外终止的资源；但手动使用lsnrctl关闭监听绝对不能算在以外终止的范畴当中。这位乙方的项目经理年纪已界中年，项目经验十分丰富，而且说起这个问题来信誓旦旦(十分反感这样的自信),不由得别人不信；当时我向客户具体介绍了CRS重启资源的原理，并阐述了我认为“不会重启”的观点，因为不能排除一些意外因素(我认识的Oracle总是带来惊喜),我的口气并不如乙方项目经理那么肯定，客户负责人也只有将信将疑，并认为可以实际测试一下。
当时的测试记录没有保留，我们来看一下RHEL 5.5上Oracle RAC 10.2.0.5版本中的表现(实际与AIX上10.2.0.4的表现一致):
[maclean@rh2 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....S13.cs application ONLINE ONLINE rh2 ora....db1.srv application ONLINE ONLINE rh2 ora.racdb.db application ONLINE ONLINE rh1 ora....b1.inst application ONLINE ONLINE rh2 ora....b2.inst application ONLINE ONLINE rh1 ora....SM2.asm application ONLINE ONLINE rh1 ora....H1.lsnr application ONLINE ONLINE rh1 ora.rh1.gsd application ONLINE ONLINE rh1 ora.rh1.ons application ONLINE ONLINE rh1 ora.rh1.vip application ONLINE ONLINE rh1 ora....SM1.asm application ONLINE ONLINE rh2 ora....H2.lsnr application ONLINE ONLINE rh2 ora.rh2.gsd application ONLINE ONLINE rh2 ora.rh2.ons application ONLINE ONLINE rh2 ora.rh2.vip application ONLINE ONLINE rh2 [maclean@rh2 ~]$ ps -ef|grep tns maclean 4098 17071 0 19:35 pts/0 00:00:00 grep tns maclean 11062 1 0 11:34 ? 00:00:00 /s01/rac10g/bin/tnslsnr LISTENER_RH2 -inherit [maclean@rh2 ~]$ lsnrctl stop LISTENER_RH2 LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 27-JUN-2010 19:35:46 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh2-vip)(PORT=1521)(IP=FIRST))) The command completed successfully [maclean@rh2 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....S13.cs application ONLINE ONLINE rh2 ora....db1.srv application ONLINE ONLINE rh2 ora.racdb.db application ONLINE ONLINE rh1 ora....b1.inst application ONLINE ONLINE rh2 ora....b2.inst application ONLINE ONLINE rh1 ora....SM2.asm application ONLINE ONLINE rh1 ora....H1.lsnr application ONLINE ONLINE rh1 ora.rh1.gsd application ONLINE ONLINE rh1 ora.rh1.ons application ONLINE ONLINE rh1 ora.rh1.vip application ONLINE ONLINE rh1 ora....SM1.asm application ONLINE ONLINE rh2 ora....H2.lsnr application OFFLINE OFFLINE // TARGET被置为OFFLINE是不会被重启的 ora.rh2.gsd application ONLINE ONLINE rh2 ora.rh2.ons application ONLINE ONLINE rh2 ora.rh2.vip application ONLINE ONLINE rh2 [maclean@rh2 ~]$ crs_start ora.rh2.LISTENER_RH2.lsnr Attempting to start `ora.rh2.LISTENER_RH2.lsnr` on member `rh2` Start of `ora.rh2.LISTENER_RH2.lsnr` on member `rh2` succeeded. [maclean@rh2 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....S13.cs application ONLINE ONLINE rh2 ora....db1.srv application ONLINE ONLINE rh2 ora.racdb.db application ONLINE ONLINE rh1 ora....b1.inst application ONLINE ONLINE rh2 ora....b2.inst application ONLINE ONLINE rh1 ora....SM2.asm application ONLINE ONLINE rh1 ora....H1.lsnr application ONLINE ONLINE rh1 ora.rh1.gsd application ONLINE ONLINE rh1 ora.rh1.ons application ONLINE ONLINE rh1 ora.rh1.vip application ONLINE ONLINE rh1 ora....SM1.asm application ONLINE ONLINE rh2 ora....H2.lsnr application ONLINE ONLINE rh2 ora.rh2.gsd application ONLINE ONLINE rh2 ora.rh2.ons application ONLINE ONLINE rh2 ora.rh2.vip application ONLINE ONLINE rh2 [maclean@rh2 ~]$ ps -ef|grep tns maclean 4629 1 0 19:37 ? 00:00:00 /s01/rac10g/bin/tnslsnr LISTENER_RH2 -inherit maclean 5212 17071 0 19:38 pts/0 00:00:00 grep tns [maclean@rh2 ~]$ kill -9 4629 [maclean@rh2 ~]$ ps -ef|grep tns maclean 5333 17071 0 19:38 pts/0 00:00:00 grep tns [maclean@rh2 ~]$ date Sun Jun 27 19:38:59 EDT 2010 //过10分钟再来看看 [maclean@rh2 ~]$ ps -ef|grep tns maclean 8655 1 0 19:47 ? 00:00:00 /s01/rac10g/bin/tnslsnr LISTENER_RH2 -inherit maclean 9252 17071 0 19:48 pts/0 00:00:00 grep tns [maclean@rh2 ~]$ lsnrctl status LISTENER_RH2 LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 27-JUN-2010 19:48:43 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh2-vip)(PORT=1521)(IP=FIRST))) STATUS of the LISTENER ------------------------ Alias LISTENER_RH2 Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production Start Date 27-JUN-2010 19:47:07 Uptime 0 days 0 hr. 1 min. 35 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /s01/rac10g/network/admin/listener.ora Listener Log File /s01/rac10g/network/log/listener_rh2.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.104)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.103)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "S13" has 1 instance(s). Instance "racdb1", status READY, has 2 handler(s) for this service... Service "racdb" has 2 instance(s). Instance "racdb1", status READY, has 2 handler(s) for this service... Instance "racdb2", status READY, has 1 handler(s) for this service... Service "racdbXDB" has 2 instance(s). Instance "racdb1", status READY, has 1 handler(s) for this service... Instance "racdb2", status READY, has 1 handler(s) for this service... Service "racdb_XPT" has 2 instance(s). Instance "racdb1", status READY, has 2 handler(s) for this service... Instance "racdb2", status READY, has 1 handler(s) for this service... The command completed successfully
LISTENER资源的默认CHECK_INTERVAL为600秒，即10分钟内CRS会检测到LISTENER的意外终止并尝试重新启动该资源。经过上述在生产主机上的测试,乙方的项目经理觉得有些不可思议，同时客户也认同了我的观点。其实如乙方项目经理所作出的那样肯定的论调即使在其他地方也是不少见的，他们大多全通过实践来学习和认识Oracle,这点没有问题，实践可以教会我们大多数东西，但同时如果我们对事物的认识全部来自实践又往往不全面了，乙方项目经理所犯得就是这种错误，可能他在某次case当中遇到过类似的带有错误指导性情况，同时他也没有反复阅读过官方文档并没有在事后去深入了解整个事件的逻辑因果，并凭借着多年的经验果断地为该问题下了十分肯定的结论。
中国企业目前的IT基建大多由集成商完成，在整个it环节中集成商扮演了十分重要的角色；随着阅历的丰富，渐渐发觉集成商处集结着大量如这位经理般，年龄或大或小，经验或多多少，但说起技术来大多没完没了，深怕别人不知道自己会这会那的，他们在发表自己论点的口气决不允许半点质疑！
6月11日5点天刚蒙蒙亮，极不自然地醒过来，因为要赶飞机的缘故又是一夜没睡好；从家中打车到浦东机场需用一个多小时，抵达时已经6点半了，我要搭乘7点50分的飞机，自上海出发到西安转机到青海西宁。这次是公司在开拓青海省期间的一次技术交流，对象是西宁市的一家政府下属单位。
早晨的浦东机场已经十分忙碌，考虑到是世博期间人流更胜往常；这里是大魔都一处永不停歇的驿站，在这里人流喷涌，为这个国家带来所有未知机遇。
一行无话，11点40分抵达西安，西安的机场还是挺大的，至少在西部诸省仍是佼佼者；我一直很向往这个古老城市：秦，两汉，唐;这个城市记录了中华最辉煌的年代，在我心目中这种气度不是帝都所能比的，不过这次是无缘游访了。
1点钟不到终于到达目的地，青海省西宁市这个边陲省会；从机场向外看去一望无际的土山让生长在上海没有出过远门的我有些惊讶，西宁机场不大，虽然离地震已经过去一段时间了但仍到处插着”青海长青，玉树不倒”的标语，西宁机场目下正在扩建，灾后重建需要一个现代化的机场。
落地后赶忙和负责西北地区的张，胡两位取得了联系，他们下榻在中心广场的如家，据说是此地的市中心，而自机场打的到市区则似乎是统一的一百元；西宁市的的士规格参差不齐，但车顶上大大的广告牌似乎要比上海的还要先进，部分的士甚至是私家的车子，起步费统一的6元；上车前最好问清楚是否能给发票，不然可能会很麻烦。
载我的那位的哥十分健谈，据他介绍青海自古是移民地区，目前是以汉族为主，而部分口音类似于南京周边的方言。青海亦是三江源头为长江，黄河，澜沧江的源头汇水区，比较好玩的景区有青海湖和贵德，土产有牦牛干，人参果，裸鲤，毛毯等。谈话中时间过得飞快，期间途遇到2个收费站，让我对外地的路费有了新的认识。进入城区后先上到高架，大致可以一览市区全貌，据司机师傅介绍目前西宁市区的房价是在6千左右，要想在此处安家却也不容易啊！
在城区里穿行片刻就抵达了旅馆，与本地的2位从未谋面的同事寒暄过后，自然是要先祭祭五脏的；他们带我去旅馆旁边一个样式古老的美食城就餐，西北向来以面食出名，古来就说西北的水偏碱做面食十分相宜，其名声远胜南方，此外就多是牛羊肉，大盘鸡之属了。牛羊肉做主食，南方人多半受不了，我点了一叠面，口味尚佳，比起上海六七元的阳春面这里要便宜得多了。
吃完饭匆匆赶赴客户单位，路上竟堵了许久，想不到此处下午1点多也会有rush hour。
来到客户单位已是2点多，从客户的张科长，朱工程师口中大致了解了系统规模，这里主用的一套Linux上的RAC系统共有3个节点，硬件是三台Dell的3950 Pc Server以及一台IBM DS4700存储，三个实例接受不同地区的业务，客户目前对这套RAC系统的性能不顶满意，希望此次技术交流能够提出初步的方案，另外他们对与主机上较高的内存使用率表示疑问。
我首先抓取了RDA以及AWR报告，托福于这些工具Oracle的诊断调优信息收集已经十分集成了。这个系统居然还采用了ASM,Linux上RAC系统大致可以采取以下几种存储管理方式:裸设备(最容易也最麻烦),NFS(生产环境无法用),ASM(Oracle 推荐),OCFS2(11g被抛弃了),其他GPFS(譬如RedHat GPFS,Veritas Cluster Filesystem);10g下最时髦的管理方式还属ASM,ASM技术新颖，但普遍认为10gASM还不够成熟，至少客户应用上经验不足，一旦出现问题，除了向Oracle GCS求助外很少能找到其他解决方法。而且这套ASM系统直接使用形如/dev/sd*的块设备作为disk，不同于裸设备，使用块设备将会引起操作系统对块的进一步缓存(OS CACHE)，这套系统除了跑数据库外没有其他应用，总内存为24g而每个实例使用的SGA不足5g,绝大多数内存都用作了缓存块设备。
[oracle@qhds2 ~]$ free
total used free shared buffers cached
Mem: 16408324 16276472 131852 0 44184 13969312
-/+ buffers/cache: 2262976 14145348
Swap: 2031608 90224 1941384
如上所示空闲物理内存131852即128M,OS cache为13G。Oracle使用的高速缓存(Buffer_Cache)要较OS的cache更为有效，而目前三个实例设置的Buffer_cache为2-3G不等，建议客户调大Buffer_Cache至5-10G，同时为杜绝操作系统因物理内存不足，瞬间换页造成宕机事件发生的可能性，建议客户将Linux内核参数vm.min_free_kbytes 设置为512000(即总是保留500M的空闲物理内存)。
数据库中三个实例，整体负载(Load)处于较低的水平。相对而言1号实例最高,16小时快照内1号实例上Average Active Session（平均活动会话数，为10g中数据库负载的重要指标）为(AAS=DB_TIME/Elapsed=2327/960)2.42；2号实例上AAS为592/960=0.61；3号上AAS为1405/960=1.463。
分析AWR 报告可以发现：
1号实例:
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| enq: TM – contention | 98,218 | 40,208 | 409 | 28.8 | Application |
| gc cr multi block request | 5,877,437 | 36,369 | 6 | 26.0 | Cluster |
| CPU time | 8,511 | 6.1 | |||
| gc buffer busy | 64,538 | 5,455 | 85 | 3.9 | Cluster |
| db file parallel read | 519,589 | 4,116 | 8 | 2.9 | User I/O |
2号实例:
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| CPU time | 7,962 | 22.4 | |||
| gc cr multi block request | 2,903,270 | 6,027 | 2 | 17.0 | Cluster |
| db file scattered read | 843,958 | 3,070 | 4 | 8.6 | User I/O |
| db file parallel read | 421,792 | 3,010 | 7 | 8.5 | User I/O |
| PX Deq Credit: send blkd | 248,625 | 1,755 | 7 | 4.9 | Other |
3号实例:
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| CPU time | 18,381 | 21.8 | |||
| gc cr multi block request | 6,534,528 | 18,204 | 3 | 21.6 | Cluster |
| db file scattered read | 1,562,020 | 5,575 | 4 | 6.6 | User I/O |
| db file parallel read | 563,113 | 4,281 | 8 | 5.1 | User I/O |
| db file sequential read | 709,698 | 2,502 | 4 | 3.0 | User I/O |
主要的几个等待是:
“Gc Multi Block request”为全局缓存多块请求事件，是一种集群类等待事件。当RAC中每个实例所涉及的数据严重交叉时该事件将成为数据库的主要性能瓶颈。在不能通过改善应用程序集群适应性或对表进行实例划分分区的情况下，建议客户将RAC中的多个实例划为主用和备用实例以获取更好的性能。举例来说可以将硬件性能较好的一号主机作为主用主机，而二三号主机作为备用主机平时不接受应用程序会话，在一号主机需要OFFLINE时做应急用。客户使用RAC系统的最主要目的是为了获取高可用性，而通过向客户解释RAC Global Cache大致的工作原理后，客户首肯了这个方案。
EnQ:TM-Contention即TM锁队列等待，一般是应用程序对表执行共享级以上的表锁时(包括:S,SSX,X锁)且锁定时间过长或表上有外键约束引起的，大多数OLTP类型应用中对整个表的S,SX,X锁是不必要的，只需要行级锁(ROW LEVEL LOCK)即可以满足需要。客户本身也在怀疑应用程序存在问题，但苦于没有有力证据，无法督促开发商修改程序，又因为目前这套应用数据量，业务量都较小，这种矛盾仍不尖锐，所以总是没有行之有效地整改。
讨论完这些已将界6点，匆匆和同事回旅馆吃了晚饭。趁着天还没彻底晚，打的找了当地一个专卖土特产的超市，因为不想回去是负重过高，随便买了几包牦牛干，人参果。这忙碌的一天也就算完了，回旅馆睡觉！
第二天早晨抓紧时间再赶到客户处，另看了一套系统，因为要赶下午2点的飞机，所以行色匆匆，客户平常是使用本地java客户端的EM和10g上web形式的EM监控数据库的，在机房中的笔记本上使用SYS用户可以成功登陆，但在科室里的台式机上登陆就报用户名/密码错误的信息，初步怀疑是设置了SQLNET.ORA的某些参数，由于时间无多，这个问题只能摆脱后续的工程师解决了，呵呵！
回程仍需到西安转机，不过由于机票订得晚，经济舱已经满座了，十分不巧地做了会头等舱，也算一种福利吧。
呵呵，西宁二十四小时，对这个城市管中窥豹了！
The customers got this error every alternative days on Version 9.2.0.7. They did increase the shared pool from 450MB to 704MB. Let’s see the alert.log and the last generated trace file.
SQL> l 1 select nam.ksppinm NAME, 2 val.KSPPSTVL VALUE 3 from x$ksppi nam, 4 x$ksppsv val 5 where nam.indx = val.indx 6 and nam.ksppinm like '%shared%' 7* order by 1 SQL> / NAME VALUE ---------------------------------------------------------------- ---------- _all_shared_dblinks _shared_pool_reserved_min_alloc 4400 _shared_pool_reserved_pct 5 hi_shared_memory_address 0 max_shared_servers 20 shared_memory_address 0 shared_pool_reserved_size 31876710 shared_pool_size 738197504 shared_server_sessions 0 shared_servers 0 10 rows selected. SQL> select FREE_SPACE,LAST_FAILURE_SIZE,REQUEST_FAILURES,LAST_MISS_SIZE from v$shared_pool_reserved; FREE_SPACE LAST_FAILURE_SIZE REQUEST_FAILURES LAST_MISS_SIZE ---------- ----------------- ---------------- -------------- 19018368 456 725 0 1 row selected. Alert log ~~~~~~~~~~ Thu May 28 19:05:11 2009 Errors in file /u01/app/oracle/admin/preg062/udump/preg062_ora_17314.trc: ORA-00600: internal error code, arguments: [729], [10992], [space leak], [], [], [], [], [] Trace File ~~~~~~~~~~~ Dump file /u01/app/oracle/admin/preg062/udump/preg062_ora_17314.trc Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production ORACLE_HOME = /u01/app/oracle/product/920preg062 System name: SunOS Node name: iccscorp Release: 5.9 Version: Generic_122300-22 Machine: sun4u Instance name: preg062 Error ----- ORA-00600: internal error code, arguments: [729], [10992], [space leak], [], [], [], [], [] Current SQL ----------- None Call Stack ---------- ksedmp kgeriv kgesiv ksesic2 ksmuhe ksmugf ksuxds ksudel opilof opiodr ttcpip opitsk opiino opiodr opidrv sou2o main start Session info ------------ SO: 411536570, type: 4, owner: 40e583e08, flag: INIT/-/-/0x00 (session) trans: 0, creator: 40e583e08, flag: (41) USR/- BSY/-/-/DEL/-/- DID: 0001-00F9-00000F5B, short-term DID: 0000-0000-00000000 txn branch: 0 oct: 0, prv: 0, sql: 417fbbf18, psql: 416fa9840, user: 31/MATRIXTWO O/S info: user: matrixadmin, term: , ospid: 17281, machine: iccscorp program: mql@iccscorp (TNS V1-V3) last wait for 'SQL*Net message from client' blocking sess=0x0 seq=3208 wait_time=836 driver id=54435000, #bytes=1, =0 ORA-04031 details ~~~~~~~~~~~~~ Begin 4031 Diagnostic Information Allocation Request ------------------- Allocation request for: kkslpkp - literal info. Heap: 3d6fb45f0, size: 4200 Call stack ----------- ksm_4031_dump ksmasg kghnospc kghalp kghsupmm kghssgai kkslpkp kkslpgo kkepsl kkecdn kkotap kkoiqb kkooqb kkoqbc apakkoqb apaqbd apadrv opitca kkssbt kksfbc kkspfda kpodny kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o main Session Info ------------- SO: 411536570, type: 4, owner: 40e583e08, flag: INIT/-/-/0x00 (session) trans: 0, creator: 40e583e08, flag: (41) USR/- BSY/-/-/-/-/- DID: 0001-00F9-00000F5B, short-term DID: 0000-0000-00000000 txn branch: 0 oct: 0, prv: 0, sql: 4311e4e30, psql: 4311e4e30, user: 31/MATRIXTWO O/S info: user: matrixadmin, term: , ospid: 17281, machine: iccscorp program: mql@iccscorp (TNS V1-V3) application name: mql@iccscorp (TNS V1-V3), hash value=0 last wait for 'SQL*Net message from client' blocking sess=0x0 seq=3196 wait_time=1975 driver id=54435000, #bytes=1, =0 Number of Subpools and allocations ---------------------------------- =============================== Memory Utilization of Subpool 1 =============================== Allocation Name Size _________________________ __________ "free memory " 25065216 "miscellaneous " 14914048 =============================== Memory Utilization of Subpool 2 =============================== Allocation Name Size _________________________ __________ "free memory " 9306608 "miscellaneous " 19358000 =============================== Memory Utilization of Subpool 3 =============================== Allocation Name Size _________________________ __________ "free memory " 25209192 "miscellaneous " 10192440 =============================== Memory Utilization of Subpool 4 =============================== Allocation Name Size _________________________ __________ "free memory " 15005800 "miscellaneous " 11097176 LIBRARY CACHE STATISTICS: namespace gets hit ratio pins hit ratio reloads invalids -------------- --------- --------- --------- --------- ---------- ---------- CRSR 400143894 0.951 1821611655 0.969 10619950 63892 TABL/PRCD/TYPE 230543353 0.996 255666572 0.934 7504796 0 Connection Mode & Relevant parameters -------------------------------------- sga_max_size = 3159332528 shared_pool_size = 738197504 db_cache_size = 956301312 cursor_sharing = SIMILAR pga_aggregate_target = 2097152000
It seems the ORA-04031 is the main issue, which triggered the ORA-00600 [729] error, after the session got abnormally terminated or killed.
Memory request failed on “shared pool” while trying to allocate 4200 bytes even though you have 9 to 25 mb of free space in 4 subpools.
I have reviewed the alert, trace and RDA report and following are my findings.
# Shared_pool_size is 738197504 and 4 subpools are used.
# Memory request failed for 4200 bytes.
# None of the components in subpools are showing any abnormal growth.
Suggestion
—————-
Issue is not exactly matching with any known bugs. Modifying the memory related parameters will help to avoid these errors.
1) Reduce the number of subpools to 2 from 4, by setting “_kghdsidx_count”=2 and restart the database. This will also help to reduce the shared pool fragmentation. Refer Note 396940.1
SQL> alter system set “_kghdsidx_count”=2 scope=spfile;
2) I have checked the memory request failure which is showing the size of 4200 bytes plus.
Set the _shared_pool_reserved_min_alloc=4000 which will help to allocate memory in reserved area, if the request is greater than 4000 bytes.
alter system set “_shared_pool_reserved_min_alloc”=4000 scope=spfile;
3) Set the shared_pool_reserved_size to 10 to 15 % of the shared pool size, by setting _shared_pool_reserved_pct parameter.
SQL> alter system set “_shared_pool_reserved_pct”=10 scope=spfile;
Implement the above changes and restart the database. This will help to avoid the shared pool fragmentation and helps to avoid the ORA-04031/ORA-00600 [729] errors.
After applying above change ,the error has not occured again.
set echo off;
set feedback off;
set heading off;
set linesize 128;
set show off;
set pagesize 55;
set trimspool on;
set verify off;
column "SID AND SERIAL#" FORMAT A19
col SNAP_COLUMN new_value SNAP_TIME
col SNAP_EOF_NAME new_value EOF_NAME
col SNAP_HOST_NAME new_value THE_HOST_NAME
col SNAP_INSTANCE_NAME new_value THE_NAME_OF_THE_INSTANCE
col SNAP_RDBMS_VERSION new_value THE_RDBMS_VERSION
set term off;
select to_char(sysdate,'YYYYMMDD_HH24MISS') "SNAP_COLUMN" from dual;
select trim(host_name) "SNAP_HOST_NAME" from v$instance;
select trim(instance_name) "SNAP_INSTANCE_NAME" from v$instance;
select trim(version) "SNAP_RDBMS_VERSION" from v$instance;
select '&THE_NAME_OF_THE_INSTANCE'||'_'||'&SNAP_TIME'||'.LST' "SNAP_EOF_NAME" from dual;
drop table maxpgauga;
create table maxpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.logon_time
from v$statname n,
v$sesstat s,
v$session s2
where n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory max%';
drop table curpgauga;
create table curpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.logon_time
from v$statname n,
v$sesstat s,
v$session s2
where n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory' and
name not like 'session%memory max%';spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME
select ‘Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session’ from dual;
select ‘Host……..: ‘||’&THE_HOST_NAME’ from dual;
select ‘Name……..: ‘||’&THE_NAME_OF_THE_INSTANCE’ from dual;
select ‘Version…..: ‘||’&THE_RDBMS_VERSION’ from dual;
select ‘Startup Time: ‘||to_char(min(logon_time),’YYYY-MM-DD HH24:MI:SS’) from curpgauga;
select ‘Current Time: ‘||to_char(sysdate,’YYYY.MM.DD-HH24:MI:SS’) from dual;
select ‘Worst possible value of concurrent PGA + UGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
sum(value),
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from maxpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select ‘Worst possible total and average values of concurrent PGA + UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||trunc(count(*)/2)||’ sessions.’ from maxpgauga;
select ‘Approximate value of current PGA + UGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
sum(value),
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from curpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select ‘Current total and average values of concurrent PGA + UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||trunc(count(*)/2)||’ sessions.’ from curpgauga;
select ‘Maximum value of PGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from maxpgauga
where name like ‘session pga memory max%’
order by value desc, sid desc;
set heading off
select ‘Worst possible total and average values of concurrent PGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from maxpgauga where name like ‘session pga memory max%’;
select ‘Maximum value of UGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from maxpgauga
where name like ‘session uga memory max%’
order by value desc, sid desc;
set heading off
select ‘Worst possible total and average values of concurrent UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from maxpgauga where name like ‘session uga memory max%’;
select ‘Current value of PGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from curpgauga
where name like ‘session pga memory%’
order by value desc, sid desc;
set heading off
select ‘Current total and average values of concurrent PGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from curpgauga where name like ‘session pga memory%’;
select ‘Current value of UGA memory usage per session:’ from dual;
set heading on
select trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from curpgauga
where name like ‘session uga memory%’
order by value desc, sid desc;
set heading off
select ‘Current total and average values of concurrent UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from curpgauga where name like ‘session uga memory%’;
select ‘Current SGA structure sizings:’ from dual;
show sga
select ‘Some initialization parameter values at instance startup:’ from dual;
select trim(name)||’=’||value
from v$parameter
where name in (‘__shared_pool_size’,
‘large_pool_size’,
‘pga_aggregate_target’,
‘sga_target’,
‘shared_pool_size’,
‘sort_area_size’,
‘streams_pool_size’) order by name;
select ‘Current Time: ‘||TO_CHAR(sysdate,’YYYY.MM.DD-HH24:MI:SS’) from dual;
spool off
set feedback on;
set heading on;
set linesize 80;
set pagesize 14;
set verify on;
set echo on;
Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session
5月26日某客户告警日志中出现的ORA-00600 [4400],[48]错误记录，并产生了trace文件:，
*** SERVICE NAME:(ETL) 2010-05-26 16:45:45.930
*** SESSION ID:(262.12024) 2010-05-26 16:45:45.930
*** 2010-05-26 16:45:45.930
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4400], [48], [], [], [], [], [], []
ORA-10387: parallel query server interrupt (normal)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 0FFFFFFFF ? 000000020 ?
ksedmp+0290 bl ksedst 1048DFFC0 ?
ksfdmp+0018 bl 03F5B014
kgeriv+0108 bl _ptrgl
kgeasi+0118 bl kgeriv 10496FEC8 ? 000000002 ?
700000010008000 ? 110000AD0 ?
110190290 ?
ktcddt+012c bl kgeasi 110190110 ? 110450040 ?
113000001130 ? 200000002 ?
100000001 ? 000000000 ?
000000030 ? 000000013 ?
ktcsod+0384 bl ktcddt 0000003C0 ? 000000018 ?
1048D4808 ?
kssdch_stage+0758 bl _ptrgl
....................................................
----- End of Call Stack Trace -----
******************* Dumping process map ****************
377144 : ora_p001_ETL
100000000 93710K read/exec oracle
1100007ef 2372K read/write oracle
9fffffff0000000 44K read/exec /usr/ccs/bin/usla64
9fffffff000b30a 0K read/write /usr/ccs/bin/usla64
900000000375880 484K read/exec /usr/lib/liblvm.a[shr_64.o]
9001000a0121ac8 135K read/write /usr/lib/liblvm.a[shr_64.o]
900000000426180 74K read/exec /usr/lib/libcfg.a[shr_64.o]
9001000a00f5ed0 26K read/write /usr/lib/libcfg.a[shr_64.o]
90000000034e280 2K read/exec /usr/lib/libcrypt.a[shr_64.o]
9001000a00d4760 0K read/write /usr/lib/libcrypt.a[shr_64.o]
90000000069a7b0 4K read/exec /usr/lib/libc.a[aio_64.o]
9001000a022a340 0K read/write /usr/lib/libc.a[aio_64.o]
90000000035f300 85K read/exec /usr/lib/libodm.a[shr_64.o]
9001000a00d5c08 35K read/write /usr/lib/libodm.a[shr_64.o]
9000000003ef080 83K read/exec /usr/lib/libperfstat.a[shr_64.o]
9001000a01d0818 9K read/write /usr/lib/libperfstat.a[shr_64.o]
900000000358000 0K read/exec /usr/lib/libdl.a[shr_64.o]
9001000a0219000 0K read/write /usr/lib/libdl.a[shr_64.o]
9000000007ff100 8588K read/exec /oracle/product/10.2.0/lib/libjox10.a[shr.o]
8001000a0000038 585K read/write /oracle/product/10.2.0/lib/libjox10.a[shr.o]
9000000004a0000 228K read/exec /usr/lib/libpthreads.a[shr_xpg5_64.o]
9001000a0144000 558K read/write /usr/lib/libpthreads.a[shr_xpg5_64.o]
900000000045500 2966K read/exec /usr/lib/libc.a[shr_64.o]
9001000a0000788 844K read/write /usr/lib/libc.a[shr_64.o]
Total 110843K
******************* End of process map dump ****************
===================================================
PROCESS STATE
-------------
Process global information:
process: 7000000cf48bf98, call: 7000000b40ebdf0, xact: 7000000cdece7a8, curses: 7000000cf666540, usrses: 7000000cf666540
----------------------------------------
SO: 7000000cf48bf98, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=43, calls cur/top: 7000000b40ebdf0/7000000b40ebdf0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 115 0 4
last post received-location: kslpsr
last process to post me: 7000000cf479c38 1 6
last post sent: 0 0 251
last post sent-location: kxfpqr: QC
last process posted by me: 7000000cf48a7f8 10 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 7000000cf50bd50
O/S info: user: oracle, term: UNKNOWN, ospid: 377144
OSD pid info: Unix process pid: 377144, image: oracle@etl_a (P001)
SO: 7000000cf666540, type: 4, owner: 7000000cf48bf98, flag: INIT/-/-/0x00
(session) sid: 262 trans: 7000000cdece7a8, creator: 7000000cf48bf98, flag: (c0100041) USR/- BSY/-/-/DEL/-/-
DID: 0001-0028-000FFB1E, short-term DID: 0001-002B-00151DA1
txn branch: 7000000cdf412c8
oct: 0, prv: 0, sql: 0, psql: 0, user: 26/ETL
O/S info: user: bo, term: pts/0, ospid: 377144, machine: etl_a
program: oracle@etl_a (P001)
last wait for 'SQL*Net message from dblink' blocking sess=0x0 seq=45 wait_time=476 seconds since wait started=0
driver id=0, #bytes=1, =0
Dumping Session Wait History
for 'SQL*Net message from dblink' count=1 wait_time=476
driver id=0, #bytes=1, =0
for 'SQL*Net message to dblink' count=1 wait_time=1
driver id=0, #bytes=1, =0
for 'log file sync' count=1 wait_time=7483
buffer#=ca9, =0, =0
for 'log file sync' count=1 wait_time=358
buffer#=ca9, =0, =0
for 'PX Deq: Execution Msg' count=1 wait_time=15527
sleeptime/senderid=1001ffff, passes=1, =0
for 'log file sync' count=1 wait_time=11855
buffer#=c57, =0, =0
for 'PX Deq: Execution Msg' count=1 wait_time=277142
sleeptime/senderid=1001ffff, passes=1, =0
for 'direct path write' count=1 wait_time=25
file number=5, first dba=103ca, block cnt=3
for 'direct path write' count=1 wait_time=6
file number=4, first dba=1abe1, block cnt=8
for 'row cache lock' count=1 wait_time=3307
cache id=5, mode=0, request=5
temporary object counter: 0
----------------------------------------
Virtual Thread:
kgskvt: 7000000c0e86b58, sess: 7000000cf666540, vc: 0, proc: 7000000cf48bf98
consumer group cur: OTHER_GROUPS (upd? 0), mapped: DEFAULT_CONSUMER_GROUP, orig:
vt_state: 0x200, vt_flags: 0x30, blkrun: 0
is_assigned: 1, in_sched: 0 (0)
vt_active: 0 (pending: 0)
used quanta: 0 (cg: 0)
cpu start time: 0, quantum status: 0x0
quantum checks to skip: 0, check thresh: 0
idle time: 0, active time: 0 (cg: 0)
cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0)
queued time outs: 0, time: 0 (cur 0, cg 0)
calls aborted: 0, num est exec limit hit: 0
undo current: 0k max: 0k
----------------------------------------
UOL used : 0 locks(used=0, free=2)
KGX Atomic Operation Log 7000000bdcf0568
Mutex 0(0, 0) idn 0 oper NONE
Cursor Parent uid 262 efd 9 whr 11 slp 0
oper=NONE pt1=7000000c69d0878 pt2=7000000bef37090 pt3=7000000b47119c8
pt4=0 u41=1 stt=0
KGX Atomic Operation Log 7000000bdcf05b0
Mutex 7000000bdc2d028(0, 2) idn 0 oper NONE
Cursor Stat uid 262 efd 12 whr 1 slp 0
oper=NONE pt1=7000000bdc2cef8 pt2=0 pt3=0
pt4=0 u41=0 stt=0
KGX Atomic Operation Log 7000000bdcf05f8
Mutex 0(0, 0) idn 0 oper NONE
Library Cache uid 262 efd 0 whr 0 slp 0
----------------------------------------
SO: 7000000cf7e3ec8, type: 5, owner: 7000000cf666540, flag: INIT/-/-/0x00
(enqueue) DX-00000010-00000000 DID: 0001-002B-00151DA9
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x2
res: 0x7000000cf8bd0b8, mode: X, lock_flag: 0x0
own: 0x7000000cf666540, sess: 0x7000000cf666540, proc: 0x7000000cf48bf98, prv: 0x7000000cf8bd0c8
从trace文件名就可以看出出错的是某个并行子进程，其最近调用堆栈为ktcsod->ktcddt->kgeasi(报错)，ktcsod与ktcddt均为Oracle中内核事务控制模块函数 (Kernel Transaction Control),又该并行子进程最近等待为”SQL*Net message from dblink”事件，共等待476s(wait_time=476 seconds)。trace显示该并行会话包含分布式事务锁：
SO: 7000000cf7e3ec8, type: 5, owner: 7000000cf666540, flag: INIT/-/-/0x00
(enqueue) DX-00000010-00000000 DID: 0001-002B-00151DA9
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x2
res: 0x7000000cf8bd0b8, mode: X, lock_flag: 0x0
own: 0x7000000cf666540, sess: 0x7000000cf666540, proc: 0x7000000cf48bf98, prv: 0x7000000cf8bd0c8
即DX(Distributed transaction entry)锁;可以猜测该并行会话在等待远程事务被清理，但等待超时，故报错。
查询metalink可以发现Bug 5223587与该错误一致，该Bug的特征为：
1. 出现ORA-600[4400]，且调用栈为ktcddt ，说明涉及到了分布式事务
2. trace显示当时无打开的游标
3. 可以看到当时的等待为’SQL*Net message from dblink’事件
Metalink文档[ID 444108.1]描 述该错误会在涉及远程数据库的并行DML操作中出现，但该错误仅会在清理阶段出现，故实际无影响(It is not critical as such, since it is happening during the cleanup of the operation.)。
该Bug可以通过实施Patch 5223587修复， 目前该补丁只有对应基础版本10.2.0.2和10.2.0.3的版本，该补丁在 10.2.0.3 上有AIX power(64 bit)和Solaris Sparc(64 bit)的版本，10.2.0.2上只有linux-64 与HP-UX的版本。因为该Bug一般不会产生不良影响，故实际上不建议应用补丁。
