One non-standard handling of IMP-00010 errors

ExpImp is a classic in the Oracle product series and is widely used among technicians for its ease of use. Although Oracle began to use DataPump for retrieval from 10 Gb

Exp/Imp is a classic in the Oracle product series and is widely used by technicians for its ease of use. Although Oracle started to use Data Pump for retrieval from 10 Gb

Exp/Imp is a classic in the Oracle product series and is widely used by technicians for its ease of use. Although Oracle began to use Data Pump gradually to replace it as a small-scale logical backup and restoration tool for Data starting from 10 Gb, Exp/Imp is still attractive to common developers.

From a technical point of view, one of the advantages of using Exp/Imp is that the client runs, and the user does not need to connect to the database server, so they can easily perform data backup and restoration operations. Imagine that DBA would not easily allow users to log on to sensitive Oracle servers.

1. Gossip Exp/Imp

Exp/Imp has two problems in practice: Character Set conversion and Client/Server version coordination. Character Set conversion is rooted in the diversity of client software. This requires that you set the nls_lang environment variable to be displayed each time you use Exp/Imp, only in this way can the correct data characters be exported and imported. Note: many developers prefer to use the exp/imp tool in PL/SQL Developer. In fact, the Oracle client executable program is called in disguise. This method cannot set the nls_lang parameter ,, there will be a lot of trouble.

Client/Server version coordination is troublesome. Use the exp/imp tool to back up and restore the data. Four software versions are involved, namely, exporting the source database version, exporting the exp Client Version, importing the target database version, and importing the imp Client Version. The dmp file generated by the exporting client of a later version is incompatible when the latter version is imported to the client. Therefore, the complex matrix tables listed in Oracle MOS official articles and articles by the Internet heroes are used to indicate the ing between the four software versions.

Theoretically, if the software compatibility principle is violated, the exported dmp file cannot be imported successfully. You need to re-plan the export operation, which is troublesome. This article records a case where the author uses unconventional strategies to solve compatibility issues, leaving friends who are "desperate" to be "out of time ".

2. Import IMP-00010 Error

The author's source database is 11gR2 and needs to import a demo data schema to another server. The version of the target database has been forgotten for a long time. I think it should be 11gR2 Oracle, so export using Exp tool.

Out of simple habits, I chose to execute the exp command on the server, so that the export database and client are 11gR2.

When the data is imported, a problem occurs.

D: \ DDD> imp \ "/as sysdba \" file = DDD_TESTING_20150901_exp.dmp fromuser = ddd_testing touser = ddd_testing

Import: Release 10.2.0.1.0-Production on Wed Sep 2 22:36:31 2015

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Produc

Tion

With the Partitioning, OLAP and Data Mining options

IMP-00010: not a valid export file, header failed verification

IMP-00000: Import terminated unsuccessfully

The file header cannot be identified. In general, there are two possible causes of this problem: one is that the file contains bad blocks and there is a problem in the process of downloading or copying from FTP. The other is version compatibility.

In theory, there is no other way to export or download the dmp file again. If the version is compatible, check the target database and the import client.

SQL> select * from v $ version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

The import destination is 10 Gb, and the client is also 10gR2. This causes compatibility issues. The earlier Client Version is "considered" and cannot parse and understand the files generated by the later Client Version. Therefore, the system reports an error and rejects the import. The displayed information indicates that the file header verification fails.

3. Atypical Processing

At this time, I have no other choice. You can only go back and use the lower version client to export the Data content, or select Data Pump to adjust the version. However, on the internet, I found a seemingly reasonable solution.

Exp/Imp versions are incompatible, which is essentially Oracle's limitation on Dmp file content. Because Oracle is worried about lower-version Imp programs and cannot understand the content and generated objects in the Dmp files of higher versions, there will be restrictions. In my Dmp package, there are only data tables and some common index objects, and there is no "advanced" object. This restriction is meaningless.

In this case, you only need to modify the file header to enable the 10gR2 client to "recognize" The Dmp file. According to this idea, the notepad ++ editing program is downloaded from the network and the Dmp file is opened directly. Note: This process is actually risky. You must be prepared to open the Dmp of an approximate binary file in text format.

After opening the file, you can see the file header information, marked with the Dmp file version 11gR2.

Change it to the 10gR2 adaptive number V10.02.01 and save the modification.

Save and call the same import program command. The import starts normally.

D: \ DDD> imp \ "/as sysdba \" file = DDD_TESTING_20150901_exp.dmp fromuser = dd_testing touser = dd_testing

Import: Release 10.2.0.1.0-Production on Wed Sep 2 22:47:50 2015

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Produc

Tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT: V10.02.01 via conventional path

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Export client uses AL32UTF8 character set (possible charset conversion)

. Importing UAT_TESTING's objects into UAT_TESTING

.. Importing table "Aa" 0 rows imported

.. Importing table "d" 0 rows imported

.. Importing table "d" 134 rows imported

(Space reasons, omitted ......)

.. Importing table "f" 0 rows imported

.. Importing table "Xd" 0 rows imported

About to enable constraints...

Import terminated successfully without warnings.

The import operation is simple and worrying. Only the start constraint command is required. Check the data table and index object to confirm the import is successful.

SQL> select count (*) from dba_indexes where owner = 'dd _ TESTING ';

COUNT (*)

----------

1977

Fortunately, it is also a test environment where data, constraints, and index objects are enough to achieve the goal.

4. Conclusion