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

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'

--上面的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

--从上面的情形来看，应该是热备恢复之后，由于临时表空间不需要做restore，所以导致了控制文件中临时表空间的相关信息与数据字典中记录的不一致
--下面我们来为临时表空间GOEX_TEMP增加一个数据文件
SQL> alter tablespace GOEX_TEMP add tempfile '/u02/database/EC0320/temp/EC0320_tempEC0320_2.dbf' size 100m autoextend on;

Tablespace altered.

--删除之前的数据文件
SQL> alter tablespace GOEX_TEMP drop tempfile '/u02/database/EC0320/temp/EC0320_tempEC0320.dbf';

Tablespace altered.

--再次查询dba_temp_files，可以看到下面的错误提示是一样的，只不过指向了不同的数据文件，依然需要修改
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'

SQL> alter tablespace TEMP add tempfile '/u02/database/EC0320/temp/tempEC0320_2.dbf' size 100m autoextend on;

Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u02/database/EC0320/temp/tempEC0320.dbf';

Tablespace altered.

SQL> alter tablespace FIX_TEMP add tempfile '/u02/database/EC0320/temp/EC0320_temp_fix.dbf' size 100m autoextend on;

Tablespace altered.

SQL> alter tablespace FIX_TEMP drop tempfile '/u02/database/EC0320/temp/EC0320_temp.dbf';

Tablespace altered.

SQL> select FILE_NAME,STATUS,AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME                                                    STATUS    AUT
------------------------------------------------------------ --------- ---
/u02/database/EC0320/temp/EC0320_temp_fix.dbf                AVAILABLE YES
/u02/database/EC0320/temp/tempEC0320_2.dbf                   AVAILABLE YES
/u02/database/EC0320/temp/EC0320_tempEC0320_2.dbf            AVAILABLE YES
--上述操作之后，对应的文件在文件系统中并没有删除，需要手动删除
--应该可以通过reuse方式来重新使用临时表空间的数据文件，而无需通过添加删除方式来解决，此未测试