临时表空间丢失处理

还原：从备份集中取出放回原来路径。还原到备份的时间点，丢失时间点后的数据。

恢复：利用日志文件重做恢复数据。

控制文件，数据文件需要恢复
归档日志，spfile不需要恢复

文件丢失的原因：
用户错误
应用程序错误
介质故障

关键性和非关键性：
非关键性文件丢失是指数据库可以继续运行的故障。
·通过创建一个新文件/重建文件/恢复丢失或损坏的文件

关键性丢失：系统表空间，undo表空间

--临时表空间丢失处理
SYS@PROD2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SYS@PROD2> select name from v$tempfile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/temp01.dbf

SYS@PROD2> select temporary_tablespace from dba_users;  --查找当前使用的临时表空间

TEMPORARY_TABLESPACE
------------------------------
TEMP

SYS@PROD2> select file_name from dba_temp_files where tablespace_name='TEMP';  --查找到当前使用的路径

FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/temp01.dbf

SYS@PROD2> ho rm -rf /u01/app/oracle/oradata/PROD2/temp01.dbf --删除临时表空间文件

SYS@PROD2> create table scott.temp1 as select * from all_objects;

Table created.

SYS@PROD2> insert into scott.temp1 select * from scott.temp1;

72778 rows created.

SYS@PROD2> select count(*) from scott.temp1;

  COUNT(*)
----------
    145556	
	
SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;
145556 rows selected.

现在还能查找出结果，因为临时表空间还保存在内存中。

SYS@PROD2> alter system flush shared_pool;  --清空内存

System altered.

SYS@PROD2> alter system flush buffer_cache;

System altered.

SYS@PROD2> alter system flush global context;

System altered.

SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;
145556 rows selected.

还是能查找出结果。。

再次扩大表大小。。
SCOTT@PROD2> insert into temp1 select * from temp1;

145556 rows created.

SCOTT@PROD2> /

291112 rows created.

SCOTT@PROD2> /

582224 rows created.

SCOTT@PROD2> commit;

Commit complete.

SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;  
select * from temp1 order by 1,2,3,4,5,6,7,8,9
              *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/PROD2/temp01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

临时表空间丢失所以报错。


解决方法：
创建新的临时表空间并更改为正在使用的临时表空间。

SYS@PROD2> alter tablespace temp rename to tempold;  --重命名旧的临时表空间

Tablespace altered.

SYS@PROD2> create temporary tablespace temp tempfile '/u01/app/oracle/oradata/PROD2/temp01.dbf' size 100m autoextend on uniform size 1m;
create temporary tablespace temp tempfile '/u01/app/oracle/oradata/PROD2/temp01.dbf' size 100m autoextend on uniform size 1m
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/PROD2/temp01.dbf' - file already part of
database
--不能与前一个临时空间文件名字相同

SYS@PROD2> create temporary tablespace temp tempfile '/u01/app/oracle/oradata/PROD2/temp02.dbf' size 100m autoextend on uniform size 1m;

Tablespace created.

SYS@PROD2> alter database default temporary tablespace temp;  --更改为默认临时表空间

Database altered.

SYS@PROD2> drop tablespace tempold;  --删除旧的临时表空间

Tablespace dropped.

SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;
46007 rows selected. --时间太长中止了
成功查询。