11g新特性

以下是Maclean Liu所编写的Oracle 11g新特性的文章汇总列表:

 

11g新特性:A useful View V$DIAG_INFO
11gR2新特性:LMHB Lock Manager Heart Beat后台进程
给11gR2 RAC添加LISTENER监听器并静态注册
11.2 中Oracle Cluster Registry(OCR)可选的存储设备
11.2.0.3 实例启动现在提供Large Pages Information大内存页信息了
Does Duplicate Target Database need Pre-existing DB backup?
11gR2新特性:STANDBY_MAX_DATA_DELAY
11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示
11g新特性SQL执行计划管理(SQL Plan Management)
11g新特性-在线实施补丁online patching
11g新特性:Rolling Upgrade With Physical Standby
11g新特性-SQL PLUS 错误日志
11g新特性:Note raised when explain plan for create index
11g compression 新特性
11g新特性之闪回事务处理取消
11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR
11g默认审计选项
Examine 11g automatic block Corruption recovery
Duplicate standby database from active database
11g Database Installation flow
Setup Oracle Direct NFS Client
Recreate failovered primary database using Flashback Database
11g中的db_block_checking参数
11.2.0.2补丁集安装体验
Oracle 11g中数据库能有多大?
undo backup optimization does not work on 11.2.0.1?
11g新特性之IO校准(IO Calibration)
11g中AWR新快照视图
11g r2中对闪回数据归档的增强
11g Release 2 enhanced Tablespace Point In Time Recovery
11g新特性:Streams同步捕获
Applying online patch on 11gr2
How to recover from root.sh on 11.2 Grid Infrastructure Failed
Uninstall/Remove 11.2.0.2 Grid Infrastructure & Database in Linux
为11.2.0.2 Grid Infrastructure添加节点
Find password cracker in 11g
11g Multi-Column Correlation Stats and Dynamic Sampling
11g内存管理新特性的internal表现
Oracle database 11g release2发布
How to check and disable Adaptive Cursor Sharing in 11g
How does SGA/PGA allocate on 11g AMM?
crsctl status resource -t -init in 11.2.0.2 grid infrastructure
Learning 11g New Background Processes

Slide:11g新特性-在线实施补丁online patching

Slide:Upgrade 11.2.0.1 RAC DB/RDBMS to 11.2.0.2 in Linux By Maclean

Know Current Oracle Database Version Usage

前2周有客户向我咨询现在市面上的Oracle数据库各版本的使用率,考虑到10g将会在2013年超出Extentd Support期,客户希望找到合适的时机升级到11gr2上。

在网上找了一圈,包括IDC似乎都没有公开的调查结果。反而在OTN Database Forum上找到一个”Poll Results: Your production database version”老外搞的生产库版本投票。

这个投票结果在一定程度上反映了国际上Oracle数据库各版本的使用率:10gr2目前使用率最高,11gr2在迅速追上。但因为国外的IT建设要领先于国内,所以我们可以推测国内的实际情况是9ir2和10gr2占主导地位,11gr2仍打酱油。

oracle_database_version_usage_poll

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

客户的一套11.2.0.1 RAC系统采用ASM diskgroup 存放ocr和votedisk,该REG diskgroup中的某个LUN disk由于硬件的原因损坏了,导致冗余的votedisk表决磁盘有一个处于OFFLINE状态,客户希望能删除该OFFLINE的votedisk并新增一个可用的。

在删除该votedisk文件时出现了CRS-4258的错误,错误如下:

crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. OFFLINE  5b3380d6367e4f94bf19e9db5f2f684e ()  []
 2. ONLINE   6802e6d139354fb3bf95725dd01a02fd (/dev/ocr2) [REG]
 3. ONLINE   a433d51ebd2d4facbfc8e95b017f5393 (/dev/asm-disk1) [REG]
 4. ONLINE   3784d344bffa4f6ebff21c4dd3c873bd (/dev/asm-disk2) [REG]
Located 4 voting disk(s).

crsctl delete css votedisk 5b3380d6367e4f94bf19e9db5f2f684e
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

居然无法移除ASM存储上的voting files,太搞笑了。

客户在MOS上找到了CRS-4258相关问题的Note:

CRS-4258: Addition and Deletion of Voting Files are not Allowed Because the Voting Files are on ASM in 11gR2 [ID 1060146.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.1 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM in 11gR2.
Changes
Stale voting files are seen after accidently dropping one of ASM disks belonging to the ASM diskgroup where voting files are stored.
And CRS-4258 occurs when trying to delete the stale voting files using crsctl delete css votedisk FUID.
[root@grid]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5b91aad0a2184f3dbfa8f970e8ae4d49 (/dev/oracleasm/disks/ASM10) [PLAY]
2. ONLINE 53b1b40b73164f9ebf3f498f6d460187 (/dev/oracleasm/disks/ASM9) [PLAY]
3. OFFLINE 82dfd04b96f14f6dbf36f5a62b118f61 () []
[root@grid]# crsctl delete css votedisk 82dfd04b96f14f6dbf36f5a62b118f61
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM
Cause
1. Seeing stale voting files is due to bug 9024611.
2. "delete" command is not available , only "replace" command is available when voting files are stored on  ASM diskgroup.    
Please see Oracle Clusterware Administration and Deployment Guide11g Release 2 (11.2)
Solution
1. This issue is permanently fixed in 11.2.0.2.0.
2. Apply patch 9024611. Please contact Oracle support if this patch is not available on your platform.
3. If CSS has stale voting files even after applying patch 9024611, do the following workaround -
WORKAROUND:
Do something to trigger ASM to try to relocate the voting file.
e.g)  $ crsctl replace votedisk  +asm_disk_group   --- Put available ASM diskgroup
$ crsctl query css votedisk         --- Check if voting files are all online on the new ASM diskgroup
$ crsctl replace votedisk +PLAY    -- Put the original ASM diskgroup where voting files were 
4. If the workaround above cannot be followed for any reason then you can request the patch for unpublished bug 9409327 for your platform.
References
BUG:9294664 - NOT ABLE TO REMOVE THE VOTEDISK WHICH IS OFFILNE
Hdr: 9294664 11.2.0.1 PCW 11.2.0.1 ADMUTL PRODID-5 PORTID-226 9024611
Abstract: NOT ABLE TO REMOVE THE VOTEDISK WHICH IS OFFILNE
PROBLEM:
--------
crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).
[root@sdc-drrac01 grid]# crsctl delete css votedisk 
a9b785a59c3c4f67bf15babc67ffb79a
CRS-4258: Addition and deletion of voting files are not allowed because the 
voting files are on ASM
DIAGNOSTIC ANALYSIS:
--------------------
Ct is performing some voting disk failover scenarios in which he has removed 
the 2 votedisk which were on ASM buy drop disk using asmlib and after that 
recreating the disk again and start the cluster in exclusive mode and start 
the ASM and mount the diskgourp So that rebalancing has been done but after 
that
crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).
and not able to drop the vote disk which is offiline 
WORKAROUND:
-----------
n/a
RELATED BUGS:
-------------
as per bug 9024611 tried the workaround:
but while running 
crsctl css votedisk delete 
we got syntax error and found that there is no command with crsctl css ...

