当前或非当前的undo表空间的恢复

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace test_undo
  2  datafile '/u01/disk1/test_undo.dbf'
  3  size 50M
  4  extent management local;

Tablespace created.

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO
TEST_UNDO                      ONLINE    UNDO


SQL> alter system set undo_tablespace = TEST_UNDO;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      TEST_UNDO

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB 
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO               50

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ho rm /u01/disk1/test_undo.dbf -rf

SQL> ho ls /u01/disk1/

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/disk1/test_undo.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database datafile 6 offline drop;

Database altered.


SQL> alter database open;

Database altered.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO
TEST_UNDO       ONLINE    UNDO

但是数据库还认为这个TEST_UNDO还存在，所以要手工删除
SQL> drop tablespace TEST_UNDO;
drop tablespace TEST_UNDO
*
ERROR at line 1:
ORA-30013: undo tablespace 'TEST_UNDO' is currently in use


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      TEST_UNDO
SQL> alter system set undo_tablespace = UNDOTBS1;

System altered.

SQL> drop tablespace TEST_UNDO;

Tablespace dropped.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO


丢失的表空间只要重建就行了
SQL> create undo tablespace test_undo
  2  datafile '/u01/disk1/test_undo.dbf'
  3  size 50M
  4  extent management local;

Tablespace created.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO               50

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO
TEST_UNDO       ONLINE    UNDO

总结：undo表空间，最好保存有两个，方便恢复时将另一个还健存的undo表空间切换成当前表空间，再重建
当前undo表空间的丢失

如果把在线而且当前的undo表空间删除，会数据库会当场down掉，出现以下提示，恢复方法跟上面的非当前的undo表空间方法一样
SQL> shutdown immediate
ORA-03113: end-of-file on communication channel