数据库出现undo表空间文件损坏，重建undo存在事务报错
Oracle 11.2.0.3 Linux 5 64bit数据库出现undo表空间文件损坏，无法打开数据库。SQL> startup
Oracle instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 4098
Session ID: 1 Serial number: 5
尝试重建undo后发现继续报错，怎么办！！ 本帖最后由 biotwang 于 2017-8-17 15:03 编辑
对于Undo损坏造成数据库无法打开的问题，面对不同情况要不同对待。
undo损坏恢复（无事务影响）sql> startup nomount;
sql> create pfile='/tmp/pfile' from spfile;修改pfile*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
#*.undo_tablespace='UNDOTBS02'
#*.undo_management='AUTO'启动数据库，Undo使用的是system下的rollback segemtsstartup pfile='/tmp/pfile' mount;
alter database datafile 2 offline drop;
alter database open;drop tablespace undotbs1 including contents;-- 此时会报错并告知相关那个回滚段正处于使用中
查看回滚段并删除select segment_name,status from dba_rollback_segs;
drop rollback segment "_SYSSMUx"$;
drop tablespace undotbs1 including contents;重建undoCREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/opt/oracle/oradata/PROD1/UNDOTBS02.dbf' SIZE 50M
shutdown immediate;修改pfile，使用自动undo管理#*.undo_management='MANUAL'
#*.undo_tablespace='SYSTEM'
*.undo_tablespace='UNDOTBS02'
*.undo_management='AUTO'startup pfile='/tmp/pfile';
create spfile from pfile='/tmp/pfile'; 本帖最后由 biotwang 于 2017-8-17 16:46 编辑
undo损坏恢复 （有事务影响）
** 接上面的例子，正在使用UNDOTBS02, 此UNDOTBS02损坏startup nomount;
create pfile='/tmp/pfile' from spfile;修改pfile*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
startup pfile='/tmp/pfile' mount;undo数据文件不用离线也可以打开，若不行则采用下面的方法alter database datafile '/opt/oracle/oradata/PROD1/UNDOTBS01.dbf' offline drop;
alter database open;
drop tablespace undotbs1;
CREATE UNDO TABLESPACE UNDOTBS01
DATAFILE '/opt/oracle/oradata/PROD1/UNDOTBS01.dbf' SIZE 50M;查看是否有回滚段被创建，处于offline状态，若应该都正常online了。select segment_name,status from dba_rollback_segs;
shutdown immediate;修改pfile：*.undo_tablespace='UNDOTBS01'
startup pfile='/tmp/pfile'使数据文件先离线，如果开始已经离线，这里不需要处理–- alter tablespace undotbs1 offline normal;
select segment_name,status from dba_rollback_segs;
drop rollback segment "_SYSSMUx$";
drop tablespace UNDOTBS02 including contents and datafiles;
select * from v$recover_file; –-验证是否还有文件需要恢复
shutdown immediate
修改参数文件#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
startup pfile='/tmp/pfile';
create spfile from pfile='/tmp/pfile';
页:
[1]