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>