oracle中undo表空间丢失处理方法

当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>