一,临时表空间的文件丢失后,在数据库启动后自动创建,不需要做干预.
模拟:在数据库shutdown后将临时文件删除,启动的时候发现自动创建.
二,undo表空间对应的文件丢失.
模拟:在数据库shutdown后将undo文件删除,启动的时候出错:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'
恢复步骤:因为undo文件不保存数据,可以直接drop 后重建.
1,drop undo文件.
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' offline drop;
Database altered.
2,将undo管理改为manual
SQL> alter system set undo_management='MANUAL' scope=spfile;
System altered.
3,重新启动数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Variable Size 264244100 bytes
Database Buffers 339738624 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
4,将丢失文件的undo表空间drop后重建
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> create undo tablespace UNDOTBS1 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' size 20M;
Tablespace created.
5,查看目前的数据库undo设置,需要将undo_management 改为auto.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_management='AUTO' scope=spfile;
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.
6,重新启动,数据库恢复成undo自动管理.
SQL> shutdown immediate;
SQL> startup
摘自 交换一个思想，能得到俩思想