ORA-1578 / ORA-26040&n…

  当使用rman恢复数据库后，发现读取某些表报ORA-1578 /ORA-26040的错误，原因及解决方法如下

 

 

ORA-26040 is rised from blocks which have been INVALIDATED dueto a NOLOGGING operation performed over the owning object(s).

When a segment is defined with the NOLOGGING attribute - OR - if aNOLOGGING/UNRECOVERABLE operation updates the segment, the onlineredo log file is updated with minimal information to invalidate theaffected blocks when a RECOVERY is later performed.
If the associated redo/archived log file is used to RECOVER thedata files, Oracle invalidates such blocks and the error ORA-26040along with error ORA-1578 are reported by SQL statements in thenext block reads.

The data inside the affected blocks is not salvageable. Methodslike "Media Recovery" or "RMAN blockrecover" will not fix theproblem unless the data file was backed up after the NOLOGGINGoperation was registered in the Redo Log.

In order to resolve the errors and if it is not an INDEX thesegment can be recovered from a backup like an export dump or fromanother source. If backups are not available the segment might berecreated 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 erroryou may either shrink it's size or move to relocate within sametablespace or even rename the bad_table and create table as selectto build a new one.
If it is a TABLE which reports error then procedureDBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corruptblock in SQL statements and decide to re-create the table.Note 556733.1 has an example ofDBMS_REPAIR.
If it is a LOB segment associated to a LOB column in a Table, useNote 293515.1
If the error is produced in a Physical STANDBY database, the optionis to restore the affected file from the PRIMARY database (only ifthe problem is not present in the PRIMARY).

. How to Avoid:
---------------
.- Ensuring backup of affected datafiles after each NOLOGGEDoperations 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 SYSobjects)

 

 

 

 

 

 

 

 

 

 

 

 

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Errorexplanation and solution (文档 ID 794505.1)

转到底部

---

In thisDocument

PurposeScopeDetails RMAN/DBV and Corrupt Blocks by NOLOGGING Monitoring NOLOGGING Operations Identify when a block was marked asNOLOGGING 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 acorruption caused by a NOLOGGING operation and how to fix theerrors.

Scope

This document is intended for Customers and Oracle Support.

Details

When a segment is defined with the NOLOGGING attribute and if aNOLOGGING/UNRECOVERABLE operation updates thesegment, the online redo log file is updated with minimalinformation to invalidate the affected blocks when a RECOVERY islater performed.

If the associated redo/archived log file is used to RECOVER thedata files, Oracle invalidates such blocks and the error ORA-26040along with error ORA-1578 are reported by SQL statements in thenext 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 datadictionary 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 nextblock 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 lowerthan 10.2.0.4 and error DBV-201 in RDBMS versions greater or equalto 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 redoapplication

In rdbms versions lower than 10.2.0.5 and11.1.0.7,  RMAN validate reports it with a genericmessage like:

10.2.0.4 and lower, 11.1.0.6,11.1.0.7:

RMAN validate reports it inv$database_block_corruption withCORRUPTION_TYPE=LOGICAL

In rdbms version 10.2.0.5 or in 11.2.0.1 and forward, RMAN hasbeen enhanced to report it in with CORRUPTION_TYPE=NOLOGGING.Reference Doc ID 7396077.8 :

10.2.0.5 and11.2.0.1+:

RMAN validate reports it in v$database_block_corruptionwith CORRUPTION_TYPE=NOLOGGING

In rdbms version 12c and forward RMAN validate no longerpopulates view v$database_block_corruption; instead the newview v$nonlogged_block is updated:

12c:

RMAN validate reports it in v$nonlogged_block

 

RMAN backups do not fail due to NOLOGGING corrupt blocks. Ingeneral RMAN does not fails with soft corrupt blocks so theMAXCORRUPT clause is not necessary in such cases. In such cases the backup will contain the soft corrupt block and arestore will leave the corruption as when the backup was made.

When there is a generic message besides the error ORA-26040, ablock dump might be taken and see if the byte 0xff is along theblock or if the block is associated to a segment, try to read itwith a SQL statement for which errors ORA-1578/ORA-26040 will beproduced as the block is corrupt due to recovery with a NOLOGGINGoperation.

