Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

2135

积分

502

好友

184

主题
1#
发表于 2014-4-17 16:39:58 | 查看: 4477| 回复: 1
1.   参考文档及关键Bug筛选规则
1.1        主要的参考文档
Oracle会定期针对每个版本所发现的所有Bug进行跟踪,并根据影响程度进行分类。如果出现关键问题都会及时发布Alert,并将这些内容都合并到已知问题-Known Issues文档中。关注这些文档可以及时了解到各个版本的最新信息。
目前两个版本的主文档为:
ALERT: Oracle 11g Release 2 (11.2) SupportStatus and Alerts (Doc ID 880782.1)

它里面包含了对各个小版本的Known Issue列表,比如
11.2.0.2/11.2.0.3等。

1.2        辅助的分析工具,RAT Patch Advisory
通常数据库的主要补丁或补丁集均在Known Issue中以PSU(PatchSet Update)的方式列出,但这却不适合10.2.0.3及以前版本,因为PSU尚未推出。
同时,在某些情况下Known Issue的更新不如RAT的补丁分析工具来得及时,因为该工具是通过直接查询Bug库获取信息的,也就保证了它的结果是最新的。

1.3        Bug的筛选规则
Oracle数据库的Bug有许多的分类,其中按照影响程度可分为30多种,包含了从实例宕机到代码提高等许多种分类。而本次主要的关注点是关键Bug,即对系统影响较大的补丁,所以我们从所有的补丁分析中选择以下分类供局方参考:
  
影响程度
  
描述-Bug触发时导致的结果
Instance  May Crash
可能导致实例宕机
Hang(Process Hang/Spins)
可能导致进程僵死
Corruption(MEM/Block/Dictionary/Index)
可能导致损坏(数据,字典表或索引等)
Process May Dump (Failure)
可能导致进程/会话异常退出
Wrong  Results
可能产生错误的返回结果
Performance  Affected
对实例性能产生影响

对于其它Bug影响程度描述请参考:
245840.1 Introduction to "BugDescription" Articles



2.   11.2.0.2版本的关键Bug筛选
2.1        11.2.0.2版本的基本情况
11.2.0.2发布于2010年9月,是11gR2第二个被大量采用的版本。


目前采用11.2.0.2版本,且未安装PSU补丁。就目前安装情况分析在11.2.0.2上有哪些软件BUG具有较高的危险性,并给出相关的介绍和建议。

PSU(PatchSet Updates)类型的补丁包是在两个PATCHSET之间以季度为周期发布的补丁,它一般包含了一些关键补丁和CPU补丁。通常建议用户周期性的应用PSU补丁来增加代码的代码的强壮性。

对于任何数据库版本而言,关键Bug以两种方式来区分:
1.     通用平台的关键Bug:即与特定的硬件通用平台的,所有相同的数据库版本均会受影响;
2.     平台相关的关键Bug:即与特定平台相关的环境,OS补丁有关的关键Bug,该Bug只在这个平台出现,而在其它平台不会触发;

接下来将按照两种分类来描述关键Bug。

2.2        11.2.0.2的关键Bug通用平台的关键Bug
参考Known Issue文档及RAT建议,目前该版本通用平台的关键Bug包含:
  
Bug/Doc
  
