数据导入时遭遇 ORA-01187 ORA-01110

数据导入时遭遇 ORA-01187 ORA-01110

最近的数据导入(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