这个Note说明不能移除ASM存储内voting files的问题在11.2.0.2.0上已经解决了,也可以通过安装one-off patch 9024611来修复。

但是实际在11.2.0.2上测试可以发现仍旧无法删除ASM上的voting files:

root@rh2 ~]# crsctl query crs  releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.2.0]
[root@rh2 ~]# crsctl query crs  activeversion
Oracle Clusterware active version on the cluster is [11.2.0.2.0]
[grid@rh2 ~]$ /s01/grid/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.1
Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.
Oracle Home       : /s01/grid
Central Inventory : /s01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/grid/oui
Log file location : /s01/grid/cfgtoollogs/opatch/opatch2011-08-04_18-50-34PM.log
Patch history file: /s01/grid/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /s01/grid/cfgtoollogs/opatch/lsinv/lsinventory2011-08-04_18-50-34PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1): 
Oracle Grid Infrastructure                                           11.2.0.2.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
Rac system comprising of multiple nodes
Local node = rh2
Remote node = rh3
--------------------------------------------------------------------------------
OPatch succeeded.
[root@rh2 ~]# crsctl delete css votedisk a433d51ebd2d4facbfc8e95b017f5393
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

又是一个伪修复的Bug….!!

无法,寄希望与replace能解决问题,结果发现:

crsctl replace votedisk +DATA
Failed to create voting files on disk group DATA.
Change to configuration failed, but was successfully rolled back.
CRS-4000: Command Replace failed, or completed with errors.

方法四中指出的unpublished bug 9409327(Patch 9409327: OFFLINE VF ENTRY REMAINS AFTER PATCH FOR BUG 9024611),目前仅在IBM AIX on POWER Systems (64-bit)的11.2.0.1上有对应的补丁。

Oracle RAC内部错误:ORA-00600[kjbmprlst:shadow]一例

一套Linux x86-64上的11.2.0.1 4节点RAC系统中LMS GCS服务进程遭遇到内部错误ORA-00600[kjbmprlst:shadow],导致节点实例意外终止,具体日志如下:

Fri Jul 08 02:04:43 2011
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc  (incident=1011732):
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/PROD/PROD1/incident/incdir_1011732/PROD1_lms1_536_i1011732.trc
Fri Jul 08 02:04:44 2011
Trace dumping is performing id=[cdmp_20110708020444]
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
LMS1 (ospid: 536): terminating the instance due to error 484
Fri Jul 08 02:04:45 2011
opiodr aborting process unknown ospid (27387) as a result of ORA-1092
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_diag_513.trc
Fri Jul 08 02:04:54 2011
Termination issued to instance processes. Waiting for the processes to exit
Fri Jul 08 02:04:58 2011
ORA-1092 : opitsk aborting process

该ORA-00600[kjbmprlst:shadow]错误定位为11.2.0.1上的Bug 10121589或Bug 9458781:

Bug 10121589  ORA-600 [kjbmprlst:shadow] can occur in RAC
Affects:
Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected 	Versions BELOW 12.1
Versions confirmed as being affected 	
11.2.0.1 
Platforms affected	Generic (all / most platforms affected)
Fixed:
This issue is fixed in	
12.1 (Future Release)
11.2.0.2 Bundle Patch 2 for Exadata Database
11.2.0.1 Bundle Patch 7 for Exadata Database 
Symptoms:
Related To:
Internal Error May Occur (ORA-600)
ORA-600 [kjbmprlst:shadow] 
RAC (Real Application Clusters) / OPS 
Description
An ORA-600 [kjbmprlst:shadow] can occur if the fix for bug 9979039
is present.
Note:
One off patches for 10200390 should also include this fix.
Bug 9458781  Missing close message to master leaves closed lock dangling crashing the instance with assorted Internal error
Affects:
Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected 	Versions >= 11.2.0.1 but BELOW 11.2.0.2
Versions confirmed as being affected 	
11.2.0.1 
Platforms affected	Generic (all / most platforms affected)
Fixed:
This issue is fixed in	
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 4 for Exadata Database 
Symptoms:
Related To:
Instance May Crash
Internal Error May Occur (ORA-600)
ORA-600 [KJBMPRLST:SHADOW]
ORA-600 [KJBMOCVT:RID]
ORA-600 [KJBRREF:PKEY]
ORA-600 [KJBRASR:PKEY] 
RAC (Real Application Clusters) / OPS 
Description
A lock is closed without sending a message to the master.
This causes closed lock dangling at the master crashing the instance with different internal errors.
Reported internal errors so far are :
- KJBMPRLST:SHADOW
- KJBMOCVT:RID
- KJBRREF:PKEY
- KJBRASR:PKEY

该kjbmprlst:shadow内部函数用以管理kjbm shadow锁(/libserver10.a/kjbm.o )信息,存在某个已关闭的lock没有及时message给master node的代码漏洞,目前除了安装补丁外没有已验证的workaround办法(disable drm似乎是无效的):