Description
Impact
Note:1561271.1P*
Solaris  / HP: ORA-29701 raised in ASM i/o path
I/O ERROR
Note:1527740.1*
ORA-600  [ktbsdp1] ORA-600 [kghfrempty:ds]. Corruption in Rollback with Clusterwide  Global Transactions in RAC
MEM/DATA/Dictionary损坏
Corrupt  Undo. ORA-600 [2015] during rollback in undo block for COMPRESS table with  SUPPLEMENTAL LOGGING
MEM/DATA/Dictionary损坏
AIX:  Excess "work USLA heap" process memory use in 11.2 on AIX
Memory Overhead
Memory  corruption / wrong results / ORA-7445 for particular type of chained row with  Exadata
MEM/DATA/Dictionary损坏 only on Exadata
PMON  block recovery loop - instance hang
HANG
ORA-600  [kcbzpbuf_1] block corruption in buffer cache for 32k block size / ORA-7445  [kdb4cpss] by cache protect
MEM/DATA/Dictionary损坏
ORA-7445  / LPX-200 / wrong results etc.. from new XML parser
WRONG RESULT
Stale  data blocks may be returned by Exadata FlashCache
WRONG RESULT only on Exadata
Wrong  results with outer join and CASE expression optimization (CASE need not to be  present) or LIKE operator
WRONG RESULT
Solaris:  Process spin and/or ASM and DB crash if RAC instance up for > 248 days
Instance Crash
Assorted  dumps and errors with function based indexes or virtual columns present
ERROR
Agent  gets timeout for check action against SVC, DB, DG, ASM resources / CRS-5818
ERROR
wrong  results / OERI:[kkooqb: bsj not used] with star transformation
WRONG RESULT
Wrong  results / ORA-7445 with DESC indexes and OR expansion
WRONG RESULT
Hang  / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool  subpools
MEM/DATA/Dictionary损坏
ORA-7445[kkslMarkLiteralBinds]  / Assorted Errors on 11.2.0.2 if cursor sharing is enabled - Affects RMAN
MEM/DATA/Dictionary损坏
Dictionary  corruption / ORA-959 due to DROP TABLESPACE
MEM/DATA/Dictionary损坏
ORA-600  [7999] [9] [1] [<lob block rdba>] / ORA-1555 double allocated LOB block
MEM/DATA/Dictionary损坏
Long  shared pool latch waits / instance crash in 11.2 with ORA-240 / ORA-15064
Instance Crash
ORA-600  / corruption possible during shutdown in RAC
MEM/DATA/Dictionary损坏
Block  Corruption with PDML UPDATE. ORA_600 [4511] OERI[kdblkcheckerror] by block  check
MEM/DATA/Dictionary损坏
Wrong  results when execution plan shows nested loop batching
WRONG RESULT
ORA-1578  / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM
MEM/DATA/Dictionary损坏
Dump  [under kxspoac] / ORA-1722 as SQL uses child with mismatched BIND metadata
ERROR




以下是各个Bug的详细描述:
Note: 1561271.1
  
影响程度
  
I/O 报错
有无补丁
Bug描述
仅仅发生在Solaris 或者 HP-ux平台,出现unrecoverable error ORA-29701、ORA-15032  raised in ASM I/O path; terminating process nnnnn
  
Workaround
减轻负载压力

Note:1527740.1*
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
Database  Block Corruption during ROLLBACK to <SAVEPOINT> when updating a RAC  database with Clusterwide Global Transactions (database link or XA  transaction).
Workaround
Set _clusterwide_global_transactions=false (at the RAC database)

Note:1191474.1*
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
Supplemental Logging for COMPRESSED table may  corrupt UNDO block.
  
  The affected UNDO block results in the  inability of tables from being read for transaction abort/recovery or  consistent-read.
  
  SMON may crash the database as it is unable to  rollback the transaction associated to the affected UNDO segment.
  
Workaround
1. Set _lmn_compression=FALSE to disable  supplemental logging on compress tables.
  
  2. Disable Supplemental Logging.
  
  3. Disable COMPRESSION :

Note:1260095.1
  
影响程度
  
Memory  Overhead
有无补丁
可用
Bug描述
仅仅在AIX平台上发生,每一个独立服务进程所消耗的内存对比9i,10g变多
  
Workaround
linking the  Oracle 11gR2 binary without -bexpful and -brtllib option.

BUG 14540423+
  
影响程度
  
MEM/DATA/Dictionary损坏 only on Exadata
有无补丁
可用
Bug描述
在Exadata上出现ORA-7445错误,或者查询结果集错误
  
