ITPub博客

首页 > 数据库 > Oracle > UNDO表空间数据文件丢失处理(二)正常关闭数据库

UNDO表空间数据文件丢失处理(二)正常关闭数据库

原创 Oracle 作者:csbin 时间:2014-02-27 22:17:53 0 删除 编辑

1.1  模拟脚本

host rm -rf /opt/oracle/oradata/orcl/*

host cp /opt/oracle/oradata/orcl_bk/* /opt/oracle/oradata/orcl/

host cp /opt/oracle/oradata/orcl/control01.ctl /opt/oracle/fast_recovery_area/orcl/control02.ctl

host cp /opt/oracle/oradata/spfileorcl.ora /opt/oracle/product/11.2.0.3/dbs/

startup

shutdown immediate

startup

delete from scott.emp;

commit;

shutdown immediate

host mv /opt/oracle/oradata/orcl/undotbs01.dbf /opt/oracle/oradata/orcl/undotbs01.dbf.bak

 

1.2  解决办法

1、在mount状态下,将undotbs1的数据文件offline

2alter database open

3、创建新的回滚表空间undotbs2,并设置undo_tablespaceundotbs2

4、重启数据库,删掉undotbs1表空间

SQL> startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.


SQL> alter database datafile '/opt/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Database altered.


SQL> alter database open;

Database altered.


SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on next 100m maxsize 30720m;

Tablespace created.


SQL> alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;

System altered.


SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.

Database opened.


SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

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

请登录后发表评论 登录
全部评论

注册时间:2009-01-12

  • 博文量
    69
  • 访问量
    1460703