oradebug lkdebug (track resources, take dumps)
KCL history
KJBL history
KJL history
PCM (GCS) and non-PCM (GES) resources are kept separate and use separate code paths.
GES:
Resource table: kjr and kjrt
Lock table: kjlt
Processes: kjpt
GCS:
Resource table: kjbr
Lock table: kjbl
DLM Structures (continued)
/* PCM resource structure */
typedef struct kjbr {                                /* 68 bytes on sun4u */
kjsolk       hash_q_kjbr;                             /* hash list : hp */
ub4          resname_kjbr[2];	                     /* the resource name */
kjsolk       scan_q_kjbr; /* chain to lmd scan q of grantable resources */
kjsolk       grant_q_kjbr;                 /* list of granted resources */
kjsolk       convert_q_kjbr;       /* list of resources being converted */
ub4          diskscn_bas_kjbr;         /* scn(base) known to be on disk */
ub2          diskscn_wrap_kjbr;        /* scn(wrap) known to be on disk */
ub2          writereqscn_wrap_kjbr;    /* scn(wrap) requested for write */
ub4          writereqscn_bas_kjbr;     /* scn(base) requested for write */
struct kjbl *sender_kjbr;                 /* lock elected to send block */
ub2          senderver_kjbr;                  /* version# of above lock */
ub2          writerver_kjbr;                  /* version# of lock below */
struct kjbl *writer_kjbr;                /* lock elected to write block */
ub1          mode_role_kjbr; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
ub1          flags_kjbr;                        /* ignorewip, free etc. */
ub1          rfpcount_kjbr;                      /* refuse ping counter */
ub1          history_kjbr;                /* resource operation history */
kxid         xid_kjbr;                          /* split transaction ID */
} kjbr ;
/* kjbl - PCM lock structure
** Clients and most of the DLM will use the KJUSER* or KJ_* modes and kscns  */
typedef struct kjbl {                                /* 52 bytes on sun4u */
union {                     /* discriminate lock@master and lock@client */
struct {                                           /* for lock@master */
kgglk        state_q_kjbl;             /* link to chain to resource */
kjbopqi     *rqinfo_kjbl;                             /* target bid */
struct kjbr *resp_kjbl;                   /* pointer to my resource */
} kjbllam;                                 /* KJB Lock Lock At Master */
struct {                                           /* for lock@client */
ub4         disk_base_kjbl;        /* disk version(base) for replay */
ub2         disk_wrap_kjbl;        /* disk version(wrap) for replay */
ub1         master_node_kjbl;                   /* master instance# */
ub1         client_flag_kjbl;     /* flags specific to client locks */
ub2         update_seq_kjbl;               /* last update to master */
} kjbllac;                                 /* KJB Lock Lock At Client */
} kjblmcd;                        /* KJB Lock Master Client Discrimnant */
void  *remote_lockp_kjbl;           /* pointer to client lock or shadow */
ub2    remote_ver_kjbl;                         /* remote lock version# */
ub2        ver_kjbl;                                     /* my version# */
ub2        msg_seq_kjbl;                         /* client->master seq# */
ub2        reqid_kjbl;                         /* requestid for convert */
ub2        creqid_kjbl; /* requestid for convert that has been cancelled */
ub2        pi_wrap_kjbl;                     /* scn(wrap) of highest pi */
ub4        pi_base_kjbl;                     /* scn(base) of highest pi */
ub1        mode_role_kjbl; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
ub1        state_kjbl;       /* _L|_R|_W|_S, notify, which q, lock type */
ub1        node_kjbl;                       /* instance lock belongs to */
ub1        flags_kjbl;                                /* lock flag bits */
ub2        rreqid_kjbl;                               /* save the reqid */
ub2         write_wrap_kjbl;        /* last write request version(wrap) */
ub4         write_base_kjbl;        /* last write request version(base) */
ub4         history_kjbl;                     /* lock operation history */
} kjbl;
PCM DLM locks that are owned by the local instance are allocated and embedded in an LE structure.
PCM DLM locks that are owned by remote instances and mastered by the local instance are allocated in SHARED_POOL.
PCM Locks and Resources
Fields of interest in the kclle structure: kcllerls or releasing; kcllelnm or name(id1,id2);
kcllemode or held-mode; kclleacq or acquiring; kcllelck or DLM lock.
Fields of interest in the kjbr structure: resname_kjbr[2] or resource name; grant_q_kjbr or grant queue;
convert_q_kjbr or convert queue; mode_role_kjbr, which is a bitwise merge of grant mode and
role-interpreted NULL(0x00), S(0x01), X(0x02), L0 Local (0x00), G0 Global without PI (0x08), G1 Global with PI (0x018).
The field mode_role_kjbl in kjbl is a bitwise merge of grant, request, and lock mode: 0x00 if grant NULL;
0x01 if grant S; 0x02 if grant X; 0x04 lock has been opened at master; 0x08 if global role (otherwise local);
0x10 has one or more PI; 0x20 if request CR; 0x40 if request S; 0x80 if request X.
Someone has to keep a list of all buffers and where they are mastered
This is called Global Resource Directory (GRD)
GRD is present on all the instances of the cluster
To find out the master:
select  b.dbablk, r.kjblmaster master_node
from x$le l, x$kjbl r, x$bh b
where b.obj =
and b.le_addr = l.le_addr
and l.le_kjbl = r.kjbllockp

Oracle Support宣称可以通过11.2.0.2 (Server Patch Set)11.2.0.1 Bundle Patch 4 for Exadata Database修复该bug,但是有迹象表明在11.2.0.2上仍可能发生该ORA-00600[kjbmprlst:shadow]内部错误,同时该bug更多地发生在超过2个节点的RAC系统中。

 

cursor_sharing=’SIMILAR’将被废弃

根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项,原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了,使用SIMILAR选项可能引发额外的version_count过多或cursor pin s on X等待事件。

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g. A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and ‘library cache lock’.
From Oracle versions 9.0 through 11.1, an oracle instance would limit the number child cursors in the shared pool associated with a single parent to 1024 before it would mark the parent OBSOLETE effectively invalidating it and all the children. Then a new parent with one child would be created and used going forward. But this would only limit the degradation of performance for some environments rather than fix something that could be addressed more effectively through improved application coding. (The attempt to address this from the database side also introduced other issues like bug 5177766). The child limit was removed by Oracle development because it was only masking an application problem at the expense of database performance for better designed applications. In addition, the obsolete code would not work in cases when SQL was wrapped within PL/SQL. The fundamental problem that obsolete code was masking is application code that was written incorrectly with regards to the ability to be shared. For example,  it is not written with user binds or the literal characteristics differ to a high degree.
Therefore, setting cursor_sharing = SIMILAR is highly discouraged in Oracle Database 11g Release 2 (and generally has not been recommended for most environments even in earlier versions) for several reasons:
1) This parameter is generally overly restrictive in what it actually allows to be shared. SIMILAR tells oracle to try and share cursors by replacing all literals with binds for legacy applications, but directs that sharing only be performed when all the replaced literal values were exactly the same (in the case of predicates referencing columns with histograms or using inequality operators such as BETWEEN, <, and !=)
2) This parameter seems to bypass a lot of the improvements made with Oracle Database 11g’s Adaptive Cursor Sharing feature and other abilities in the Cost Based Optimizer code to make better decisions on what execution plans should and should not be shared.
3) Having many child cursors all associated with 1 parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT (or FORCE). The scenario of many thousands of child cursors associated with 1 parent results in a potential bottleneck for searches for matching cursors (soft parsing) within the library cache.
The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary.

What do you need to do?

Change the cursor_sharing to either FORCE or EXACT, keeping in mind the effects of either.

this is due to the setting in the init.ora:
cursor_sharing=SIMILAR

Please set immediately
alter system set cursor_sharing=’FORCE’ scope=both;

to reduce the number of versions for a single sql statement whic is overloading your shared_pool.

377847.1 for SIMILA not FORCE are not the same

When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.

CURSOR_SHARING=SIMILAR

With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
OR
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key column.eg:

select … from orders where orderid=’literal’;

If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.

CURSOR_SHARING = FORCE

If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and 11.1.0.7 Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.

From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

Background Information

This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).

 

在11g中Oracle官方已经不再推荐使用SIMILAR选项,对于已经升级到11g的仍在使用cursor_sharing=’SIMILAR’的用户,建议尽早修改应用做到绑定变量,这样可以最稳妥的将cusror_sharing设置为EXACT,对于无法做到绑定变量的应用那么FORCE还会是一个和好的折中选择。

在版本12g中我们将不再看到SIMILAR选项。

 

Note #1: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Note #2: Setting CURSOR_SHARING to FORCE causes an increase in the
maximum lengths (as returned by DESCRIBE) of any selected
expressions that contain literals (in a SELECT statement).
However, the actual length of the data returned will not change.

Cursor_sharing 相关的BUG 列表:

 

