Extracting Data from a Corrupt Table -Phase 1

There are several ways to extract data from the corrupt table. I will demonstrate total procedure in two phases.

(A)Methods of extracting data from a corrupt table AROUND a corrupt block.

(B)Extracting data from the corrupt block itself.

In this post I will try to demonstrate phase A only. In next post inshallah I will try to demonstrate phase B.

(A)Methods of extracting data from a corrupt table AROUND a corrupt block:

There are several ways to extract data around corrupt table. Some of well known methods are,

(a)Extracting Data from corrupt table using SKIP_CORRUPT_BLOCKS.

(b)Using ROWID Range Scans to extract data.

(c)Using salvage programs / PLSQL scripts which can be used to salvage data

In next section I will try to give a brief idea about these procedures.

(a)Extracting Data from corrupt table using SKIP_CORRUPT_BLOCKS:

-Connect as sysdba, rename the table that holds corrupt block (so that the new object can be created with correct name.) and then mark the table as needing to skip corrupt blocks thus:

execute dbms_repair.skip_corrupt_blocks('schema', 'table_name' );

-Now you should be able to use create table as select or export operation against the corrupt table to extract data from all no-corrupt blocks.

-Clear the attribute for the table.

execute dbms_repair.skip_corrupt_blocks('schema','table_name',

flags=dbms_repair.noskip_flag);

(b)Using ROWID Range Scans to extract data:
At first we need to determine the corrupted block lowest rowid and highest rowid. For this let us have a look at rowid formation.ROWID in oracle is 18 digit character string.

function ROWID_CREATE(rowid_type IN number,
object_number IN number,
relative_fno IN number,
block_number IN number,
row_number IN number)
return ROWID;

– rowid_type - type (restricted=0/extended=1)
– object_number - data object number
– relative_fno - relative file number
– block_number - block number in this file
– row_number - row number in this block
To construct a ROWID for a ROWID range scan we use the following input
to the ROWID_CREATE function with DBMS_ROWID package.

ROWID_TYPE: 1

RELATIVE_FNO: SELECT tablespace_name, relative_fno,
segment_type, owner, segment_name, partition_name
FROM dba_extents
WHERE file_id =
AND between block_id and block_id + blocks -1
;

Also be found from the DBA_EXTENTS view given the absolute file number and block number of the corrupt block.

OBJECT_NUMBER:

SELECT data_object_id
FROM dba_objects
WHERE object_name = ‘’
AND owner = ‘’
;

BLOCK_NUMBER and ROW_NUMBER:

The block number of the corrupt block is in the error or by using dbverify scan.For a ROWID range scan we generally want to select all rows BEFORE the corrupt block, then all rows AFTER the corrupt block. The first row in a block is row zero (0) and so we want all rowids LESS THAN “Block row 0″ and then GREATER THAN OR EQUAL TO “Block +1 row 0″.

You can now create the rowid strings to use in a predicate thus:

The “LOW_RID” is the lowest rowid INSIDE the corrupt block:

SELECT dbms_rowid.rowid_create(1,,,,0) LOW_RID from DUAL;

The “HI_RID” is the first rowid AFTER the corrupt block:

SELECT dbms_rowid.rowid_create(1,,,+1,0) HI_RID from DUAL;

It is now possible to use CREATE TABLE AS SELECT or INSERT … SELECT
to get data without accessing the corrupt block using a query of the form:

CREATE TABLE salvage_table AS
SELECT /*+ ROWID(A) */ * FROM A
WHERE rowid < ‘’
;

INSERT INTO salvage_table
SELECT /*+ ROWID(A) */ * FROM A
WHERE rowid >= ‘’
;
(c)Using salvage programs / PLSQL scripts which can be used to salvage data:

1. Create the exceptions table which stores the corrupted block information:

CREATE TABLE corrupt_block(file_id NUMBER, block_id NUMBER);

Then insert INTO this table the file number and the block number
you got from the 1578 error.

2. Create a table named ‘SALVAGE_ROWS_TABLE’ with the storage parameters
you prefer. This table must have the same structure as the original
corrupt table and must be owned by the user who will run this program.

3. Edit the PL/SQL script and change the lines:

table_name VARCHAR2(30):= ‘dept’;
user_name VARCHAR2(30):= ’scott’;

Modify the variables so that it will point to the corrupt table and the
table owner.

