undofile故障恢复


undofile故障恢复
 
测试表：
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空间，删除旧的