Backup & Recovery

Logical Corruption (indexes)

User Rating:

/ 0

Details: Parent Category: Articles; Created on Tuesday, 16 March 2010 22:23; Last Updated on Wednesday, 22 January 2014 21:20; Published on Tuesday, 16 March 2010 22:23; Written by Guy Lambregts; Hits: 8953

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