实战坏块

今天碰到一个变态的错误，由于在生产库上执行了大的事务，导致出现ORA-01555

一开始一直以为是撤销表空间的问题，撤销表空间重建后，问题依旧

这个错语太具有迷惑性了：

SQL> select * from tecdslh where wsbid=5727083;

select * from tecdslh where wsbid=5727083

              *

ERROR 位于第 1 行:

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$"

too small

后来检查到，其它表SELECT没有问题，select * from tecdslh在PLSQL DEVELOPER中也不报错，好像只有在

TECDSLH上的某个块时，就报错

2.检查对象

SQL> set serveroutput on

declare

cc number;

begin

     dbms_repair.check_object(schema_name => 'GISYN',

                              object_name => 'TECDSLH',

                              corrupt_count => cc);

     dbms_output.put_line(a => to_char(cc));

end;

/

3.如果MARKED_CORRUPT为FALSE的话，需要使用fix_corrupt_blocks语句标识一下

declare

cc number;

begin

  dbms_repair.fix_corrupt_blocks(schema_name => 'GISYN',object_name => 'TECDSLH',fix_count => cc);

  dbms_output.put_line(a => to_char(cc));

end;

/

SELECT object_name, relative_file_id, block_id,marked_corrupt, corrupt_description, 

repair_description,                      

 CHECK_TIMESTAMP from repair_table; 

MARKED_CORRUPT为TRUE

4.如果skip_corrupt_blocks后，RMAN好像无法恢复了，必须要使用RMAN来恢复

SQL> exec dbms_repair.skip_corrupt_blocks(schema_name => 'DLINGER',object_name => 'TEST',flags => 

1);

5.RMAN恢复

rman target sys/sysgiszd@gis

RMAN> run {

2> allocate channel c1 type 'sbt_tape' connect 'sys/sysgiszd@gis' parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_gis.opt)';

3> blockrecover datafile 8 block 230317;

4> release channel c1;

5> }

RMAN> run {

2> allocate channel c1 type 'sbt_tape' connect 'sys/sysgiszd@gis' parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_gis.opt)';

3> blockrecover datafile 5 block 3351167;

4> release channel c1;

5> }

由于skip_corrupt_blocks后无法恢复

released channel: c1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of blockrecover command at 08/26/2009 20:25:08

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN> exit