ANBOB™

The following errors have recently occurred in our database( Oracle 19c RAC):

ORA-01578: ORACLE data block corrupted (file # xxx, block # yyyyy)
ORA-01110: data file xxx: 'xxxxxxxxxxxxxxxxx.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

There are many possible causes of a block corruption including:
1. Bad IO hardware / firmware
2. OS problems
3. Oracle Bugs
4. Recovering through “UNRECOVERABLE” or “NOLOGGING” database actions

When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment or if datapump import parameter disable_archive_logging:y is used, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed.
In Oracle8i and greater an ORA-26040 is also signalled (“ORA-26040: Data block was loaded using the NOLOGGING option” ) which makes the cause fairly obvious, but earlier releases have no additional error message.

“NOLOGGING ” It almost equals to “No Recover” in terms of data protection, even though your database is in ARCHIVELOG mode. So don’t expect the corrupted data is salvageable.Since there’s no way to recover the corrupted data file, so we need to treat the object differently. First, we have to know what type and object was affected.

SQL> select * from dba_extents where file_id = xxx and yyyyy  between block_id and block_id + blocks - 1;

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';

#In 12c:
select file#, block#, first_time, next_time
from v$nonlogged_block, v$archived_log
where NONLOGGED_START_CHANGE# between first_change# and next_change#;

The “VALIDATE” RMAN command is used to identify NOLOGGING blocks and populates the view v$database_block_corruption (versions lower than 12c) and v$nonlogged_block (12c and greater).

In 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:

The VALIDATE RMAN command reports the NOLOGGING blocks v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

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

The VALIDATE RMAN command reports the NOLOGGING blocks in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

In version 12c and forward RMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated:

RMAN backups do not fail due to NOLOGGING corrupt blocks. In general RMAN does not fails with soft corrupt blocks; the MAXCORRUPT clause is not necessary in such cases. The backup will contain the soft corrupt block and a restore will leave the corruption as when the backup was made.

How to fixed?

FREE Block
until the block is reused which will automatically re-format the block or force re-formatting the block using Doc ID 336133.1

INDEX Block
drop and create the index

TABLE Block
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip, move it to other tablespace. then to modify the table back to NOSKIP_FLAG , insert data manual if possible.

At that time if the corruption is still reported in v$database_block_corruption or v$nonlogged_block (12c+), run a rman validate to clear that view. RMAN commands are introduced:

RMAN> validate [database / datafile] nonlogged block;
RMAN> recover [database / datafile] nonlogged block;  -> for Standby Databases

in my case ,the Nologging Corrupted Blocks are AWR objects stored in the SYSAUX tablespace. it’s WRH$_ACTIVE_SESSION_HISTORY, the v$active_session_history in ASH buffer (Circular Buffer )- 1M to 128M (~2% of SGA) Flushed every snapshot interval to disk or when buffer 2/3 full (it protects itself so you can relax) Avg row around 150bytes 3600 secs in an hour ~ ½ Meg per Active Session per hour That’s generally over an hour of ASH.

We can manually clean up this object as written in my previous article ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTOR.

How to check the time it takes AWR to refresh each table？

SQL> select table_name_kewrtb name, end_time-begin_time time 
2 from wrm$_snapshot_details, x$kewrtb 
3 where snap_id = :snap_id 
4 and dbid = :dbid 
5 and table_id = table_id_kewrtb 
6 order by table_id; 

References Mos Doc ID 794505.1

打赏

微信扫一扫，打赏作者吧～