当undo表空间数据文件丢失或损坏时，我们数据库在有事务提交等操作时，就会报错误
SQL> conn scott/scott;
ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/orcl11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Warning: You are no longer connected to ORACLE.
SQL>

这里，我们需要把数据库的undo管理改成手工管理，并关闭数据库
alter system set undo_management=manual scope=spfile;

关闭数据库
shutdown immediate    如果关不了，则强制进行关闭shutdown abort

SQL> shutdown immediate;
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/orcl11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>

打开数据库，并把数据文件脱机并删除；

SQL> startup;
ORACLE instance started.

Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             310380528 bytes
Database Buffers          633339904 bytes
Redo Buffers                6041600 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/oracle/oradata/orcl11g/undotbs01.dbf'


SQL> alter database datafile 3 offline drop;

Database altered.

SQL>

打开数据库
SQL> alter database open;

Database altered.

SQL>

然后删除表空间，并重新创建

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/orcl11g/undotbs01.dbf' size 100m autoextend on;

Tablespace created.

SQL>

修改表空间为自动管理，并重启数据库
SQL> alter system set undo_management=auto scope=spfile;

System altered.

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

Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             310380528 bytes
Database Buffers          633339904 bytes
Redo Buffers                6041600 bytes
Database mounted.
Database opened.
SQL>