联系:手机/微信(+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]
《使用plsql抢救数据》有 2 条评论