Workaround
Turn  off cell offload
  
eg: Set CELL_OFFLOAD_PROCESSING = false
  

BUG 13645917+
  
影响程度
  
Hang(Process  Hang/Spins)
有无补丁
可用
Bug描述
由于PMON进程在线做block recover导致实例HANG
  
Workaround
shutdown  abort then startup
BUG 11799496+
  
影响程度
  
MEM/DATA/Dictionary损坏
有无补丁
可用
Bug描述
32k的表空间上会出现ORA-600 [kcbzpbuf_1]
  
数据块损坏
Workaround
Use smaller  block sizes such as 8k.
  
BUG 11666959+
  
Bug
  
影响程度
WRONG RESULT
有无补丁
可用
Bug描述
当解析XML时出现错误的结果集或者ORA-7445错误
  
Workaround
alter  session set events=
  
    '31156 trace name context forever, level  0x400';
  

Bug 10425010
  
影响程度
  
WRONG RESULT  only on Exadata
有无补丁
可用
Bug描述
Exadata  Flashcache上的陈旧数据可能被标记为有效,导致返回错误的结果集合
  
Workaround
  

Bug 10269193
  
影响程度
  
WRONG RESULT
有无补丁
可用
Bug描述
当存在以下情况时可能导致错误的结果集:
  
1.  Wrong Results OR an ORA-600 in Parallel Query
  
2. Outer Join
  
3. CASE Expression
  
4. ROWID column is involved in CASE  Expression
  
  
Workaround

Bug 10194190P+
  
影响程度
  
Instance  Crash
有无补丁
可用
Bug描述
仅仅在Solaris平台上发生,当实例运行超过248天,ASM和DB的进程可能spin导致实例CRASH
  
Workaround
Instance  needs to be restarted, but before doing that take pstack and truss
  
on the spinning process for diagnostic  purposes. This will help to determine
  
whether this is same issue or different one.
  
  
To proactively avoid this issue schedule  instance restarts to occur before
  
248 days of uptime.
  

Bug 9965278
  
影响程度
  
ERROR
有无补丁
可用
Bug描述
包含虚拟字段或者虚拟表达式字段的查询可能出现ORA-7445错误
  
Workaround
This  workaround should help in all cases:
  
  alter session set  "_replace_virtual_columns" = false;
  
  
These workarounds may work for specific  variants of the issue:
  
  alter session set  "_disable_function_based_index"=true;
  
  or
  
  alter session set  "_fix_control"="6754080:off";
  
  or
  
  alter session set  "_fix_control"="8706652:off";
  

Bug 11807012
  
影响程度
  
ERROR
有无补丁
可用
Bug描述
Agents gets  timeout for check action against SVC, DB, DG, ASM resources.
  
eg:
  
CRS-5818:Aborted command 'check for  resource: ora.REDO1.dg orac11g04 1'
  
  for resource 'ora.REDO1.dg'. Details at  (:CRSAGF00113:) {0:19:2} in
  
   /.../agent/crsd/oraagent_grid/oraagent_grid.log.
  
  
Rediscovery  Notes:
  
If Agents gets timeout for check action  against SVC, DB, DG, ASM resources
  
in 11.2.0.2 even though the is database is  healthy, you might be hitting
  
this issue.
  
If you can get call stack against agent  threads before check action aborts,
  
check if there are several threads waiting  for mutex via ConnectionPool class.
  
Example for such thread is
  
  #0   0xb7fd4402 in __kernel_vsyscall ()
  
  #1   0x00c43379 in __lll_lock_wait ()
  
  #2   0x00c3ea2f in _L_lock_885
  
  #3   0x00c3e8f6 in pthread_mutex_lock ()
  
  #4   0xb74b4a34 in sltsmna ()
  
  #5   0x081d3fc2 in ConnectionPool::releaseConnection
  
   :
  
  
Workaround
  

