今天碰到一个变态的错误,由于在生产库上执行了大的事务,导致出现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