NB Bug Fixed Description
14456124 12.1.0.0 Predicate push may not occur with cursor sharing
14053457 11.2.0.4, 12.1.0.0 ORA-917 parsing SQL with indicator binds with CURSOR_SHARING
12723295 11.2.0.4, 12.1.0.0 ORA-600 [qerixGetKey:optdesc] with function based index and CURSOR_SHARING
9877960 11.2.0.4, 12.1.0.0 ORA-600 or similar using CURSOR_SHARING with HS connections
14087914 12.1.0.0 Wrong results from ExistsNode with CURSOR_SHARING
+ 13550185 11.2.0.2.BP17, 11.2.0.3.4, 11.2.0.3.BP06, 12.1.0.0 Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools
13023854 11.2.0.4, 12.1.0.0 Long parse time / hang for SQL with nested CASE expressions with CURSOR_SHARING enabled
12862170 12.1.0.0 INSERT ALL fails with ORA-600[kkslhsh1] with CURSOR_SHARING enabled / High Version Count on HASH_MATCH_FAILED
12797420 11.2.0.3.3, 11.2.0.3.BP07, 12.1.0.0 “library cache: mutex X” waits on DB instance handle with CURSOR_SHARING
12649442 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 ORA-7445 [kxscod] with CURSOR_SHARING=FORCE or SIMILAR
12596444 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.BP08, 12.1.0.0 Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
12534597 12.1.0.0 Bind Peeking is disabled for remote queries
12374212 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 Assorted dump , internal errors, memory corruptions with cursor_sharing = force
12345980 11.2.0.3, 12.1.0.0 high parse time with cursor_sharing=force when session_cached_cursors set
12334286 11.2.0.3, 12.1.0.0 High version counts with CURSOR_SHARING=FORCE (BIND_MISMATCH and INCOMP_LTRL_MISMATCH)
11858021 11.2.0.3, 12.1.0.0 ORA-600 [kpoal8-1] using DG4DRDA with CURSOR_SHARING=force
11806961 11.2.0.3, 12.1.0.0 ORA-600 [kkspsc0: basehd] using CURSOR_SHARING
11738259 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.0 ORA-600 [kghssgfr2] using CURSOR_SHARING=FORCE
11714159 11.2.0.3, 12.1.0.0 ORA-917 occurs with CURSOR_SHARING even if patch:9877980 is applied – superseded
11076030 11.2.0.3, 12.1.0.0 Wrong results for XDB when CURSOR_SHARING enabled
11069199 11.2.0.2.2, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.0 ORA-600 [kksObsoleteCursor:invalid stub] with CURSOR_SHARING = SIMILAR | FORCE if fix 10187168 present
11063191 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.2, 11.2.0.3.BP04, 12.1.0.0 ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10126094 11.2.0.2.3, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.0 ORA-600 [kglLockOwnersListAppend-ovf] from literal replacement on SQL issued from PLSQL
10013170 11.2.0.3, 12.1.0.0 ORA-600 [736] from literal replacement with a “WAIT n” clause
* 9877980 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.0 ORA-7445[kkslMarkLiteralBinds] / Assorted Errors on 11.2.0.2 if cursor sharing is enabled – Affects RMAN
9877964 11.2.0.3, 12.1.0.0 ORA-600 [19003] raised by LIKE :BIND in query
9680430 11.2.0.3, 12.1.0.0 High version count with CURSOR_SHARING = FORCE due to CBO transformation
9548104 11.2.0.2, 12.1.0.0 OERI [qcsfbdnp:1] instead of ORA-1788 with cursor sharing
9413962 11.2.0.2, 12.1.0.0 Many child cursors / ORA-600 [opixrb-3.0] [2005] [ORA-02005] binding literal for remote RPI
9411496 11.2.0.2, 12.1.0.0 ORA-979 on GROUP BY query with CURSOR_SHARING set
9362218 11.2.0.2, 12.1.0.0 Literals replaced by binds when CURSOR_SHARING=EXACT
9348402 12.1.0.0 OERI [kks-hash-collision] can occur with CURSOR_SHARING=FORCE|SIMILAR
9223586 11.2.0.2, 12.1.0.0 Problems with variable length NCHAR literals with cursor sharing
9031183 11.2.0.2, 12.1.0.0 ORA-1722 with CURSOR_SHARING=SIMILAR and with NCHAR
9008904 11.2.0.2, 12.1.0.0 Dump (audTransFro) with CURSOR_SHARING
8913729 11.2.0.2, 12.1.0.0 ORA-979 with CURSOR_SHARING=SIMILAR or FORCE
8545377 11.2.0.2, 12.1.0.0 ORA-1780 with CURSOR_SHARING on XML queries
8246445 11.2.0.2, 12.1.0.0 Query rewrite not working for multi-MV rewrite with literal replacement
5751866 11.2.0.2 Wrong Results with CASE and CURSOR_SHARING
9767674 10.2.0.5.5 Dump [kkslmtl] using CURSOR_SHARING – superceded
8794693 11.2.0.2 Dump [kkscsmtl] using literal replacement (CURSOR_SHARING)
8491399 11.2.0.1 Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
8453245 11.2.0.1 Many child cursors with CURSOR_SHARING = FORCE
8264642 11.2.0.1 ORA-600 [kkexbindopn0] with CURSOR_SHARING = SIMILAR
7651092 11.2.0.1 ORA-1000 with Literal Replacement, EXECUTE IMMEDIATE and CURSOR_SHARING_EXACT hint (affects DBMS_STATS)
7516867 10.2.0.5, 11.1.0.7.1, 11.2.0.1 Intermittent Wrong results from literal replacement with fix for bug 6163785
7272297 10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1 Memory corruption / OERI[17114] / OERI[17125] with literal replacement
7212120 11.1.0.7, 11.2.0.1 Session cursor cache not used properly when CURSOR_SHARING=force/similar
6337716 10.2.0.5, 11.1.0.7, 11.2.0.1 Wrong max column size for NULL strings with literal replacement
5757106 10.2.0.4, 11.1.0.7, 11.2.0.1 OERI[15851] selecting aggregate of a constant with literal replacement
4071519 10.2.0.5, 11.1.0.7, 11.2.0.1 GROUP BY query with CURSOR_SHARING fails with ORA-1802
3461251 11.1.0.7, 11.2.0.1 V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING
7296258 10.2.0.5, 11.1.0.7.1 Intermittent Wrong results from literal replacement and remote objects
6163785 10.2.0.5, 11.1.0.7 Intermittent Wrong Results with dblink and cursor_sharing
8202234 Intermittent Wrong Results with dblink and cursor_sharing
5863277 10.2.0.4, 11.1.0.6 ORA-1008 from SQL on second run when cursor_sharing=similar/force
5762750 10.2.0.4, 11.1.0.6 ORA-907 when cursor_sharing is enabled
5476507 10.2.0.4, 11.1.0.6 OERI[15868] / OERI[15160] can occur with cursor sharing
5364819 10.2.0.4, 11.1.0.6 OERI[kkslpbp:1] when using literal replacement
5254759 10.2.0.4, 11.1.0.6 ORA-12801/ORA-1008 occurs on a parallel query with bind variables
5177766 10.2.0.4, 11.1.0.6 OERI[17059] with SESSION_CACHED_CURSORS
5155885 10.2.0.4, 11.1.0.6 OERI[kkslgbv0] with CURSOR_SHARING=similar
+ 5146740 10.2.0.4, 11.1.0.6 Wrong results with bind variables/CURSOR_SHARING
5082178 10.2.0.4, 11.1.0.6 Bind peeking may occur when it should not
5055175 10.2.0.3, 11.1.0.6 Dump [kkslpkp] using literal replacement with timezone literals – superceded
4867724 10.2.0.5, 11.1.0.6 Literal replacement limits column names to 30 characters
4698156 10.2.0.3, 11.1.0.6 ORA-12850 querying GV$ views when CURSOR_SHARING=FORCE
4607460 9.2.0.8, 10.2.0.3, 11.1.0.6 Dump [opipls] when CURSOR_SHARING = SIMILAR | FORCE
4513695 10.2.0.4, 11.1.0.6 Poor performance for SELECT with ROWNUM=1 with literal replacement
4458226 9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 High version count with cursor_sharing=force
4456646 10.2.0.2, 11.1.0.6 Dump (kxscod/memcmp) using literal replacement
4451881 9.2.0.8, 10.2.0.2, 11.1.0.6 OERI[kkslhsh1] from Insert as SELECT
4436832 10.2.0.4, 11.1.0.6 False ORA-979 “not a group by expression” with literal replacement
4359367 10.1.0.5, 10.2.0.2, 11.1.0.6 High version_count with cursor sharing
4254094 10.2.0.3, 11.1.0.6 OERI[qerrmObnd1][932] possible for queries over DB links
4202503 10.2.0.4, 11.1.0.6 Parse errors possible using CONTAINS with cursor_sharing=similar/force
4200541 10.2.0.3, 11.1.0.6 Parse error when CURSOR_SHARING=FORCE|SIMILAR with user binds
3880881 9.2.0.8, 10.2.0.2, 11.1.0.6 Dump (kkslMarkLiteralBinds) using cursor sharing
2837580 10.2.0.4, 11.1.0.6 Dump if SQL has > ~35000 literals
13349665 10.2.0.5.6 ORA-600 [kkslmtl-valnotfound] with fix for bug 9767674 (eg After applying PSU 10.2.0.5.5)
3980673 9.2.0.8, 10.1.0.5, 10.2.0.1 Literal replacement may dump (kkslpbp) if user binds have names like SYS_B_xx
3842253 9.2.0.7, 10.1.0.4, 10.2.0.1 Dump (kgghash) using literal replacement
3819834 9.2.0.7, 10.1.0.4, 10.2.0.1 Dump (kkslflb) with literal replacement
3818541 9.2.0.7, 10.1.0.4, 10.2.0.1 CURSOR_SHARING=force does not work if SQL has an NVL or DECODE predicate
3737955 10.1.0.4, 10.2.0.1, 9.2.0.7 Long parse times for long inlists / many AND/OR terms
3668572 9.2.0.6, 10.1.0.4, 10.2.0.1 ORA-979 when CURSOR_SHARING = force|similar with inline view and GROUP BY
3645694 10.1.0.5, 10.2.0.1 Poor plan from SQL with literal replacement and LIKE .. ESCAPE ..
3406977 9.2.0.6, 10.1.0.3, 10.2.0.1 High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
3148830 9.2.0.6, 10.1.0.3, 10.2.0.1 Select over database link fails with OERI[opixmp-7] if CURSOR_SHARING=similar
3128363 9.2.0.6, 10.1.0.4 OCIAttrGet returns wrong size when CURSOR_SHARING=SIMILAR or FORCE
3007574 9.2.0.5 OERI:15212 using CURSOR_SHARING=FORCE with database links
5526018 Dump [kkslpbp] / OERI[kkslpbp:1] with materialized view and literal replacement
3132071 9.2.0.6, 10.1.0.2 Wrong results possible with CURSOR_SHARING=FORCE|SIMILAR
3045623 9.2.0.5, 10.1.0.2 OERI[kkslhsh1] possible with CURSOR_SHARING=FORCE
2958096 9.2.0.5, 10.1.0.2 Using XMLType / objects over DBLINKS with CURSOR_SHARING may dump
2843601 9.2.0.5, 10.1.0.2 DML across a dblink fails with ORA-24370 if cursor_sharing is enabled
2643579 9.2.0.4, 10.1.0.2 ORA-7445 [kxspoac] using CURSOR_SHARING=FORCE (or similar)
2620541 9.2.0.5, 10.1.0.2 ORA-7445 [KXSPOAC] can occur on DML using CURSOR_SHARING
2508702 9.2.0.3, 10.1.0.2 ORA-7445 [KKSLHIB] with CURSOR_SHARING=FORCE with INTO clause
2474192 9.2.0.3, 10.1.0.2 OERI:[OPIBND1] on CREATE of TEXT INDEX with CURSOR_SHARING
2442125 9.2.0.3, 10.1.0.2 Wrong results from PQ which compares a CHAR column to a BIND variable
2308292 9.0.1.4, 9.2.0.2, 10.1.0.2 ORA-7445 [kkslhib] for INTO <bind> using CURSOR_SHARING and SESSION_CACHED_CURSORS
2273604 9.0.1.4, 9.2.0.2, 10.1.0.2 CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
2052836 8.1.7.4, 9.0.1.4, 9.2.0.2, 10.1.0.2 Client dump possible using CALL statement with CURSOR_SHARING=FORCE
4197915 9.2.0.8 CAST( x as number(n)) fails in PQ slaves with ORA-1727 with cursor_sharing
3614299 9.2.0.6 Dump adding a subscriber with a rule containing literals with CURSOR_SHARING = FORCE|SIMILAR
2442097 9.2.0.2 INSERTS into USER-DEFINED TYPES with CURSOR_SHARING=FORCE does not share cursors
2237610 9.2.0.2 OERI:[kgskbwt1] / OERI:[kskbind1] possible using CURSOR_SHARING
2300719 9.0.1.4, 9.2.0.1 Spin possible in PRSGNT when CURSOR_SHARING=SIMILAR
2262665 8.1.7.4, 9.0.1.4, 9.2.0.1 ORA-7445 [kkslpbp] possible with CURSOR_SHARING=FORCE
2259787 9.0.1.4, 9.2.0.1 Dump possible in EXPCMPBND() with CURSOR_SHARING and GROUPING SETS
2154645 9.0.1.3, 9.2.0.1 Spin in KKSFBC possible when CURSOR_SHARING=FORCE
1947974 9.0.1.2, 9.2.0.1 False ORA-1008 possible using EXECUTE IMMEDIATE with CURSOR_SHARING=SIMILAR
1840199 9.0.1.2, 9.2.0.1 OERI:EVAGGSID-1 possible using user defined aggregates (UDAG) with CURSOR_SHARING=FORCE|SIMILAR
1782025 8.1.7.4, 9.2.0.1 Client may dump/error using DML RETURNING with CURSOR_SHARING=FORCE|SIMILAR
1777504 8.1.7.4, 9.0.1.4, 9.2.0.1 Hang possible with CURSOR_SHARING=FORCE (or SIMILAR in 9i)
1764925 9.0.1.4, 9.2.0.1 OERI:QCTSTC2O1 possible using CURSOR_SHARING = SIMILAR
2159152 8.1.7.4, 9.0.1.0 Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
1538450 8.1.7.3, 9.0.1.0 Dump in KXSPOAC possible CURSOR_SHARING=FORCE set
1365873 8.1.7.2, 9.0.1.0 OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE
1358871 8.1.7.3, 9.0.1.0 CURSOR_SHARING=FORCE may dump if first bind is a literal
984251 8.1.7.2, 9.0.1.0 CURSOR_SHARING=FORCE can cause ORA-29909 when using an ANCILLARY OPERATOR
2485018 OERI:[KKSLGOP1] from SQL issued over a DBLINK with CURSOR_SHARING
2221407 8.1.7.4 ORA-3106 / OERI:15212 using BINDS over DBLINK with CURSOR_SHARING=FORCE
1545473 8.1.7.2, 9.0.1.0 ORA-979 possible using CURSOR_SHARING=FORCE with GROUP BY & ORDER BY & LITERALS
1111796 8.1.7.0 Dump possible from CURSOR_SHARING=FORCE
984132 8.1.7.0 Literal in cursor expression can CORE DUMP with CURSOR_SHARING=FORCE

