oracle临时表空间迁移,Oracle rman迁移后临时表空间报错：ORA-01187，ORA-01110

问题：

ORA-01187: cannot read from file  because it failed verification tests

ORA-01110: data file 201: '/u01/oradata/md/temp01.dbf'

问题重现：

[sql]

sys@DENVER>alterdatabasebackup controlfiletotraceas'/tmp/t1.sql';

alterdatabasebackup controlfiletotraceas'/tmp/t1.sql'

*

ERRORatline 1:

ORA-01187: cannotreadfromfile  because it failed verification tests

ORA-01110: data file 201:'/u01/oradata/md/temp01.dbf'

或

[sql]

sys@DENVER>selectfile_name, statusfromdba_temp_files;

selectfile_name, statusfromdba_temp_files

*

ERRORatline 1:

ORA-01187: cannotreadfromfile  because it failed verification tests

ORA-01110: data file 201:'/u01/oradata/md/temp01.dbf'

sys@DENVER>selectfile#, ts#,name, statusfromv$tempfile;

FILE#        TS#NAMESTATUS

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

1          3 /u01/oradata/md/temp01.dbf               ONLINE

解决办法：

1. 册除临时表

2. 再重建数据文件

[sql]

ys@DENVER>alterdatabasetempfile'/u01/oradata/md/temp01.dbf'drop;

Databasealtered.

sys@DENVER>selectfile#, ts#,name, statusfromv$tempfile;

norowsselected

sys@DENVER>altertablespacetempaddtempfile'/u01/oradata/md/temp01.dbf'size512m reuse;

Tablespace altered.

sys@DENVER>selectfile_name, statusfromdba_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