ORA-01187 and ORA-01110 problems during data import

The ORA-01187 ORA-01110 error was encountered during data import, the most recent data import (IMP), because the database was restored using hot standby and the recovery was successful, because the database can be opened successfully, what are the omissions? If you have a similar problem, let's look at it. 1. Fault symptom [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/Shanghai' 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 "using" 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. Fault Analysis [SQL] -- errors related to the preceding ORA error and temporary tablespace data files, unable to read temp data file -- View ora-01187 error Information Description, the following description tells us to use lter system check datafiles oracle @ vmdb01p: /u02/database/EC0320/BNR/full> oerr ora 01187 01187,000 00, "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. -- check whether the corresponding data file exists, the following check shows that all data files are stored in SQL> ho ls-hltr/u02/database/EC0320/temp/total 603 M-rw-r ----- 1 oracle oinstall 201 M 2013-06-08 42 tempEC0320.dbf- rw-r ----- 1 oracle oinstall 404 M 2013-06-08 06:40 EC0320_tempEC0320.dbf-rw-r ----- 1 oracle oinstall 101 M 2013-06-09 25 EC0320_temp.dbf -- check the information about the log file -- The following query looks like a temporary tablespace the data files under are all online, this query comes from the control file, and the previous query comes from the data dictionary. When querying the data dictionary, the 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 ------------- certificate ---------- ------- TEMP/u02/database/EC0320/temp/tempEC0320.dbf 200 ONLINE GOEX_TEMP/u02/database/EC0320/temp/limit 403 ONLINE FIX_TEMP/u02/database/ EC0320/temp/EC0320_temp.dbf 100 ONLINE -- check the default temporary tablespace configuration, the configuration here is temp, that is, the DEFAULT temporary tablespace is 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 when -------------------- ult_temp_tablespace TEMP when GOEX_ACCOUNT_TBL ult_tbs_type SMALLFILE -- check the user SQL> select username, TEMPORARY_TABLESPACE from dba_users where username = 'goex _ admin '; USERNAME TEMPORARY_TABLESPACE ------------------------------ GOEX_ADMIN GOEX_TEMP 3. troubleshooting [SQL] -- from the above situation, it is necessary to restore the temporary tablespace after Hot Backup recovery, therefore, the temporary tablespace information in the control file is inconsistent with that recorded in the data dictionary. Next we will add a data file SQL> alter tablespace GOEX_TEMP add tempfile for the temporary tablespace GOEX_TEMP '/ u02/database/EC0320/temp/EC0320_tempEC0320_2.dbf 'size 100 m autoextend on; tablespace altered. -- delete the previous data file SQL> alter tablespace GOEX_TEMP drop tempfile '/u02/database/EC0320/temp/EC0320_tempEC0320.dbf'; Tablespace altered. -- Query dba_temp_files again. You can see that the following error message is the same, but points to different data files. You still need to modify 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 100 m 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 100 m 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 certificate --------- ---/u02/database/EC0320/temp/your available yes/u02/database/EC0320/temp/tempEC0320_2.dbf available yes/u02/database/EC0320/temp/your available yes -- after the preceding operation, the corresponding file is not deleted in the file system. You need to manually delete it. You can use the reuse method to re-use the temporary tablespace data file, instead of adding or deleting the file, this is not tested 4. Summary a. After the database is restored, in addition to being able to open normally, you should consider whether the temporary tablespace is in the available State B and for errors such as ORA, the fastest possible error details are oerr ora input at the system command prompt <err_no>