Bug 13384397
  
影响程度
  
WRONG RESULT
有无补丁
可用
Bug描述
星型转换时可能出现错误的结果集
  
Workaround
_optimizer_use_cbqt_star_transformation=false
  

Bug 10259620+
  
影响程度
  
WRONG RESULT
有无补丁
可用
Bug描述
Wrong results  may be produced by a query when the following
  
conditions  are all true for the query:
  
- A column is  included in a normal index and in a DESCending indexes
  
- The same  column is used in an OR predicate (or an INLIST)
  
-  OR-expansion is applied (CONCATENATE shown in Execution Plan)
  
  This is particularly likely for queries  using a USE_CONCAT hint
  
Workaround
Any of the  following can avoid this problem:
  
- alter session set "_fix_control"  ='8352378:OFF';
  
- alter session set  "_no_or_expansion" = true;
  
- alter session set  "_disable_function_based_index" = true;
  
- Hint the query not to use OR expansion, or  to avoid use of
  
   the function based index.

Bug 13550185
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
This problem  is introduced in 11.2.0.2.
  
SGA memory  corruption and/or ORA-7445[kglic0] can occur when the
  
shared pool  has multiple subpools. This is more likely to occur if literal replacement is  used
  
(CURSOR_SHARING=FORCE  or SIMILAR) but it can also occur without cursor sharing
  
explicitly  enabled as described in the rediscovery notes below.
  
Workaround
Use a single  shared pool subpool, but note that this can
  
  affect performance in highly concurrent  environments which
  
  have high shared pool load.
  
  eg: Add init parameter _kghdsidx_count =  1  and restart database.
  

Note:1472116.1
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
当cursor_sharing  = FORCE or SIMILAR时在11.2.0.2下RMAN可能报ORA-7445错误
  
Workaround
Possible  workaround is to flush the shared pool.
  
  
SQL> alter  system flush shared_pool;
  
  
This will not  work in all circumstances, but might allow you to connect with RMAN again.

Bug 1390632.1
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
If a segment  is created using Deferred Segment Creation and the related tablespace is  later dropped before the segment is populated with data, accessing the object  fails with errors ORA-00604 ORA-00959.
Workaround
To prevent  this problem from being introduced (if Patch 13326736 is not applied), always  attempt DROP TABLESPACE with INCLUDING CONTENTS AND DATAFILES clause, e.g.:
  
  
DROP  TABLESPACE TEST2 INCLUDING CONTENTS AND DATAFILES ;
  

Bug  11814891
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
ORA-600  [7999] [9] [1] [<lob block rdba>] / ORA-1555 caused by
  
a double  allocated LOB block after a LOB column UPDATE.
  
  
The second  argument [1] in the ORA-600 [7999] indicates ORA-1.
  
  
The problem  is introduced by an UPDATE producing an ORA-1551 (internal
  
error not  visible to user) of a LOB column based on a subquery. The
  
statement is  basically of the form:
Workaround

Bug 12830339
  
影响程度
  
Instance  Crash
有无补丁
可用
Bug描述
Long  shared pool latch waits may be seen in the DB
  
with  shared pool contention in 11.2.
  
  
If  the DB uses ASM the ASM may terminate the DB instance.
  
Workaround
  

Note 1318986.1
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
The issue  could happen under certain timing conditions during shutdown normal/  transactional/ immediate of RAC instances in a rolling fashion when the other  instances are still under normal workload. Note that this issue does not  happen with shutdown abort. Oracle ASM instance is not impacted by this  issue.
Workaround
1. Shutdown  database as a whole using abort option:
  
  
SQL> alter  system checkpoint;
  
srvctl stop  database -d <db_uniqueue_name> -o abort -f
  
The first  command writes out dirty buffers for all instances to minimize instance  recovery; The srvctl command shutdown all instances with abort option and its  dependent resources. Shutdown abort completely by-passes the vulnerable code  path of the bug.
  
  
2. If  shutdown instances in rolling fashion is needed, instead of shutdown  normal/transactional/immediate, one should shutdown each instance with the  following 2 commands:
  
  
  
