Oracle 如何从丢失的临时文件或空的临时表空间中恢复

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

 

 

目的

-------

 

在使用有临时文件的TEMPORARY 表空间时，你可能遇到2 种临时文件丢失的情况。本公告解释了如何快速地从两种情况中恢复。

 

1. TEMP01临时文件（默认名称）在OS级别丢失。

   当一个用户尝试sort to 排序到TEMPORARY 表空间时，生成各种错误。

  

     SQL> select * from dba_objects order by object_name;

     select * from dba_objects order by object_name

              *

     ERROR at line 1:

     ORA-01115: IO error reading block from file 201 (block # 3)

     ORA-01110: data file 201: '/oracle/oradata/ORCL/temp2_01.tmp'

     ORA-27041: unable to open file

     SVR4 Error: 2: No such file or directory

     Additional information: 3

 

     或

 

     SQL> select * from dba_objects order by object_name;

     select * from dba_objects order by object_name

                  *

     ERROR at line 1:

     ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file

     ORA-01110: data file 1026: '/oracle/oradata/ORCL/temp2_01.tmp'

 

     或

      

     SQL> select * from dba_objects order by object_name;

     select * from dba_objects order by object_name

                   *

     ORA-01116: error in opening database file 202

     ERROR at line 1:

     ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'

     ORA-27041: unable to open file

     SVR4 Error: 2: No such file or directory

     Additional information: 3

 

 

2. 临时文件在数据库级别被DROP命令意外drop：

当用户尝试排序到临时表空间时，生成各种错误。

 

     SQL> alter table test add primary key (c);

     alter table test add primary key (c)

     *

     ERROR at line 1:

     ORA-25153: Temporary Tablespace is Empty

 

3. 确认哪些临时文件可能正在使用：

 

    SQL> select * from database_properties where property_name =

         'DEFAULT_TEMP_TABLESPACE';

 

    SQL> select TEMPORARY_TABLESPACE  from dba_users where username= ...

 

 

范围 & 应用

-------------------

适用于想要从临时表空间中恢复的DBA们。

 

 

1. 当临时文件在OS级别丢失时，如何恢复？

   --------------------------------------------

   情况

   ---------

      临时文件位于一个崩溃，有坏的控制器，或者有其他类型媒体故障的磁盘上。由于Oracle不记录在临时文件中的检查点信息，Oracle可以使用一个丢失的临时文件启动数据库。如果当数据库联机时一个临时文件不存在，DBW0写入跟踪文件表明未发现临时文件，但数据库可以正常打开。

 

      例如

      -------

 

      SQL> create temporary tablespace TEMP2

        2  TEMPFILE '/oracle/oradata/ORCL/temp2_01.tmp' size 5M;

 

      Tablespace created.

 

      SQL> select tablespace_name, file_name from dba_temp_files;

 

      TABLESPACE_NAME    FILE_NAME

      ------------------ --------------------------------------

      TEMP2             /oracle/oradata/ORCL/temp2_01.tmp

 

      SQL> select tablespace_name, contents

        2  from dba_tablespaces where tablespace_name = 'TEMP2';

 

      TABLESPACE_NAME                CONTENTS

      ------------------------------ ---------

      TEMP2                          TEMPORARY

 

      SQL> select * from dba_objects order by object_name;

      select * from dba_objects order by object_name

                    *

      ORA-01116: error in opening database file 202

      ERROR at line 1:

      ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'

      ORA-27041: unable to open file

      SVR4 Error: 2: No such file or directory

      Additional information: 3

 

      SQL> connect / as sysdba

      Connected.

      SQL> shutdown immediate

      Database closed.

      Database dismounted.

      ORACLE instance shut down.

 

      SQL> startup

      ORACLE instance started.

      ....

      Database opened.

 

      SQL> connect scott/tiger

      Connected.

 

      SQL> select * from dba_objects order by object_name;

      select * from dba_objects order by object_name

                   *

      ERROR at line 1:     

      ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

      ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'

 

      Solution : Drop the tempfile at the database level and add a new one

      --------

 

      SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp' drop;

      Database altered.

 

      SQL> select tablespace_name, file_name from dba_temp_files;

      no rows selected.

 

      SQL> alter tablespace temp2

        2  add tempfile '/oracle/oradata/ORCL/temp2_01.tmp' size 5m;

      Tablespace altered.

 

 

2. 当临时文件在数据库级别被意外drop时，如何恢复？

   ----------------------------------------------------------------------------------

   情况

   ---------

   临时文件被一个DROP命令意外drop：

   可以从临时表空间中删除所有临时文件并保持为空。

   但当用户尝试排序到TEMPORARY表空间时，生成错误。

 

      例如

      -------

      => in 8i: the drop clause only removes the logical entry from the

                tablespace, but not the OS file

 

         SQL> alter tablespace TEMP_TEMPFILE_LOCAL

          2   add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';

 

         Tablespace altered.

 

         SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp' drop;

 

         Database altered.

 

      => From 9i: you can use the new clause INCLUDING DATAFILES to remove OS files

 

         SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp'

          2   drop including datafiles;

 

         Database altered.

 

      SQL> alter table test add primary key (c);

      alter table test add primary key (c)

      *

      ERROR at line 1:

      ORA-25153: Temporary Tablespace is Empty

 

   解决方法 :添加一个新的临时文件

   --------

      8i中：在添加新的临时文件之前删除OS 临时文件

 

      9i起：直接添加一个新的临时文件

      要添加临时文件：  

      SQL> alter tablespace TEMP_TEMPFILE_LOCAL

          2   add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';

 

总结

-------

 

OS 临时文件丢失      

   ----> 从临时表空间中drop逻辑临时文件

   ----> 将新的临时文件添加到临时表空间

 

逻辑临时文件丢失

   ----> 8i：从临时表空间中删除OS临时文件

             将新的临时文件添加到临时表空间中

   ----> 9i及以上：将新的临时文件添加到临时表空间中

 

 

相关文档

-----------------

Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?

 

其他搜索词

-----------------------

TEMPFILE TEMPORARY