ORA-01187 ORA-01110 for Data Import

The recent data import (IMP) encountered a ORA-01187 ORA-01110 error because the database was restored with hot standby, and the recovery was successful because the database was successfully open, so where are the omissions? If you have a similar problem, let's look at it.

 

1. Fault

IMP-00003: ORACLE error 1187 encounteredORA-01187: cannot read from file 202 because it failed verification testsORA-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 testsORA-01110: data file 201: '/u02/database/EC0320/temp/tempEC0320.dbf'

2. Fault Analysis

-- The preceding ora error is related to the temporary tablespace data file and the temp data file cannot be read -- check the ora-01187 error description, the following description tells us to use LTER system check datafilesoracle @ vmdb01p:/u02/database/ec0320/BNR/full> oerr ora 0118701187,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 603m-rw-r ----- 1 Oracle oinstall 201 m 2013-06-08 tempec0320.dbf-RW-r ----- 1 oracle oinstall 404 M EC0320_tempEC0320.dbf-rw-r ----- 1 Oracle oinstall 101 m 2013-06-09 ec0320_temp.dbf -- check the log file information -- The following query looks like the data files in the temporary tablespace are 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 a60sql> set linesize 160sql> 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/keys 200 onlinegoex_temp/u02/database/ec0320/temp/keys 403 onlinefix_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 tempsql> Col property_value format a20sql> select property_name, property_value from database_properties 2 where property_name like 'default % '; property_name property_value has already existed before smallfile -- check which user SQL the goex_temp temporary tablespace belongs to> select username, temporary_tablespace from dba_users where username = 'goex _ admin'; username has already existed goex_admin goex_temp

3. troubleshooting

-- From the above situation, it should be because temporary tablespace does not need to be restore 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 testsORA-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 available.
B. For errors such as ora, enter oerr ora <err_no> at the system command prompt.
C. For more information about temporary tablespaces, see managing temporary tablespaces and restoring damaged tables.

 

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan