管理还原数据-管理还原数据

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