Many of the DBAs face this problem day by day is Oracle Data
Block Corrupted. Here I like to give a demonstration to solve this problem
where index type segment is corrupted.
Symptom of the Problem:Queries with where clause against a table fail but queries without where clause run without error.
Cause of the Problem: This problem is caused due to a corrupted block in one or more blocks belonging to an index type of segment. Since the corrupted block does not belong to the table, some statements could be executed successfully if the information is retrieved via full tablescan.
How to Fix:
(A) Find out the objects where the corrupted block belongs:
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = and between block_id AND block_id + blocks - 1 ;
For detail result, I used to use dbverify utility for details about the corrupted data file.
(B) Find out which table the INDEX is on:
SELECT table_owner, table_name FROM dba_indexes WHERE owner=’’ AND index_name=’’;
(C) Determine if the index supports a CONSTRAINT:
SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner=’’ AND constraint_name=’’ ;
Possible values for CONSTRAINT_TYPE are:
P The index supports a primary key constraint.
U The index supports a unique constraint.
(D) Check if it is type P:
If the INDEX supports a PRIMARY KEY constraint (type “P”) then check if the primary key is referenced by any foreign key constraints: Eg:
SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE r_owner=’’ AND r_constraint_name=’’ ;
Solution to the problem:
1)Recreate the index. It will require to delete all constraints supported or referenced by the index before.
2)Database recover when the size of the index is too big to be recreated and the size of the table involved does not enable the application to access the information without impacting the performance of the application.
Symptom of the Problem:Queries with where clause against a table fail but queries without where clause run without error.
Cause of the Problem: This problem is caused due to a corrupted block in one or more blocks belonging to an index type of segment. Since the corrupted block does not belong to the table, some statements could be executed successfully if the information is retrieved via full tablescan.
How to Fix:
(A) Find out the objects where the corrupted block belongs:
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = and between block_id AND block_id + blocks - 1 ;
For detail result, I used to use dbverify utility for details about the corrupted data file.
(B) Find out which table the INDEX is on:
SELECT table_owner, table_name FROM dba_indexes WHERE owner=’’ AND index_name=’’;
(C) Determine if the index supports a CONSTRAINT:
SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner=’’ AND constraint_name=’’ ;
Possible values for CONSTRAINT_TYPE are:
P The index supports a primary key constraint.
U The index supports a unique constraint.
(D) Check if it is type P:
If the INDEX supports a PRIMARY KEY constraint (type “P”) then check if the primary key is referenced by any foreign key constraints: Eg:
SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE r_owner=’’ AND r_constraint_name=’’ ;
Solution to the problem:
1)Recreate the index. It will require to delete all constraints supported or referenced by the index before.
2)Database recover when the size of the index is too big to be recreated and the size of the table involved does not enable the application to access the information without impacting the performance of the application.
Causes of Block Corruption:
- Bad IO hardware / firmware
- OS problems
- Oracle problems
- Recovering through “UNRECOVERABLE” or “NOLOGGING” database actions
- Bad IO hardware / firmware
- OS problems
- Oracle problems
- Recovering through “UNRECOVERABLE” or “NOLOGGING” database actions
Tackle corruption problems:
1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty.
2) Replace or move away from any faulty or suspect hardware.
3) Determine which database objects are affected.
4) Choose the most appropriate database recovery / data salvage option.
(1)Determine the Extent of the Corruption Problem
Whenever a corruption error occurs note down the FULL error messages and look in the instance’s alert log and trace files for any associated errors. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem.
(2) Replace or Move Away from Suspect Hardware
The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option.Here we can move datafile to another volume.
(3) Which Objects are Affected ?
It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption may be on objects which can easily be re-created.
Options:
If the OWNER is “SYS” then contact Oracle support with all details.
For non dictionary tables possible options include:
i)Recovery
OR
ii)Salvage data from the table (or partition) THEN Recreate the table (or partition)
OR
iii)Leave the corruption in place (eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)
(4) Choosing a Recovery Option
-Is any Recovery Required ?
-Is Complete Recovery an option ?
-Can the object be Dropped or Re-created without needing to extract any data from the object itself ?
-Is it required to salvage data before recreating the object ?
-Is it acceptable to leave the corruption in place for the moment?
1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty.
2) Replace or move away from any faulty or suspect hardware.
3) Determine which database objects are affected.
4) Choose the most appropriate database recovery / data salvage option.
(1)Determine the Extent of the Corruption Problem
Whenever a corruption error occurs note down the FULL error messages and look in the instance’s alert log and trace files for any associated errors. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem.
(2) Replace or Move Away from Suspect Hardware
The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option.Here we can move datafile to another volume.
(3) Which Objects are Affected ?
It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption may be on objects which can easily be re-created.
Options:
If the OWNER is “SYS” then contact Oracle support with all details.
For non dictionary tables possible options include:
i)Recovery
OR
ii)Salvage data from the table (or partition) THEN Recreate the table (or partition)
OR
iii)Leave the corruption in place (eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)
(4) Choosing a Recovery Option
-Is any Recovery Required ?
-Is Complete Recovery an option ?
-Can the object be Dropped or Re-created without needing to extract any data from the object itself ?
-Is it required to salvage data before recreating the object ?
-Is it acceptable to leave the corruption in place for the moment?
No comments:
Post a Comment