Monthly Archives: 12月 2017

表坏块处理技术文档

表坏块处理技术文档

  • 创建测试用户

CREATE USER TEST IDENTIFIED BY TEST;

GRANT DBA TO TEST;

CONN TEST/TEST

  • 创建测试表

create table TEST as select * from dba_objects;

 

alter table TEST modify object_id not null;  ————-非空

 

create unique index ind_TEST on TEST(object_id); ——————索引

注:这两项可以不需要,为了模拟真实表

 

SQL> select count(*) from TEST;

COUNT(*)

———-

50088

 

–extent的分布情况

SQL> set pages 100

SQL> select file_id,block_id,block_id+blocks-1

from dba_extents

where segment_name =’TEST’ AND owner=’TEST’;

 

FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1

———- ———- —————–

9       1545              1552

9       1553              1560

9       1561              1568

9       1569              1576

9       1577              1584

9       1585              1592

9       1593              1600

9       1601              1608

9       1609              1616

9       1617              1624

9       1625              1632

9       1633              1640

9       1641              1648

9       1649              1656

9       1657              1664

9       1665              1672

9       1673              1800

9       1801              1928

9       1929              2056

9       2057              2184

9       2185              2312

 

21 rows selected.

–2200数据块包含记录

 

SQL> select  count(*)

from TEST.TEST where dbms_rowid.rowid_block_number(rowid)=2200; —-坏块记录

COUNT(*)

———-

69

3、关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down

 

4、破坏数据块

dd if=/dev/zero of=/oradata/standby/ORCL/datafile/o1_mf_users_8wl8oqxp_.dbf  bs=8192  count=1 seek=2200 conv=notrunc

 

5、模拟坏块

 

–启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area  318767104 bytes

Fixed Size                  1267236 bytes

Variable Size             104860124 bytes

Database Buffers          205520896 bytes

Redo Buffers                7118848 bytes

Database mounted.

Database opened.

 

–查询结果

SQL>  select /*+ full(TEST) */ count(*) from TEST.TEST;

ERROR at line 1:

ORA-08103: object no longer exists

 

SQL> create table TEST.TEST_NEW

2  as

3  select * from TEST.TEST;

ERROR at line 3:

ORA-08103: object no longer exists

 

6、修复坏块

create table TEST.TEST_NEW as select * from TEST.TEST where 1=0;  ———创建备份表

 

执行PL/SQL包

connect / as sysdba

set serveroutput on

set concat off

DECLARE

nrows number;

rid rowid;

dobj number;

ROWSPERBLOCK number;

BEGIN

ROWSPERBLOCK:=1000;

nrows:=0;

select data_object_id  into dobj

from dba_objects

where owner = ‘TEST’

and object_name = ‘TEST’

— and subobject_name = ‘<table partition>’  Add this condition if table is partitioned

;

for i in (select relative_fno, block_id, block_id+blocks-1 totblocks

from dba_extents

where owner = ‘TEST’

and segment_name = ‘TEST’

— and partition_name = ‘<table partition>’ Add this condition if table is partitioned

— and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 (A)

order by extent_id)

loop

for br in i.block_id..i.totblocks loop

for j in 1..ROWSPERBLOCK loop

begin

rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);

insert into TEST.TEST_NEW

select /*+ ROWID(A) */ *

from  TEST.TEST A

where rowid = rid;

if sql%rowcount = 1 then nrows:=nrows+1; end if;

if (mod(nrows,10000)=0) then commit; end if;

exception when others then null;

end;

end loop;

end loop;

end loop;

COMMIT;

dbms_output.put_line(‘Total rows: ‘||to_char(nrows));

END;

/

7、验证数据

SQL> select count(*) from TEST.TEST_NEW;

 

COUNT(*)

———-

50019

 

50088-50019=69和被破坏块中记录一致,证明所有好块中记录全部被找回来

8、修复表

ALTER TABLE TEST RENAME TO TEST_BAK;

ALTER TABLE TEST_NEW TEST;

创建相关索引

 

 

9、另外处理的方式

 

1、确定坏块

 

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents where file_id = 44 and 1535968 between block_id AND block_id + blocks -1 ;

 

2、设置事件并处理坏块

 

ALTER SESSION SET EVENTS ‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’;

 

alter table  TEST.TEST nologging;

 

create table test.bak as select * from test;

 

truncate table TEST.TEST drop storage;

 

insert /*+ APPEND */ into  TEST.TEST select * from TEST.TEST.BAK;

 

alter table  TEST.TEST logging;

 

analyze table  TEST  validate structure cascade;