当前位置：首页 > news >正文

[2023-09-12]Oracle备库查询报ORA-01187

news 2025/8/5 15:43:50

一个多表关联的语句在备库执行查询时提示ORA-01187: cannot read from file because it failed verification tests，单独对某一个表查询则正常返回（因为不需要排序等，没有用到临时表空间）。查看报错信息发现是提示的临时数据文件无法访问导致。

1、查询dba_temp_files提示报错

SQL> SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = 'TEMP';
SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = 'TEMP'
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 14: '/u01/oracle/oradata/temp01.dbf'

报错信息和业务提示的报错一致。

处理方法（参考文档：Database upgrade fails with Error: "ORA-01110: data file N: '<location/temp.dbf>', as tempfile is corrupted/non-existing. (Doc ID 2381382.1)）

Recreate tempfile as below:1)First query from v$tempfile; 
SQL> select FILE#,NAME  from  v$tempfile;
FILE#    NAME
------  --------------------------------                                
14        /u01/oracle/oradata/temp01.dbf2)check for tablespace name:
SQL> SELECT PROPERTY_VALUE   FROM DATABASE_PROPERTIES  WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
----------------
TEMP3)Create new tempfile;
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/oracle/oradata/temp02.dbf' SIZE 512M  ;
Tablespace altered.4)Drop the tempfile 'temp01.dbf'
SQL> ALTER DATABASE TEMPFILE '/u01/oracle/oradata/temp01.dbf' DROP INCLUDING DATAFILES; 
Database altered.5) Now  query from "dba_temp_files" will not fail:
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
TABLESPACE_NAME        FILE_NAME
----------------    -------------------------------
TEMP                '/u01/oracle/oradata/temp02.dbf

查看全文

http://www.lryc.cn/news/168486.html