SQL> alter  system checkpoint local;
  
SQL>  shutdown abort;
  
  
The first  command writes out dirty buffers for this instance to minimize instance  recovery. Shutdown abort completely by-passes the vulnerable code path of the  bug. Instance can also be shutdown abort using "srvctl stop instance -d  <db_unique_name> -i <instance_name> -o abort".




Note 9724970
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
A parallel  DML UPDATE may cause block corruption with several dumps or ORA-600
  
errors on a  large table if there are any row which has a NULL value in the
  
last-N  column(s).
  
  
If  db_block_checking is enabled, ORA-600 [kdBlkCheckError] or
  
ORA-600  [kddummy_blkchk] can be produced with different error codes;
  
e.g. 6135,  6110, 6255, etc.
Workaround
使用串行更新

Note 14668670+
  
影响程度
  
WRONG RESULT
有无补丁
可用
Bug描述
A query may  return wrong results if the execution plan shows Nested Loop Batching being  used, usually during the first execution.
  
  
Nested Loop  Batching is being used if the execution plan shows
  
access to a  table's index that is being joined using a nested loop to its table.
  
  
In the below  example, T5 is being accessed by Nested Loop (Batching) using the result of  I_T5.
  
The rest of  the tables are not using Nested Loop Batching.
  
Workaround
The best  workaround for this issue is to set  "_nlj_batching_misses_enabled"=0
  
  This can be set at session or system level  and will avoid the above bug scenario
  
  without impacting execution plans.
  
  
  Other possible workarounds are shown below  but these may alter execution plans:
  
  Set   _projection_pushdown=false
  
  or
  
  set _NLJ_BATCHING_ENABLED=0

Note 10209232+
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
Blocks can be  misplaced in ASM after using a wrong extent map to write blocks
  
during  rebalance.  The Blocks intended to  write may not be written.  
  
Those blocks  become stale blocks (Lost Write).
  
  
Misplaced  Blocks
  
================
  
The blocks  are written to wrong locations and those blocks become misplaced
  
with wrong  rdba producing ORA-1578 and dbverify may report the misplaced
  
blocks as  follow:
Workaround



Note 9735237
  
影响程度
  
ERROR
有无补丁
可用
Bug描述
It is  possible for a session to end up using a mismatched
  
child cursor  where the bind metadata of the child cursor
  
differs from  the actual binds used. This can lead to
  
a dump  (typically under kxspoac), unexpected errors, such
  
as ORA-1722,  as the bind data is interpreted as a different
  
type or in  the case of DMLs logical data corruption.
  
Workaround
For the  problem statement ensure that bind types and
  
  metadata are always the same wherever that  SQL is
  
  issued from. If bind types differ for calls  from different
  
  locations then alter the SQL to include  comment (or similar)
  
  so that the SQL text itself differs for the  different bind
  
  types used.


11.2.0.2与Soaris平台相关的Bug
Note: 1561271.1
  
影响程度
  
I/O 报错
有无补丁
Bug描述
仅仅发生在Solaris 或者 HP-ux平台,出现unrecoverable error ORA-29701、ORA-15032  raised in ASM I/O path; terminating process nnnnn
  
Workaround
减轻负载压力

Bug 10194190P+
  
影响程度
  
Instance  Crash
有无补丁
可用
Bug描述
仅仅在Solaris平台上发生,当实例运行超过248天,ASM和DB的进程可能spin导致实例CRASH
  
Workaround
Instance  needs to be restarted, but before doing that take pstack and truss
  
on the spinning process for diagnostic  purposes. This will help to determine
  
whether this is same issue or different one.
  
  
To proactively avoid this issue schedule  instance restarts to occur before
  
248 days of uptime.
  


