11g新特性问题解决

  • liujl_

    2015-04-30 16:35:13
  • Oracle
  • 转载

cursor_sharing=’SIMILAR’将被废弃

2011/06/29  by 暂无评论

根据metalink文档在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 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: , ,

11g默认审计选项

2011/06/29  by 1条评论

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

Filed Under: Oracle, Oracle 11g新特性, Oracle数据库安全Security Tagged With: , , ,

11.2.0.2 asmcmd lsdg show incorrect diskgroup number

2011/06/28  by 暂无评论

今天在给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: , ,

Oracle等待事件kfk:async disk IO

2011/06/11  by 1条评论

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

DataGuard与异构平台

2011/05/31  by 5条评论

DataGuard对主备库异构平台的支持一直是让很多人纠结的问题，我们在学习Oracle数据卫士时必要优先阅读的官方文档是，在这个文档中给出了极为苛刻的硬件环境限制条件: 


All members of a Data Guard configuration must run an Oracle image that is built for the same platform.

For example, this means a Data Guard configuration with a primary database on a 32-bit Linux on Intel system
can have a standby database that is configured on a 32-bit Linux on Intel system. However, a primary database
on a 64-bit HP-UX system can also be configured with a standby database on a 32-bit HP-UX system,
as long as both servers are running 32-bit images.

以上主要提出了2点要求：即平台(platform)和字长(word size)都必须一致，注意这里不管是physical standby还是logical standby都要求遵守。

官方文档这样撰写的原因是Oracle并不推荐用户在异构平台上搭建DataGuard，如果有用户大量地部署异构平台上的DataGuard可能给后续的服务和支持带来麻烦；所以除非是找不到其他可用的硬件了，否则不推荐采用异构平台搭建的DataGuard环境来提供高可用性。

实际情况是在10g中已经有少数几个异构平台组合可以兼容physical standby或logical standby，而在11g中更增加了对physical standby支持的几种异构平台组合(As of Oracle Database 11g Data Guard provides increased flexibility for Data Guard configurations in which the primary and standby systems may have different CPU architectures, operating systems (for example, Windows & Linux), operating system binaries (32-bit/64-bit), or Oracle database binaries (32-bit/64-bit). For specific information about mixed-platform support, see the My Oracle Support note 413484.1)。

具体10g/11g DataGuard可以利用的异构平台组合，见下列图表:

10g Physical Standby Logical Standby
Heterogeneous
Support 		No Win<->Linux Only
Different
Word-size
(32 / 64 bit) 		Win32<->Win64
Linux32<->Linux64 		Win32->Win64


Linux32->Linux64 (1 way only)

Heterogeneous AND Word-size No No


11g Physical Standby Logical Standby
Heterogeneous
Support 		Win<–>Linux
Solaris <–>AIX
Solaris<–>Linux 		Win<->Linux Only
Different
Word-size
(32 / 64 bit) 		Win32<->Win64
Linux32<->Linux64 		Win32->Win64 Linux32->Linux64 (1 way only)
Heterogeneous AND Word-size Win32<->Linux64 Win32->Linux64
(1 way only)


而在metalink文档和中列出了更为详尽的平台间的兼容信息，在这里一并引用:

Physical Standbys

In addition to general support when using the same Oracle platform, Data Guard Redo Apply (physical standby) can support specific mixed Oracle Platform combinations.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using Redo Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard Redo Apply.

Table Notes

  1. Prior to Data Guard 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring management from the SQL*Plus command line for mixed word-size Data Guard configurations.  This restriction is lifted from Data Guard 11g onward.
  2. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  3. A standby database cannot be open read-only in any environment that has binary-level PL/SQL-related incompatibilities between primary and standby databases.  Support Note 414043.1 is referenced in the table below for any platform combinations where this is the case (the note provides instructions for eliminating incompatibilities post role transition).  It is possible to access a standby database in such environments in Oracle Database 11g by temporarily converting it to a Snapshot Standby database, or in Oracle Database 10g by opening the standby read/write as described in the Data Guard 10g Concepts and Administration guide: Using a Physical Standby Database for Read/Write Testing and Reporting. Both procedures require following the steps in note 414043.1 before making the database available to users.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name 		PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard Redo Apply (Physical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit) 		2
6 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
3 HP-UX (64-bit)
HP-UX PA-RISC 		3
4 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
4 HP-UX IA (64-bit)
HP-UX Itanium 		4
3 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
5 HP Tru64 UNIX
HP Tru64 UNIX 		5
6 IBM AIX on POWER Systems (64-bit) 2 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86) 		7
8, 12  – Oracle 10g onward, see Support Note 414043.1
10 – Oracle 11g onward
11, 13 – Oracle 11g onward, see Support Note 414043.1
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium) 		7 – Oracle 10g onward, see Support Note 414043.1
8
12 – Oracle 10g onward
11, 13 – Oracle 11g onward
9 IBM zSeries Based Linux
z/Linux 		9
18 (64-bit zSeries only)
10 Linux (32-bit)
Linux x86 		7 – Oracle 11g onward
10
11, 13 – Oracle 10g onward, see Support Note 414043.1
11 Linux IA (64-bit)
Linux Itanium 		10 – Oracle 10g onward, see Support Note 414043.1
11
13 – Oracle 10g onward
7 – Oracle 11g onward, see Support Note 414043.1
8, 12 – Oracle 11g onward
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64) 		7 – Oracle 10g onward, see Support Note 414043.1
8 – Oracle 10g onward
12
11, 13 – Oracle 11g onward
13 Linux 64-bit for AMD
Linux x86-64 		7 – Oracle 11g onward, see Support Note 414043.1
10 – Oracle 10g onward, see Support Note 414043.1
11 – Oracle 10g onward
8, 12 – Oracle 11g onward
13
20 – Oracle 11g onward
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium 		15
16 Apple Mac OS
Mac OS X Server 		16
17 Solaris Operating System (x86)
Solaris Operating System (x86) 		17
20 – Oracle 10g onward, see Support Note 414043.1
18 IBM Power Based Linux
Linux on Power 		9 (64-bit zSeries only)
18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64) 		13 – Oracle 11g onward
17 – Oracle 10g onward, see Support Note 414043.1
20

