undo表空间故障恢复

时间:2022-04-03 12:40:49

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-----