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: 9272
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