corrupt datablocks

[Roshan]

Oracle Database 12.1.0.2.0

 

Hi,

 

I am getting a datafile block corruption error for datafile 2

 

RMAN> list failure

2> ;

 

 

using target database control file instead of recovery catalog

Database Role: PRIMARY

 

 

List of Database Failures

=========================

 

 

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

21922      HIGH     OPEN      30-OCT-17     Datafile 2: '/u01/oradata/DLP/datafile/LOB02.dbf' contains one or more corrupt blocks

 

RMAN> advise failure;

 

 

Database Role: PRIMARY

 

 

List of Database Failures

=========================

 

 

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

21922      HIGH     OPEN      30-OCT-17     Datafile 2: '/u01/oradata/DLP/datafile/LOB02.dbf' contains one or more corrupt blocks

 

 

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=7 device type=DISK

analyzing automatic repair options complete

 

 

Mandatory Manual Actions

========================

1. No backup of block 4500 in file 2 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

 

 

Optional Manual Actions

=======================

no manual actions available

 

 

Automated Repair Options

========================

no automatic repair options available

Can you please guide me with the recovery of the table?

how do I find the table name?

 

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

---------- ---------- ---------- ------------------ --------- ----------

         2       4500          1                  0 FRACTURED          0

 

 

 

 

SEGMENT_TYPE       SEGMENT_NAME                

------------------ ------------------------------

TABLE              L7STATISTICS

 

BEGIN

  DBMS_REPAIR.admin_tables (

  table_name => ' ',

  table_type =>' ',

  action => DBMS_REPAIR.create_action,

  tablespace => 'LOB_TABLESPACE');

 

Thanks,

Roshan

[ErmanArslansOracleBlog]

First find the database segment which is associated with that corrupt block.
If it is an index, you may recreate.
If it is an important table, we 'll se..

It is a fractured block it seems -> read this - > http://ermanarslan.blogspot.com.tr/2017/05/fractured-blocks-ora-01578-tail-checks.htm

Well, there are 4 things that I can say on this ->

1)We can recreate the index, if the fractured block is an index block.

2)We can use methods for skipping block corruption (documented in 2199133.1)

3)We can use "bbed" utility to update the consistency value in tail and computed block checksum properly in order to at least start our database. (using bbed at on our own risk) -- this method is a very advanced one.  (You just ignore this .. :)

4)There are cases for specific scenarios.. For example this one -> Error ORA-1578 reported on the database after changing compatible.rdbms >= 11 and resizing a datafile (Doc ID 965751.1)Taking a look to Oracle Support is always a good idea.

[Roshan]

Hi,

I am planning to create a backup of L7STATISTICS as L7STATISTICS_NEW and truncate the original table afterwards.

Why is there a difference between the original table and new one? Is it the data which is corrupted?

SQL> select count(1) from protect.L7STATISTICS_NEW ;

  COUNT(1)
----------
     33748

SQL> select count(1) from protect.L7STATISTICS ;

  COUNT(1)
----------
     33817

Regards,

Roshan

[ErmanArslansOracleBlog]

How did you be able to create table as select from a corrupted table?
did you skipped the corrupt blocks? If so, that difference may be normal.

[Roshan]

create table protect.L7STATISTICS_NEW as select * from protect.L7STATISTICS;

[ErmanArslansOracleBlog]

Did you enabled "skip corrupt blocks" for this table? (DBMS_REPAIR.SKIP_CORRUPT_BLOCKS)

what is the output of the following query?

SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES   WHERE TABLES='L7STATISTICS'

[Roshan]

Hi,

Yes. I enabled it.

SQL>  SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES   WHERE TABLE_NAME='L7STATISTICS';

OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SKIP_COR
--------
PROTECT
L7STATISTICS
ENABLED

[ErmanArslansOracleBlog]

If so, then that difference may be normal.

You are skipping a block and all the rows inside of it..

[ErmanArslansOracleBlog]

When you query that corrupted table using "select * from table_name;" --not count(*)

how many rows returned? (actually)?

[Roshan]

select * from protect.L7STATISTICS;
33748 rows selected.

select * from protect.L7STATISTICS_NEW;
33748 rows selected.

[ErmanArslansOracleBlog]

You see :) they are the same.
So as I said..

[Roshan]

Hello,

Why is there difference between count and select *?

If blocks are being skipped, will data be loss?

[ErmanArslansOracleBlog]

Data is still there, but it is not getting read..
You already said, that you made the database to skip those blocks.. (you probably did it with dbms_repair.skip_corrupt_blocks)