最近的数据导入(imp)时碰到了ora-01187 ora-01110 错误，由于这个数据库是使用热备恢复过来的，且恢复也是成功的，因为数据库能够成功open，那到底是哪里有遗漏呢？如你有类似的问题，不妨往下看。
1、故障现象
imp-00003: oracle error 1187 encountered
ora-01187: cannot read from file 202 because it failed verification tests
ora-01110: data file 202: '/u02/database/ec0320/temp/ec0320_tempec0320.dbf'
imp-00017: following statement failed with oracle error 1187:
create index idx_goaah1 on go_ga_acc_hist_tbl (goaahaccnum ) pctfree
10 initrans 2 maxtrans 255 storage(initial 142606336 freelists 1 freelist
groups 1 buffer_pool default) tablespace goex_account_idx logging
sql> select file_name,status,autoextensible from dba_temp_files;
select file_name,status,autoextensible from dba_temp_files
*
error at line 1:
ora-01187: cannot read from file 201 because it failed verification tests
ora-01110: data file 201: '/u02/database/ec0320/temp/tempec0320.dbf'
2、故障分析
--上面的ora错误时和临时表空间数据文件有关的错误，，无法读取temp数据文件
--查看一下ora-01187错误信息描述，下面的描述中告诉我们使用lter system check datafiles
oracle@vmdb01p:/u02/database/ec0320/bnr/full> oerr ora 01187
01187, 00000, cannot read from file %s because it failed verification tests
// *cause: the data file did not pass the checks to insure it is part of the
// database. reads are not allowed until it is verified.
// *action: make the correct file available to the database. then, either open
// the database, or execute alter system check datafiles.
--检查一下对应的数据文件是否存在，下面的检查发现数据文件都在
sql> ho ls -hltr /u02/database/ec0320/temp/
total 603m
-rw-r----- 1 oracle oinstall 201m 2013-06-08 04:42 tempec0320.dbf
-rw-r----- 1 oracle oinstall 404m 2013-06-08 06:40 ec0320_tempec0320.dbf
-rw-r----- 1 oracle oinstall 101m 2013-06-09 13:25 ec0320_temp.dbf
--检查一下日志文件的相关信息
--下面的查询貌似临时表空间下的数据文件都处于 online 状态，这个查询来自控制文件，而前面的哪个查询来自数据字典，查询数据字典报错
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 /u02/database/ec0320/temp/tempec0320.dbf 200 online
goex_temp /u02/database/ec0320/temp/ec0320_tempec0320.dbf 403 online
fix_temp /u02/database/ec0320/temp/ec0320_temp.dbf 100 online
--看一下缺省的临时表空间配置，此处的配置为temp，也就是说系统缺省的临时表空间为temp
sql> col property_value format a20
sql> select property_name,property_value from database_properties
2 where property_name like 'default%';
property_name property_value
------------------------------ --------------------
default_temp_tablespace temp
default_permanent_tablespace goex_account_tbl
default_tbs_type smallfile
--查看一下goex_temp临时表空间属于哪个用户
sql> select username,temporary_tablespace from dba_users where username='goex_admin';
username temporary_tablespace
------------------------------ ------------------------------
goex_admin goex_temp