Backup & Recovery

Logical Corruption (IOT) detection and solution

User Rating:  / 1
PoorBest 
Parent Category: Articles
Created on Monday, 21 March 2011 16:11
Last Updated on Monday, 12 March 2012 13:54
Published on Monday, 21 March 2011 16:11
Written by Guy Lambregts
Hits: 5853

Logical Corruption (Index Organized Tables)

 

We backup using rman with the check logical attribute, hence we detect logical corruption during our backup ( see below how to use the check logical attribute )

We double check with dbverify whether our data file is indeed corrupted.

 


bash-3.2$ dbv file=/ora01/oradata/PLATINUM/drsys01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Mon Mar 7 12:05:10 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /ora01/oradata/PLATINUM/drsys01.dbf
Block Checking: DBA = 12587238, Block Type = KTB-managed data block
**** row 0: row length 1925 past end of block
**** row 0: row skipped so other stats may be wrong
---- end index block validation
Page 4326 failed with check code 6401
Block Checking: DBA = 12589178, Block Type = KTB-managed data block
**** row 0: row length 538 past end of block
**** row 0: row skipped so other stats may be wrong
---- end index block validation
Page 6266 failed with check code 6401


DBVERIFY - Verification complete

Total Pages Examined         : 54800
Total Pages Processed (Data) : 46644
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 3956
Total Pages Failing   (Index): 2
Total Pages Processed (Other): 2294
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1906
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3499034754 (3.3499034754)

 

We use rman with the check logical attribute and the skip maxcorrupt option to populate v$database_block_corruption

 

RMAN> run {
2> set maxcorrupt for datafile 3 to 100;
3> backup as compressed backupset check logical tablespace DRSYS format '/ora01/oracle/rman/PLATINUM/DB_%d_%U';
4> }

executing command: SET MAX CORRUPT

Starting backup at 07-MAR-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/ora01/oradata/PLATINUM/drsys01.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAR-11
channel ORA_DISK_1: finished piece 1 at 07-MAR-11
piece handle=/ora01/oracle/rman/PLATINUM/DB_PLATINUM_67m6k8lh_1_1 tag=TAG20110307T110001 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 07-MAR-11

Starting Control File and SPFILE Autobackup at 07-MAR-11
piece handle=/ora01/oracle/rman/PLATINUM/ctrl_file_c-2196165104-20110307-01 comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAR-11



11:35:38 PLATINUM_CAITLANNDBA_27/01/2011 14:51>select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3       4326          1         1.0701E+10 LOGICAL
3       6266          1         9464373153 LOGICAL

 

To which object does these blocks belong ?

 

11:31:47 PLATINUM_DBA_27/01/2011 14:51>select owner,segment_name,segment_type from dba_extents where file_id = 3 and 4326 between block_id and block_id + blocks - 1;

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE
------------------
CTXSYS
SYS_IOT_TOP_27405
INDEX

 

 

We move the index organized table online. One of the advantages of index organized tables is that we can move the structure online. there is no need to rebuild the indexes online since they reference the logical rowid ( which does not change when we move the IOT )

 

11:31:47 PLATINUM_DBA_27/01/2011 14:51> alter table ctxsys.dr$pending move online;

We create a dummy table to force the resue of the previously logical corrupted blocks. Once created we fill this table with dummy records. Blocks will then become reformatted. ( that is the goal of this operation )

 

12:12:31 PLATINUM_DBA_27/01/2011 14:51>create table T1 (col1 number) tablespace DRSYS;

Table created.

12:13:47 PLATINUM_DBA_27/01/2011 14:51>alter table t1 allocate extent;

Table altered.

12:13:56 PLATINUM_DBA_27/01/2011 14:51>alter table t1 allocate extent;

Table altered.

12:13:56 PLATINUM_DBA_27/01/2011 14:51>alter session set db_block_checking =  full;

13:09:19 PLATINUM_DBA_27/01/2011 14:51>begin
13:09:27   2  for i in 1 .. 10000000
13:09:31   3  loop
13:09:33   4  insert into T1 values (i);
13:09:37   5  end loop;
13:09:40   6  end;
13:09:41   7  /

 

With dbverify we check whether the corruption has gone

 

bash-3.2$  dbv file=/ora01/oradata/PLATINUM/drsys01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Mon Mar 7 12:14:14 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /ora01/oradata/PLATINUM/drsys01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 57200
Total Pages Processed (Data) : 52650
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 878
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2340
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1332
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3499108504 (3.3499108504)

 

 

With rman we check whether the  corruption has gone

 

RMAN> run {
2>  set maxcorrupt for datafile 3 to 100;
3> backup as compressed backupset check logical tablespace DRSYS format '/ora01/oracle/rman/PLATINUM/DB_%d_%U';
4> }

executing command: SET MAX CORRUPT
using target database control file instead of recovery catalog

Starting backup at 07-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=277 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=312 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=276 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=293 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/ora01/oradata/PLATINUM/drsys01.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAR-11
channel ORA_DISK_1: finished piece 1 at 07-MAR-11
piece handle=/ora01/oracle/rman/PLATINUM/DB_PLATINUM_69m6kdta_1_1 tag=TAG20110307T122930 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 07-MAR-11

Starting Control File and SPFILE Autobackup at 07-MAR-11
piece handle=/ora01/oracle/rman/PLATINUM/ctrl_file_c-2196165104-20110307-02 comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAR-11

 

 

it is all OK now