プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com
バックアップなしにORA-1578、ORA-8103、ORA-1410エラになると、以下のようなPL/SQLでROWIDを構造することで大部分の非ベッドブロックデータを救える。一般的に二つの状況に分けられる：インディクスががある場合とインディクスを使えなくて、dbms_rowid.ROWID_CREATEでROWIDを構造する必要がある場合である。
インディクスがある場合に対して、MOSのスクリプトをそのままに運用できる：
REM Create a new table based on the table that is producing errors with no rows:
create table
as
select *
from
where 1=2;
REM Create the table to keep track of ROWIDs pointing to affected rows:
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 tab1
where 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
select /*+ ROWID(A) */
from 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, 1578) 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;
/
インディクスがないあるいはインディクスがこわれた場合に以下の方法を使ってください：
インスタンスデータを作成する
create table maclean_tab1 (t1 int,t2 date default sysdate) tablespace users
partition by range(t1)
(partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p8 values less than (80000),
partition p9 values less than (90000),
partition p10 values less than (100000),
partition p11 values less than (110000),
partition p12 values less than (120000),
partition p13 values less than (130000),
partition p14 values less than (140000),
partition p15 values less than (150000),
partition p16 values less than (160000))
;
insert into maclean_tab1(t1) select rownum from dual connect by level<160000; commit; SQL> select count(*) from maclean_tab1;
COUNT(*)
----------
159999
exec dbms_stats.gather_table_stats(USER,'MACLEAN_TAB1');
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
そして一部のブロックをサンプリングして、こわれたブロックは１０個のブロックに関している
set linesize 200 pagesize 1400
select dbms_rowid.rowid_block_number(rowid) blkid,
dbms_rowid.rowid_relative_fno(rowid) rfile
from maclean_tab1 sample(0.01)
where rownum <= 200 group by dbms_rowid.rowid_block_number(rowid), dbms_rowid.rowid_relative_fno(rowid) order by 1; BLKID RFILE ---------- ---------- 741833 4 741850 4 741994 4 742030 4 742085 4 742141 4 742159 4 742172 4 742173 4 742179 4 ベッドブロックを作成する [oracle@vrh8 udump]$ rman target / RMAN> blockrecover datafile 4 block 741833,741850,741994,742030,742085,742141,742159,742172,742173,742179 clear;
Starting blockrecover at 21-APR-13
using channel ORA_DISK_1
Finished blockrecover at 21-APR-13
SQL> select count(*) from maclean_tab1;
select count(*) from maclean_tab1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 741833)
ORA-01110: data file 4:
'/s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf'
blockrecover datafile block clearで一連のベッドブロックを構造した、それにバックアップもない。以下のスクリプトで大多数のデータを救える。
実際の操作
drop table maclean_tab_backup;
create table maclean_tab_backup
tablespace users
nologging compress pctfree 0 pctused 99 --可以注释掉的
as select * from maclean_tab1 where 1=0;
drop table bad_rows;
create table bad_rows (row_id rowid,oracle_error_code varchar2(50))
tablespace users
nologging compress pctfree 0 pctused 99 --可以注释掉的;
set serveroutput on;
set timing on;
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR Crowid_info IS
select Do.DATA_OBJECT_ID dataid,
DE.FILE_ID fid,
DE.BLOCK_ID blkid,
DE.BLOCKS blkcnt
from dba_objects DO, dba_extents DE
where DO.OBJECT_NAME = 'MACLEAN_TAB1'
--and DE.PARTITION_NAME='&PARTITION_NAME' --パーティションを指定したら、ノートをキャンセルする
and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')
and DO.OBJECT_NAME = DE.SEGMENT_NAME
and DO.owner = 'SYS'
order by 1, 2, 3 asc;
bad_rows number := 0;
errors varchar2(500);
error_code varchar2(500);
myrowid rowid;
BEGIN
/* Maclean Liu https://www.askmac.cn * Copy Right 2013-4-20 */
execute immediate 'alter session set commit_write=''batch,nowait'' ';
for i in Crowid_info loop
for j in 0 .. i.blkcnt - 1 loop
for z in 0 .. 2000 loop
begin
myrowid := dbms_rowid.ROWID_CREATE(1,
i.dataid,
i.fid,
i.blkid + j,
z);
insert into maclean_tab_backup
select /*+ ROWID(A) */
*
from maclean_tab1 A
where rowid = myrowid;
EXCEPTION
when OTHERS then
BEGIN
errors := SQLERRM;
error_code := SQLCODE;
if (error_code like '%1410%' or error_code like '%8103%' or error_code like '%1578%') then
bad_rows := bad_rows + 1;
insert into bad_rows values (myrowid, error_code);
commit;
else
raise;
end if;
END;
commit;
end;
end loop;
end loop;
end loop;
dbms_output.put_line('Total Bad Rows: ' || bad_rows);
commit;
END;
/
Elapsed: 00:01:10.16
SQL> select count(*) from maclean_tab_backup;
COUNT(*)
----------
155921
===>１０個のブロックのデータをなくした
原始スクリプトは以下の通り：
ステップ１ バックアップテーブルを作成する
create table
as
select *
from
where 1=2;
ステップ2 bad_rowsテーブルを作成する
create table bad_rows (row_id rowid,oracle_error_code varchar2(50));
ステップ3 以下のスクリプトを運用する
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR Crowid_info IS
select Do.DATA_OBJECT_ID dataid,
DE.FILE_ID fid,
DE.BLOCK_ID blkid,
DE.BLOCKS blkcnt
from dba_objects DO, dba_extents DE
where DO.OBJECT_NAME = '&TABNAME'
--and DE.PARTITION_NAME='&PARTITION_NAME'
and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')
and DO.OBJECT_NAME = DE.SEGMENT_NAME
and DO.owner = '&OWNER'
order by 1, 2, 3 asc;
bad_rows number := 0;
errors varchar2(500);
error_code varchar2(500);
myrowid rowid;
BEGIN
/* Maclean Liu https://www.askmac.cn * Copy Right 2013-4-20 */
execute immediate 'alter session set commit_write=''batch,nowait'' ';
for i in Crowid_info loop
for j in 0 .. i.blkcnt - 1 loop
for z in 0 .. 2000 loop
begin
myrowid := dbms_rowid.ROWID_CREATE(1,
i.dataid,
i.fid,
i.blkid + j,
z);
insert into &backup_table
select /*+ ROWID(A) */
*
from &source_table A
where rowid = myrowid;
EXCEPTION
when OTHERS then
BEGIN
errors := SQLERRM;
error_code := SQLCODE;
if (error_code like '%1410%' or error_code like '%8103%' or error_code like '%1578%') then
bad_rows := bad_rows + 1;
insert into bad_rows values (myrowid, error_code);
commit;
else
raise;
end if;
END;
commit;
end;
end loop;
end loop;
end loop;
dbms_output.put_line('Total Bad Rows: ' || bad_rows);
commit;
END;
/
Comment