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

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

2、alter database open

3、创建新的回滚表空间undotbs2，并设置undo_tablespace为undotbs2

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/720091/viewspace-1096678/