Oracle 10g dump to be imported in Oracle9i

[Ankit Grover]

Hi all,

I have got an Oracle10G dump and want to import it in Oracle9i. Any way how can we do it without using any Oracle Prop tool?

[Peter Godron]

Ankit,
sorry, not sure you can.

If I have to generate a lower version export file, I normally connect to the database with the lower version client and then use the export(9i) version to generate the file from the 10g database.
It is then in the correct format to be imported into a 9i DB.

Perhaps somebody else has found a way to use an existing 10g dmp into 9i ?!

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

[SANTOSH S. MHASKAR]

Hi,

If u have Oracle dump taken by Oracle 10g's
Data pump utility then u cannot import it into
9i db. If u have dump taken with export utility
then u can import it into 9i using import utility of 9i (imp).


-Santosh

[Ankit Grover]

Thanks Peter. I'll try it out with your option.

Santosh,
The export has been taken from 10g using EXP utility and I am trying to import it into 9i using IMP. But it is giving Wrong Header error.

[Vadim Loginov]

Hi,

How big is *.dmp file ?

Maybe you can try to convert it to .sql file:

strings file.dmp > file.sql

(probably sql editing will be required, not sure)

export ORACLE_SID=
connect /as sysdba
SQL> @/path/file.sql

Regards,

Vadim

[SANTOSH S. MHASKAR]

Hi,

Following is from Oracle 10g rel.2 docs.

-------------------------------------
19 Original Export and Import

This chapter describes how to use the original Export and Import utilities, invoked with the exp and imp command, respectively. These are called the original Export and Import utilities to differentiate them from the new Oracle Data Pump Export and Import utilities available as of Oracle Database 10g. These new utilities are invoked with the expdp and impdp commands, respectively. In general, Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features. Original Export and Import do not support all Oracle Database 10g features.

However, you should still use the original Export and Import utilities in the following situations:

* You want to import files that were created using the original Export utility (exp).
* You want to export files that will be imported using the original Import utility (imp). An example of this would be if you wanted to export data from Oracle Database 10g and then import it into an earlier database release.


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

How do u take export (What commands/syntax)
and what is the exact error mesg.?

-Santosh

[SANTOSH S. MHASKAR]

Hi,

Although it was mentioned that data can be imported to earlier versions oracle also mentions in 10g rel 2 docs that,

--------------------------------------
Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
----------------------------------------


I tried this on my server and got following.

-------------------------
Connected to: Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

IMP-00002: failed to open expdat.dmp for read
Import file: expdat.dmp > Exp_full_wfms.SAN-30-03-07.dmp

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
-------------------------


It is also mentioned in docs
---------------------------------------
Using Different Releases and Versions of Export

This section describes compatibility issues that relate to using different releases of Export and the Oracle database.

Whenever you are moving data between different releases of the Oracle database, the following basic rules apply:

* The Import utility and the database to which data is being imported (the target database) must be the same version.
* The version of the Export utility must be equal to the earliest version of the source or target database.

For example, to create an export file for an import into a later release database, use a version of the Export utility that is equal to the source database. Conversely, to create an export file for an import into an earlier release database, use a version of the Export utility that is equal to the version of the target database.
-------------------------------------------
Hence u need to use 10g client imp utility to import on 9i and it is recommended that u have to take export dmp with 9i exp utility.


-Santosh

[Yogeeraj_1]

hi,

The Oracle 10g dump if taken using an oracle 9i client would work perfectly during an import on an oracle 9i database.

In case, the oracle 10g client was used for "dumping", import in an Oracle 9i database will NOT work! The 9i client will not recognise the file format. Unfortunately, these tools are not backward compatible.

If you want to create the objects without the data, you can always generate a script using the "indexfile" switch. E.g.
imp userid=user/password full=y indexfile=user.sql

good luck!

kind regards
yogeeraj

[Kamarudin Tik]

hi ankit,

U can't use exp/imp utility to dump Oracle 10G and import it on Oracle 9i. Please refer to Oracle Documentation.

What you can do is write a script and copied all the data and download it to your Oracle 9i database.

[Peter Godron]

Ankit,
how are you getting on with this ?

Please read:
http://66.34.90.71/ITRCForumsEtiquette/after.html

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.