Oracle 11g新特性

客户的一套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上有对应的补丁。

Filed Under: Oracle, Oracle 11g新特性, Oracle ASM, Oracle Bug Tagged With: ASM, CRS-4258, css, Grid Infrastructure, Oracle 11g新特性, voting disk

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

2011/07/25 by admin 2 Comments

一套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系统中。

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug, Oracle RAC Tagged With: kjbl, kjbr, ora-00600, Oracle 11g新特性, Oracle Bug, RAC

根据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

Filed Under: Oracle, Oracle 11g新特性 Tagged With: bind variable, cursor_sharing, Oracle 11g新特性

11g默认审计选项

2011/06/29 by admin 1 Comment

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.askmac.cn


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.

Filed Under: Oracle, Oracle 11g新特性 Tagged With: AUD$, audit, Oracle 11g新特性, Security

11.2.0.2 asmcmd lsdg show incorrect diskgroup number

2011/06/28 by admin Leave a Comment

今天在给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网友的指出。

Filed Under: Oracle, Oracle 11g新特性, Oracle ASM Tagged With: asmcmd, lsdg, Oracle 11g新特性

Oracle等待事件kfk:async disk IO

2011/06/11 by admin 1 Comment

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 -----

Filed Under: Oracle, Oracle 11g新特性, Oracle ASM Tagged With: ASM, Exadata, io, Oracle 11g新特性, wait event