2.3        11.2.0.2版本的补丁建议
针对11.2.0.2如果不想升级到最新的11.2.0.4+LatestPSU的话,建议考虑安装上述这些关键的BUG的一次性补丁one-offPatch,特别是可能导致Instance Crash、InstanceHang和Corruption数据块损坏的bug。

Instance Crash Bug列表:
10194190P+、12830339+

Instance Hang Bug列表:
13645917+、13550185+




3.   11.2.0.3版本的关键Bug筛选
3.1        11.2.0.3版本的基本情况

本系统采用版本11.2.0.3 且安装了PSU 补丁"Database Patch Set Update :11.2.0.3.3 (13923374)"和"Grid InfrastructurePatch Set Update : 11.2.0.3.3 (13919095)",我们在后续介绍11.2.0.3上的BUG时会排除那些已经在11.2.0.3.3 PSU上修复了的问题。

11.2.0.4是11gr2最新版本,第一次发布时间是2013年8月发布的。ORACLE 官方推荐用户升级到最新的PatchSet。


11.2.0.3是11gR2第二个PatchSet,第一次发布时间是2011年9月。 目前版本11.2.0.3仍是 ORACLE官方主要支持的版本。

  
Release
  
Platform
Release Date
Patch End
PSU
CPU
Bundle Patch
11.2.0.3
Solaris
29-OCT-2011
Jan-2015
Patch: 17478514
Jan 2014
N/A


一个数据库版本DeSupport就意味着:
l  如果是已知的Bug,已经有相应Patch的,仍然可以下载使用;
l  如果问题可能是新的Bug引发的,后台部门不再提供补丁开发支持;

对于任何数据库版本而言,关键Bug以两种方式来区分:
1.     通用平台的关键Bug:即与特定的硬件通用平台的,所有相同的数据库版本均会受影响;
2.     平台相关的关键Bug:即与特定平台相关的环境,OS补丁有关的关键Bug,该Bug只在这个平台出现,而在其它平台不会触发;

接下来将按照两种分类来描述关键Bug。
3.2        11.2.0.3的关键Bug
通用平台的关键Bug
参考Known Issue文档及RAT建议,目前该版本通用平台的关键Bug包含:
  
Bug/Doc
  
Description
Fixed in PSU/Bundle
Note:1260095.1P+
AIX: Excess "work USLA heap" process memory use in  11.2 on AIX
  
 AIX (ignore)
  
13605839+
ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds]. Corruption in  Rollback with Clusterwide Global Transactions in RAC
 12.1
14668670+
wrong results the first time a query is executed
11.2.0.3.BP14
13786142+
Cannot drop/replace trigger in editioning environment
11.2.0.3.BP07
13384397+
wrong results / OERI:[kkooqb: bsj not used] with star  transformation
11.2.0.3.BP05
13645917+
PMON block recovery loop - instance hang
11.2.0.3.5
13550185+
Hang / SGA memory corruption / ORA-7445 [kglic0] when using  multiple shared pool subpools
11.2.0.3.4
Solaris: Process spin and/or ASM and DB crash  if RAC instance up for > 248 days
Solaris / HP: ORA-29701 raised in ASM i/o path
11.2.0.3.GIPSU08
ORA-1206 in SOURCE database after RMAN  duplicate
11.2.0.3.BP21
wrong results / OERI:[kkooqb: bsj not used]  with star transformation
11.2.0.3.BP05
ORA-600 [kclchkblkdma_3] ORA-600 [3020] or  ORA-600 [kcbchg1_16] Join of temp and permanent table in RAC might lead to  corruption
11.2.0.3.9
ORA-600 [kclchkblkdma_3] ORA-600 [3020] RAC  diagnostic/fix to avoid a block being modified in Shared Mode and prevent  corruption
11.2.0.3.9
ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds].  Corruption in Rollback with Clusterwide Global Transactions in RAC
11.2.0.3.8
Wrong results when execution plan shows nested  loop batching
11.2.0.3.7

