联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用plsql抢救数据
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
“在oracle出现ORA-8103/ORA-1578/ORA-376″等情况下抢救数据的争论没有停止过,很多人想到的是使用bbed,dul等工具来抢救,其实在很多时候我们使用pl/sql也可以完美的抢救数据.在这里我们通过模拟ORA-8103错误，然后使用plsql来找回数据.这中处理方法相对于bbed风险小,但是缺点是如果数据量大处理时间可能比较长,可能比dul有的一比,但是dul的工具不是任何人都有的.所以整体来说,在大部分情况下,这种方法处理某个数据块错误,抢救某个对象数据,还是很好的方法.
1.有非空列index情况
--创建测试表
SQL> create table xifenfei
2 as
3 select * from dba_objects;
Table created.
--修改某个项为非空值
SQL> alter table xifenfei modify object_id not null;
Table altered.
--创建一个唯一index
SQL> create unique index ind_xifenfei on xifenfei(object_id);
Index created.
--表总记录
SQL> select count(*) from xifenfei;
COUNT(*)
----------
50088
--extent的分布情况
SQL> set pages 100
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='XIFENFEI' AND owner='CHF';
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(*)
2 from chf.xifenfei where dbms_rowid.rowid_block_number(rowid)=2200;
COUNT(*)
----------
69
--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf bs=8192 count=1 seek=2200 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000151554 seconds, 54.1 MB/s
--启动数据库
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(xifenfei) */ count(*) from chf.xifenfei;
select /*+ full(xifenfei) */ count(*) from chf.xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
SQL> create table chf.xifenfei_new
2 as
3 select * from chf.xifenfei;
select * from chf.xifenfei
*
ERROR at line 3:
ORA-08103: object no longer exists
--创建备份表
SQL> create table chf.xifenfei_new
2 as
3 select * from chf.xifenfei where 1=0;
Table created.
--创建坏块相关rowid记录表
SQL> create table chf.bad_rows (row_id rowid, oracle_error_code number);
Table created.
--执行plsql脚本
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(xifenfei ind_xifenfei) */ rowid
from chf.xifenfei
where object_id is NOT NULL;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into chf.xifenfei_new
select /*+ ROWID(A) */ *
from chf.xifenfei A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into chf.bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
--查询错误记录
SQL> select count(*) from chf.bad_rows ;
COUNT(*)
----------
69
SQL> select * from chf.bad_rows where rownum<10;
ROW_ID ORACLE_ERROR_CODE
------------------ -----------------
AAAMugAAJAAAAiYAAA 8103
AAAMugAAJAAAAiYAAB 8103
AAAMugAAJAAAAiYAAC 8103
AAAMugAAJAAAAiYAAD 8103
AAAMugAAJAAAAiYAAE 8103
AAAMugAAJAAAAiYAAF 8103
AAAMugAAJAAAAiYAAG 8103
AAAMugAAJAAAAiYAAH 8103
AAAMugAAJAAAAiYAAI 8103
9 rows selected.
--查询备份表记录
SQL> select count(*) from chf.xifenfei_new;
COUNT(*)
----------
50019
50088-50019=69和被破坏块中记录一致,证明所有好块中记录全部被找回来
2.无非空列index情况
--创建表
SQL> CONN CHF/XIFENFEI
Connected.
SQL> create table t_xifenfei
2 as
3 select * from dba_objects;
Table created.
--表中记录总数
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
50086
--extent分布
SQL> SET PAGES 100
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='T_XIFENFEI' AND owner='CHF';
FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
9 9 16
9 17 24
9 25 32
9 33 40
9 41 48
9 49 56
9 57 64
9 65 72
9 73 80
9 81 88
9 89 96
9 97 104
9 105 112
9 113 120
9 121 128
9 129 136
9 137 264
9 265 392
9 393 520
9 521 648
9 649 776
21 rows selected.
--700数据块中记录数
SQL> select count(*)
2 from chf.t_xifenfei where dbms_rowid.rowid_block_number(rowid)=700;
COUNT(*)
----------
73
--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏block 700的数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf bs=8192 count=1 seek=700 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000156576 seconds, 52.3 MB/s
--启动数据库
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 count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
--创建备份表
SQL> CREATE TABLE T_XIFENFEI_NEW
2 AS
3 SELECT * FROM T_XIFENFEI WHERE 1=0;
--找回记录
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 = 'CHF'
and object_name = 'T_XIFENFEI'
-- 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 = 'CHF'
and segment_name = 'T_XIFENFEI'
-- 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 CHF.T_XIFENFEI_NEW
select /*+ ROWID(A) */ *
from CHF.T_XIFENFEI 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;
/
--找回记录数
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI_NEW;
COUNT(*)
----------
50013
50086-50013=73 证明非坏块中的数据都被完全寻找回来
参考：
ORA-8103 Troubleshooting, Diagnostic and Solution [ID 268302.1]
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS [ID 422547.1]
加我QQ
rem RECOVER_OPTION_1 create table bad_rows (row_id rowid, oracle_error_code number); set serveroutput on DECLARE TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER; CURSOR c1 IS select /*+ index(tab1) */ rowid from <original table name> tab1 where <indexed column> is NOT NULL; r RowIDTab; rows NATURAL := 20000; bad_rows number := 0 ; errors number; error_code number; myrowid rowid; BEGIN OPEN c1; LOOP FETCH c1 BULK COLLECT INTO r LIMIT rows; EXIT WHEN r.count=0; BEGIN FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS insert into <new table name> select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)> from <original table name> A where rowid = r(i); EXCEPTION when OTHERS then BEGIN errors := SQL%BULK_EXCEPTIONS.COUNT; FOR err1 IN 1..errors LOOP error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE; if error_code in (1410, 8103) then myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX); bad_rows := bad_rows + 1; insert into bad_rows values(myrowid, error_code); else raise; end if; END LOOP; END; END; commit; END LOOP; commit; CLOSE c1; dbms_output.put_line('Total Bad Rows: '||bad_rows); END; /connect / as sysdba set serveroutput on set concat off DECLARE nrows number; rid rowid; dobj number; ROWSPERBLOCK number; BEGIN ROWSPERBLOCK:=<VALUE CALCULATED IN STEP 1>; nrows:=0; select data_object_id into dobj from dba_objects where owner = '&&table_owner' and object_name = '&&table_name' -- 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 = '&&table_owner' and segment_name = '&&table_name' -- 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 <OWNER.NEW_TABLE> (<columns here>) select /*+ ROWID(A) */ <columns here> from &&table_owner.&&table_name 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; /