undo空间出现问题的处理
一、数据库正常关闭immediate或normal
①、创建pfile并使用pfile启动数据库
startup nomount
create pfile= from spfile;
shutdown abort
startup mount restrict pfile=
②、尝试恢复文件
select name,file#,status from v$datafile;
recover datafile ;
③、创建新的undo表空间替换损坏的undo表空间
alter database datafile offline drop;
alter database open;
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
shutdown immediate;
④、修改pfile:
undotablespace=UNDOTBS2
⑤、删除损坏undo表空间并创建新的SPFILE
startup pfile=
drop tablespace UNDOTBS1 including contents and datafile;
create spfile from pfile=''shshutdown immediate
startup
二、abort或crash
①、创建pfile
startup nomount
create pfile= from spfile;
②、修改pfile
undo_tablespace='SYSTEM'
undo_management='MANUAL'
*._allow_resetlogs_corruption=true
(注:允许在数据库文件SCN不一致的情况下启动数据库)
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
(注:允许在rollback segments损坏的情况下启动数据库)
(注:rollback的名称要与已经损坏的undo表空间的rollback segment号保持一致,不然导致无法删除损坏的undo表空间)
③、使用修改后pfile启动数据库
startup mount restrict pfile=''
④、修改损坏数据文件状态
select name,file#,status from v$datafile;
alter database datafile offline drop;
④、打开数据库
alter database open;
⑤、并创建新的undo表空间并关闭数据库
CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE '/u02/oracle/oradata/ora9i/undotbs03.dbf' size 10m autoextend on next 2m maxsize 3000m;
⑥、修改pfile
undo_tablespace='UNDOTBS2'
undo_management='AUTO'
⑦、启动数据库并删除损坏undo表空间
startup restrict pfile=
修改损坏undo表空间状态
alter tablespace undotbs1 offline immediate;
删除损坏undo表空
drop tablespace UNDOTBS1 including contents and datafile;
三、online
①、创建新的undo表空间
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
②、改变默认undo表空间
ALTER SYSTEM SET UNDO_TABLESPACE='';
③、删除损坏undo表空间
DROP TABLESPACE INCLUDING CONTENTS AND DATAFILE;
四、相关视图:
v$datafile
v$rollname
v$rollstat
v$tablespace
dba_rollback_segs
dba_data_files
dba_talbespaces
dba_segments
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29371470/viewspace-1098685/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29371470/viewspace-1098685/