ITPub博客

首页 > 数据库 > Oracle > Solving UNDO Corruption (文档 ID 1950230.1)

Solving UNDO Corruption (文档 ID 1950230.1)

原创 Oracle 作者:desert_xu 时间:2015-12-09 16:38:31 0 删除 编辑
 
收藏已添加 隐藏
单击此项可从收藏夹中删除 客户建议Solving UNDO Corruption (文档 ID 1950230.1) 转到底部转到底部

In this Document

Purpose
Troubleshooting Steps
  List of errors
  Scenarios
  How to check if there are pending transactions:
  Solution:
  Block corruption
  No pending transactions
  There are pending transactions:


APPLIES TO:

Oracle Database Products
Information in this document applies to any platform.

PURPOSE

Help in fixing an UNDO corruption depending the kind of corruptions.

TROUBLESHOOTING STEPS

List of errors

  1. Block Corruption ORA-01578 belonging to UNDO Segment
  2. ORA-00600 [4193] , ORA-00600 [4194], ORA-00600 [4037], etc...
    these errors raises normally together with:
    Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.

Scenarios

There are several scenarios:

  • Scenario 1: There are no pendings transactions.
  • Scenario 2: There are pending transactions with the database up
  • Scenario 3: There are pending transactions with database crash

How to check if there are pending transactions:

To check pending transacions, the following query can be executed

Select u.inst#            instid    ,
       u.name             useg      ,
       u.status$          status    ,
       x.ktuxeusn         usn       ,
       x.ktuxeslt         slt       ,
       x.ktuxesqn         wrp       ,
       x.ktuxesiz         undoblocks
From   x$ktuxe            x,
       undo$              u
Where  x.ktuxeusn = u.us#
And    x.ktuxesta = 'ACTIVE'
And    x.ktuxecfl like '%DEAD%' ;

if the DB must be opened to work.

Solution:

The solutions will depends of the situation of our backups and if there are pending transactions or not.

Block corruption

The best option in this case is to do a RMAN block Recover or Restore-Recover
If there is not any backup, then use the solutions "No pending transactions" or "With pending transactions" deppending the situation.

No pending transactions

Drop and recreate the undo tablespace. Note: 431652.1 can help in that

There are pending transactions:

If the error comes together with the message

Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.

 in this case, we need to recreate the object reported, and that should fix the issue unless we have more objects affected.

There are different cases:

  • Error 376 encountered while recovering transaction (A, B) on object YYYY.
    (A, B) shows the UNDO segment id and slot number (USN , SLOT)
    YYYY   shows the object id (NOT the data object id)
  • Error 600 encountered while recovering transaction (A, B) on object YYYY.
    (A, B) shows the UNDO segment id and slot number (USN , SLOT)
    YYYY   shows the object id (NOT the data object id) 

This error can raise without reference to an object_id, but affecting directly to the UNDO segment.

  • Error XXXX encountered while recovering transaction (A, B).
    Note this error has no "on object ...." clause in the error.
    This is reported to the alert log when error XXXX is encountered on a UNDO SEGMENT Block.
     XXXX    is the ORA-XXXX error encountered
    (A, B)  shows the rollback segment id and slot number (USN , SLOT) of the transaction being recovered.

    In this case, the best option is to RESTORE -RECOVER or RMAN Block Recover. If this is not possible, then continue with the following point

If the database is down and can't be started, then do the following:

  1. STARTUP MOUNT;
  2. Set the following parameter
    alter system set "_smu_debug_mode" = 1024
    this is a temporary action, so must be unset after finish the process of attempt to fix the issue
  3. Open the DB
    Alter database open;
  4. If there is any object_id reported in the alert.log, please find it an recreate if possible
  5. If there is not any object reported, try to recreate the UNDO tablespace (if there are pending transactions ORACLE will not allow you to recreate it)
  6. Take out the parameter set
    alter system set "_smu_debug_mode" =

If after doing that the DB can't be started, please open a SR with Support as the situation will need more analysis.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21980353/viewspace-1869158/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-10-23

  • 博文量
    79
  • 访问量
    257107