Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 10g dump to be imported in Oracle9i

Ankit Grover
Frequent Advisor

Oracle 10g dump to be imported in Oracle9i

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?
9 REPLIES
Peter Godron
Honored Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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
Trusted Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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
Frequent Advisor

Re: Oracle 10g dump to be imported in Oracle9i

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.

Re: Oracle 10g dump to be imported in Oracle9i

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
Trusted Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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
Trusted Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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
Honored Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Kamarudin Tik
Occasional Advisor

Re: Oracle 10g dump to be imported in Oracle9i

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
Honored Contributor

Re: Oracle 10g dump to be imported in Oracle9i

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.