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

今天发现oracle的dataguard备库临时表空间temp无法查询dba_temp_files的视图：

SQL> select tablespace_name, file_id, file_name,status,autoextensible, round(bytes/(1024*1024),0) total_space  from dba_temp_files order by tablespace_name,file_name;
select tablespace_name, file_id, file_name,status,autoextensible, round(bytes/(1024*1024),0) total_space  from dba_temp_files order by tablespace_name,file_name
*
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 8194: '+CCSYS/cc/temp_001.dbf'
           

进一步检查：

SQL> col name format a60
SQL> set linesize 160
SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) bytes,status
  2      from v$tablespace s,v$tempfile t
  3      where s.ts# = t.ts#;

TBSNAME                        NAME                                                              BYTES STATUS
------------------------------ ------------------------------------------------------------ ---------- -------
TEMP                           +CCDATA/DR_CC/TEMPFILE/temp.300.1052334269                           20 ONLINE
TEMP                           +CCSYS/cc/temp_001.dbf                                            30720 ONLINE
           

发现表空的这个数据文件是online的，而且检查物理文件，发现也是存在的：

ASMCMD> ls -l +CCSYS/cc/temp_001.dbf
Type      Redund  Striped  Time             Sys  Name
TEMPFILE  MIRROR  COARSE   OCT 23 10:00:00  N    temp_001.dbf => +CCSYS/CC/TEMPFILE/TEMP.260.1048969595
           

由于该库是dg的备库，是通过主库热备恢复的，但是临时表空间文件是不需要restore的，所以原表空间该数据文件跟恢复后的控制文件中记录的不一致，数据库并没有识别该数据文件，所以只需要先删除该临时数据文件人，然后重新添加一个一样数据文件名的数据文件即可：

SQL> alter database tempfile '+CCSYS/cc/temp_001.dbf' drop including datafiles;

Database altered.
           

此时再查询dba_temp_files视图就可以查询了：

SQL> select tablespace_name, file_id, file_name,status,autoextensible, round(bytes/(1024*1024),0) total_space  from dba_temp_files order by tablespace_name,file_name;

TABLESPACE_NAME         FILE_ID FILE_NAME                                STATUS  AUT TOTAL_SPACE
-------------------- ---------- ---------------------------------------- ------- --- -----------
TEMP                          1 +CCDATA/DR_CC/TEMPFILE/temp.300.10523342 ONLINE  NO           20
                                69
           

 再增加同名的数据文件到temp表空间，保持跟之前一致：

SQL> alter tablespace temp add tempfile '+CCSYS/cc/temp_001.dbf' SIZE 30g REUSE AUTOEXTEND off;

Tablespace altered.
           

SQL> select tablespace_name, file_id, file_name,status,autoextensible, round(bytes/(1024*1024),0) total_space  from dba_temp_files order by tablespace_name,file_name;

TABLESPACE_NAME         FILE_ID FILE_NAME                                STATUS  AUT TOTAL_SPACE
-------------------- ---------- ---------------------------------------- ------- --- -----------
TEMP                          1 +CCDATA/DR_CC/TEMPFILE/temp.300.10523342 ONLINE  NO           20
                                69

TEMP                          2 +CCSYS/cc/temp_001.dbf                   ONLINE  NO        30720

SQL>