undofile故障恢复

来源：互联网发布：淘宝网如何刷单编辑：程序博客网时间：2024/05/17 08:48

测试表：

SQL> select count(*) from t1;

COUNT(*)

--------------------

100958

活动事物模拟：

SQL> insert into t1 select * from dba_objects;

50479 rows created.

模拟回滚数据文件损坏：

SQL> !

oracle@readerlogdb-> rm -rf /database2/oradata/skyread/undotbs02.dbf

正常无法关闭，因为需要用到undo

SQL> shutdown immediate;

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 5049942016 bytes

Fixed Size 2090880 bytes

Variable Size 1375733888 bytes

Database Buffers 3657433088 bytes

Redo Buffers 14684160 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/database2/oradata/skyread/undotbs02.dbf'

新建文件进行恢复并正常打开数据库：

SQL> alter database create datafile '/database2/oradata/skyread/undotbs02.dbf';

Database altered.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select count(*) from t1;

COUNT(*)

--------------------

100958

总结：这里只举了一个最简单的例子，个人觉的undo file的备份恢复是最简单的，如果没有断电关机，可以杀掉有活动事物的session，然后新建undo表空间，切换至新建的undo空间，删除旧的