ORA-27041 ORA-01110 ORA-01116 物理删除undo后报错

测试数据库，非归档模式。模拟误删undo数据文件的处理方法。
先连接test用户，创建表、插入数据，然后 rm  删除 undo 的物理文件。
然后尝试删除刚创建的表a
SQL> drop table a;
drop table a
           *
ERROR at line 1:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01116: 打开数据库文件 3 时出错
ORA-01110: 数据文件 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select t.tablespace_name,t.status from dba_tablespaces t where t.tablespace_name='UNDOTBS1';


TABLESPACE_NAME                                        STATUS
------------------------------------------------------------ ------------------
UNDOTBS1                                                     ONLINE

SQL> select name, status from v$datafile;


NAME                                                         STATUS
------------------------------------------------------------ --------------
...
/app/oradata/datafile/undotbs01.dbf                          ONLINE

...

SQL> shutdown immediate
ORA-01116: 打开数据库文件 3 时出错
ORA-01110: 数据文件 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter database datafile '/app/oradata/datafile/undotbs01.dbf' offline drop;  
alter database datafile '/app/oradata/datafile/undotbs01.dbf' offline drop
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
进程 ID: 22892
会话 ID: 9 序列号: 6877

SQL> create undo tablespace undotbs2 datafile '/app/oradata/datafile/undotbs02.dbf' size 1024m;
create undo tablespace undotbs2 datafile '/app/oradata/datafile/undotbs02.dbf' size 1024m
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/app/oradata/datafile/undotbs01.dbf'
进程 ID: 23015
会话 ID: 12 序列号: 5

SQL> conn /as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area 4175568896 bytes
Fixed Size                  2234960 bytes
Variable Size            2852128176 bytes
Database Buffers         1308622848 bytes
Redo Buffers               12582912 bytes
Database mounted.
Database opened.

可以正常启动。

SQL> select name, status from v$datafile;


NAME                                                         STATUS
------------------------------------------------------------ --------------
...
/app/oradata/datafile/undotbs01.dbf                          RECOVER

...

SQL> drop table test;


Table dropped.


SQL> create table test as select * from dba_objects;
create table test as select * from dba_objects
                                   *
ERROR at line 1:
ORA-01552: 非系统表空间 'TEST_DB' 不能使用系统回退段


SQL> create undo tablespace undotbs2 datafile '/app/oradata/datafile/undotbs02.dbf' size 1024m;


Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2'; 


System altered.


SQL> show parameter undo


NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS2

SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: 已找到活动回退段 '_SYSSMU1_3780397527$', 终止删除表空间




SQL> alter database datafile '/app/oradata/datafile/undotbs01.dbf' offline drop;


Database altered.


SQL> drop rollback segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU1_3780397527$"
*
ERROR at line 1:
ORA-30025: 不允许删除段 '_SYSSMU1_3780397527$' (在还原表空间中)


SQL> create pfile from spfile;


File created.


在pfile里面，增加如下参数
_offline_rollback_segments=('_SYSSMU1_3780397527$')
_corrupted_rollback_segments=('_SYSSMU1_3780397527$')

重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/app/oracle/product/11.2.3/db_1/dbs/initjava11g.ora' 
ORACLE instance started.


Total System Global Area 4175568896 bytes
Fixed Size                  2234960 bytes
Variable Size            2852128176 bytes
Database Buffers         1308622848 bytes
Redo Buffers               12582912 bytes
Database mounted.
Database opened.
SQL> drop rollback segment "_SYSSMU1_3780397527$";


Rollback segment dropped.


采用如上方式，找到undo对应的segment，逐一删除
SQL> select segment_name from dba_segments where tablespace_name='UNDOTBS1';


SEGMENT_NAME
--------------------------------------------------------------------------------
_SYSSMU10_3550978943$
_SYSSMU9_1424341975$
_SYSSMU8_2012382730$
...

把上面的segment添加到前面pfile的参数里面
_offline_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$', ...)
_corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$', ...)
然后再次重启数据库
SQL> select 'drop rollback segment "'||segment_name||'";' from dba_segments where tablespace_name='UNDOTBS1';


'DROPROLLBACKSEGMENT"'||SEGMENT_NAME||'";'
--------------------------------------------------------------------------------
drop rollback segment "_SYSSMU9_1424341975$";
drop rollback segment "_SYSSMU8_2012382730$";
drop rollback segment "_SYSSMU7_3286610060$";

...

执行上面SQL的查询结果命令，删除undo的segment
SQL> 
Rollback segment dropped.


SQL> 
Rollback segment dropped.

...

最后，再次执行删除undo表空间
SQL> drop tablespace UNDOTBS1;


Tablespace dropped.


去掉pfile参数文件新加的两个参数，重启数据库即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-1075073/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-1075073/