oracle坏块的rowid方式修复

江西行的问题：8月磁盘空间就满了，没人管，这是一个影像的库，无备份。现在终于发现库不正常，表无法访问了。
远程支持解决过程：查看alert日志，搜“computed block checksum”，可以看到非常多的报错。类似这样的
Corrupt block relative dba: 0x048cd809 (file 18, block 841737)
Bad header found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0xf5c65dde
 last change scn: 0x0000.d0e1728d seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x728d0601
 check value in block header: 0xf5d0
 computed block checksum: 0x0
Reading datafile '/u01/neap/data/9999/9999/neap_data_99999999_04.dbf' for corruption at rdba: 0x048cd809 (file 18, block 841737)
Reread (file 18, block 841737) found same corrupt data
Sun Apr 28 13:16:17 2013
Corrupt Block Found
         TSN = 9, TSNAME = NEAP_DATA_99999999
         RFN = 18, BLK = 841737, RDBA = 76339209
         OBJN = 116433, OBJD = 116433, OBJECT = SYS_LOB0000070352C00005$$, SUBOBJECT = SYS_LOB_P3142
         SEGMENT OWNER = NEAP, SEGMENT TYPE = Lob Partition

以oracle用户执行以下操作： 
1. 确定坏块范围：DBVERIFY检查数据文件
$ dbv file=/u01/neap/data/9999/9999/neap_data_99999999_04.dbf blocksize=8192
看结果中的这一行 ,坏块个数
Total Pages Marked Corrupt   : 79036
查看这个数据文件所在的表空间
Select  TableSpace_Name from  DBA_DATA_FILES Where  FILE_NAME='/u01/neap/data/9999/9999/neap_data_99999999_04.dbf';
查看这个表空间下的所有数据文件
Select  * from  DBA_DATA_FILES Where  TableSpace_Name='NEAP_DATA_99999999';
再用dbv查看其它的数据文件有没有坏块
dbv file=/u01/neap/data/9999/9999/neap_data_99999999_01.dbf blocksize=8192
。。。
Total Pages Marked Corrupt   : 0   看到坏块都为0。
查下这个表空间里有哪些表
SQL> select owner,table_name  from dba_tables where tablespace_name = 'NEAP_DATA_99999999';
OWNER TABLE_NAME
------------------------------------------------------------
NEAP Z_NEAP_BATCH_99999999
NEAP Z_NEAP_BATCH_IMAGE_99999999
NEAP Z_NEAP_IMAGE_99999999
2. Analyze table检查关键表
SQL>  analyze table NEAP.Z_NEAP_BATCH_99999999 validate structure cascade online;
Table analyzed.
SQL> analyze table NEAP.Z_NEAP_BATCH_IMAGE_99999999 validate structure cascade online;
Table analyzed.
SQL> analyze table NEAP.Z_NEAP_IMAGE_99999999 validate structure cascade online;
analyze table NEAP.Z_NEAP_IMAGE_99999999 validate structure cascade online
*
ERROR at line 1:
ORA-01502: 索引 'NEAP.IDX_Z_NEAP_IMAGE_99999999' 或这类索引的分区处于不可用状态

------------------
3. 实施数据抢救。思路：先设置event 10231，检查那2张表看看有没有问题，再把索引报错这个Z_NEAP_IMAGE_99999999表挪一挪 
用neap用户登录
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
在session 或database级设10231 event，做全表扫描时，可以跳过soft corrupt block坏块
CREATE TABLE Z_NEAP_BATCH_99999999_new AS SELECT * FROM Z_NEAP_BATCH_99999999;
CREATE TABLE Z_NEAP_BI_99999999_new AS SELECT * FROM Z_NEAP_BATCH_IMAGE_99999999;
都没有暂停，so这2张表没有问题。
select table_name,partition_name from user_tab_partitions where table_name='Z_NEAP_IMAGE_99999999'
没有分区！
取表script
set long 1000000 linesize 1000
column ddl format a1000
select dbms_metadata.get_ddl('TABLE','Z_NEAP_IMAGE_99999999') as DDL from dual where rownum=1;
想查看数据量
select count(*) from Z_NEAP_IMAGE_99999999
                     *
ERROR at line 1:ORA-01110: 数据文件 18: '/u01/neap/data/9999/9999/neap_data_99999999_04.dbf'

