Oracle 无备份情况下undo文件损坏处理

SQL> select ename,sal from scott.emp where deptno=10; ENAME   SAL ---------- ---------- CLARK 2450 KING 5000 MILLER 1300 SQL> show undo SP2-0158: unknown SHOW option "undo" SQL> show parameter undo NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management      string AUTO undo_retention      integer 900 undo_tablespace      string UNDOTBS2 SQL> create undo tablespace undotbs3 datafile '/u01/oracle/oradata/ora10g/undotbs03.dbf' size 20M; Tablespace created. SQL> alter system set undo_tablespace=undotbs3; System altered. 开新会话 产生事务 SQL> select XIDUSN,XIDSLOT,XIDSQN,name from v$transaction;     XIDUSN    XIDSLOT   XIDSQN NAME ---------- ---------- ---------- -------------------------------------------------- 9     0      300 SQL> select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=9; SEGMENT_NAME   TABLESPACE_NAME    FILE_ID ------------------------- --------------- ---------- _SYSSMU9$   UNDOTBS3    2 SQL> ! rm -rf /u01/oracle/oradata/ora10g/undotbs03.dbf SQL> shut abort   也可以不停库 使用alter database datafile 2 offline;来触发数据库认识到数据文件损坏    再使用alter database create datafile 2;这样不用停库 事务最后能保持住. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  285212672 bytes Fixed Size     1218992 bytes Variable Size    83887696 bytes Database Buffers   197132288 bytes Redo Buffers     2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/oracle/oradata/ora10g/undotbs03.dbf' SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;      FILE# CHECKPOINT_CHANGE# REC FUZ ---------- ------------------ --- --- 1        513395 NO  YES 2     0 3        513395 NO  YES 4        513395 NO  YES 5        513395 NO  YES 6        513395 NO  YES 6 rows selected. SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;      FILE# STATUS     CHECKPOINT_CHANGE# NAME ---------- ---------- ------------------ -------------------------------------------------- 1 SYSTEM   513395 /u01/oracle/oradata/ora10g/system01.dbf 2 ONLINE   513572 /u01/oracle/oradata/ora10g/undotbs03.dbf 3 ONLINE   513395 /u01/oracle/oradata/ora10g/sysaux01.dbf 4 ONLINE   513395 /u01/oracle/oradata/ora10g/users01.dbf 5 ONLINE   513395 /u01/oracle/oradata/ora10g/mytest01.dbf 6 ONLINE   513395 /u01/oracle/oradata/ora10g/undotbs02.dbf 6 rows selected. SQL> set line 110 SQL> select status,CHECKPOINT_CHANGE#,name,creation_change# from v$datafile;  STATUS     CHECKPOINT_CHANGE# NAME CREATION_CHANGE# ---------- ------------------ -------------------------------------------------- ---------------- SYSTEM   513395 /u01/oracle/oradata/ora10g/system01.dbf    9 ONLINE   513572 /u01/oracle/oradata/ora10g/undotbs03.dbf    513571 ONLINE   513395 /u01/oracle/oradata/ora10g/sysaux01.dbf    6609 ONLINE   513395 /u01/oracle/oradata/ora10g/users01.dbf    10566 ONLINE   513395 /u01/oracle/oradata/ora10g/mytest01.dbf    459576 ONLINE   513395 /u01/oracle/oradata/ora10g/undotbs02.dbf    492642 6 rows selected. 依据控制文件的信息 重建这个文件 SQL> alter database create datafile '/u01/oracle/oradata/ora10g/undotbs03.dbf'; Database altered. SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;      FILE# CHECKPOINT_CHANGE# REC FUZ ---------- ------------------ --- --- 1        513395 NO  YES 2        513571 YES NO 3        513395 NO  YES 4        513395 NO  YES 5        513395 NO  YES 6        513395 NO  YES 6 rows selected. select * from v$recover_file;file;      FILE# ONLINE  ONLINE_ ERROR    CHANGE# TIME ---------- ------- ------- -------------------- ---------- --------- 2 ONLINE  ONLINE     513571 25-FEB-11 SQL>  需要 5号日志进行恢复 SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh; FILENUMBER    STATUS SCN      SEQUENCE ---------- -------------- ---------------- ---------- 1      8196 513395     5 2 0 513571     5 3 4 513395     5 4 4 513395     5 5 4 513395     5 6 4 513395     5 6 rows selected. SQL> recover datafile 2; Media recovery complete. SQL> alter database open; Database altered. SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;      FILE# CHECKPOINT_CHANGE# REC FUZ ---------- ------------------ --- --- 1        533737 NO  YES 2        533737 NO  YES 3        533737 NO  YES 4        533737 NO  YES 5        533737 NO  YES 6        533737 NO  YES 6 rows selected. SQL> 数据库shut abort过 事务被回退 SQL> select ename,sal from scott.emp where deptno=10; ENAME   SAL ---------- ---------- CLARK 2450 KING 5000 MILLER 1300 SQL>