11g默认审计选项

11g默认启用强大的审计选项,AUDIT_TRAIL参数的缺省值为DB,这意为着审计数据将记录在数据库中的AUD$审计字典基表上。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响,同时Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。

注意因为在11g中CREATE SESSION将被作为受审计的权限来被记录,因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成,这将最终导致普通用户的新会话将无法正常创建,普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话,在将审计数据合适备份后删除一部分记录,或者干脆TRUNCATE AUD$都可以解决上述问题。

当AUDIT_TRAIL设置为OS时,审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成。全部这些文件均可以随时被删除或复制。

注意在默认情况下会以AUTOEXTEND ON自动扩展选项创建SYSTEM表空间,因此系统表空间在必要情况下还是会自动增长的,我们所需注意的是磁盘上的剩余空间是否能够满足其增长需求,以及数据文件扩展的上限,对于普通的8k smallfile表空间而言单个数据文件的最大尺寸是32G。

以下权限将对所有用户审计:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
SQL> select privilege,success,failure from dba_priv_audit_opts;
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER DATABASE                           BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
AUDIT SYSTEM                             BY ACCESS  BY ACCESS
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
23 rows selected.

以下语句也将对所有用户审计:

SQL> select audit_option,success,failure from dba_stmt_audit_opts;
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
SYSTEM AUDIT                             BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
DATABASE LINK                            BY ACCESS  BY ACCESS
ROLE                                     BY ACCESS  BY ACCESS
PROFILE                                  BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
SYSTEM GRANT                             BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS
28 rows selected.

