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

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号