临时表空间的管理与受损恢复

　　三、临时表空间故障

　　前面已提到，临时表空间不需要备份，如果对临时表空间备份将收到错误提示

SQL> alter tablespace temp begin backup;                      
alter tablespace temp begin backup                            
*                                                             
ERROR at line 1:                                              
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE   
                                                              
                                                              
SQL> alter temporary tablespace temp begin backup;            
alter temporary tablespace temp begin backup                  
      *                                                       
ERROR at line 1:                                              
ORA-00940: invalid ALTER command                              

　　临时表空间出错的错误提示(位于告警日志中alert_orcl.log)

　　Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_2230.trc:
　　ORA-01186：file 3 failed verification tests
　　ORA-01157：cannot identify/lock data file 3 - see DBWR trace file
　　ORA-01110：data file 3:'/u01/app/oracle/oradata/orcl/temp01.dbf'

　　四、还原受损的临时表空间

　　1、在session 1中删除临时表空间的数据文件

SQL> show user;                                  
USER is "SYS"                                    
SQL> ho rm /u01/app/oracle/oradata/orcl/temp01.dbf
                                                 
SQL> alter system checkpoint;  -->执行检查点进程 
                                                 
System altered.                                  

　　2、在session 2中执行排序查询，提示临时数据文件错误

SQL> show user;                                                                 
USER is "SCOTT"                                                                 
SQL> set autotrace traceonly;                                                   
SQL> select owner，object_name from big_table order by id，owner，object_name;     
select owner，object_name from big_table order by id，owner，object_name           
                              *                                                 
ERROR at line 1:                                                                
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf'  
ORA-27037: unable to obtain file status                                         
Linux Error: 2: No such file or directory                                       
Additional information: 3                                                       

　　3、在session 1中为临时表空间增加数据文件

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf'    
  2  size 30m autoextend on next 1m maxsize 2g;                                      
                                                                                     
Tablespace altered.                                                                  

　　4、在session 2中继续执行排序操作，此次执行成功

SQL> select owner，object_name from big_table order by id，owner，object_name;      
                                                                                 
1000000 rows selected.                                                           

　　5、查询临时数据文件状态及文件存在性

SQL> set linesize 160                            --> session 1中查看临时数据文件状态，大小为0，但仍然为online状态 
SQL> select s.name tbsname，t.name，(t.bytes/1024/1024) bytes，t.status                                             
  2  from v$tablespace s，v$tempfile t                                                                            
  3  where s.ts# = t.ts#;                                                                                        
                                                                                                                 
TBSNAME              NAME                                               BYTES STATUS                             
-------------------- --------------------------------------------- ---------- -------                            
TEMP                 /u01/app/oracle/oradata/orcl/temp01.dbf                0 ONLINE                             
TEMP                 /u01/app/oracle/oradata/orcl/temp02.dbf               30 ONLINE                             
                                                                                                                 
SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf                                                               
ls: /u01/app/oracle/oradata/orcl/temp01.dbf: No such file or directory                                           

　　6、清除控制文件内记录的不存在的临时文件信息

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf'; 
                                                                                    
Tablespace altered.                                                                 

　　7、基于表空间级别的恢复操作（下面给出主要步骤，不在演示）

　　a）创建一个新的临时表空间

　　b）使用alter database 将用户切换到新的临时表空间，即设置新的默认的临时表空间

　　c）删除受损的临时表空间