创建测试数据

SQL> delete t;

50351 rows deleted.

SQL> alter system checkpoint;（如果故障前发生了检查点，未提交的数据写入数据文件，这种情况下会出现不一致）

System altered.

SQL> shutdown abort;
ORACLE instance shut down.

破坏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

ITPub博客

undo损坏测试

最新文章