17 | 08 | 2017
Latest Articles
Popular Articles

Backup & Recovery

Logical Corruption (indexes)

User Rating:  / 0
PoorBest 

Logical Corruption : detection and solution

 

Logical Corruption : Detection

 

If db_block_checking has been set to true — note that this is not the default — then v$database_block_corruption gets populated as soon as logical corruption is detected. If Enterprise Manager GRID is setup then the view v$database_block_corruption is automatically checked and alerts are triggered. Otherwise you should set up your own procedure to have queried this view at regular basis.

 

Target Name=COCONUT

Target Type=Database Instance
Host=MYHOST.my_domain
Metric=Corrupt Data Block Count
Metric Value=426
Timestamp=Jul 15, 2009 4:06:17 PM CEST
Severity=Critical
Message=Number of corrupt data blocks is 426.

Notification Rule Name=COCONUT_NOTIFICATION_RULE
Notification Rule Owner=COCONUT
Notification Count=1


SQL> show parameter db_block_checking

NAME                                 TYPE        VALUE


db_block_checking                    string      TRUE


SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION


        10     128356          2         8.8177E+12 LOGICAL
        10     193216          1         8.8177E+12 LOGICAL

 

 

Logical Corruption : Solution

 

Which datafile, which tablespace, which datatype ? We are "lucky", in this case we have logical corruption of indexes. More over we will notice that a simple online rebuild will solve our issue. If we would have had logical corrupted table data the solution could have been harder and less online. the CTAS or alter table move syntax could be the solution in those cases.

 

 

SQL> select file_name,tablespace_name from dba_data_files where file_id=10;


FILE_NAME



TABLESPACE_NAME


E:\ORADATA\COCONUT\COCONUT_INDX_01.DBF
COCONUT_INDX


SQL> select distinct segment_type from dba_segments where tablespace_name='COCONUT_INDX';

SEGMENT_TYPE


INDEX

SQL> desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

 


We have 2 entries in v$block_corruption. To which segment belong these corrupted blocks ? I found the below query rather fast while I googled around ( but did not write it myself ). I know it is the good and easy one to search for the problem segment. Note that the below numbers 128356 and 193216 I retrieved from v$database_block_corruption, you obviously should replace them with your own.

 

SQL> select segment_name, segment_type, owner from dba_extents where file_id = 10 and 128356 between block_id and block_id + blocks -1;


COCONUT1_IDX
INDEX              COCONUT


SQL> select segment_name, segment_type, owner from dba_extents where file_id = 10 and 193216 between block_id and block_id + blocks -1;

COCONUT2_IDX
INDEX              COCONUT

 

We are that lucky we have to deal with logical corruption of indexes. The online rebuild introduced in 8.1.x can be used to have (re)build the indexes elsewhere in the same tablespace. Since they are built elsewhere the space where they used to be only contains empty blocks. These blocks will and can be reused afterwards by other extents belonging to segments built in the same tablespace. However v$database_block_corruption is still populated with the old entries.

 

SQL> alter index COCONUT.testid_idx rebuild online;


Index altered.

SQL> alter index COCONUT.olptid_idx rebuild online;

Index altered.



SQL> select segment_name, segment_type, owner from dba_extents where file_id = 10 and 193216 between block_id and block_id + blocks -1;

no rows selected

SQL> select segment_name, segment_type, owner from dba_extents where file_id = 10 and 128356 between block_id and block_id + blocks -1;

no rows selected

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO


        10     128356          2         8.8177E+12 LOGICAL
        10     193216          1         8.8177E+12 LOGICAL

 

When did the corruption occur ? ( note the number 13 )

 

SQL > column CORRUPTION_CHANGE# format 9999999999999999999


SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS           CORRUPTION_CHANGE# CORRUPTIO


        10     128356          2                8817722292550 LOGICAL
        10     193216          1                8817722293822 LOGICAL

SQL> select scn_to_timestamp(8817722292550) from v$database_block_corruption;

SCN_TO_TIMESTAMP(8817722292550)


13-JUL-09 03.04.08.000000000 AM
13-JUL-09 03.04.08.000000000 AM

 

Question : How can we "reset" v$database_block_corruption; ?

Answer : USE RMAN

 

connected to recovery catalog database


RMAN> backup datafile 10;

Starting backup at 16-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=105 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=E:\ORADATA\COCONUT\COCONUT_INDX_01.DBF
channel ORA_DISK_1: starting piece 1 at 16-JUL-09
channel ORA_DISK_1: finished piece 1 at 16-JUL-09
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\COCONUT\BACKUPSET\2009_07_16\O1_MF_NNNDF_TAG20090716T150926_55Y9N6W0_.BKP tag=TAG20090716T150926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 16-JUL-09

RMAN> exit

 
C:\Documents and Settings\myself>sqlplus / as sysdba


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 16 15:10:57 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> select * from v$database_block_corruption;

no rows selected

 

The OEM GRID automatically detects the associated alert is cleared

 

Target Name=COCONUT

Target Type=Database Instance
Host=MYHOST.my_domain
Metric=Corrupt Data Block Count
Metric Value=0
Timestamp=Jul 16, 2009 3:10:43 PM CEST
Severity=Clear
Message=Number of corrupt data blocks is 0.

Notification Rule Name=COCONUT_NOTIFICATION_RULE
Notification Rule Owner=COCONUT
Notification Count=1

 

See alos VALIDATE ONLINE PHYSICAL AND LOGICAL CONSISTENCY OF THE ORACLE DATABASE

See also DETECT ORACLE BLOCK CORRUPTIONS

See also LOGICAL CORRUPTION (INDEXES) TABLE/INDEX CROSS REFERENCE FAILURE