count(*)都不行，那就直接rowid方式，
4.重建坏块对象ROWID保存数据
估算每个数据块中的行数，一般可通过dba_tables.avg_row_len进行估算
若估算值比实际值低，会造成数据丢失；若估算值远大于实际值，会造成执行效率低。一般采用估算值的两倍作为ROWSPERBLOCK的取值
select table_name,avg_row_len,tablespace_name from user_tables where table_name='Z_NEAP_IMAGE_99999999';
查出为空。那么就估算为300。
建一个临时表
create table T_Z_NEAP_IMAGE_99999999 tablespace neap_data_99999999 as select * from Z_NEAP_IMAGE_99999999 where 1=2;
查下原表的大小。
select sum(bytes/1024/1024) MB from dba_segments where SEGMENT_NAME='Z_NEAP_IMAGE_99999999';
2240M
这个表带lob字段，所以还要查lob大小
select SEGMENT_NAME from dba_lobs where table_NAME='Z_NEAP_IMAGE_99999999';
SYS_LOB0000118278C00005$$
select sum(bytes/1024/1024/1024) GB from dba_segments where SEGMENT_NAME='SYS_LOB0000118278C00005$$';
170 G  好大，好慢
把那个临时表建上索引和约束
CREATE UNIQUE INDEX IDX_T_Z_NEAP_IMAGE_99999999 ON
        T_Z_NEAP_IMAGE_99999999(IMAGE_ID, CREATE_DATE) TABLESPACE neap_data_99999999;
        ALTER TABLE T_Z_NEAP_IMAGE_99999999 ADD CONSTRAINT PK_T_Z_NEAP_IMAGE_99999999  PRIMARY KEY (IMAGE_ID, CREATE_DATE)
            USING INDEX IDX_T_Z_NEAP_IMAGE_99999999;

sqlplus '/as sysdba'
@recreaterowid.sql
。。。
。。。
------------------20131227  挺慢的，现在还在等着，周一看吧
------------------20131230  看到空间爆了。。哎 ，而且进度非常缓慢，可能是跑时间长了操作系统把它优先级降低了？
ORA-01578: ORACLE 数据块损坏 (文件号 18, 块号 840704)
掐断了，
select relative_fno, block_id, block_id+blocks-1 totblocks            
           from dba_extents            
           where owner = 'NEAP'              
             and segment_name = 'Z_NEAP_IMAGE_99999999'  
          order by extent_id;
结果中relative_fno值分布为15-22。按这个修改脚本为8个并发。 明天再看
----------------20140102 今天跑完了。后续操作：
检查新表确认无坏块：
analyze table neap.TT_Z_NEAP_IMAGE_99999999 validate structure cascade online;
select * from neap.TT_Z_NEAP_IMAGE_99999999;
保存原表的索引及约束定义，将原表名改为一个临时表名：
        alter table Z_NEAP_IMAGE_99999999 rename to C_Z_NEAP_IMAGE_99999999;
删除原表的索引和约束定义：
Alter table C_Z_NEAP_IMAGE_99999999 drop constraint PK_Z_NEAP_IMAGE_99999999;
        Drop index IDX_Z_NEAP_IMAGE_99999999;        
将新建表改名为原表名：
        alter table TT_Z_NEAP_IMAGE_99999999 rename to Z_NEAP_IMAGE_99999999;
重建新建表的索引和约束定义：
        Alter table Z_NEAP_IMAGE_99999999 drop constraint PK_TT_Z_NEAP_IMAGE_99999999;
        Drop index IDX_TT_Z_NEAP_IMAGE_99999999;
        CREATE UNIQUE INDEX IDX_Z_NEAP_IMAGE_99999999 ON
        Z_NEAP_IMAGE_99999999(IMAGE_ID, CREATE_DATE) TABLESPACE neap_data_99999999;
        ALTER TABLE Z_NEAP_IMAGE_99999999 ADD CONSTRAINT PK_Z_NEAP_IMAGE_99999999  PRIMARY KEY (IMAGE_ID, CREATE_DATE)   USING INDEX IDX_Z_NEAP_IMAGE_99999999;
业务验证

------------------------------我是最关键的rowid脚本--------------------------------------------------
recreaterowid.sql内容：
set serveroutput on
set concat off        
DECLARE   nrows number;
 rid rowid;
 dobj number;
 ROWSPERBLOCK number; BEGIN
 ROWSPERBLOCK:=300;
 nrows:=0;
 select data_object_id  into dobj  
 from dba_objects  
 where owner = 'NEAP'  
 and object_name = 'Z_NEAP_IMAGE_99999999'  
 ;

 for i in (select relative_fno, block_id, block_id+blocks-1 totblocks            
           from dba_extents            
           where owner = 'NEAP'              
             and segment_name = 'Z_NEAP_IMAGE_99999999' and relative_fno=15  ---15~22分成了8个脚本
          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 NEAP.T_Z_NEAP_IMAGE_99999999      
      select /*+ ROWID(A) */ *      
      from NEAP.Z_NEAP_IMAGE_99999999 A  
      where rowid = rid;                
      if sql%rowcount = 1 then nrows:=nrows+1; end if;
      if (mod(nrows,1000)=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;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7590112/viewspace-1065093/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7590112/viewspace-1065093/