数据导入时遭遇ORA-01187、ORA-01110问题
最近的数据导入(IMP)时碰到了ORA-01187 ORA-01110 错误，由于这个数据库是使用热备恢复过来的，且恢复也是成功的，因为数据库能够成功open，那到底是哪里有遗漏呢？如你有类似的问题，不妨往下看。
1、故障现象
[sql]
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、故障分析
[sql]
--上面的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
3、故障解决
[sql]
--从上面的情形来看，应该是热备恢复之后，由于临时表空间不需要做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;
&nb
