1、如果UNDO当中有事务,UNDO文件被删除如何恢复
i.在scott用户下更改emp表,不提交
SQL> update empset sal = sal +1;
14 rows updated.
Ii.删除undo数据文件
SQL> select namefrom v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/wyzc10g/system01.dbf
/u01/oracle/oradata/wyzc10g/undotbs01.dbf
/u01/oracle/oradata/wyzc10g/sysaux01.dbf
/u01/oracle/oradata/wyzc10g/users01.dbf
/u01/oracle/oradata/wyzc10g/example01.dbf
SQL> ho rm/u01/oracle/oradata/wyzc10g/undotbs01.dbf
Iii.abort关闭并重新启动数据库
SQL> shutdownabort
ORACLE instance shutdown.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBuffers 222298112 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
Oracle在启动时需要为保证事务一致性需要做实例恢复,然后此时找不到UNDO没办法恢复,所以没办法打开数据库
Iv.解决方法:
(1)修改undo_management参数为手工,并重新启动数据库
SQL> alter systemset undo_management=manual scope=spfile;
System altered.
SQL> shutdown
ORA-01109: databasenot open
Database dismounted.
ORACLE instance shutdown.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBu