UNDO表空间丢失恢复一例(续)

在<<undo表空间丢失恢复一例>>中, 提出了undo表空间丢失的步骤, 实验以后, 没有做下一步的分析, 今天试图建立表空间时提示如下错误:

SQL> create tablespace aqua2 datafile '/home/oracle/oradata/orcl/aqua02.dbf'
  2  size 200m autoextend on next 50m;
create tablespace aqua2 datafile '/home/oracle/oradata/orcl/aqua02.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'

查看系统rollback segment 内容如下:

QL> select segment_name,file_id,status from dba_rollback_segs
  2  ;

SEGMENT_NAME                      FILE_ID STATUS
------------------------------ ---------- ----------------
SYSTEM                                  1 ONLINE
_SYSSMU1$                               2 OFFLINE
_SYSSMU2$                               2 OFFLINE
_SYSSMU3$                               2 OFFLINE
_SYSSMU4$                               2 OFFLINE
_SYSSMU5$                               2 OFFLINE
_SYSSMU6$                               2 OFFLINE
_SYSSMU7$                               2 OFFLINE
_SYSSMU8$                               2 OFFLINE
_SYSSMU9$                               2 OFFLINE
_SYSSMU10$                              2 OFFLINE

11 rows selected.

将undo_management修改为manual

SQL> alter system set undo_management ="manual" scope=spfile;

System altered.

生成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.

重启系统, 这次起动是用的是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 undomanagement="auto" scope=spfile;
alter system set undomanagement="auto" scope=spfile
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> c/undo/undo_/
  1* alter system set undo_management="auto" scope=spfile
SQL> /

System altered.

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.

现在可正常创建表空间了:

SQL> create tablespace aqua2 datafile '/home/oracle/oradata/orcl/aqua02.dbf'
  2  size 200m autoextend on next 50m;

Tablespace created.

反思:

1 在<<undo表空间丢失恢复一例>>中提出"建立新的undo表空间,或者向原有表空间增加数据文件.", 现在看来, 需要新建立一个undo表空间才可以. 增加数据文件虽然可以保证数据库可以起来, 但是却不能正常运作.

2 这次操作中添加了_offline_rollback_segments=true, 这句话可能是无效的, 并不需要.

3 这次事故给我一个教训: 对系统修改完毕后, **必须**进行完整的测试, 尤其是一个生产系统. 一个数据库可以正常起动, 并不意味着他可以正常的提供服务.