创建测试数据

破坏UNDO数据文件

[oracle@racdg rac]$ mv undotbs1.dbf  undotbs1.dbf.bak

启动数据库

SQL> startup ORACLE instance started. Total System Global Area  314572800 bytes Fixed Size              2020448 bytes Variable Size             96471968 bytes Database Buffers       213909504 bytes Redo Buffers              2170880 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/oradata/rac/undotbs1.dbf'

简单offline drop虽然能打开数据库，但是创建新的UNDO表空间时，由于修改数据字典需要UNDO段，因此无法继续

SQL> alter database datafile '/u01/oradata/rac/undotbs1.dbf' offline drop; Database altered. SQL> alter database open; Database altered. SQL> create undo tablespace undo datafile '/u01/oradata/rac/undo.dbf' size 100M; create undo tablespace undo datafile '/u01/oradata/rac/undo.dbf' size 100M * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/oradata/rac/undotbs1.dbf'

将UNDO管理改为手动，重启数据库

SQL> alter system set undo_management = 'MANUAL' scope=spfile; System altered. SQL> alter system set rollback_segments = 'SYSTEM' scope=spfile; System altered.

SQL> alter system set undo_tablespace = '' scope=spfile; System altered.

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup; ORACLE instance started. Total System Global Area  314572800 bytes Fixed Size              2020448 bytes Variable Size             96471968 bytes Database Buffers       213909504 bytes Redo Buffers              2170880 bytes Database mounted. Database opened.

创建新UNDO表空间，修改参数

create undo tablespace undo datafile '/u01/oradata/rac/undo.dbf' size 100M;

alter system set undo_management = 'AUTO' scope=spfile;

alter system set rollback_segments = '' scope=spfile;

alter system set undo_tablespace = 'UNDO' scope=spfile;

重启数据库....

此时查询，还是会找之前的undo文件

SQL> select count(*) from t; select count(*) from t                      * ERROR at line 1: ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oradata/test/undotbs01.dbf'

删除原来的UNDO表空间会提示还有活动的UNDO段

SQL> drop tablespace UNDOTBS1; drop tablespace UNDOTBS1 * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU1_1240252155$' found, terminate dropping tablespace

通过隐含参数将老UNDO表空间的undo段标记为损坏

SQL> set long 999

SQL>  select '*._corrupted_rollback_segments=''' || replace(wm_concat(segment_name), ',', ''',''') || '''' from DBA_ROLLBACK_SEGS where tablespace_name = 'UNDOTBS1'; '*._CORRUPTED_ROLLBACK_SEGMENTS='''||REPLACE(WM_CONCAT(SEGMENT_NAME),',',''',''' -------------------------------------------------------------------------------- *._corrupted_rollback_segments='_SYSSMU1_1240252155$','_SYSSMU2_111974964$','_SY SSMU3_4004931649$','_SYSSMU4_1126976075$','_SYSSMU5_4011504098$','_SYSSMU6_36541 94381$','_SYSSMU7_4222772309$','_SYSSMU8_3612859353$','_SYSSMU9_3945653786$','_S YSSMU10_3271578125$'

将查询结果整理一下，变成一行，然后添加到pfile里

SQL> create pfile from spfile; File created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit

修改pfile。。。

SQL> startup pfile='/u01/app/oracle/product/11.2/db_1/dbs/inittest.ora'; ORACLE instance started. Total System Global Area  346562560 bytes Fixed Size              2228264 bytes Variable Size            264245208 bytes Database Buffers        75497472 bytes Redo Buffers              4591616 bytes Database mounted. Database opened.

可惜的是，由于做了检查点，因此delete操作被记录到数据文件里，没法回滚 SQL> select count(*) from t;   COUNT(*) ----------      0