Logical Standby

In addition to general support when using the same Oracle platform, Data Guard SQL Apply (logical standby) can support specific mixed Oracle Platform combinations as of Oracle Database 11g.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using SQL Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard SQL Apply.

Table Notes

  1. All mixed platform combinations for SQL Apply in the table below are supported from Oracle Database 11g onward.
  2. Prior to Data Guard Broker 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring all management from the SQL*Plus command line.  This restriction is lifted from Data Guard 11g onward.
  3. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role unless otherwise specified.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name 		PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard SQL Apply (Logical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit) 		2
3 HP-UX (64-bit)
HP-UX PA-RISC 		3, 4
4 HP-UX IA (64-bit)
HP-UX Itanium 		3, 4
5 HP Tru64 UNIX
HP Tru64 UNIX 		5
6 AIX-Based Systems (64-bit)
AIX5L 		6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86) 		7, 10
8, 12 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium) 		7 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 12, 13
9 IBM zSeries Based Linux
z/Linux 		9
10 Linux (32-bit)
Linux x86 		7, 10
11, 13 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
11 Linux IA (64-bit)
Linux Itanium 		10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64) 		7 – from Oracle 11g onward.  Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 12
13 Linux 64-bit for AMD
Linux x86-64 		10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium 		15
16 Apple Mac OS
Mac OS X Server 		16
17 Solaris Operating System (x86)
Solaris Operating System (x86) 		17
18 IBM Power Based Linux
Linux on Power 		18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64) 		20

Reference:

-Owen Ireland

on [ID 413484.1]>

Filed Under: Oracle, Oracle 11g新特性, Oracle Data Guard Standby Tagged With: , , , ,

Oracle内部错误:ORA-00600[kfioTranslateIO03]一例

2011/05/30  by 3条评论

一套Linux x86-64上的11.2.0.2 RAC+ASM系统，其中一个节点出现了ORA-00600[kfioTranslateIO03]内部错误，其具体日志如下: 


=============================alert.log===============================
adrci> show alert -tail -f 

2011-05-30 20:29:12.657000 +08:00
Starting background process RSMN
RSMN started with pid=31, OS id=22084
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /s01/orabase
ALTER DATABASE MOUNT /* db agent *//* {0:7:3} */
This instance was first to mount
2011-05-30 20:29:15.026000 +08:00
Sweep [inc][100831]: completed
Sweep [inc2][100831]: completed
NOTE: Loaded library: System
ORA-15025: could not open disk "/dev/raw/raw1"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/raw/raw2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/raw/raw3"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/raw/raw5"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database PROD and diskgroup resource ora.DATA.dg is established
Errors in file /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ckpt_22056.trc  (incident=104831): ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []  Incident details in: /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_104831/PROD1_ckpt_22056_i104831.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

adrci> show problem 

ADR Home = /s01/orabase/diag/rdbms/prod/PROD1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT
-------------------- ----------------------------------------------------------- --------------------
2                    ORA 7445 [kghdmp_new()+1133]                                18387
3                    ORA 7445 [kghfnd()+2672]                                    20701
5                    ORA 7445 [kcldmp()+246]                                     28229
6                    ORA 7445 [kclxle()+311]                                     28230
1                    ORA 4031                                                    56918
4                    ORA 445                                                     90278
7                    ORA 600 [kfioTranslateIO03]                                 108831

adrci> show incident -mode detail -p "incident_id=108831"

ADR Home = /s01/orabase/diag/rdbms/prod/PROD1:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   108831
   STATUS                        ready
   CREATE_TIME                   2011-05-30 20:31:55.484000 +08:00
   PROBLEM_ID                    7
   CLOSE_TIME
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  600
   ERROR_ARG1                    kfioTranslateIO03
   ERROR_ARG2
   ERROR_ARG3
   ERROR_ARG4
   ERROR_ARG5
   ERROR_ARG6
   ERROR_ARG7
   ERROR_ARG8
   ERROR_ARG9
   ERROR_ARG10
   ERROR_ARG11
   ERROR_ARG12
   SIGNALLING_COMPONENT          ASM
   SIGNALLING_SUBCOMPONENT
   SUSPECT_COMPONENT
   SUSPECT_SUBCOMPONENT
   ECID
   IMPACTS                       0
   PROBLEM_KEY                   ORA 600 [kfioTranslateIO03]
   FIRST_INCIDENT                96831
   FIRSTINC_TIME                 2011-05-30 20:24:40.372000 +08:00
   LAST_INCIDENT                 108831
   LASTINC_TIME                  2011-05-30 20:31:55.484000 +08:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     19.1
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@rh2.oracle.com.22504_139763918456544
   KEY_NAME                      SID
   KEY_VALUE                     397.1
   OWNER_ID                      1
   INCIDENT_FILE                 /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_108831/PROD1_ckpt_22504_i108831.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ckpt_22504.trc
1 rows fetched

===================================trace===================================

adrci> view /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_108831/PROD1_ckpt_22504_i108831.trc

Dump continued from file: /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ckpt_22504.trc
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 108831 (ORA 600 [kfioTranslateIO03]) ========
----- Beginning of Customized Incident Dump(s) -----
kfioRqSet=0x7f1d524151c0 parent=0x7fffb2642d30 gn=(64.0) cnt=0
  size=32768 vxn=0 byte offset=16384 buf offset=0
  tried[0]=0 tried[1]=0 tried[2]=0 tried[3]=0 tried[4]=0 tried[5]=0
  skipped[0]=0 skipped[1]=0 skipped[2]=0 skipped[3]=0 skipped[4]=0 skipped[5]=0
parent :
DDE: Ending a split invocation on error recording!
----- End of Customized Incident Dump(s) -----

*** 2011-05-30 20:31:55.548
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000001 ? 000000002 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000002 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksfdmp()+64          call     ksedmp()             000000003 ? 000000002 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgexPhaseII()+1764  call     ksfdmp()             000000003 ? 000000002 ?
                                                   7FFFB2634D58 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgexExplicitEndInc  call     dbgexPhaseII()       7F1D5281F710 ? 7F1D52822500 ?