4. Modify the INS procedure declared in the PL/SQL script. The following
lines should be modified to select from the corrupt table:

INSERT INTO salvaged_rows_table
SELECT *
FROM SCOTT.DEPT
WHERE rowid = v_rowid;

5. Connect to the DBA user who owns the CORRUPT_BLOCK and the
SALVAGE_ROWS_TABLE table and run the script. The output will not be
printed until the program completes due to the way DBMS_OUTPUT works.

Script

REM NAME: salvage.sql

REM NOTE: This program will not handle long, long raw colums or chained rows.

SET ECHO OFF;
SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION dectohex(a IN NUMBER) RETURN VARCHAR2 IS
x VARCHAR2(8) := ”;
y VARCHAR2(1);
z NUMBER;
w NUMBER;
BEGIN
IF a > POWER(2,32) OR a < 0 THEN
RAISE invalid_number;
END IF;
w := a;
WHILE w > 0 LOOP
z := w MOD 16;
IF z = 10 THEN y := ‘A’;
ELSIF z = 11 THEN y := ‘B’;
ELSIF z = 12 THEN y := ‘C’;
ELSIF z = 13 THEN y := ‘D’;
ELSIF z = 14 THEN y := ‘E’;
ELSIF z = 15 THEN y := ‘F’;
ELSE y := TO_CHAR(z);
END IF;
w := TRUNC(w / 16);
x := CONCAT(y,x); — build x string backwards
END LOOP;
RETURN x;
END;
/

DECLARE

table_name VARCHAR2(30):= ‘dept’;
user_name VARCHAR2(30):= ’scott’;

file_id NUMBER;
block_id NUMBER;
extentid number;
blocks NUMBER;
file_id_hex VARCHAR2(4);
block_id_hex VARCHAR2(8);
row_count_hex VARCHAR2(4);
row_count NUMBER;
block_counter NUMBER;
for_loop_counter NUMBER;
corrupt_block_flag NUMBER:=0;
check_file NUMBER;
check_block NUMBER;
row_id_hex CHAR(18);
invalid_rowid EXCEPTION;
rows_per_block NUMBER:=100;

CURSOR c1 IS SELECT file_id, block_id, blocks,extent_id FROM dba_extents
WHERE SEGMENT_NAME = UPPER(table_name) AND OWNER = UPPER(user_name)
ORDER BY EXTENT_ID;

CURSOR c2 IS SELECT file_id,block_id FROM corrupt_block;

PROCEDURE ins (v_rowid VARCHAR2) is
bad_rowid EXCEPTION;
PRAGMA EXCEPTION_INIT (bad_rowid, -01410);
BEGIN
INSERT INTO salvaged_rows_table
SELECT *
FROM SCOTT.DEPT
WHERE rowid = v_rowid;

EXCEPTION
WHEN bad_rowid THEN
NULL;
END ins;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO file_id, block_id, blocks,extentid;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(’extent: file:’||to_char(file_id)||’
block_id:’||to_char(block_id)||’ blocks:’||to_char(blocks));
file_id_hex := DECTOHEX(file_id);
block_counter:= block_id;
for_loop_counter:= block_id + blocks-1;
FOR i IN block_id..for_loop_counter LOOP
corrupt_block_flag:= 0;
OPEN c2;
LOOP
FETCH c2 INTO check_file, check_block;
EXIT WHEN c2%NOTFOUND;
IF (check_block = block_counter AND check_file = file_id) THEN
corrupt_block_flag :=1;
dbms_output.put_line(’….Skipping corrupt
block:’||to_char(block_counter)|| ‘ file id:’||to_char(file_id));
END IF;
END LOOP;
CLOSE c2;
block_id_hex := dectohex(block_counter);
IF corrupt_block_flag = 0 THEN
FOR row_count IN 0..rows_per_block LOOP
IF row_count = 0 THEN
row_count_hex := ‘0′;
ELSE
row_count_hex:=dectohex(row_count);
END IF;
row_id_hex:=LPAD(block_id_hex,8,0) ||’.'||
LPAD(row_count_hex,4,0) ||’.'|| LPAD(file_id_hex,4,0);
ins(row_id_hex);
END LOOP;
END IF;
block_counter := block_counter+1;
COMMIT;
END LOOP;
END LOOP;
CLOSE c1;
COMMIT;
END;
/