问题:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/u01/oradata/md/temp01.dbf'
问题重现:
- sys@DENVER> alter database backup controlfile to trace as '/tmp/t1.sql';
- alter database backup controlfile to trace as '/tmp/t1.sql'
- *
- ERROR at line 1:
- ORA-01187: cannot read from file because it failed verification tests
- ORA-01110: data file 201: '/u01/oradata/md/temp01.dbf'
- sys@DENVER> select file_name, status from dba_temp_files;
- select file_name, status from dba_temp_files
- *
- ERROR at line 1:
- ORA-01187: cannot read from file because it failed verification tests
- ORA-01110: data file 201: '/u01/oradata/md/temp01.dbf'
- sys@DENVER> select file#, ts#, name, status from v$tempfile;
- FILE# TS# NAME STATUS
- ---------- ---------- ---------------------------------------- -------
- 1 3 /u01/oradata/md/temp01.dbf ONLINE
1. 册除临时表
2. 再重建数据文件
- ys@DENVER> alter database tempfile '/u01/oradata/md/temp01.dbf' drop;
- Database altered.
- sys@DENVER> select file#, ts#, name, status from v$tempfile;
- no rows selected
- sys@DENVER> alter tablespace temp add tempfile '/u01/oradata/md/temp01.dbf' size 512m reuse;
- Tablespace altered.
- sys@DENVER> select file_name, status from dba_temp_files;
- FILE_NAME STATUS
- ---------------------------------------- -------
- /u01/oradata/md/temp01.dbf ONLINE
--------------------------------
SQL> select * from dba_temp_files;
select * from dba_temp_files
*
第 1 行出现错误:
ORA-01187: 由于验证测试失败而无法从文件 201 读取
ORA-01110: 数据文件 201: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEMP01.DBF'
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEMP01.DBF' offline;
alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEMP01.DBF' offline
*
第 1 行出现错误:
ORA-01516: 不存在的日志文件, 数据文件或临时文件
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEMP01.DBF"
实际上这个文件是存在的
解决:
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED
---------- ---------------- -------------- ---------- ---------- ------- -------
1 535313 03-9月 -07 3 1 OFFLINE READ WR
SQL> alter database tempfile 'D:\oracle\product\10.2.0\oradata\devdb\temp01.dbf' drop;
数据库已更改。
SQL> alter tablespace temp add tempfile 'D:\oracle\product\10.2.0\oradata\devdb\temp.dbf' size 100m;
表空间已更改。
SQL> alter database tempfile 'D:\oracle\product\10.2.0\oradata\devdb\temp.dbf' online;
数据库已更改。
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DEVDB\TEMP.DBF
我今天也碰到这个问题了。新建数据文件就OK了
SQL> select * from dba_temp_files;
select * from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 2001: '/datafile/vcm01/temp01.dbf'
SQL> alter database tempfile '/datafile/vcm01/temp01.dbf' drop;
Database altered.
SQL> alter tablespace TEMPTBS1 add tempfile '/datafile/vcm01/temp03.dbf' size 200m;
Tablespace altered.
SQL> alter database tempfile '/datafile/vcm01/temp03.dbf' ONLINE;
Database altered.
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/datafile/vcm01/temp03.dbf
1 TEMPTBS1 209715200 25600 ONLINE
1 NO
我也遇到,解决办法是
alter database tempfile '/u01/XXXXXXX/temp01.dbf' drop;
提示成功,
alter tablespace temp add tempfile '/u01/XXXXX/temp01.dbf' reuse;
提示成功。
然后执行报错的操作,显示OK