测试数据库,非归档模式。模拟误删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
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$')
先连接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
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;
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
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
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
...
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.
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
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> 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参数文件新加的两个参数,重启数据库即可。
SQL>
Rollback segment dropped.
SQL>
Rollback segment dropped.
...
最后,再次执行删除undo表空间
SQL> drop tablespace UNDOTBS1;
Tablespace dropped.
去掉pfile参数文件新加的两个参数,重启数据库即可。