UNDO表空间下的数据文件被误删除后的处理方法：
操作前备份数据库，以避免更大的损失。
思路：
1、把误删除的数据文件offline
2、正常打开数据库后创建新的UNDO表空间及数据文件
3、修改相应参数指向新的UNDO表空间
4、重新启动数据库验证
5、删除旧的UNDO表空间。
以下为我在虚拟机上模拟的场景：在数据库打开的状态下，用操作系统命令将undo表空间对应的唯一数据文件改名，以模拟数据文件被删除的情况。
关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
启动数据库，报错，提示找不到undo表空间下的数据文件/oradata/orcl/undotbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 150998568 bytes
Database Buffers 377487360 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl/undotbs01.dbf'
将该数据文件offline
SQL> alter database datafile 2 offline
2 ;
Database altered.
正常打开数据库
SQL> alter database open;
Database altered.
显示UNDO相关的参数
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
创建新的UNDO表空间
SQL> create undo tablespace undotbs2 datafile '/oradata/orcl/undotbs02.dbf' size 320M;
Tablespace created.
修改UNDO_TABLESPACE参数，指向新创建的UNDO表空间
SQL> alter system set undo_tablespace='undotbs2' scope=spfile;
System altered.
重新启动数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 150998568 bytes
Database Buffers 377487360 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
3 TEMP NO NO YES
4 USERS YES NO YES
5 HRTBS YES NO YES
6 DEMO YES NO YES
8 UNDOTBS2 YES NO YES
8 rows selected.
确认UNDO_TABLESPACE参数已修改
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
删除旧的UNDO表空间
SQL> drop tablespace undotbs1;
Tablespace dropped.
