undo 恢复场景四之数据库abort后undo 部分坏损坏处理办法

--session 1:
sqlplus scott/oracle
drop table t1;
create table t1 (id int);
insert into t1 select object_id from dba_objects where rownum <=1000;
commit;
insert into t1 select object_id from dba_objects where rownum <=1000;
--此时t1 里面有2000条数据，其中1000条未提交

--查看活动回话对应的undo地址，损坏掉
set linesize  1000
col username for a20
SELECT s.sid, s.serial#, s.username, 
       r.name AS rollback_segment,
       t.used_ublk, t.used_urec
FROM v$session s, v$transaction t, v$rollname r
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn;

       SID    SERIAL# USERNAME             ROLLBACK_SEGMENT                USED_UBLK  USED_UREC
---------- ---------- -------------------- ------------------------------ ---------- ----------
       498      50601 SCOTT                _SYSSMU10_930580995$                    1          6
	   
	   
	   
	   
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where segment_name='_SYSSMU10_930580995$';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        272          8
         1          4        288          8
         2          4        896        128

--session 2:故意破坏 4,272 block
copy file 4 block 273 to file 4 block 272
sum apply

--强制重启库
--session 3:
sqlplus / as SYSDBA
startup force


Total System Global Area 4680840344 bytes
Fixed Size                  8905880 bytes
Variable Size             872415232 bytes
Database Buffers         3791650816 bytes
Redo Buffers                7868416 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 4, block # 272)
ORA-01110: data file 4: '/oradata/ORCL/undotbs01.dbf'
Process ID: 99682
Session ID: 498 Serial number: 16206


--屏蔽掉问题回滚掉就行
alter system set "_offline_rollback_segments"='_SYSSMU1_1261223759$' scope=spfile;

--如果定位不到问题回滚段只能当undo丢失处理：
alter  database  datafile 4 offline  drop;

alter database open;

set linesize  1000
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;


SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU1_1261223759$           PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2_27624015$             PUBLIC UNDOTBS1                       ONLINE
_SYSSMU3_2421748942$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU4_625702278$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU5_2101348960$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU6_813816332$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU7_2329891355$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU8_399776867$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU9_1692468413$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU10_930580995$           PUBLIC UNDOTBS1                       ONLINE


oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
oradebug tracefile_name
alter database open;



--删除有问题的回滚段
drop rollback segment "_SYSSMU1_1261223759$";

alter system set "_offline_rollback_segments"='' scope=spfile;
shutdown immediate;
startup;
SQL> select count(1) from scott.t1;

  COUNT(1)
----------
      2000

--由于回滚段损坏，之前未提交的数据没回滚调



--屏蔽所有回滚段
--末尾记得加$
dd if=/oradata/ORCL//system01.dbf of=dd.undo bs=8192 skip=224 count=10
strings dd.undo| grep _SYSSMU | cut -d $ -f 1 | sort -u