If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Mapping block # to object - we have block corruption

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
Mapping block # to object - we have block corruption

Our SAN was getting low on space (1.8Gb left or so) and we started getting msgs in the alert log like

Code:
ORA-3297 signalled during: ALTER DATABASE DATAFILE '/u02/oradata/dev/claims_m...
I wasn't able to find out what these error msgs actually meant, but I think I know now. We took a cold backup, extended the amount of space available on the SAN, and restarted the database. When it came up, it signalled that there were about 70 corrupt blocks, which we found after running

Code:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
and then

Code:
SQL> select *
  2  from v$database_block_corruption;
 
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         1      63550          1                  0 CHECKSUM
         2      31878          1                  0 CHECKSUM
         2      91205          1                  0 FRACTURED
...
So the sol'n on metalink is to export the problem objects and then import them, but I don't know how to map the BLOCK# to the object it belongs to. Could someone help me with that query?

-Thanks
Chuck
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
Alternatively, you might consider the following:
http://download-west.oracle.com/docs...8/d_repair.htm

or
Code:
SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = <filenumber>
AND <blocknumber> BETWEEN block_id AND block_id + blocks - 1;
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
So, I'm looking into using DBMS_REPAIR first, in order to fix this problem. I'm following the instructions and looking at a "low-risk" table. I've gone and set up the REPAIR_TABLE, and I've fed the problem table into the DBMS_REPAIR.CHECK_OBJECT procedure. Then I queries REPAIR_TABLE as indicated in the instructions.

According to the instructions, the table should be inducate that the block has a MARKED_CORRUPT values = FALSE.

http://download-west.oracle.com/docs...htm#sthref2912

For us, though, the values is TRUE
Code:
SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
  2         CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
  3       FROM REPAIR_TABLE;

OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
PA_COVERAGE_VAP                      6164         6148 TRUE

mark block software corrupt
The main reason it confuses me is that the instructions claim that after you run DBMS_REPAIR.FIX_CORRUPT_BLOCKS, you'll be notified of success using the same query - because you'd notice that MARKED_CORRUPT had changed from FALSE to TRUE. The following is a quote from the Oracle manual

Code:
This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN 
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     SCHEMA_NAME => 'SCOTT',
     OBJECT_NAME=> 'DEPT',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

SQL*Plus outputs the following line:

num fix: 1

To following query confirms that the repair was done.

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
     FROM REPAIR_TABLE;

OBJECT_NAME                      BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT                                    3 TRUE
So, I haven't run the FIX_CORRUPT_BLOCKS yet, as I'm not sure if that piece has already been performed by ORacle in some other step?

-Chuck
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
Tried the FIX_CORRUPT_BLOCKS procedure anyway, but Oracle thought it had to fix 0 blocks.

So, now I'm trying to retrieve the data from an expdp two days ago, which should contain the corrupt blocks.

-cf
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
Turns out after I started restoring Tables via datapump, which was working, we decided to try another approach via RMAN, which worked.
Code:
RMAN> BACKUP VALIDATE DATABASE;
RMAN> BLOCKRECOVER CORRUPTION LIST;
This goes through all the blocks listed in v$database_block_corruption and restores them, and in this case, fixed the corruption. I'm not sure why recovering a datablock from a backupset that RMAN says already contains a corrupt block would fix it, but it does. Perhaps the RMAN command should really say something like FIX CORRUPT BLOCKS CORRUPTION LIST.

-Chuck
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 2
YOu can try out the fix_corrupt_blocks procedure to solve out the issue:

dbms_repair.FIX_CORRUPT_BLOCKS (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
repair_table_name IN VARCHAR2 DEFAULT 'REPAIR_TABLE',
flags IN BINARY_INTEGER DEFAULT NULL,
fix_count OUT BINARY_INTEGER);

If after running this procedure if the problem still persists then you need to go for any oracle repair software third party software program that will do the job of repairing the oracle database tables.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On