undo数据文件损坏 ORA-01157

碰到多次undo数据文件损坏，或者误删undo数据文件的，且undo表空间存在需要恢复的回滚段（active rollback segment）的情况，这时就只能查出需要恢复的回滚段，然后使用_CORRUPTED_ROLLBACK_SEGMENTS参数跳过，再切换到新的UNDO表空间。
下面是误删undo数据文件的测试，启动数据库报错ORA-01157、ORA-01110
[code]SQL> startup
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 306186652 bytes
Database Buffers 104857600 bytes
Redo Buffers 6103040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/oracle/oradata/orasql/datafile/undotbs01.dbf'[/code]
离线undotbs01.dbf文件，打开数据库
[code]SQL> alter database datafile 3 offline;
alter database datafile 3 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


Elapsed: 00:00:00.32
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: '/oracle/oradata/orasql/datafile/undotbs01.dbf'
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/oracle/oradata/orasql/datafile/undotbs01.dbf'
SQL> alter database datafile 3 offline drop;

Database altered.

Elapsed: 00:00:00.06
SQL> alter database open;

Database altered.

Elapsed: 00:00:04.05[/code]
打开数据库后，操作报错ORA-00604、ORA-00376、ORA-01110
[code]SQL> drop user tt_test cascade;
drop user tt_test cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oracle/oradata/orasql/datafile/undotbs01.dbf'[/code]
创建新的undo表空间，并设为默认的undo表空间
[code]SQL> create undo tablespace undotbs2 datafile '/u01/oradata/undo1.dbf' size 200m;

Tablespace created.

Elapsed: 00:00:17.92
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SQL> drop tablespace undotbs including contents and datafiles;
drop tablespace undotbs including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_2432923906$' found, terminate dropping tablespace[/code]
查出NEEDS RECOVERY的rollback segment name，创建初始化参数文件，关闭数据库
[code]SQL> drop tablespace undotbs including contents and datafiles;
drop tablespace undotbs including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_2432923906$' found, terminate dropping tablespace


Elapsed: 00:00:00.01

SQL> create pfile='/u01/oradata/init.ora' from spfile;

File created.

Elapsed: 00:00:00.07



SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1_2432923906$ NEEDS RECOVERY
_SYSSMU2_2440578203$ NEEDS RECOVERY
_SYSSMU3_2803030565$ NEEDS RECOVERY
_SYSSMU4_1515338583$ NEEDS RECOVERY
_SYSSMU5_1734532521$ NEEDS RECOVERY
_SYSSMU6_2751457965$ NEEDS RECOVERY
_SYSSMU7_941188636$ NEEDS RECOVERY
_SYSSMU8_51336753$ NEEDS RECOVERY
_SYSSMU9_2607541442$ NEEDS RECOVERY
_SYSSMU10_541967353$ NEEDS RECOVERY

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU11_1390905751$ OFFLINE
_SYSSMU12_3487291074$ OFFLINE
_SYSSMU13_3877357141$ OFFLINE
_SYSSMU14_1628962174$ OFFLINE
_SYSSMU15_2988144322$ OFFLINE
_SYSSMU16_1060335529$ OFFLINE
_SYSSMU17_1562306091$ OFFLINE
_SYSSMU18_3054388210$ OFFLINE
_SYSSMU19_251999627$ OFFLINE
_SYSSMU20_524750302$ OFFLINE
_SYSSMU21_293166800$ OFFLINE

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU22_3057082303$ OFFLINE

23 rows selected.

Elapsed: 00:00:00.08

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.[/code]

修改init.ora，添加如下（括号中的为active rollback segment）
[code]undo_management=manual
undo_retention=10800
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1_2432923906$,_SYSSMU2_2440578203$,_SYSSMU3_2803030565$,_SYSSMU4_1515338583$,_SYSSMU5_1734532521$,_SYSSMU6_2751457965$,_SYSSMU7_941188636$,_SYSSMU8_51336753$,_SYSSMU9_2607541442$,_SYSSMU10_541967353$)[/code]
使用初始化参数文件打开数据库，删除旧的
[code]SQL> startup pfile='/u01/oradata/init.ora' ;
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 306186652 bytes
Database Buffers 104857600 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:00.72
SQL>


SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 10800
undo_tablespace string UNDOTBS2[/code]

还原init.ora，并重启数据库
[code]*.undo_tablespace='UNDOTBS2'
undo_management=auto
undo_retention=10800
~
SQL> startup force pfile = '/u01/oradata/init.ora';
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 306186652 bytes
Database Buffers 104857600 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS2[/code]