Block corrupted record.

miloluo

Hi, guys
Assume that I have a table and no related backup availabe.
If some block is broken, then , how can I get the data with the blocks that are not broken?

Test environment:

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 1m;

Tablespace created.

SQL> create table scott.test_bad_block (id int, name varchar2(20)) tablespace test;

Table created.


SQL> r
1 begin
2 for i in 1..1000000 loop
3 insert into scott.test_bad_block values (i, 'test' || i);
4 commit;
5 end loop;
6* end;
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST_BAD_BLOCK by 8 in tablespace TEST
ORA-06512: at line 3


SQL> select count(*) from scott.test_bad_block;

COUNT(*)
----------
40984

Now I know, I have 40984 rows in the table.

In order to simulating the block corrupt, I shutdown the database and I used the bvi (a binary file editor to modify a line of record) , then start the database, when I am issuing the same statement, the problem shows:

If you don't understant the above steps, please skip it and see what's happenning here:

SQL> select count(*) from scott.test_bad_block;
select count(*) from scott.test_bad_block
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted[b] (file # 7, block # 72)[/b]
ORA-01110: data file 7: '/u01/app/oracle/oradata/test/test.dbf'

It says that the block is corrupted.

Now, here is what I can do to get the data right now:

#get the object id
SQL> r
1* select OBJECT_NAME, DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='TEST_BAD_BLOCK'

OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
TEST_BAD_BLOCK 10327


# The first bad block rowid:
SQL> select dbms_rowid.rowid_create(1,10327,7,72,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAChXAAHAAAABIAAA

#The valid data before corrupted block:
SQL> select count(*) from scott.test_bad_block where rowid < 'AAAChXAAHAAAABIAAA';

COUNT(*)
----------
21745
# This data is that I can fetch to anther table to save my data.
SQL> create table scott.temp as select * from scott.test_bad_block where 1=2;

Table created.

SQL> insert into scott.temp select * from scott.test_bad_block where rowid < 'AAAChXAAHAAAABIAAA';

21745 rows created.

SQL> commit;

Commit complete.

Here are my questions:
1. How can I know, that if there is valid data(the block is not corrupted) after this bad block?
2. How can I get the data after this bad block?

Thanks very much,
BR,
Milo


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Hi, Milo. The package DBMS_REPAIR is what you need to look at, if you aren;t allowed to use a backup.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

More precisely the SKIP_CORRUPT_BLOCKS procedure.

Regards
Michel
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Hi, John , Michel
Thanks for helping.
Here is the output, after using the procedure.

SQL> exec dbms_repair.skip_corrupt_blocks('SCOTT','TEST_BAD_BLOCK');

PL/SQL procedure successfully completed.

SQL> select count(*) from scott.test_bad_block;

COUNT(*)
----------
40621
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

My way with rowid is too complicated.
This procedure is very amazing and efficient.

BR,
Milo

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
If you want to take this further, it is possible to recover the lost data (up to a point) if there are indexes on the table. The DUMP_ORPHAN_KEYS procedure can extract the values for all the indexed columns of the missing rows, and then you can re-insert them. Until you do that, you can get very odd results: a query that uses (for example) the INDEX_JOIN access method will include the damaged rows, a query that uses a full table scan will not.

I've always meant to write up a full demonstration of the technique, but never did - anyone want to have a go? Present it at the next Open World, and be famous!

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Take a look at this:

orcl> create tablespace tabs datafile size 1m;

Tablespace created.

orcl> create tablespace inds datafile size 1m;

Tablespace created.

orcl> create table t1 (c1 number, c2 number) tablespace tabs;

Table created.

orcl> create index i1 on t1(c1);

Index created.

orcl> alter table t1 modify c1 not null;

Table altered.

orcl> insert into t1 values(1,1);

1 row created.

orcl> alter tablespace tabs offline;

Tablespace altered.

orcl> select count(*) from t1;

COUNT(*)
----------
1

orcl> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12:
'C:\ORACLE\ORADATA\ORCL\ORCL\DATAFILE\O1_MF_TABS_6R9VXRLP_.DBF'


orcl>

I create a table in one tablespace, then an index on a not nullable column in another tablespace. Even though I take the tablespace with the table offline, a query such as SELECT COUNT(*) still works, because Oracle can execute it by counting the index: the table isn't needed. But a query that needs the table, such as SELECT *, fails because the table is not accessible.
That's the best I can do as an example: I don't have a utility that can corrupt a block. But you can take this further. After using SKIP_CORRUPT_BLOCKS to "repair" the table, you would probably find that a query that selects an indexed column returns more rows than a query that selects an unindexed column. That's why you can sometimes reconstruct the missing rows from the indexes.
Have fun...

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
You must set the indexed column as NOT NULL.

Regards
Michel

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Hi, Michel
Do you mean I should add a not null constrain to the indexed column?

Thanks,
BR,
Milo

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Yes. It is mandatory. Carefully read John's test.

Quote:

I create a table in one tablespace, then an index on a not nullable column in another tablespace

Regards
Michel

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Actually, I make it, but before I get this done, I can't make the block currpted. I don't know why.

SYS@test> create table scott.t1 (c1 number, c2 number) tablespace tbs;

Table created.

SYS@test> create index i1 on scott.t1(c1);

Index created.

SYS@test> alter table scott.t1 modify c1 not null;

Table altered.

SYS@test> r
1 begin
2 for i in 1..1000000 loop
3 insert into scott.t1 values ( i, i);
4 commit;
5 end loop;
6* end ;
begin
*
ERROR at line 1:
ORA-01653: unable to extend table %s.%s by %s in tablespace %s

-- There are 25,556 lines in this table

SYS@test> select count(*) from scott.t1;

COUNT(*)
----------
25556


--destory the file
--unfortunately, I can't corrupt some block in this file,
--but it's enough for making a trouble of this datafile


-- create a table to copy the indexed column data to a table
SYS@test> create table temp as select c1 from scott.t1;

Table created.

-- check if the last line of this table is correct
SYS@test> select max(c1) from temp;

MAX(C1)
----------
25556

-- access the table without indexed column will failed
SYS@test> select c2 from scott.t1;
select c2 from scott.t1
*
ERROR at line 1:
ORA-00376: file %s cannot be read at this time

As john mentioned,
Quote:

Even though I take the tablespace with the table offline, a query such as SELECT COUNT(*) still works, because Oracle can execute it by counting the index: the table isn't needed

So I check the execution plan in this sql, but I dont see the execution tells using the index column.

Is that because the execution plan is not contain this information or is that because oracle detected that the table is not available during execution, so it turns to the index?

BR,
Milo

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Oracle uses the index because it is cheaper to scan it rather than the index because the index
is smaller.

Regards
Michel

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Quote:

Oracle uses the index because it is cheaper to scan it rather than the index because the index is smaller.

Hi, Michel
Do you mean the index will take smaller cost than other ways,such as full table scan?
So if the same scenario is come again, except the datafile(that means datafile is not been broken), why I can't see it's using the index?

SCOTT@test> create table t2 (c1 number, c2 number);

Table created.

SCOTT@test> create index i2 on t2(c1);

Index created.

SCOTT@test> alter table t2 modify c1 not null;

Table altered.


SCOTT@test> ed
Wrote file afiedt.buf

1 begin
2 for i in 1..25556 loop
3 insert into t2 values (i, i);
4 commit;
5 end loop;
6* end;
7 /

PL/SQL procedure successfully completed.

SCOTT@test> select count(*) from t2;

COUNT(*)
----------
25556

SCOTT@test> set autotrace on
SCOTT@test> r
1* select count(*) from t2

COUNT(*)
----------
25556


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| [color=red]2 | TABLE ACCESS FULL| T2 | 25556 | 15 (0)| 00:00:01 [/color]|
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Is this index scan related with the datafile corrupted and is there any rule to know it will using or not using the index?

Thanks very much,

BR,
Milo