Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

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

81

积分

0

好友

4

主题
发表于 2012-3-6 16:03:49 | 查看: 8888| 回复: 4
Oracle: RAC 10.2.0.5, 生产库
OS: AIX
症状:alertlog显示如下
Mon Mar 05 22:02:32 GMT+08:00 2012GATHER_STATS_JOB encountered errors.  Check the trace file.
Mon Mar 05 22:02:32 GMT+08:00 2012Errors in file /u001/app/oracle/admin/justin/bdump/justin1_j000_27525252.trc:
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/CMX_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
其中trace文件大致为
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
*** 2012-03-05 22:00:16.032
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
*** 2012-03-05 22:02:32.085
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/C_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
*** 2012-03-05 22:02:32.103
GATHER_STATS_JOB: GATHER_TABLE_STATS('"JUSTIN"','"C_SEJ"','""', ...)
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/CMX_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

查看v$database_block_corruption,返回3000多行且corruption_type为unknown

MOS上有相关文档ID 794505.1

运行
select distinct e.owner,e.segment_type,e.segment_name from dba_extents e, v$database_block_corruption c
where c.file# = e.file_id
and c.block# between e.block_id and e.block_id + e.blocks;
返回2000多行,且全为index

请问Maclean,遇到此种情况,除了将index rebuild online外,有无更好的解决方案
谢谢
发表于 2012-3-6 21:22:49
ODM Finding:
  1. ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution [ID 794505.1]

  2. Applies to:
  3. Oracle Server - Enterprise Edition - Version: 7.1.6.0 to 11.2.0.2 - Release: 7.1.6 to 11.2
  4. Oracle Server - Enterprise Edition - Version: 7.1.6.0 to 11.2.0.2   [Release: 7.1.6 to 11.2]
  5. Information in this document applies to any platform.
  6. Purpose
  7. This note is intended to describe how Oracle reports a corruption caused by a NOLOGGING operation and how to fix the errors.
  8. Scope and Application
  9. This document is intended for Customers and Oracle Support.
  10. ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

  11. When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

  12. If the associated redo/archived log file is used to RECOVER the data files, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads.

  13. Errors Example:
  14. SQL> select * from test_nologging;

  15. ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
  16. ORA-01110: data file 4: '/oradata/users.dbf'
  17. ORA-26040: Data block was loaded using the NOLOGGING option


  18. The NOLOGGING attribute is stored in column LOGGING in data dictionary views like:

  19. DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

  20. LOGGING='NO' indicates NOLOGGING.


  21. The way for Oracle to identify that the block was previously invalidated due to NOLOGGING is by updating most of the bytes in that block with 0xff only if that "invalidate" redo is applied in a Recovery.

  22. The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.



  23. The SCN in the block corresponds to the SCN in the REDO RECORD for when the "INVALIDATE" change was applied in a recovery. This is useful to know the timestamp for when the block was marked as soft corrupt due to NOLOGGING.
  24. RMAN/DBV and Corrupted Blocks by NOLOGGING

  25. DBV prints the generic message DBV-200 in rdbms versions lower than 10.2.0.4 and error DBV-201 in RDBMS versions greater or equal to 10.2.0.4  ( Note  5031712.8 ):
  26. DBV-00200: Block, dba 46137428, already marked corrupted
  27. DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

  28. In rdbms versions lower than 10.2.0.5 and 11.1.0.7,  RMAN reports it with a generic message like:

  29. 10.2.0.4 and lower, 11.1.0.6, 11.1.0.7:

  30. RMAN reports it in v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

  31. In rdbms version 10.2.0.5 or in 11.2.0.1 and forward, RMAN has been enhanced to report it in with CORRUPTION_TYPE=NOLOGGING. See Note 7396077.8 :
  32. 10.2.0.5 and 11.2.0.1+:

  33. RMAN reports it in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

  34. RMAN backups don't fail due to NOLOGGING corrupt blocks. In general RMAN does not fails with soft corrupt blocks so the MAXCORRUPT clause is not necessary in such cases.

  35. When there is a generic message besides the error ORA-26040, a block dump might be taken and see if the byte 0xff is along the block or if the block is associated to a segment, try to read it with a SQL statement for which errors ORA-1578/ORA-26040 will be produced if the block is corrupt due to a recovery with a NOLOGGING operation.
  36. Important change in 11.1

  37. FORCE LOGGING is irrelevant in NOARCHIVELOG mode; this was a change introduced in 11.1.0.6 and 11.1.0.7.  This restriction has been lifted in 11.2.0.2.

  38. Reference Note 1071869.1

  39. SOLUTION


  40. Note that the data inside the affected blocks is not salvageable. Methods like "Media Recovery" or "RMAN blockrecover" will not fix the problem unless the data file was backed up after the NOLOGGING operation was registered in the Redo Log.

  41. In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

  42.     Identify the object as described in Note 819533.1

  43.     If it is an INDEX, drop/create the index.

  44.     If it is a TABLE then procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements and decide to re-create the table. Note 556733.1 has an example of DBMS_REPAIR.

  45.     If it is a LOB segment associated to a LOB column in a Table, use Note 293515.1

  46.     If the error is produced in a Physical STANDBY database, the option is to restore the affected file from the PRIMARY database (only if the problem is not present in the PRIMARY).

  47. Run script provided in Note 472231.1 to identify any additional corrupted objects.
  48. References
  49. NOTE:1071869.1 - ORA-1578 ORA-26040 in 11g for DIRECT PATH with NOARCHIVELOG even if LOGGING is enabled
  50. NOTE:290161.1 - The Gains and Pains of Nologging Operations
  51. NOTE:293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
  52. NOTE:472231.1 - How to identify all the Corrupted Objects in the Database reported with RMAN
  53. NOTE:556733.1 - DBMS_REPAIR SCRIPT
  54. NOTE:7396077.8 - Bug 7396077 - RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
  55. NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
