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

Posted by PDSERVICE on Apr 14, 2016 In

如果自己搞不定可以找诗檀软件专业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及以上：将新的临时文件添加到临时表空间中

You are here

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