当前数据库中的现有的审计记录:

SQL> select action_name,count(*) from dba_audit_trail group by action_name;
ACTION_NAME                    COUNT(*)
---------------------------- ----------
LOGOFF BY CLEANUP                    40
LOGON                               460
LOGOFF                              377
ALTER USER                            2
SYSTEM GRANT                         12
ALTER SYSTEM                         10
CREATE PUBLIC SYNONYM                 5
ALTER DATABASE                        2
CREATE DATABASE LINK                  1
DROP PUBLIC SYNONYM                   5
10 rows selected.

11.2.0.2 asmcmd lsdg show incorrect diskgroup number

今天在给ASM扩磁盘组的时候发现11.2.0.2上asmcmd中lsdg命令所显示的磁盘组数不正确,现象如下:

Node A lsdg正常:
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    142976    50596                0           50596              0             Y  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     51200    50742                0           50742              0             N  FRA/
Node B lsdg仅显示DATA磁盘组
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    142976    50596                0           50596              0             Y  DATA/
从Node B节点上ASM实例的动态视图查询显示正常:
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DATA
FRA
ASM diag info:
SQL> select sysdate "Date and Time" from dual;
Date and Time
--------------------
28-JUN-2011 23:30:27
SQL> 
SQL> select * from v$asm_diskgroup order by 1;
select * from v$asm_disk order by 1, 2, 3;
select * from gv$asm_operation order by 1;
select * from v$version where banner like '%Database%' order by 1;
select * from gv$asm_client order by 1;
prompt
prompt ASM Disk Groups
prompt ===============
select group_number  "Group"
,      name          "Group Name"
,      state         "State"
,      type          "Type"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
from   v$asm_diskgroup
/
prompt
prompt ASM Disks
prompt ==============
col "Group"          form 999
col "Disk"           form 999
col "Header"         form a9
col "Mode"           form a8
GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE           TYPE      TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V
------------ ------------------------------ ----------- ---------- -------------------- --------------- ------- ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
0 FRA                                      0       4096                    0 DISMOUNTED                       0          0           0            0              0               0             0 0.0.0.0.0                                                    0.0.0.0.0                                                    N
1 DATA                                   512       4096              1048576 MOUNTED         EXTERN      142976      50596           0        92380              0           50596             0 11.2.0.0.0                                                   10.1.0.0.0                                                   Y
SQL> col "Redundancy"     form a10
col "Failure Group"  form a10
col "Path"           form a19
select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
GROUP_NUMBER DISK_NUMBER COMPOUND_INDEX INCARNATION MOUNT_S HEADER_STATU MODE_ST STATE           REDUNDA LIBRARY                                                              OS_MB   TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB NAME                    FAILGROUP                      LABEL
------------ ----------- -------------- ----------- ------- ------------ ------- --------------- ------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------- ------------ ------------------------------ ------------------------------ -------------------------------
PATH                                                                                                                                                                    UDID                                                              PRODUCT                          CREATE_DATE          MOUNT_DATE           REPAIR_TIMER      READS    WRITES  READ_ERRS WRITE_ERRS  READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- -------------------- -------------------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
P HASH_VALUE  HOT_READS HOT_WRITES HOT_BYTES_READ HOT_BYTES_WRITTEN COLD_READS COLD_WRITES COLD_BYTES_READ COLD_BYTES_WRITTEN V SECTOR_SIZE FAILGRO
- ---------- ---------- ---------- -------------- ----------------- ---------- ----------- --------------- ------------------ - ----------- -------
0           0              0  3915932260 CLOSED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200           0          0           0            0
/dev/raw/raw8                                                                                                                                                             28-JUN-2011 17:08:16 28-JUN-2011 17:08:25             0
0                                                                                                                  N         512 REGULAR
1           0       16777216  3915932274 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2827           0         5269 DATA_0000                      DATA_0000
/dev/raw/raw1                                                                                                                                                             29-MAR-2011 22:39:53 31-MAY-2011 06:42:41             0         26          1          0          0    .948212    .001678    1118208          4096
1452020686          0          0              0                 0          0           0               0                  0 Y         512 REGULAR
1           1       16777217  3915932273 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2861           0         5235 DATA_0001                      DATA_0001
/dev/raw/raw2                                                                                                                                                             29-MAR-2011 22:39:53 31-MAY-2011 06:42:41             0         15          0          0          0    .868808          0    1105920             0
559735068          0          0              0                 0          0           0               0                  0 N         512 REGULAR
1           2       16777218  3915932272 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2859           0         5237 DATA_0002                      DATA_0002
/dev/raw/raw3                                                                                                                                                             29-MAR-2011 23:36:28 31-MAY-2011 06:42:41             0         14          0          0          0    .738089          0      57344             0
2179807744          0          0              0                 0          0           0               0                  0 N         512 REGULAR
1           4       16777220  3915932270 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8192        8192       2896           0         5296 DATA_0004                      DATA_0004
/dev/raw/raw5                                                                                                                                                             21-APR-2011 00:57:45 31-MAY-2011 06:42:41             0         14          0          0          0    .738083          0      57344             0
3104258115          0          0              0                 0          0           0               0                  0 N         512 REGULAR
1           5       16777221  3915932271 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                        8096        8096       2856           0         5240 DATA_0005                      DATA_0005
/dev/raw/raw4                                                                                                                                                             30-MAY-2011 20:36:59 31-MAY-2011 06:42:41             0         15          0          0          0    .911934          0    1105920             0
2075303794          0          0              0                 0          0           0               0                  0 N         512 REGULAR
1           7       16777223  3915932269 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200       51200      18149           0        33051 DATA_0007                      DATA_0007
/dev/raw/raw6                                                                                                                                                             28-JUN-2011 17:09:22 28-JUN-2011 17:09:22             0         39          5          0          0    .998594   6.022413     159744         20480
,      mode_status   "Mode"
2166785847          0          0              0                 0         18           0           73728                  0 N         512 REGULAR
1           8       16777224  3915932268 CACHED  MEMBER       ONLINE  NORMAL          UNKNOWN System                                                               51200       51200      18148           0        33052 DATA_0008                      DATA_0008
/dev/raw/raw7                                                                                                                                                             28-JUN-2011 17:09:58 28-JUN-2011 17:09:58             0         25          0          0          0    .760427          0     102400             0
1385034587          0          0              0                 0          6           0           24576                  0 N         512 REGULAR
8 rows selected.
SQL> ,      state         "State"
,      redundancy    "Redundancy"
,      total_mb      "Total MB"
,      free_mb       "Free MB"
,      name          "Disk Name"
,      failgroup     "Failure Group"
,      path          "Path"
from   v$asm_disk
order by group_number
,        disk_number
/
prompt
prompt Instances currently accessing these diskgroups
prompt ==============================================
no rows selected
SQL> 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
SQL> 
select c.group_number  "Group"
,      g.name          "Group Name"
,      c.instance_name "Instance"
INST_ID GROUP_NUMBER INSTANCE_NAME                                                    DB_NAME  STATUS       SOFTWARE_VERSION                                            COMPATIBLE_VERSION
---------- ------------ ---------------------------------------------------------------- -------- ------------ ------------------------------------------------------------ ------------------------------------------------------------
1            1 +ASM1                                                            +ASM     CONNECTED    11.2.0.2.0                                                  11.2.0.2.0
1            2 PROD1                                                            PROD     CONNECTED    11.2.0.2.0                                                  11.2.0.0.0
1            1 PROD1                                                            PROD     CONNECTED    11.2.0.2.0                                                  11.2.0.0.0
2            1 +ASM2                                                            +ASM     CONNECTED    11.2.0.2.0                                                  11.2.0.2.0
SQL> SQL> 
SQL> SQL> ASM Disk Groups
SQL> ===============
SQL> SQL>   2    3    4    5    6    7    8  from   v$asm_client c
,      v$asm_diskgroup g
where  g.group_number=c.group_number
/
Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
1 DATA                      MOUNTED         EXTERN     139.625      49
0 FRA                       DISMOUNTED                       0       0
SQL> SQL> 
SQL> ASM Disks
SQL> ==============
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3  prompt
4    5    6    7    8    9   10   11   12   13   14   15  SQL> prompt Report the Percentage of Imbalance in all Mounted Diskgroups
prompt ==============================================
select dfail, count(dfail) from
(
select disk, count(failgroup) as dfail
from x$kfdpartner, v$asm_disk where
number_kfdpartner=disk_number and grp=group_number
Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr Path
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ---------- -------------------
0    0 MEMBER    ONLINE   NORMAL          UNKNOWN             0          0                                           /dev/raw/raw8
1    0 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2827 DATA_0000                      DATA_0000  /dev/raw/raw1
1    1 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2861 DATA_0001                      DATA_0001  /dev/raw/raw2
1    2 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2859 DATA_0002                      DATA_0002  /dev/raw/raw3
1    4 MEMBER    ONLINE   NORMAL          UNKNOWN          8192       2896 DATA_0004                      DATA_0004  /dev/raw/raw5
1    5 MEMBER    ONLINE   NORMAL          UNKNOWN          8096       2856 DATA_0005                      DATA_0005  /dev/raw/raw4
1    7 MEMBER    ONLINE   NORMAL          UNKNOWN         51200      18149 DATA_0007                      DATA_0007  /dev/raw/raw6
1    8 MEMBER    ONLINE   NORMAL          UNKNOWN         51200      18148 DATA_0008                      DATA_0008  /dev/raw/raw7
8 rows selected.
SQL> SQL> 
SQL> Instances currently accessing these diskgroups
SQL> ==============================================
SQL> SQL>   2    3    4    5    6    7  group by disk, failgroup
)
group by dfail; 
select g.name as "GROUP", d.name as "DISK", d.failgroup, fcnt, pcnt,
decode(pcnt - fcnt, 0, 'MUST', 'SHOULD') as action from
Group Group Name                Instance
----- ------------------------- ----------------------------------------------------------------
1 DATA                      +ASM2
SQL> SQL> 
SQL> Report the Percentage of Imbalance in all Mounted Diskgroups
SQL> ==============================================
SQL>   2    3    4    5    6    7    8  (select gnum, DISK1, failgroup, count(failgroup) as fcnt from
(select gnum, DISK1
from
(
select d.group_number as gnum, disk as disk1,
count(distinct failgroup) as dfail
from x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number
no rows selected
SQL> SQL>   2    3    4    5    6    7    8    9   10   11  and active_kfdpartner=1
12  group by d.group_number, disk
13  ), v$asm_disk_stat
14  where dfail < 3
15  and disk1=disk_number
16  and gnum=group_number),
17  x$kfdpartner, v$asm_disk_stat d where
18  number_kfdpartner=disk_number and grp=d.group_number and grp=gnum
19  and disk1=disk
20  and active_kfdpartner=1
21  group by gnum, disk1, failgroup),
22  (select grp, disk, count(disk) as pcnt from x$kfdpartner where
23  active_kfdpartner=1 group by grp, disk),
24  v$asm_diskgroup_stat g, v$asm_disk_stat d
25  where gnum=grp and gnum=g.group_number and gnum=d.group_number and
26  disk=disk1 and disk=disk_number and
27  ((fcnt = 1 and (pcnt - fcnt) > 3) or ((pcnt - fcnt) = 0))
28  /
no rows selected
SQL> 
SQL> col TYPE form a15
SQL> col FILE_NUMBER form 9999 head FILE_NUM
SQL> col GROUP_NUMBER form 9999 head GR_NUM
SQL> col GB for 9999.99
SQL> 
SQL> select GROUP_NUMBER   ,
2   FILE_NUMBER          ,
3   COMPOUND_INDEX       ,
4   INCARNATION          ,
5   BLOCK_SIZE           ,
6   BLOCKS               ,
7   BYTES/1024/1024/1024 GB ,
8   TYPE                 ,
9   STRIPED              ,
10   CREATION_DATE        ,
11   MODIFICATION_DATE
12  from v$asm_file
13  where TYPE != 'ARCHIVELOG'
14  /
GR_NUM FILE_NUM COMPOUND_INDEX INCARNATION BLOCK_SIZE     BLOCKS       GB TYPE            STRIPE CREATION_DATE        MODIFICATION_DATE
------ -------- -------------- ----------- ---------- ---------- -------- --------------- ------ -------------------- --------------------
1      253       16777469   747096005        512          3      .00 ASMPARAMETERFIL COARSE 29-MAR-2011 22:40:05 29-MAR-2011 22:00:00
E
1      255       16777471   747096007       4096      66591      .25 OCRFILE         COARSE 29-MAR-2011 22:40:07 28-JUN-2011 19:00:00
1      256       16777472   747100091       8192     116481      .89 DATAFILE        COARSE 29-MAR-2011 23:48:10 28-JUN-2011 18:00:00
1      257       16777473   747100091       8192     122881      .94 DATAFILE        COARSE 29-MAR-2011 23:48:11 28-JUN-2011 18:00:00
1      258       16777474   747100093       8192      78721      .60 DATAFILE        COARSE 29-MAR-2011 23:48:12 28-JUN-2011 18:00:00
1      259       16777475   747100093       8192     883201     6.74 DATAFILE        COARSE 29-MAR-2011 23:48:12 28-JUN-2011 18:00:00
1      260       16777476   747100215      16384       1545      .02 CONTROLFILE     FINE   29-MAR-2011 23:50:14 28-JUN-2011 19:00:00
1      261       16777477   747100215      16384       1545      .02 CONTROLFILE     FINE   29-MAR-2011 23:50:14 28-JUN-2011 19:00:00
1      262       16777478   747100217        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:16 28-JUN-2011 22:00:00
1      263       16777479   747100219        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:18 28-JUN-2011 22:00:00
1      264       16777480   747100219        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:19 28-JUN-2011 20:00:00
1      265       16777481   747100223        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:50:22 28-JUN-2011 20:00:00
1      266       16777482   747100227       8192    1889537    14.42 TEMPFILE        COARSE 29-MAR-2011 23:50:27 28-JUN-2011 23:00:00
1      267       16777483   747100231       8192      44241      .34 DATAFILE        COARSE 29-MAR-2011 23:50:31 28-JUN-2011 19:00:00
1      268       16777484   747100469       8192     144001     1.10 DATAFILE        COARSE 29-MAR-2011 23:54:29 28-JUN-2011 18:00:00
prompt
1      269       16777485   747100547        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:47 28-JUN-2011 19:00:00
1      270       16777486   747100549        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:48 28-JUN-2011 19:00:00
1      271       16777487   747100549        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:49 28-JUN-2011 19:00:00
1      272       16777488   747100553        512     102401      .05 ONLINELOG       COARSE 29-MAR-2011 23:55:52 28-JUN-2011 19:00:00
1      273       16777489   747100553        512          9      .00 PARAMETERFILE   COARSE 29-MAR-2011 23:55:53 12-MAY-2011 17:00:00
prompt free ASM disks and their paths
1      274       16777490   754587291       8192       2561      .02 DATAFILE        COARSE 23-JUN-2011 15:34:50 28-JUN-2011 18:00:00
1      419       16777635   754281899       4096         91      .00 DUMPSET         COARSE 20-JUN-2011 02:44:58 20-JUN-2011 02:00:00
1      657       16777873   755047049       8192     256001     1.95 TEMPFILE        COARSE 28-JUN-2011 23:17:28 28-JUN-2011 23:00:00
1      658       16777874   755032881       8192    8171521    62.34 DATAFILE        COARSE 28-JUN-2011 19:21:20 28-JUN-2011 22:00:00
24 rows selected.
SQL> SQL> 
SQL> free ASM disks and their paths
SQL> prompt ===========================
===========================
SQL> select header_status , mode_status, path from V$asm_disk
2  where header_status in ('FORMER','CANDIDATE')
3  /
show parameter asm
show parameter size
show parameter proc
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter pfile
show sga
spool off
no rows selected
SQL> SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string          /dev/raw/raw*
asm_power_limit                      integer         10
asm_preferred_read_failure_groups    string
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
db_cache_size                        big integer     0
large_pool_size                      big integer     12M
max_dump_file_size                   string          unlimited
sga_max_size                         big integer     272M
shared_pool_reserved_size            big integer     7969177
shared_pool_size                     big integer     0
sort_area_size                       integer         65536
workarea_size_policy                 string          AUTO
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
processes                            integer         100
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
cluster_database                     boolean         TRUE
cluster_database_instances           integer         4
cluster_interconnects                string
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
instance_type                        string          asm
SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
instance_name                        string          +ASM2
SQL> SQL> 
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
spfile                               string          +DATA/rh-cluster/asmparameterf
ile/registry.253.747096005
SQL> SQL> 
Total System Global Area  283930624 bytes
Fixed Size                  2225792 bytes
Variable Size             256539008 bytes
ASM Cache                  25165824 bytes