复制代码

回复 显示全部楼层 道具 举报

发表于 2012-3-6 21:31:34
Note that the data inside the affected blocks is not salvageable. Methods like "Media Recovery" or "RMAN blockrecover" will not fix the problem unless the data file was backed up after the NOLOGGING operation was registered in the Redo Log.

In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

    Identify the object as described in Note 819533.1

    If it is an INDEX, drop/create the index.

    If it is a TABLE then procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements and decide to re-create the table. Note 556733.1 has an example of DBMS_REPAIR.

    If it is a LOB segment associated to a LOB column in a Table, use Note 293515.1

    If the error is produced in a Physical STANDBY database, the option is to restore the affected file from the PRIMARY database (only if the problem is not present in the PRIMARY).

Run script provided in Note 472231.1 to identify any additional corrupted objects.




1. 因为涉及到NOLOGGING的segment , 所以物理备份恢复的方法是无效的

2.  如果仅仅涉及到 INDEX的话 , 建议还是drop后重建

利用一下查询语句定位 坏块和 具体的segment:
  1. SELECT e.owner,
  2.        e.segment_type,
  3.        e.segment_name,
  4.        e.partition_name,
  5.        c.file#,
  6.        greatest(e.block_id, c.block#) corr_start_block#,
  7.        least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
  8.        least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
  9.        greatest(e.block_id, c.block#) + 1 blocks_corrupted,
  10.        null description
  11.   FROM dba_extents e, v$database_block_corruption c
  12. WHERE e.file_id = c.file#
  13.    AND e.block_id <= c.block# + c.blocks - 1
  14.    AND e.block_id + e.blocks - 1 >= c.block#
  15. UNION
  16. SELECT s.owner,
  17.        s.segment_type,
  18.        s.segment_name,
  19.        s.partition_name,
  20.        c.file#,
  21.        header_block corr_start_block#,
  22.        header_block corr_end_block#,
  23.        1 blocks_corrupted,
  24.        'Segment Header' description
  25.   FROM dba_segments s, v$database_block_corruption c
  26. WHERE s.header_file = c.file#
  27.    AND s.header_block between c.block# and c.block# + c.blocks - 1
  28. UNION
  29. SELECT null owner,
  30.        null segment_type,
  31.        null segment_name,
  32.        null partition_name,
  33.        c.file#,
  34.        greatest(f.block_id, c.block#) corr_start_block#,
  35.        least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
  36.        least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
  37.        greatest(f.block_id, c.block#) + 1 blocks_corrupted,
  38.        'Free Block' description
  39.   FROM dba_free_space f, v$database_block_corruption c
  40. WHERE f.file_id = c.file#
  41.    AND f.block_id <= c.block# + c.blocks - 1
  42.    AND f.block_id + f.blocks - 1 >= c.block#
  43. order by file#, corr_start_block#;
复制代码
3. 重建索引可以 参考这个Note 加速索引重建 http://www.oracledatabase12g.com ... create-rebuild.html

回复 显示全部楼层 道具 举报

发表于 2012-3-6 22:22:19
谢谢回复

看样子只能重建了

不过可不可以直接rebuilid online,drop后再重建可不是一般的麻烦,2000多个索引啊。。。

回复 显示全部楼层 道具 举报

发表于 2012-3-6 22:29:59
If the corrupt object is a user INDEX you can simply drop and
  re-create it PROVIDED the underlying table is not also corrupt.
  If the underlying table is also corrupt it is advisable to sort out
  the TABLE before recreating any indexes.

  If the information collected shows that the index has dependent FOREIGN
  KEY constraints then you will need to do something like this:

    - ALTER TABLE <child_table> DISABLE CONSTRAINT <fk_constraint>;
      for each foreign key

    - Rebuild the primary key using
        ALTER TABLE <table> DISABLE CONSTRAINT <pk_constraint>;
        DROP INDEX <index_name>;
        CREATE INDEX <index_name> .. with appropriate storage clause
        ALTER TABLE <table> ENABLE CONSTRAINT <pk_constraint>;

    - Enable the foreign key constraints
        ALTER TABLE <child_table> ENABLE CONSTRAINT <fk_constraint>;

  For an index partition you can:
    ALTER INDEX ... REBUILD PARTITION ...;

  Notes:
    (1) It is important not to REBUILD a non-partitioned corrupt index
        using an "ALTER INDEX ..  REBUILD" command as this will usually
        try to build the new index from the existing index segment,
          which contains a corrupt block.
        "ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD
        PARTITION ..." do not build the new index from the old index
        segment and so can be used.

    (2) Create INDEX can use the data from an existing index
        if the new index is a sub-set of the columns in the
        existing index. Hence if you have 2 corrupt indexes drop
        them BOTH before re-creating them.

    (3) Be sure to use the correct storage details when recreating indexes.





"ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD
        PARTITION ..." do not build the new index from the old index
        segment and so can be used.


rebuild online build index from table ,所以在这里可以用。 反而简单的"rebuild"因为 build new index from old index 所以不能用。

对于以上这种 ORA-1578 物理讹误, rebuild online 往往是有效的。 如果是逻辑讹误的话,必须drop 后recreate

回复 显示全部楼层 道具 举报

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

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2019-11-15 06:05 , Processed in 0.053063 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

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