Investigating DBV-00201: Block, DBA number, marked corrupt for invalid redo application


More or less forced 😉 by the procedures discussed in my last post (Rman duplicating an oracle 11g database in active state on the same host), this post will investigate how to verify and fix data corruption induced by RMan trying to restore nologging objects. Indeed, we know, there’s no actual restore of nologging objects, since there’s no cold or hot redo to process. But what does this mean in practice? Me, I learned now. Let’s have a look.

Ok, there’re some (partitioned) tables in the database duplicated, previous post, that for performance reasons (see : Oracle global temp tabs or nologgingappend saved redo in numbers) have been set nologging. I won’t explain the why here, that’s another subject, but, however, the first post-duplicate database backup showed up with block corruption errors in Quest’s Backup Reporter for Oracle Community. I today examined the overall database integrity status with a rman validate, verified the affected tablespace, but rman list failures, asking Data Recovery Advisor under the covers, did not seem to be up to complain about anything. We see that 10 blocks have been marked corrupt but the file check status is ok though.

RMAN> validate check logical database;
Starting validate at 22.07.2016-08:55:54
allocated channel: ORA_DISK_1
...
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
31   OK     10             58357        557056          8784584429990
  File Name: H:\ORACLE\SAN_4\ORADATA\UTL_01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              265313
  Index      0              223903
  Other      0              9483
...
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              778
Finished validate at 22.07.2016-08:59:46

RMAN> list failure all;
no failures found that match specification

So what about Data Recovery Advisor in place, that is within Enterprise Manager? Nothing, only the notes from yesterday’s rman duplicate. Such a way, list failure all; indeed does not have anything to emit.

Next is v$backup_corruption, which does show dedicated information, in contrast to v$database_block_corruption, but declares the corruption type as UNKNOWN and furthermore states that the marked corrupt has been executed before. Nevertheless, v$backup_corruption at least points out what actual segments are affected, a couple of table partitions, obviously.

select y.owner, y.segment_name, y.partition_name, y.segment_type, y.block_id, y.blocks,
  x.marked_corrupt, x.corruption_type
from dba_extents y,
    (select distinct file#, block#, marked_corrupt, corruption_type from v$backup_corruption) x
  where y.file_id = x.file# and (x.block# between y.block_id and (y.block_id + y.blocks - 1));

OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BLOCK_ID	BLOCKS	MARKED_CORRUPT	CORRUPTION_TYPE
CODE	TMP_S2SQ_IU_DATA	SOLR_CORE_MAIN	TABLE PARTITION	9360	8	NO	UNKNOWN
CODE	TMP_S2SQ_IU_DATA2	SOLR_CORE_MAIN	TABLE PARTITION	9608	8	NO	UNKNOWN
CODE	TMP_S2SQ_IU_DATA	SOLR_CORE_STBY	TABLE PARTITION	9688	8	NO	UNKNOWN
CODE	TMP_S2SQ_IU_DATA2	SOLR_CORE_STBY	TABLE PARTITION	9728	8	NO	UNKNOWN

Ok, what helped out finally, turned on the light, was old friend dbverify, another attempt motivated by Some hints to make handling of oracle datablock corruptions more efficient. There again, no “real” errors will be logged but at least an exception to deal with, to look up, to help understand the matter, as entitled above:

DBV-00201: Block, DBA number, marked corrupt for invalid redo application
Cause: The block was marked corrupt by the Oracle database server for invalid redo application, ex: media recovery of a NOLOGGING object or direct loaded data.
Action: If the block is not currently allocated to a database object, then no action is required. If the block is allocated, then the object will need to be rebuilt, or data to be reloaded.

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Jul 21 14:04:24 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = H:\ORACLE\SAN_4\ORADATA\UTL_01.DBF
DBV-00201: Block, DBA 130032787, marked corrupt for invalid redo application
...
DBVERIFY - Verification complete
Total Pages Examined         : 557056
Total Pages Processed (Data) : 265313
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 223903
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 9483
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 58357
Total Pages Marked Corrupt   : 10
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1376309670 (2045.1376309670)

Understand… that’s on of the costs you pay for the nologging stuff, kind of corruption that does not seem to be critical enough to let any Oracle tools come up with actual error messages. But will this “virtual” corruption affect further operation? Do I need to rebuild the segments in questions etc. pp? The answer is, probably not. Already the first insert statements to enter data into the partitions decreased the number of corrupt blocks reported, which i verified with rman validate underway. Note however, that a truncate... drop storage on the tables did not show any effects. Maybe because no storage was allocated at that time.

RMAN> validate check logical tablespace hugo_utl;
Starting validate at 22.07.2016-11:53:23
allocated channel: ORA_DISK_1
...
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
31   OK     0              58357        557056          8784584555962
  File Name: H:\ORACLE\SAN_4\ORADATA\UTL_01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              265321
  Index      0              223895
  Other      0              9483
Finished validate at 22.07.2016-11:53:49

End of story? Well, according to the docs of v$backup_corruption, the reported corrupt blocks apply to “block ranges in datafile backups from the control file”. v$database_block_corruption instead “displays information about database blocks that were corrupted after the last backup”. After submitting the insert statements mentioned above, both rman validate and dbverify no longer reported any corruption but v$backup_corruption still did. I would expect finding v$backup_corruption empty finally, as soon as the last backup with invalid redo has been deleted.

Jep, and here it is. Inspecting the following output from v$backup_set along with v$backup_corruption outer joined shows, that the backup as compressed backupset incremental level 1 cumulative database plus archivelog delete all input; I did to proof test my inductive thoughts correctly marks the last backup as no longer containing corrupt blocks. Yeah!

select a.set_stamp, a.set_count, a.backup_type, a.incremental_level, a.start_time,
  b.marked_corrupt, b.corruption_type
from v$backup_set a, v$backup_corruption b
  where a.backup_type in ('I', 'D') and a.incremental_level is not null
    and a.set_stamp = b.set_stamp(+) and a.set_count = b.set_count(+)
order by a.set_stamp, a.set_count;

SET_STAMP	SET_COUNT	BACKUP_TYPE	INCREMENTAL_LEVEL	START_TIME	MARKED_CORRUPT	CORRUPTION_TYPE
917779629	4	D	0	21.07.2016 10:47:09	NO	UNKNOWN
917779629	4	D	0	21.07.2016 10:47:09	NO	UNKNOWN
917779629	4	D	0	21.07.2016 10:47:09	NO	UNKNOWN
917779629	4	D	0	21.07.2016 10:47:09	NO	UNKNOWN
917791157	12	I	1	21.07.2016 13:59:17	NO	UNKNOWN
917791157	12	I	1	21.07.2016 13:59:17	NO	UNKNOWN
917791157	12	I	1	21.07.2016 13:59:17	NO	UNKNOWN
917791157	12	I	1	21.07.2016 13:59:17	NO	UNKNOWN
917828160	18	I	1	22.07.2016 00:16:00	NO	UNKNOWN
917828160	18	I	1	22.07.2016 00:16:00	NO	UNKNOWN
917828160	18	I	1	22.07.2016 00:16:00	NO	UNKNOWN
917828160	18	I	1	22.07.2016 00:16:00	NO	UNKNOWN
917871557	41	I	1	22.07.2016 12:19:17		

Have fun, Peter

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s