Bug 13605839
  
影响程度
  
Corruption  (Logical)
有无补丁
Bug描述
ORA-600  [ktbsdp1],  ORA-600 [kghfrempty:ds],  ORA-600 [4156] with ktcrsp1 on stack,
  
ORA-600  [kdBlkCheckError] with check codes [6101] or [6110] when
  
db_block_checking  is enabled.  
  
  
Corruption  during Rollback with Clusterwide Global Transactions when using
  
savepoints  and updating a RAC database through a database link.
Workaround
Set  _clusterwide_global_transactions=false (at the RAC database).
  
Setting this parameter to false does not  affect performance.


Bug 16299065
  
影响程度
  
Corruption (datafile)
有无补丁
Bug描述
当使用RMAN  duplicate命令时可能损坏数据文件头
Workaround
Restore  the affected datafile from backup
  



Bug 14668670
  
影响程度
  
Wrong Results
有无补丁
Bug描述
A query may  return wrong results if the execution plan shows Nested Loop Batching being  used.
  
  
Nested Loop  Batching is being used if the execution plan shows
  
access to a  table's index that is being joined using a nested loop to its table.
Workaround
_projection_pushdown=false
  
or
  
_NLJ_BATCHING_ENABLED=0


BUG 13645917+
  
影响程度
  
Hang(Process  Hang/Spins)
有无补丁
可用
Bug描述
由于PMON进程在线做block recover导致实例HANG
  
Workaround
shutdown  abort then startup

Bug 13550185  
  
影响程度
  
Performance  Affected
有无补丁
可用
Bug描述
当两个会话同时初始化某个游标结构时可能会造成SGA内存的损坏。往往这个游标具备以下特点:
  
1.      系统启用了游标共享即Cursor_sharing=similar/force;
  
2.      许多会话以不同的字符串来执行该游标;
  
3.      系统的共享池子池超过1;
  
Workaround
将共享池子池数设置为1,即alter system set  "_kghdsidx_count"=1 scope=spfile;
  

Bug 13384397
  
影响程度
  
Wrong Result
有无补丁
可用
Bug描述
使用星形转换时产生错误结果或内部错误。
Workaround
_optimizer_use_cbqt_star_transformation=false

Note: 1561271.1
  
影响程度
  
I/O 报错
有无补丁
Bug描述
仅仅发生在Solaris 或者 HP-ux平台,出现unrecoverable error ORA-29701、ORA-15032  raised in ASM I/O path; terminating process nnnnn
  
Workaround
减轻负载压力

Bug 10194190P+
  
影响程度
  
Instance  Crash
有无补丁
可用
Bug描述
仅仅在Solaris平台上发生,当实例运行超过248天,ASM和DB的进程可能spin导致实例CRASH
  
Workaround
Instance  needs to be restarted, but before doing that take pstack and truss
  
on the spinning process for diagnostic  purposes. This will help to determine
  
whether this is same issue or different one.
  
  
To proactively avoid this issue schedule  instance restarts to occur before
  
248 days of uptime.
  

Bug 13467683
  
影响程度
  
MEM/DATA/Dictionary损坏
有无补丁
可用
Bug描述
在RAC环境,在两个不同的实例中,相同的块可能被错误的修改,临时表和永久表作join时可能引起永久表损坏。
  
数据库在恢复时可能产生ORA-600 [3020]错误
Workaround


Bug 17761775
  
影响程度
  
MEM/DATA/Dictionary损坏
有无补丁
可用
Bug描述
this bug is only relevant when using Real Application Clusters (RAC)
  
Joining temporary and permanent tables in RAC might lead to  corruption causing
  
ORA-600 [kclchkblkdma_3] and/or ORA-600 [3020] during recovery.  If the diagnostic/fix
  
of bug 17752121 is in place, it may cause ORA-600 [kcbchg1_16]  preventing further
  
