undo表空间故障和ORA-01548处理

现场无法保留处理日志记录下来，记忆中写出来，给自己一个思路的提示一个开发库启动时候报undo出现问题，环境是Oracle 10.2.0.5和aix 5308启动数据库，提示undo损坏
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: "/u01/app/oracle/oradata/YONGDB/undotbs01.dbf"查看：
select file#,name,status from v$datafile;
结果: undotbs1 表空间的状态是media recover.
因此先删除undo的数据文件，打开数据库SQL> alter database datafile "/oradata/gyldb/undotbs01.dbf" offline for drop;Database altered.SQL> alter database open;Database altered.创建新的undo表空间SQL> create undo tablespace UNDOTBS2 datafile "/oradata/gyldb/undotbs021.dbf" size 500m;Tablespace created.SQL> alter system set undo_tablespace=UNDOTBS2;System altered.然后删除有问题的undo表空间
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment "_SYSSMU1_3638931391$" found, terminate
dropping tablespace
select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segscreate pfile="/home/oracle/yd.ora" from spfile
关闭数据库，修还pfile，在加上
_CORRUPTED_ROLLBACK_SEGMENTS=（_SYSSMU2_3033359625$",_SYSSMU9_2087597455$,_SYSSMU8_2755301871$,_SYSSMU7_700714424$,_SYSSMU6_3548494004$,_SYSSMU5_1738828719$ ,_SYSSMU4_286801445$,_SYSSMU3_2670780772$,_SYSSMU2_3033359625$）
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment "_SYSSMU10_3267518184$" found, terminate dropping tablespace 继续检查SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------
SYSTEM SYSTEM ONLINE
_SYSSMU10_3267518184$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU24_1022883919$ UNDOTBS2 ONLINE
_SYSSMU23_1498951573$ UNDOTBS2 ONLINE
_SYSSMU22_1897326838$ UNDOTBS2 ONLINE
_SYSSMU21_3263997952$ UNDOTBS2 ONLINE
_SYSSMU16_4093610673$ UNDOTBS2 ONLINE
_SYSSMU15_758499186$ UNDOTBS2 ONLINE
_SYSSMU14_3461607053$ UNDOTBS2 ONLINE
_SYSSMU13_3534707649$ UNDOTBS2 ONLINE
_SYSSMU12_2563089079$ UNDOTBS2 ONLINESEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------
_SYSSMU11_3918295226$ UNDOTBS2 ONLINE重新关闭，在pfile后面继续添加：
_OFFLINE_ROLLBACK_SEGMENTS=（_SYSSMU10_3267518184$）或者尝试
ALTER ROLLBACK SEGMENT "_SYSSMU10_3267518184$" OFFLINE;
DROP ROLLBACK SEGMENT "_SYSSMU10_3267518184$";
重新以pfile启动后删除
drop tablespace undotbs1 including contents and datafiles;然后创建新的默认的undotbs1create undo tablespace UNDOTBS1 datafile "/oradata/gyldb/undotbs01.dbf" size 10000m reuse; alter system set undo_tablespace=UNDOTBS1;以下略问题遂解决Oracle一致性读总结Oracle安全管理之权限管理相关资讯 undo表空间 ORA-01548

如何删除回滚段状态为NEEDS （09/02/2015 19:28:49）
undo表空间概述（02/24/2015 20:32:43）
监控和管理Oracle UNDO表空间的使（01/29/2015 11:20:57）

undo表空间使用率（07/23/2015 16:29:56）
Oracle 11gR2 Database UNDO表空间（01/29/2015 11:30:59）
在线扩大数据库UNDO表空间（07/17/2014 08:10:22）

本文评论查看全部评论（0）