()+750                                             7FFFB2640890 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgeEndDDEInvocatio  call     dbgexExplicitEndInc  7F1D5281F710 ? 7F1D52822500 ?
nImpl()+767                   ()                   7FFFB2640890 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgeEndSpltInvokOnR  call     dbgeEndDDEInvocatio  7F1D5281F710 ? 7F1D52822500 ?
ec()+265                      nImpl()              7FFFB2640890 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgePostErrorKGE()+  call     dbgeEndSpltInvokOnR  7F1D5281F710 ? 7F1D52822500 ?
248                           ec()                 7FFFB2640890 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   000000000 ? 7F1D52830E40 ?
63                                                 000003AE9 ? 000000000 ?
                                                   100000000 ? 000000002 ?
kgeade()+351         call     dbkePostKGE_kgsf()   00B7C8EA0 ? 7F1D52830E40 ?
                                                   000003AE9 ? 000000000 ?
                                                   100000000 ? 000000002 ?
kgerelv()+135        call     kgeade()             00B7C8EA0 ? 00B7C9050 ?
                                                   7F1D52830E40 ? 000003AE9 ?
                                                   100000000 ? 000000002 ?
kserecl0()+157       call     kgerelv()            00B7C8EA0 ? 7F1D52830E40 ?
                                                   000003AE9 ? 00952980C ?
                                                   7FFFB2641C10 ? 000000000 ?
kfioErrorRecord()+7  call     kserecl0()           00B7C8EA0 ? 7F1D52830E40 ?
6                                                  000003AE9 ? 000000005 ?
                                                   7FFFB2641C60 ? 000000000 ?
kfiorq_dump()+129    call     kfioErrorRecord()    7FFFB2642D30 ? 7F1D52830E40 ?
                                                   000003AE9 ? 000000005 ?
                                                   7FFFB2641C60 ? 000000000 ?
kfioRqSetDump()+565  call     kfiorq_dump()        7FFFB2642D30 ? 7F1D52830E40 ?
                                                   000003AE9 ? 000000005 ?
                                                   7FFFB2641C60 ? 000000000 ?
kfioTranslateIO()+3  call     kfioRqSetDump()      7F1D524151C0 ? 7F1D52830E40 ?
079                                                000003AE9 ? 000000005 ?
kfioRqSetPrepare()+  call     kfioTranslateIO()    7F1D524151C0 ? 7F1D52415098 ?
1017                                               7FFFB26421D4 ? 7FFFB26421D0 ?
                                                   0D4F338B0 ? 000000000 ?
kfioSubmitIO()+2852  call     kfioRqSetPrepare()   7F1D524151C0 ? 7F1D52415098 ?
                                                   7FFFB26425D8 ? 7FFFB2642608 ?
                                                   0D4F338B0 ? 000000000 ?
kfioRequestPriv()+1  call     kfioSubmitIO()       7FFFB2642E10 ? 000000001 ?
94                                                 7FFFB26425D8 ? 7FFFB2642608 ?
                                                   0D4F338B0 ? 000000000 ?
kfioRequest()+701    call     kfioRequestPriv()    000000000 ? 000000001 ?
                                                   7FFFB2642E18 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksfd_kfioRequest()+  call     kfioRequest()        7FFFB2642E10 ? 000000001 ?
644                                                7FFFB2642E18 ? 000000001 ?
                                                   000000000 ? 7FFF00000000 ?
ksfd_osmio()+1050    call     ksfd_kfioRequest()   7FFFB2642E10 ? 000000001 ?
                                                   7FFFB2642E18 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksfd_io()+2717       call     ksfd_osmio()         000000001 ?
                                                   FFFFFFFFB2642D30 ?
                                                   FFFFFFFFB2642D30 ?
                                                   0D400A0B0 ? 000008000 ?
                                                   7FFFB2643170 ?
ksfdread()+576       call     ksfd_io()            0D400A0B0 ? 000000001 ?
                                                   7F1D52417E00 ? 000008000 ?
                                                   000000000 ? 000000703 ?
kcc_identify_file()  call     ksfdread()           0D400A0B0 ? 000000001 ?
+309                                               7F1D52417E00 ? 000008000 ?
                                                   000000000 ? 000000703 ?
kcc_identify()+225   call     kcc_identify_file()  0D400A0B0 ? 7F1D52417E00 ?
                                                   000000000 ? 060019450 ?
                                                   060019630 ? 0DAC34670 ?
kccida()+225         call     kcc_identify()       000000000 ? 7F1D52417E00 ?
                                                   060019630 ? 7FFFB26434A4 ?
                                                   000000000 ? 0DAC34670 ?
ksbabs()+771         call     kccida()             7FFFB2643B08 ? 7F1D52417E00 ?
                                                   060019630 ? 7FFFB26434A4 ?
                                                   000000000 ? 0DAC34670 ?
ksbrdp()+971         call     ksbabs()             7FFFB2643B08 ? 7F1D52417E00 ?
                                                   060019630 ? 7FFFB26434A4 ?
                                                   000000000 ? 0DAC34670 ?

adrci> view /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ckpt_22504.trc

NOTE: disk 4 is missing from group 1
Incident 108831 created, dump file: /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_108831/PROD1_ckpt_22504_i108831.trc
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []

=========Start of 'kfiorq = [0x7fffb2642d30]' dumping =========
        Status            =  UNKWOWN
        Flags             =  READ |  SYNC
        Mirror side       = 0
        Fib               = 0xd4f338b0
        Offset            = 1
        buffer ptr        = 0x7f1d52417e00
        Rcount            = 32768
        err_kfiorq        = 15081
        Inflight disk IO  = 0
        Completed disk IO = 0
        Oracle error      = 0
        Intended zone     = 48
  ===Dump of all attached kfiodrq's===
=========End of 'kfiorq = [0x7fffb2642d30]' dumping =========