corruptions.
  
  
This bug may commonly affect INDEX blocks associated to the permanent  
  
TABLE rather than affecting the TABLE blocks but it also may affect  TABLE blocks.  
  
Workaround


Bug 17752121
  
影响程度
  
MEM/DATA/Dictionary损坏
有无补丁
可用
Bug描述
This bug is only relevant when using Real Application Clusters (RAC)
  
RAC specific diagnostic fix to avoid a block buffer being modified in  Shared Mode
  
instead of Exclusive mode and prevent further errors or corruptions  caused by stale
  
blocks in buffer cache.
  
  
The inconsistency/corruption can be also manifested by TABLE/INDEX  mismatch
  
reported with ORA-1499 by "analyze table validate structure  cascade" or ORA-8102.
Workaround

Note:1527740.1*
  
影响程度
  
Corruption(MEM/Block/Dictionary/Index)
有无补丁
可用
Bug描述
Database  Block Corruption during ROLLBACK to <SAVEPOINT> when updating a RAC  database with Clusterwide Global Transactions (database link or XA  transaction).
Workaround
Set _clusterwide_global_transactions=false (at the RAC database)



Note: 13786142
  
影响程度
  
ERROR
有无补丁
可用
Bug描述
if a  trigger fails creation on an editioning environment, the trigger will not
  
be  able to be dropped or replaced.
  
Attempts  to do so will raise ora-4045 and ora-4098.
  
Workaround




3.3        11.2.0.3版本的补丁建议
针对11.2.0.3如果不想升级到最新的11.2.0.4+Latest PSU的话,建议考虑安装上述这些关键的BUG的一次性补丁one-off Patch,特别是可能导致Instance Crash、Instance Hang和Corruption数据块损坏的bug。

目前已知的11.2.0.3上的Instance Crash Bug列表:
10194190P

目前已知的11.2.0.3上的Instance Hang Bug列表:
13645917 13550185

当然,RAT的补丁建议是基于现有的补丁库信息,如果在若干个月以后搭建11gRAC的话,就需要在搭建前重新获取这个版本的关键补丁分析,这样可以获取知道最新的补丁情况。
另外,11.2.0.3开始每个Patchset均为一个完整的软件发布包,而不是一个增量的修订包。数据库的升级基本是一个软件的重新安装过程。

4.    Bug分析及Patch建议总结
4.1        补丁冲突及解决建议
以上无论是通用平台的关键补丁,还是平台相关的关键补丁,在应用补丁前需要考虑以下情况:
l  是否与已应用的补丁冲突;
l  是否在多个关键补丁之间存在冲突;

考虑到10.2.0.3/11.2.0.2已经终止支持,如果存在以上冲突,则需要选择关键级别最高的补丁来应用,除非有其它Merge Patch可用。

有关补丁冲突检测的方法因为不在本次现场支持的范围内,详细情况请参考文档:
459360.1 How To Do ThePrerequisite/Conflicts Checks Using OUI(Oracle Universal Installer) And OpatchBefore Applying/Rolling Back A Patch


4.2        PSU补丁及安装步骤
从11.2.0.2开始,Oracle推出的PSU补丁成为大的PATCHSET之后最为常用的小补丁集,如果暂时不能从以上关键补丁中确认的,也可以通过应用每个版本最新的PSU补丁来增强代码强壮性。

各个版本的最新PSU补丁已经在上面的各部分列出来了,局方可以根据情况选择合适的PSU补丁。

补丁安装过程:在PSU补丁包里都有详细的安装说明,局方可以参考这些说明自行安装补丁,或者向ACS部门申请人天现场应用补丁。

下载专业ORACLE数据库恢复工具PRM-DUL  For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638  QQ: 47079569   
2#
发表于 2014-4-18 08:48:48
刘大,10g 的bug review 也可以贴下么?

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-17 19:26 , Processed in 0.066280 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569