ORA-01186: file 201 failed verification tests

本文主要是介绍ORA-01186: file 201 failed verification tests，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境：oracle11.2.0.4RAC+ASM+red hat6.1x64
主库两节点RAC，备库也为两节点RAC。
备库启用为实时应用查询。
日志应用等都是正常的。

主库asm group如下：
ASMCMD> ls
CRS/
DATA/
FRA/
备库asm group如下：
ASMCMD> ls
CRS/
SDATA/
SFRA/

备库启动后报错如下：（查询某些数据字典也会报红色的错误）

Dictionary check beginning
Fri Jul 11 01:29:23 2014
Errors in file /u01/app/oracle/diag/rdbms/byscx/byscx2/trace/byscx2_dbw0_78064.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA'
File 201 not verified due to error ORA-01157
Dictionary check complete
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
      This condition can occur when a backup controlfile has
      been restored.  It may be necessary to add files to these
      tablespaces.  That can be done using the SQL statement:

      ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

      Alternatively, if these temporary tablespaces are no longer
      needed, then they can be dropped.
         Empty temporary tablespace: BPEX_TEMP
         Empty temporary tablespace: TEMP3
*********************************************************************
Cannot re-create tempfile +DATA, the same name file exists
Database Characterset is ZHS16GBK
No Resource Manager plan active
Starting background process GTX0
Fri Jul 11 01:29:25 2014
GTX0 started with pid=62, OS id=78459
replication_dependency_tracking turned off (no async multimaster replication found)
--More--
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN

主库查询如下：
SQL> select * from v$tempfile;
   FILE# CREATION_CHANGE# CREATION_    TS#    RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
   BYTES    BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
  1       3226 30-JAN-13       3 1 ONLINE  READ WRITE
213909504 26112 20971520  8192
+DATA/bysjy/tempfile/temp.262.806053181

备库查询结果如下：
SQL> select * from v$tempfile;
   FILE# CREATION_CHANGE# CREATION_    TS#    RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
   BYTES    BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
  1       3226 30-JAN-13       3 1 ONLINE  READ WRITE
  0    0 65011712  8192
+DATA

备库根本就没有+DATA组，所以尝试删除报错：
SQL> alter database tempfile 1 drop;
alter database tempfile 1 drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "+DATA"
可能这个方法行，以后有机会再试了。
alter tablespace temp drop tempfile 201;
当然尝试删TEMP临时表空间也是不行的，因为库为只读备库。
SQL> alter database default temporary tablespace temp3;
alter database default temporary tablespace temp3
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

SQL> drop tablespace TEMP including contents;
drop tablespace TEMP including contents
*
ERROR at line 1:
ORA-16000: database open for read-only access

如何删除掉这个不正确的临时表空间文件？

可行操作如下：

主库：
方法1：主库创建新的临时表空间，切换为默认的。备库自动跟着切换。
conn /as sysdba
select a.property_name, a.property_value from database_properties a where a.property_name like '%default%';
show parameter db_create_file_dest;
create temporary tablespace temp1 tempfile '+data' size 1g reuse autoextend on next 1024k maxsize unlimited;
alter database default temporary tablespace temp1;
alter tablespace temp1 drop tempfile 'xxx';
drop tablespace temp including contents;
select name from v$tempfile;
方法2：重建备库文件，然后备库重命名各类文件，参看原来asmcmd对应的。或者提前备份好名字。
alter database create standby controlfile as 'xxxxxx';传输到备库，修改pfile文件。

备库：

startup mount
alter database rename file '+SDATA/asm2/datafile/system.260.875189797','+SDATA/asm2/datafile/sysaux.261.875189815' to '+SDATA/asm2/datafile/SYSTEM.257.875227135','+SDATA/asm2/datafile/SYSAUX.258.875227135';
alter database rename file '+SDATA/asm1/onlinelog/group_1.257.875189795' to '+SDATA/asm2/onlinelog/group_1.261.875227329';
alter database rename file '+SDATA/asm2/tempfile/temp1.270.875269435' to '+SDATA/asm2/tempfile/temp1.270.875270407';

alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
select name from v$tempfile;

经测试，备库不能直接通过脚本重建控制文件，只能通过主库创建，然后rename相关数据文件。

最终解决办法：
主库创建新的临时表空间temp1，切换为默认的。备库自动跟着切换，然后删掉主库的temp，备库增加临时文件。
主库：
create temporary tablespace temp1 tempfile '+DATA' size 1024M;
select file_name,TABLESPACE_NAME,sum(BYTES)/1024/1024 from dba_temp_files group by file_name,tablespace_name;
alter database default temporary tablespace temp1;
select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';
drop tablespace temp including contents and datafiles;

备库：
alter tablespace temp1 add tempfile '+SDATA' size 1024M;

可能这个方法也行，以后有机会再试了。
alter tablespace temp drop tempfile 201;
源于：