parent :
############# kfiofib = 0xd4f338b0 #################
Diskgroup Name     =
File number        = 261.747100215
File type          = 1
Flags              = 10
Blksize            = 16384
File size          = 1131 blocks
Blk one offset     = 1
Redundancy         = 17
Physical blocksz   = 512
Open name          = +DATA/prod/controlfile/current.261.747100215
Fully-qualified nm =+DATA/prod/controlfile/current.261.747100215
Mapid             = 2
Slave ID          = -1
Connection        = 0x(nil)
############################################
Error ORA-600 signaled at ksedsts()+461<-ksf_short_stack()+77<-kge_snap_callstack()+63<-kge_sigtrace_dump()+69<-kgepop()+712<-kgersel()+175<-kfioTranslateIO()+3138<-kfi
oRqSetPrepare()+1022<-kfioSubmitIO()+2857<-kfioRequestPriv()+199<-kfioRequest()+706<-ksfd_kfioRequest()+649<-ksfd_osmio()+1055<-ksfd_io()+2722<-ksfdread()+581<-kcc_iden
tify_file()+314<-kcc_identify()+230<-kccida()+230<-ksbabs()+771<-ksbrdp()+971<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+266<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+244<-_start()+36
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 22504
----- Abridged Call Stack Trace -----
ksedsts()+461<-kfioRequest()+2157<-ksfd_kfioRequest()+649<-ksfd_osmio()+1055<-ksfd_io()+2722<-ksfdread()+581<-kcc_identify_file()+314<-kcc_identify()+230<-kccida()+230<
-ksbabs()+771<-ksbrdp()+971<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+266<-ssthrdmain()+252
<-main()+201<-__libc_start_main()+244<-_start()+36 ----- End of Abridged Call Stack Trace ----- *** 2011-05-30 20:31:56.271 KSU: Terminating fatal process 'oracle@rh2.oracle.com (CKPT)' adrci> ips create package
Created package 2 without any contents, correlation level typical

adrci> ips add problem 7 package 2
Added problem 7 to package 2

adrci> ips finalize package 2
Finalized package 2

adrci> ips generate package 2 in /tmp
Generated package 2 in file /tmp/IPSPKG_20110531224208_COM_1.zip, mode complete

诊断发现由于ASM diskgroup磁盘组中的磁盘设备文件/dev/raw/raw*的权限被修改成了0600，而这些裸设备的拥有者为grid用户，导致oracle用户无法读写这些裸设备，通过将设备文件的权限修改为0660，解决了该问题。

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

11g新特性:A useful View V$DIAG_INFO

2011/05/30  by 暂无评论

在11g中引入了自动诊断资料档案库(ADR)特性，默认情况下各种trace,dump存放的目录位置区别于9i/10g显得更加难以查找了。

ADR 基目录中可以包含多个 ADR 主目录，其中每个 ADR 主目录都是一个根目录，用于存放特定 Oracle 产品或组件的特定实例的全部诊断数据。前一张幻灯片的图形中显示了数据库的 ADR 主目录位置。

另外，还生成了两个预警文件。一个是文本形式的预警文件（与早期版本 Oracle DB 使用的预警文件非常相似），位于各个 ADR 主目录的 TRACE 目录下。还有一个符合 XML 标准的预警消息文件，存储在 ADR 主目录内的 ALERT 子目录下。可使用 Enterprise Manager 和 ADRCI 实用程序查看文本格式的预警日志（已删除了 XML 标记）。

此幻灯片中的图形显示了 ADR 主目录的目录结构。INCIDENT 目录包含多个子目录， 每个子目录均以特定意外事件命名，并且仅包含与该意外事件相关的转储。

HM 目录包含由健康状况监视器生成的检查器运行报告。

还有一个 METADATA 目录，其中包含资料档案库自身的重要文件。可以将此目录比作数据库字典。可使用 ADRCI 查询此字典。

ADR 命令解释器 (ADRCI) 是一个实用程序，可用于执行支持工作台允许的所有任务（但是仅限于在命令行环境中）。使用 ADRCI 实用程序，您还可以查看 ADR 中跟踪文件的名称以及删除了 XML 标记、具有和不具有内容筛选功能的预警日志。

此外，还可以使用 V$DIAG_INFO 列出一些重要的 ADR 位置。

自动诊断资料档案库 (ADR)的主要目录结构:

1.Server Directory Structure

adr1

2.Client Directory Structure

adr2

一个需要注意的细节是启用了ADR自动诊断资料档案库后,LISTENER监听器日志的默认位置也被移动到diagnostic_dest下了，而不在如9i/10g那样存放在$ORACLE_HOME/network/log目录下，有不少人因为忘记了这个细节而花费了大量时间去寻找listener.log，这个人也包括我。实际上我们还是可以将listener.log日志的位置还原回10g的形式，这一点可以通过修改$ORACLE_HOME/network/admin/listener.ora来达成: 


[oracle@rh2 ~]$ cd $ORACLE_HOME/network/admin

[oracle@rh2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /s01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rh2.oracle.com)(PORT = 1521))
  ) ADR_BASE_LISTENER = /s01/orabase 将以上listener.ora文件的ADR_BASE_LISTENER条目删除，并加上

DIAG_ADR_ENABLED_LISTENER = OFF

[oracle@rh2 log]$ lsnrctl reload

[oracle@rh2 admin]$ tnsping PROD
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 30-MAY-2011 21:39:21
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
/s01/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rh-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
OK (0 msec)

[oracle@rh2 log]$ cat $ORACLE_HOME/network/log/listener.log

TNSLSNR for Linux: Version 11.2.0.2.0 - Production on 30-MAY-2011 21:42:52

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

System parameter file is /s01/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /s01/oracle/product/11.2.0/dbhome_1/network/log/listener.log
Trace information written to /s01/oracle/product/11.2.0/dbhome_1/network/trace/listener.trc
Trace level is currently 6
Started with pid=24331
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh2)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
30-MAY-2011 21:42:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=rh2.oracle.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.122)(PORT=1521)))
30-MAY-2011 21:42:59 * service_register * PROD1 * 12542
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use
Error listening on: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.133)(PORT=1521)))
30-MAY-2011 21:42:59 * service_register * PROD1 * 12542
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use
30-MAY-2011 21:43:02 * service_update * PROD1 * 0
30-MAY-2011 21:43:02 * service_update * PROD1 * 0
30-MAY-2011 21:43:37 * service_register * G10R2 * 0
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.122)(PORT=1521)))
30-MAY-2011 21:43:39 * service_register * +ASM1 * 12542
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

