oracle error 1552,【案例】Oracle报错ORA-8103 使用plsql抢救恢复数据

天萃荷净

使用plsql抢救数据，在oracle出现ORA-8103/ORA-1578/ORA-376等错误时，使用plsql来找回数据.这中处理方法相对于bbed风险小,但是缺点是如果数据量大处理时间可能比较长

“在oracle出现ORA-8103/ORA-1578/ORA-376″等情况下抢救数据的争论没有停止过,很多人想到的是使用bbed,dul等工具来抢救,其实在很多时候我们使用pl/sql也可以完美的抢救数据.在这里我们通过模拟ORA-8103错误，然后使用plsql来找回数据.

这中处理方法相对于bbed风险小,但是缺点是如果数据量大处理时间可能比较长,可能比dul有的一比,但是dul的工具不是任何人都有的.所以整体来说,在大部分情况下,这种方法处理某个数据块错误,抢救某个对象数据,还是很好的方法.

1.有非空列index情况

--创建测试表

SQL> create table oracleplus

2 as

3 select * from dba_objects;

Table created.

--修改某个项为非空值

SQL> alter table oracleplus modify object_id not null;

Table altered.

--创建一个唯一index

SQL> create unique index ind_oracleplus on oracleplus(object_id);

Index created.

--表总记录

SQL> select count(*) from oracleplus;

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 ='oracleplus' 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.oracleplus where dbms_rowid.rowid_block_number(rowid)=2200;

COUNT(*)

----------

69

--关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

--破坏数据块

[oracle@oracleplus ~]$ 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(oracleplus) */ count(*) from chf.oracleplus;

select /*+ full(oracleplus) */ count(*) from chf.oracleplus

*

ERROR at line 1:

ORA-08103: object no longer exists

SQL> create table chf.oracleplus_new

2 as

3 select * from chf.oracleplus;

select * from chf.oracleplus

*

ERROR at line 3:

ORA-08103: object no longer exists

--创建备份表

SQL> create table chf.oracleplus_new

2 as

3 select * from chf.oracleplus 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(oracleplus ind_oracleplus) */ rowid

from chf.oracleplus

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.oracleplus_new

select /*+ ROWID(A) */ *

from chf.oracleplus 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.oracleplus_new;

COUNT(*)

----------

50019

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

2.无非空列index情况

--创建表

SQL> CONN CHF/oracleplus

Connected.

SQL> create table t_oracleplus

2 as

3 select * from dba_objects;

Table created.

--表中记录总数

SQL> select count(*) from t_oracleplus;

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_oracleplus' 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_oracleplus where dbms_rowid.rowid_block_number(rowid)=700;

COUNT(*)

----------

73

--关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

--破坏block 700的数据块

[oracle@oracleplus ~]$ 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_oracleplus;

select count(*) from chf.t_oracleplus

*

ERROR at line 1:

ORA-08103: object no longer exists

--创建备份表

SQL> CREATE TABLE T_oracleplus_NEW

2 AS

3 SELECT * FROM T_oracleplus 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_oracleplus'

-- and subobject_name = '' 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_oracleplus'

-- and partition_name = '

' Add this condition if table is partitioned -- and file_id != 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_oracleplus_NEW select /*+ ROWID(A) */ * from CHF.T_oracleplus 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_oracleplus_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]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-8103 使用plsql抢救恢复数据