oracle undo数据文件坏,oracle中undo表空间丢失处理方法

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

2020-09-03 11:33:48

当undo表空间数据文件丢失或损坏时，我们数据库在有事务提交等操作时，就会报错误

SQL> conn scott/scott;

ERROR:

ORA-02002: error while writing to audit trail

ORA-00604:

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