从以上ADR目录结构图中可以看到整个架构变得更复杂了，实际使用中的ADR档案资料库的复杂度要更高： 


[grid@rh2 raw]$ tree -d  /s01/orabase/diag
/s01/orabase/diag
|-- asm
|   `-- +asm
|       `-- +ASM1
|           |-- alert
|           |-- cdump
|           |-- hm
|           |-- incident
|           |   |-- incdir_4897
|           |   |-- incdir_4898
|           |   |-- incdir_4899
|           |   |-- incdir_4900
|           |   |-- incdir_4901
|           |   |-- incdir_4902
|           |   `-- incdir_4903
|           |-- incpkg
|           |-- ir
|           |-- lck
|           |-- metadata
|           |-- metadata_dgif
|           |-- metadata_pv
|           |-- stage
|           |-- sweep
|           `-- trace
|               |-- cdmp_20110424212230
|               |-- cdmp_20110424214914
|               |-- cdmp_20110424220022
|               |-- cdmp_20110424221131
|               |-- cdmp_20110424222254
|               |-- cdmp_20110424223546
|               `-- cdmp_20110427154653
|-- clients
|   `-- user_oracle
|       `-- host_3070836769_80
|           |-- alert
|           |-- cdump
|           |-- incident
|           |-- incpkg
|           |-- lck
|           |-- metadata
|           |-- metadata_dgif
|           |-- metadata_pv
|           |-- stage
|           |-- sweep
|           `-- trace
|-- crs
|-- diagtool
|-- lsnrctl
|-- netcman
|-- ofm
|-- rdbms
|   |-- maclean
|   |   `-- MACLEAN
|   |       |-- alert
|   |       |-- cdump
|   |       |-- hm
|   |       |-- incident
|   |       |-- incpkg
|   |       |-- ir
|   |       |-- lck
|   |       |-- metadata
|   |       |-- metadata_dgif
|   |       |-- metadata_pv
|   |       |-- stage
|   |       |-- sweep
|   |       `-- trace
|   `-- prod
|       `-- PROD1
|           |-- alert
|           |-- cdump
|           |   |-- core_27975
|           |   |-- core_28006
|           |   |-- core_28013
|           |   `-- core_28034
|           |-- hm
|           |-- incident
|           |   |-- incdir_100831
|           |   |-- incdir_104831
|           |   |-- incdir_108831
|           |   |-- incdir_18201
|           |   |-- incdir_18202
|           |   |-- incdir_18266
|           |   |-- incdir_18361
|           |   |-- incdir_18362
|           |   |-- incdir_18369
|           |   |-- incdir_18385
|           |   |-- incdir_18387
|           |   |-- incdir_18393
|           |   |-- incdir_18402
|           |   |-- incdir_18410
|           |   |-- incdir_18411
|           |   |-- incdir_18441
|           |   |-- incdir_18457
|           |   |-- incdir_18458
|           |   |-- incdir_18465
|           |   |-- incdir_19737
|           |   |-- incdir_19738
|           |   |-- incdir_19739
|           |   |-- incdir_19745
|           |   |-- incdir_19746
|           |   |-- incdir_20589
|           |   |-- incdir_20701
|           |   |-- incdir_20736
|           |   |-- incdir_20737
|           |   |-- incdir_20738
|           |   |-- incdir_20739
|           |   |-- incdir_20740
|           |   |-- incdir_20957
|           |   |-- incdir_20973
|           |   |-- incdir_20989
|           |   |-- incdir_21005
|           |   |-- incdir_21778
|           |   |-- incdir_21936
|           |   |-- incdir_21937
|           |   |-- incdir_21938
|           |   |-- incdir_22200
|           |   |-- incdir_22201
|           |   |-- incdir_22216
|           |   |-- incdir_22232
|           |   |-- incdir_22233
|           |   |-- incdir_23306
|           |   |-- incdir_23506
|           |   |-- incdir_28123
|           |   |-- incdir_28147
|           |   |-- incdir_28148
|           |   |-- incdir_28227
|           |   |-- incdir_28228
|           |   |-- incdir_28229
|           |   |-- incdir_28230
|           |   |-- incdir_28235
|           |   |-- incdir_29323
|           |   |-- incdir_29324
|           |   |-- incdir_29325
|           |   |-- incdir_29326
|           |   |-- incdir_29327
|           |   |-- incdir_30681
|           |   |-- incdir_30682
|           |   |-- incdir_30683
|           |   |-- incdir_31724
|           |   |-- incdir_35253
|           |   |-- incdir_36453
|           |   |-- incdir_37653
|           |   |-- incdir_38853
|           |   |-- incdir_40053
|           |   |-- incdir_41253
|           |   |-- incdir_42453
|           |   |-- incdir_43653
|           |   |-- incdir_44853
|           |   |-- incdir_46053
|           |   |-- incdir_47253
|           |   |-- incdir_48453
|           |   |-- incdir_49653
|           |   |-- incdir_54525
|           |   |-- incdir_56918
|           |   |-- incdir_89183
|           |   |-- incdir_89184
|           |   |-- incdir_89185
|           |   |-- incdir_89186
|           |   |-- incdir_89187
|           |   |-- incdir_89327
|           |   |-- incdir_89343
|           |   |-- incdir_89351
|           |   |-- incdir_89359
|           |   |-- incdir_90270
|           |   |-- incdir_90271
|           |   |-- incdir_90272
|           |   |-- incdir_90273
|           |   |-- incdir_90274
|           |   |-- incdir_90275
|           |   `-- incdir_96831
|           |-- incpkg
|           |   `-- pkg_1
|           |       `-- seq_1
|           |           |-- crs
|           |           `-- export
|           |-- ir
|           |-- lck
|           |-- metadata
|           |-- metadata_dgif
|           |-- metadata_pv
|           |-- stage
|           |-- sweep
|           `-- trace
|               |-- cdmp_20110502214850
|               |-- cdmp_20110502214947
|               |-- cdmp_20110502221010
|               |-- cdmp_20110502221029
|               |-- cdmp_20110502221204
|               |-- cdmp_20110502221221
|               |-- cdmp_20110502221257
|               |-- cdmp_20110502221318
|               |-- cdmp_20110502221450
|               |-- cdmp_20110502221505
|               |-- cdmp_20110502222225
|               |-- cdmp_20110502222315
|               |-- cdmp_20110502222402
|               |-- cdmp_20110502224708
|               |-- cdmp_20110502230815
|               |-- cdmp_20110503202436
|               |-- cdmp_20110503202521
|               |-- cdmp_20110509231250
|               |-- cdmp_20110512171047
|               |-- cdmp_20110512171231
|               |-- cdmp_20110512171437
|               |-- cdmp_20110512171635
|               |-- cdmp_20110520165537
|               |-- cdmp_20110520232601
|               |-- cdmp_20110520234343
|               |-- cdmp_20110521000119
|               |-- cdmp_20110521000218
|               |-- cdmp_20110521065437
|               |-- cdmp_20110521065455
|               |-- cdmp_20110521065512
|               |-- cdmp_20110521074224
|               |-- cdmp_20110521074737
|               |-- cdmp_20110521075206
|               |-- cdmp_20110521080019
|               |-- cdmp_20110521080642
|               |-- cdmp_20110521140052
|               |-- cdmp_20110530202441
|               |-- cdmp_20110530202442
|               |-- cdmp_20110530202846
|               |-- cdmp_20110530202848
|               |-- cdmp_20110530202916
|               |-- cdmp_20110530202918
|               |-- cdmp_20110530203156
|               `-- cdmp_20110530203158
`-- tnslsnr
    `-- rh2
        `-- listener
            |-- alert
            |-- cdump
            |-- incident
            |-- incpkg
            |-- lck
            |-- metadata
            |-- metadata_dgif
            |-- metadata_pv
            |-- stage
            |-- sweep
            `-- trace

240 directories

所幸的是在11g中提供了比传统的gettrcname.sql脚本更为给力的诊断文件位置信息汇总的视图V$DIAG_INFO: 


V$DIAG_INFO 视图列出了所有重要的 ADR 位置：
ADR Base：ADR 基目录的路径
ADR Home：当前数据库实例的 ADR 主目录的路径
Diag Trace：文本预警日志和后台/前台进程跟踪文件的位置
Diag Alert：XML 版本的预警日志的位置
Default Trace File：会话的跟踪文件的路径。SQL 跟踪文件将写入到这里。
Health Monitor: 健康检查报告所在目录
Active Problem Count：当前激活的问题总数
Active Incident Count  当前激活的事故总数

SQL>  select name,value from v$diag_info;

NAME                                    VALUE
--------------------------------------- ------------------------------------------------------------
Diag Enabled                            TRUE
ADR Base                                /s01/orabase
ADR Home                                /s01/orabase/diag/rdbms/prod/PROD1
Diag Trace                              /s01/orabase/diag/rdbms/prod/PROD1/trace
Diag Alert                              /s01/orabase/diag/rdbms/prod/PROD1/alert
Diag Incident                           /s01/orabase/diag/rdbms/prod/PROD1/incident
Diag Cdump                              /s01/orabase/diag/rdbms/prod/PROD1/cdump
Health Monitor                          /s01/orabase/diag/rdbms/prod/PROD1/hm
Default Trace File                      /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_22893.trc
Active Problem Count                    7
Active Incident Count                   373

11 rows selected.

adrci> show hm_run
**********************************************************
HM RUN RECORD 119
**********************************************************
   RUN_ID                        2481
   RUN_NAME                      HM_RUN_2481
   CHECK_NAME                    DB Structure Integrity Check
   NAME_ID                       2
   MODE                          2
   START_TIME                    2011-05-21 20:11:38.612669 +08:00
   RESUME_TIME
   END_TIME                      2011-05-21 20:11:38.619530 +08:00
   MODIFIED_TIME                 2011-05-21 20:11:38.619530 +08:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   

adrci> create report hm_run HM_RUN_2481

adrci> show hm_run  -p "RUN_ID=2481"

ADR Home = /s01/orabase/diag/rdbms/prod/PROD1:
*************************************************************************

**********************************************************
HM RUN RECORD 1
**********************************************************
   RUN_ID                        2481
   RUN_NAME                      HM_RUN_2481
   CHECK_NAME                    DB Structure Integrity Check
   NAME_ID                       2
   MODE                          2
   START_TIME                    2011-05-21 20:11:38.612669 +08:00
   RESUME_TIME
   END_TIME                      2011-05-21 20:11:38.619530 +08:00
   MODIFIED_TIME                 2011-05-30 21:09:43.071150 +08:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   /s01/orabase/diag/rdbms/prod/PROD1/hm/HMREPORT_HM_RUN_2481.hm

[oracle@rh2 ~]$ cd /s01/orabase/diag/rdbms/prod/PROD1/hm

[oracle@rh2 hm]$ cat HMREPORT_HM_RUN_2481.hm



 HM Report: HM_RUN_2481
 
 DB Structure Integrity Check
 2481
 HM_RUN_2481
 REACTIVE
 COMPLETED
 0
 0
 0
 2011-05-21 20:11:38.612669 +08:00
 2011-05-21 20:11:38.619530 +08:00

通过查询V$diag_info可以很容易找到自身服务进程的trace文件位置，对于其他进程的trace文件则可以查询v$process新加入的tracefile列: 


SQL> select spid,tracefile from v$process;

SPID                     TRACEFILE
------------------------ --------------------------------------------------------------------------------
                         /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_0.trc
22789                    /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_pmon_22789.trc
22791                    /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_psp0_22791.trc

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

crsctl status resource -t -init in 11.2.0.2 grid infrastructure

2011/05/28  by 暂无评论

11.2.0.2的grid infrastructure中crsctl stat res 命令不再显示如ora.cssd、ora.ctssd、ora.diskmon等基础资源的信息，如果用户想要了解这些resource状态需要加上-init选项: 


[grid@rh2 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.2.0]

[grid@rh2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rh2
ora.LISTENER.lsnr
               OFFLINE OFFLINE      rh2
ora.asm
               ONLINE  ONLINE       rh2
ora.gsd
               OFFLINE OFFLINE      rh2
ora.net1.network
               ONLINE  ONLINE       rh2
ora.ons
               ONLINE  ONLINE       rh2
ora.registry.acfs
               OFFLINE OFFLINE      rh2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        OFFLINE OFFLINE
ora.cvu
      1        OFFLINE OFFLINE
ora.dw.db
      1        OFFLINE OFFLINE
ora.maclean.db
      1        OFFLINE OFFLINE
ora.oc4j
      1        OFFLINE OFFLINE
ora.prod.db
      1        OFFLINE OFFLINE
      2        OFFLINE OFFLINE
ora.prod.maclean.svc
      1        OFFLINE OFFLINE
      2        OFFLINE OFFLINE
ora.prod.maclean_pre.svc
      1        OFFLINE OFFLINE
      2        OFFLINE OFFLINE
ora.prod.maclean_pre_preconnect.svc
      1        OFFLINE OFFLINE
ora.prod.maclean_taf.svc
      1        OFFLINE OFFLINE
      2        OFFLINE OFFLINE
ora.rh2.vip
      1        OFFLINE OFFLINE
ora.rh3.vip
      1        OFFLINE OFFLINE
ora.scan1.vip
      1        OFFLINE OFFLINE [grid@rh2 ~]$ crsctl stat res -t -init  --------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources

--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rh2                      Started
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       rh2
ora.crf
      1        ONLINE  ONLINE       rh2
ora.crsd
      1        ONLINE  ONLINE       rh2
ora.cssd
      1        ONLINE  ONLINE       rh2
ora.cssdmonitor
      1        ONLINE  ONLINE       rh2
ora.ctssd
      1        ONLINE  ONLINE       rh2                      OBSERVER
ora.diskmon
      1        ONLINE  ONLINE       rh2
ora.drivers.acfs
      1        ONLINE  OFFLINE
ora.evmd
      1        ONLINE  ONLINE       rh2
ora.gipcd
      1        ONLINE  ONLINE       rh2
ora.gpnpd
      1        ONLINE  ONLINE       rh2
ora.mdnsd
      1        ONLINE  ONLINE       rh2

此外在11.2.0.2的grid中当我们想启动、停止、修改这些init资源时都需要加上-init选项，否则将出现CRS-2613: Could not find resource错误: 


[grid@rh2 ~]$ crsctl stat res ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on rh2

[grid@rh2 ~]$ crsctl modify res ora.asm -attr AUTO_START=never

[grid@rh2 ~]$ crsctl stat res ora.asm -p
NAME=ora.asm
TYPE=ora.asm.type
ACL=owner:grid:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=ora.%CRS_CSS_NODENAME%.ASM%CRS_CSS_NODENUMBER%.asm
AUTO_START=never
CHECK_INTERVAL=60
CHECK_TIMEOUT=30
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(rh2)=+ASM1
GEN_USR_ORA_INST_NAME@SERVERNAME(rh3)=+ASM2
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
START_DEPENDENCIES=weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM%CRS_CSS_NODENUMBER%
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.2.0

[grid@rh2 ~]$ crsctl status resource  -init -t|grep -v ONLINE|tail -13
ora.asm
ora.cluster_interconnect.haip
ora.crf
ora.crsd
ora.cssd
ora.cssdmonitor
ora.ctssd
ora.diskmon
ora.drivers.acfs
ora.evmd
ora.gipcd
ora.gpnpd
ora.mdnsd

[grid@rh2 ~]$ crsctl status resource  -init -t|grep -v ONLINE|tail -13|xargs crsctl status resource CRS-2613: Could not find resource 'ora.cluster_interconnect.haip'.
CRS-2613: Could not find resource 'ora.crf'.
CRS-2613: Could not find resource 'ora.crsd'.
CRS-2613: Could not find resource 'ora.cssd'.
CRS-2613: Could not find resource 'ora.cssdmonitor'.
CRS-2613: Could not find resource 'ora.ctssd'.
CRS-2613: Could not find resource 'ora.diskmon'.
CRS-2613: Could not find resource 'ora.drivers.acfs'.
CRS-2613: Could not find resource 'ora.evmd'.
CRS-2613: Could not find resource 'ora.gipcd'.
CRS-2613: Could not find resource 'ora.gpnpd'.
CRS-2613: Could not find resource 'ora.mdnsd'. NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on rh2 [grid@rh2 ~]$ crsctl status res ora.crsd -init -p NAME=ora.crsd
TYPE=ora.crs.type
ACL=owner:root:rw-,pgrp:oinstall:rw-,other::r--,user:grid:r-x
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
AUTO_START=always
CARDINALITY=1
CHECK_ARGS=
CHECK_COMMAND=
CHECK_INTERVAL=30
CLEAN_ARGS=
CLEAN_COMMAND=
DAEMON_LOGGING_LEVELS=AGENT=1,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=1,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1
DAEMON_TRACING_LEVELS=AGENT=0,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=0,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION="Resource type for CRSD"
DETACHED=true
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=3
FAILURE_THRESHOLD=5
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ORA_VERSION=11.2.0.2.0
PID_FILE=
PLACEMENT=balanced
PROCESS_TO_MONITOR=
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=10
SCRIPT_TIMEOUT=60
SERVER_POOLS=
START_ARGS=
START_COMMAND=
START_DEPENDENCIES=hard(ora.asm,ora.cssd,ora.ctssd,ora.gipcd)pullup(ora.asm,ora.cssd,ora.ctssd,ora.gipcd)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_ARGS=
STOP_COMMAND=
STOP_DEPENDENCIES=hard(shutdown:ora.asm,intermediate:ora.cssd,intermediate:ora.gipcd)
STOP_MODE=NONE
STOP_TIMEOUT=43200
UPTIME_THRESHOLD=1m
USR_ORA_ENV=

[grid@rh2 ~]$ crsctl modify res ora.crsd -init -attr "SCRIPT_TIMEOUT"=65   
CRS-0245:  User doesn't have enough privilege to perform the operation
CRS-4000: Command Modify failed, or completed with errors.

/* 修改某些资源的属性要求root权限 */

[root@rh2 ~]# crsctl modify res ora.crsd -init -attr "SCRIPT_TIMEOUT"=65 

[root@rh2 ~]# crsctl status res ora.crsd -init -p|grep SCRIPT_TIMEOUT
SCRIPT_TIMEOUT=65

[root@rh2 ~]# crsctl status res ora.ctssd -p -init
NAME=ora.ctssd
TYPE=ora.ctss.type
ACL=owner:root:rw-,pgrp:oinstall:rw-,other::r--,user:grid:r-x
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
AUTO_START=always
CARDINALITY=1
CHECK_ARGS=
CHECK_COMMAND=
CHECK_INTERVAL=30
CLEAN_ARGS=
CLEAN_COMMAND=
DAEMON_LOGGING_LEVELS=CLUCLS=0,CSSCLNT=0,CRSCCL=1,CTSS=5,OCRAPI=1,OCRCLI=1,OCRMSG=1
DAEMON_TRACING_LEVELS=CLUCLS=0,CSSCLNT=0,CRSCCL=1,CTSS=5,OCRAPI=1,OCRCLI=1,OCRMSG=1
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION="Resource type for Ctss Agents"
DETACHED=true
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=3
FAILURE_THRESHOLD=5
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ORA_VERSION=11.2.0.2.0
PID_FILE=
PLACEMENT=balanced
PROCESS_TO_MONITOR=
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SERVER_POOLS=
START_ARGS=
START_COMMAND=
START_DEPENDENCIES=hard(ora.cssd,ora.gipcd)pullup(ora.cssd,ora.gipcd)
START_TIMEOUT=60
STATE_CHANGE_TEMPLATE=
STOP_ARGS=
STOP_COMMAND=
STOP_DEPENDENCIES=hard(ora.cssd,ora.gipcd)
STOP_TIMEOUT=60
UPTIME_THRESHOLD=1m
USR_ORA_ENV=

[root@rh2 ~]# crsctl status res ora.diskmon -p -init
NAME=ora.diskmon
TYPE=ora.diskmon.type
ACL=owner:root:rw-,pgrp:oinstall:rw-,other::r--,user:grid:r-x
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
AUTO_START=never
CARDINALITY=1
CHECK_ARGS=
CHECK_COMMAND=
CHECK_INTERVAL=3
CHECK_TIMEOUT=30
CLEAN_ARGS=
CLEAN_COMMAND=
DAEMON_LOGGING_LEVELS=
DAEMON_TRACING_LEVELS=
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION="Resource type for Diskmon"
DETACHED=true
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=3
FAILURE_THRESHOLD=5
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ORA_VERSION=11.2.0.2.0
PID_FILE=
PLACEMENT=balanced
PROCESS_TO_MONITOR=
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=10
SCRIPT_TIMEOUT=60
SERVER_POOLS=
START_ARGS=
START_COMMAND=
START_DEPENDENCIES=weak(concurrent:ora.cssd)pullup:always(ora.cssd)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_ARGS=
STOP_COMMAND=
STOP_DEPENDENCIES=
STOP_TIMEOUT=60
UPTIME_THRESHOLD=5s
USR_ORA_ENV=ORACLE_USER=grid
VERSION=11.2.0.2.0

[root@rh2 ~]# crsctl status res ora.cssd -init -p
NAME=ora.cssd

TYPE=ora.cssd.type
ACL=owner:root:rw-,pgrp:oinstall:rw-,other::r--,user:grid:r-x
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/cssdagent%CRS_EXE_SUFFIX%
AGENT_HB_INTERVAL=0
AGENT_HB_MISCOUNT=10
AUTO_START=always
CARDINALITY=1
CHECK_ARGS=
CHECK_COMMAND=
CHECK_INTERVAL=30
CLEAN_ARGS=abort
CLEAN_COMMAND=
CSSD_MODE=
CSSD_PATH=%CRS_HOME%/bin/ocssd%CRS_EXE_SUFFIX%
CSS_USER=grid
DAEMON_LOGGING_LEVELS=CSSD=2,GIPCNM=2,GIPCGM=2,GIPCCM=2,CLSF=0,SKGFD=0,GPNP=1,OLR=0
DAEMON_TRACING_LEVELS=CSSD=0,GIPCNM=0,GIPCGM=0,GIPCCM=0,CLSF=0,SKGFD=0,GPNP=0,OLR=0
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION="Resource type for CSSD"
DETACHED=true
ENABLED=1
ENV_OPTS=
FAILOVER_DELAY=0
FAILURE_INTERVAL=3
FAILURE_THRESHOLD=5
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
OMON_INITRATE=1000
OMON_POLLRATE=500
ORA_OPROCD_MODE=
ORA_VERSION=11.2.0.2.0
PID_FILE=
PLACEMENT=balanced
PROCD_TIMEOUT=1000
PROCESS_TO_MONITOR=
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=3
SCRIPT_TIMEOUT=600
SERVER_POOLS=
START_ARGS=
START_COMMAND=
START_DEPENDENCIES=weak(concurrent:ora.diskmon)hard(ora.cssdmonitor,ora.gpnpd,ora.gipcd)pullup(ora.gpnpd,ora.gipcd)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_ARGS=
STOP_COMMAND=
STOP_DEPENDENCIES=hard(intermediate:ora.gipcd,shutdown:ora.diskmon,intermediate:ora.cssdmonitor)
STOP_TIMEOUT=900
UPTIME_THRESHOLD=1m
USR_ORA_ENV=
VMON_INITLIMIT=16
VMON_INITRATE=500
VMON_POLLRATE=500

Filed Under: Oracle, Oracle 11g新特性, Oracle RAC, Oracle网格技术Grid Tagged With: , , , , ,
?Previous Page
Next Page?










请使用浏览器的分享功能分享到微信等