time: 2008/04/15
author: skate
参考文档: http://blog.chinaunix.net/u/7667/showart_163271.html
undo表空间故障恢复
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'
1.
将undo_management修改为manual
SQL> alter system set undo_management ="manual" scope=spfile;
System altered.
2.
生成pfile, 向pfile中添加_offline_rollback_segments=true, 以修改过的pfile起动系统. 删除原来的undo tablespace, 增加新的undo tablespace
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
File created.
SQL> ! vi initorcl.ora
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/initorcl.ora';
ORACLE instance started.
Total System Global Area 147920392 bytes
Fixed Size 452104 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1
2 ;
Tablespace dropped.
SQL> create undo tablespace undotbs2
2 datafile '/home/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on next 10m;
Tablespace created.
3.
重启系统, 这次起动是用的是spfile, 不包含_offline_rollback_segments=true, 修改 undo_management和undo_tablespaces初试化参数, 并检查系统.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 147920392 bytes
Fixed Size 452104 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace="UNDOTBS2" scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 147920392 bytes
Fixed Size 452104 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> SELECT SEGMENT_NAME,tablespace_name,status FROM DBA_ROLLBACK_SEGS;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS2 ONLINE
_SYSSMU2$ UNDOTBS2 ONLINE
_SYSSMU3$ UNDOTBS2 ONLINE
_SYSSMU4$ UNDOTBS2 ONLINE
_SYSSMU5$ UNDOTBS2 ONLINE
_SYSSMU6$ UNDOTBS2 ONLINE
_SYSSMU7$ UNDOTBS2 ONLINE
_SYSSMU8$ UNDOTBS2 ONLINE
_SYSSMU9$ UNDOTBS2 ONLINE
_SYSSMU10$ UNDOTBS2 ONLINE
11 rows selected.
-----end-----