ORA-1578 / ORA-26040&n…

当使用rman恢复数据库后，发现读取某些表报ORA-1578 / ORA-26040的错误，原因及解决方法如下     ORA-26040 is rised from blocks which have been INVALIDATED due to a NOLOGGING operation performed over the owning object(s). When a segment is defined with the NOLOGGING attribute - OR - 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. 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. 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. If it is an INDEX, drop/create the index. If it is a TABLE and you may select full contents w/out any error you may either shrink it's size or move to relocate within same tablespace or even rename the bad_table and create table as select to build a new one. If it is a TABLE which reports error 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). . How to Avoid: --------------- .- Ensuring backup of affected datafiles after each NOLOGGED operations is performed to avoid having them recovered .- Check V$Datafile for UNRECOVERABLE_CHANGE# / UNRECOVERABLE_TIME .- Setting Database into FORCE LOGGING mode (which is true for SYS objects)                         ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (文档 ID 794505.1)

转到底部

---

In this Document Purpose Scope Details   RMAN/DBV and Corrupt Blocks by NOLOGGING   Monitoring NOLOGGING Operations   Identify when a block was marked as NOLOGGING   NOARCHIVELOG and NOLOGGING in SYSAUX tablespace / AWR, EM, etc   Changes by RDBMS versions   SOLUTION   Is error after RMAN DUPLICATE?   Is error produced in a PHYSICAL STANDBY Database?   Identify the affected segment   Is it a FREE Block?   Is it an INDEX?   Is it a TABLE?   Is it a LOB? References Applies to: Oracle Database - Enterprise Edition - Version 7.1.6.0 to 12.1.0.2 [Release 7.1.6 to 12.1] Information in this document applies to any platform. Purpose This note is intended to describe how Oracle reports a corruption caused by a NOLOGGING operation and how to fix the errors. Scope This document is intended for Customers and Oracle Support. Details 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. 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. Errors Example: SQL> select * from test_nologging; ORA-01578: ORACLE data block corrupted (file # 11, block # 84) ORA-01110: data file 4: '/oradata/users.dbf' ORA-26040: Data block was loaded using the NOLOGGING option The NOLOGGING attribute is stored in column LOGGING in data dictionary views like: DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc. LOGGING='NO' indicates NOLOGGING.   The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.     RMAN/DBV and Corrupt Blocks by NOLOGGING 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  Doc ID 5031712.8: DBV-00200: Block, dba 46137428, already marked corrupted DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application In rdbms versions lower than 10.2.0.5 and 11.1.0.7,  RMAN validate reports it with a generic message like: 10.2.0.4 and lower, 11.1.0.6, 11.1.0.7: RMAN validate reports it in v$database_block_corruption with CORRUPTION_TYPE=LOGICAL 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. Reference Doc ID 7396077.8 : 10.2.0.5 and 11.2.0.1+: RMAN validate reports it in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING In rdbms version 12c and forward RMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated: 12c: RMAN validate reports it in v$nonlogged_block   RMAN backups do not 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.  In such cases the backup will contain the soft corrupt block and a restore will leave the corruption as when the backup was made. 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 as the block is corrupt due to recovery with a NOLOGGING operation. Monitoring NOLOGGING Operations V$DATAFILE has several columns that are updated when a NOLOGGING operation takes place when parameter db_unrecoverable_scn_tracking is set to true (default value); db_unrecoverable_scn_tracking is not available in 10g.  Reference the next V$DATAFILE columns in our Oracle Database Reference Documentation: UNRECOVERABLE_CHANGE#       UNRECOVERABLE_TIME       FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME Identify when a block was marked as NOLOGGING To identify when a block was marked as NOLOGGING, use the block scn in the trace file or use the value in column CORRUPTION_CHANGE# in v$database_block_coruption to translate it to a timestamp: Use the block scn from a trace file Example from trace file:   Start dump data blocks tsn: 60 file#: 4 minblk 84 maxblk 84   buffer tsn: 3 rdba: 0x02c00054 (11/84)   scn: 0x0771.4fa24eb5 seq: 0xff flg: 0x04 tail: 0x4eb500ff Take 0x0771.4fa24eb5, remove the '.' and convert 0x07714fa24eb to decimal which is 511453045995 Use the value in column CORRUPTION_CHANGE# in v$database_block_coruption If RMAN validate is run the view v$database_block_coruption is populated with corruption_type='NOLOGGING' (10.2.0.5 and 11.2.0.1+) and column CORRUPTION_CHANGE# has the decimal scn value. Get the SCN Timestamp To get the timestamp use any of these methods: select scn_to_timestamp(&&decimal_scn) from dual; If RMAN validate was run: select file#, block#, scn_to_timestamp(CORRUPTION_CHANGE#) from v$database_block_corruption where CORRUPTION_TYPE='NOLOGGING'; In 12c: select file#, block#, scn_to_timestamp(NONLOGGED_START_CHANGE#) from v$nonlogged_block; If error ORA-08181 is produced query gv$archived_log or gv$log_history to get a time interval approximation: alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'; select first_time, next_time from   gv$archived_log where  &decimal_scn between first_change# and next_change#; OR select first_time from   gv$log_history where  &decimal_scn between first_change# and next_change#; if RMAN validate was run: alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'; select file#, block#, first_time, next_time from   v$archived_log, v$database_block_corruption where  CORRUPTION_CHANGE# between first_change# and next_change#   and CORRUPTION_TYPE='NOLOGGING'; OR select file#, block#, first_time from   v$log_history, v$database_block_corruption where  CORRUPTION_CHANGE# between first_change# and next_change#   and CORRUPTION_TYPE='NOLOGGING'; In 12c: alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS'; select file#, block#, first_time, next_time from v$nonlogged_block, v$archived_log where NONLOGGED_START_CHANGE# between first_change# and next_change#; OR select file#, block#, first_time from v$nonlogged_block, v$log_history where NONLOGGED_START_CHANGE# between first_change# and next_change#; NOARCHIVELOG and NOLOGGING in SYSAUX tablespace / AWR, EM, etc If the database is running on rdbms versions 11.1.0.6 or 11.1.0.7 or 11.2.0.1, ORA-1578 and ORA-26040 can be produced due to NOLOGGING for DIRECT PATH operations after a manual RECOVER DATABASE in a NOARCHIVELOG mode database even if FORCE LOGGING is enabled in the database or even if LOGGING is defined for the segment. This is most commonly seen in AWR or EM objects (Enterprise Manager) stored in the SYSAUX tablespace. Reference Doc Id 1071869.1 for details and solution.  Note that the database may currently be in a version greater than 11.1 or 11.2.0.1 but the problem could be introduced before upgrade from one of those affected versions. The restriction has been lifted in 11.2.0.2+ and this issue did not occur in 10g.   Changes by RDBMS versions RDBMS Version Change 10.2.0.4+ DBverify reports a NOLOGGING block with error "DBV-00201: Block, DBA , marked corrupt for invalid redo application" 10.2.0.5, 10.2.0.1+ RMAN validate reports the NOLOGGING block in v$database_block_coruption with corruption_type='NOLOGGING' 11g+ Parameter db_unrecoverable_scn_tracking is introduced.  11.1.0.6 or 11.1.0.7 or 11.2.0.1 ORA-1578 and ORA-26040 can be produced due to NOLOGGING for DIRECT PATH operations after a manual RECOVER DATABASE in a NOARCHIVELOG mode database even if FORCE LOGGING is enabled.  The restriction has been lifted in 11.2.0.2+ and problem did not happen in 10g. 12c RMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated     SOLUTION 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. Is error after RMAN DUPLICATE? If the error is after a RMAN DUPLICATE or RESTORE, enable FORCE LOGGING at SOURCE database and perform the DUPLICATE or RESTORE (after new BACKUP) steps again: alter database force logging; Is error produced in a PHYSICAL STANDBY Database? 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) and to avoid the problem from being introduced there is the option to force logging in the PRIMARY database with: alter database force logging; 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 affected segment Identify the affected segment  as described in Doc ID 819533.1 or identify all the corrupt objects as described in Doc ID 472231.1, then: Is it a FREE Block? If the NOLOGGING Block is a FREE Block (the associated extent is in dba_free_space), which could be discovered by running DBVerify with error DBV-00201 or shown in view v$database_block_corruption, there is the option to wait until the block is reused which will automatically re-format the block or force re-formatting the block using Doc ID 336133.1 Is it an INDEX? If it is an INDEX, drop and create the index Is it a TABLE? If it is a TABLE, procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements; Doc ID 556733.1 has a DBMS_REPAIR example.  Then decide to re-create the segment: by moving the table: alter table &table_name move;  OR by saving the data (export, Create Table as Select, etc) and then truncate or drop/create. Is it a LOB? If it is a LOB use Doc ID 293515.1