原来是因为lsdg只列出已经mount的diskgroup,因为在Node B上FRA磁盘组没有mount所以不被列出,我本来以为是Bug造成的。
感谢Liqin Zhang网友的指出。

Oracle等待事件kfk:async disk IO

kfk: async disk IO等待事件是ASM下异步的System I/O等待事件,kfk内核层面在disk_asynch_io=true时被激活。当rbal或其他ASM相关后台进程在维护ASM磁盘组时可能进入kfk: async disk IO等待。

SQL> col name for a20
SQL> col PARAMETER1 for a10
SQL> col PARAMETER2 for a10
SQL> col PARAMETER3 for a10
SQL> col WAIT_CLASS for a15
SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name='kfk: async disk IO';
NAME                 PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
-------------------- ---------- ---------- ---------- ---------------
kfk: async disk IO   count      intr       timeout    System I/O
SQL> select * from v$version;    
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select name,value from v$system_parameter where name in ('instance_type','asm_power_limit');
NAME                 VALUE
-------------------- ----------
instance_type        asm
asm_power_limit      10
SQL> conn / as sysasm
Connected.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> alter diskgroup data check all;
Diskgroup altered.
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/asm/+asm/+ASM1/trace/+ASM1_ora_29405.trc
=====================trace=====================
PARSING IN CURSOR #140442102181424 len=30 dep=0 uid=0 oct=193 
lid=0 tim=1313673029551496 hv=2849532521 ad='6bd58b50' sqlid='ft5h7dunxhum9'
alter diskgroup data check all
END OF STMT
PARSE #140442102181424:c=1999,e=14171,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1313673029551493
WAIT #140442102181424: nam='Disk file operations I/O' ela= 573 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='Disk file operations I/O' ela= 33 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=0 filetype=15 obj#=-1 
WAIT #140442102181424: nam='kfk: async disk IO' ela= 941 count=1 intr=0 timeout=4294967295 obj#=-1
fdp_checkDsk(): 20
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1610 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029798048
kfdp_checkDsk(): 21
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1677 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029885645
kfdp_checkDsk(): 22
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----
WAIT #140442102181424: nam='rdbms ipc reply' ela= 1350 from_process=19 timeou
t=2147483647 p3=0 obj#=-1 tim=1313673029970397
kfdp_checkDsk(): 23
----- Abridged Call Stack Trace -----
kfdp_checkDsk(): 24
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfdp_checkDsk()+476<-kfdCheck()+1649<-kfgCheck()+477<-kfxdrvAl
terOne()+5976<-kfxdrvAlter()+2287<-kfxdrvEntry()+1306<-opiexe()+20028<-opiosq
0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+
1670<-opiino()+966<-opiodr()+910
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
----- End of Abridged Call Stack Trace -----

沪ICP备14014813号

沪公网安备 31010802001379号