Oracle数据库数据恢复、性能优化、故障诊断来问问Maclean

恢复恢复是Oracle中永恒的话题， 只要有数据 就有备份恢复的需求。 而在国内对于备份以及备份的可用性往往被企业所忽视。这造成了再数据库恢复上存在着东西方的差异。 更多的老外DBA把经历花在对Oracle内部原理和性能优化的研究上。

而我们国内 DBA似乎必须要精通一门额外的技术==》 在没有任何备份的情况下 恢复Oracle数据库中数据的技术！  虽然这在大多数情况下是屠龙之技， 但很多时候却又变成了衡量一个DBA技术水准的标准了，(这样不好..  不好)。

当然也并不是说 这种无备份下的数据恢复是无技术含金量的，实际上它们很需要对Oracle数据文件、数据块及其数据结构的理解，以及对数据字典构成的了解。

这里我们总结Oracle数据恢复专题的专题，包括一些在无备份情况下的数据恢复：例如DUL和BBED工具恢复等技术。

【数据恢复】利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题

【数据恢复】ORA-600[kccpb_sanity_check_2]一例

Oracle rman中set newname可能很慢

如何清除Oracle控制文件中的无用记录，例如v$archived_log中的deleted归档日志记录

如何找回被create or replace覆盖的PL/SQL对象

Archivelog Completed Before VS UNTIL TIME

ASM丢失disk header导致ORA-15032、ORA-15040、ORA-15042 Diskgroup无法mount

Overcome ORA-600[4xxx] open database

数据恢复:解决ORA-600[kghstack_free2][kghstack_err+0068]一例

清理RMAN Catalog恢复目录

如何rename datafile name中存在乱码的数据文件

11g新特性recover corruption list

解决ORA-01578错误一例

Script:收集介质恢复诊断信息

如何重建SYSAUX表空间上的对象

Oracle数据恢复:解决ORA-00600:[4000] ORA-00704: bootstrap process failure错误一例

Script:检查数据库当前是否有备份操作在执行中

数据恢复：模拟2个逻辑坏块

Script:收集Oracle备份恢复信息

Oracle备份恢复:Rman Backup缓慢问题一例

了解rman catalog的兼容性

Oracle内部错误:ORA-00600[2608]一例

使用bbed解决ORA-01189错误

Fractured block found during backing up datafile

手动递增SCN号的几种方法：How to increase System Change Number by manual

DBMS_REPAIR example

对于无备份情况下的ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题，可以通过如下PL/SQL 构造ROWID的方式挽救绝大多数非坏块的数据， 一般分成 2种情况 有索引可以用来获取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.

随即采样一些块来做 坏块 约涉及到10个块的数据

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 http://www.askmaclean.com * 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

 ===>损失了少量的10个块的数据

原始脚本如下：

步骤1 创建备份表

create table <new table name>
as
select *
from   <original table name>
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 http://www.askmaclean.com * 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;
/

构造ROWID绕过ORA-1578、ORA-8103、ORA-1410脚本下载