Monitoring NOLOGGING Operations

V$DATAFILE has several columns that are updated when a NOLOGGINGoperation takes place when parameter db_unrecoverable_scn_tracking is set to true (default value);db_unrecoverable_scn_tracking is not available in10g.  Reference the next V$DATAFILE columns in ourOracle Database Reference Documentation:

UNRECOVERABLE_CHANGE#    
UNRECOVERABLE_TIME   
FIRST_NONLOGGED_SCN
FIRST_NONLOGGED_TIME

Identify when a block was marked asNOLOGGING

To identify when a block was marked as NOLOGGING, use the blockscn 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 atrace file

Example from trace file:

  Start dump data blocks tsn: 60 file#: 4 minblk84 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 todecimal which is 511453045995

Use the value in columnCORRUPTION_CHANGE# in v$database_block_coruption

If RMAN validate is run the view v$database_block_coruption ispopulated with corruption_type='NOLOGGING' (10.2.0.5 and 11.2.0.1+)and column CORRUPTION_CHANGE# has the decimal scn value.

Get the SCNTimestamp

To get the timestamp use any of these methods:

selectscn_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 orgv$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# andnext_change#;

OR

select first_time
from   gv$log_history
where  &decimal_scn between first_change# andnext_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# andnext_change#;

OR

select file#, block#, first_time
from v$nonlogged_block, v$log_history
where NONLOGGED_START_CHANGE# between first_change# andnext_change#;

NOARCHIVELOG and NOLOGGING in SYSAUX tablespace / AWR,EM, etc

If the database is running on rdbms versions 11.1.0.6 or11.1.0.7 or 11.2.0.1, ORA-1578 and ORA-26040 can be produced due toNOLOGGING for DIRECT PATH operations after a manual RECOVERDATABASE in a NOARCHIVELOG mode database even if FORCE LOGGING isenabled in the database or even if LOGGING is defined for thesegment.

This is most commonly seen in AWR or EM objects (EnterpriseManager) 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 than11.1 or 11.2.0.1 but the problem could be introduced before upgradefrom one of those affected versions.

The restriction has been lifted in 11.2.0.2+ and this issue didnot occur in 10g.

 

Changes by RDBMS versions

RDBMS VersionChange10.2.0.4+DBverify reports a NOLOGGING block with error"DBV-00201: Block, DBA , marked corrupt forinvalid redo application"10.2.0.5, 10.2.0.1+RMAN validate reports the NOLOGGING block inv$database_block_coruption withcorruption_type='NOLOGGING'11g+Parameter db_unrecoverable_scn_tracking isintroduced. 11.1.0.6 or 11.1.0.7 or 11.2.0.1ORA-1578 and ORA-26040 can be produced due to NOLOGGING for DIRECTPATH operations after a manual RECOVER DATABASE in a NOARCHIVELOGmode database evenif FORCE LOGGING is enabled.  Therestriction has been lifted in 11.2.0.2+ and problem did not happenin 10g.12cRMAN validate no longer populates view v$database_block_corruption;instead the new view v$nonlogged_block isupdated

 

 

SOLUTION

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

Is error after RMAN DUPLICATE?

If the error is after a RMANDUPLICATE or RESTORE, enable FORCE LOGGING at SOURCEdatabase 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 aPHYSICAL STANDBY database, the option is torestore the affected file from the PRIMARY database (only if theproblem is not present in the PRIMARY) and to avoid the problemfrom being introduced there is the option to force logging in thePRIMARY database with:

alter database force logging;

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

Identify the affected segment

Identify the affectedsegment  as described in Doc ID 819533.1 or identify all the corrupt objects asdescribed in Doc ID 472231.1, then:

Is it a FREE Block?

If the NOLOGGING Block is aFREE Block (the associated extent is indba_free_space), which could be discovered by running DBVerify witherror DBV-00201 or shown in view v$database_block_corruption, thereis the option to wait until the block is reused which willautomatically re-format the block or force re-formatting the blockusing 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 thecorrupt 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, CreateTable as Select, etc) and then truncate or drop/create.

Is it a LOB?

If it is a LOB useDoc ID 293515.1