How to export from oracle 11.2 and import into 10.2 version
I think many OTN users asked repeately this question How do i export from higher version and import into lower version.I just show here How do we do.
First : Using Datapump from Oralce 11.2 and import into 10.2 version
Source DB 11.2 Version :
Step 1: Create Directory
SQL> create or replace directory test_dir as 'D:\backupnew\dump'; Directory created. SQL> grant read,write on directory test_dir to scott; Grant succeeded. SQL> conn scott/tiger@azardb Connected. SQL> create table testversion(version varchar2(20)); Table created. SQL> insert into testversion values('oralce11gr2'); 1 row created. SQL> commit; Commit complete.
Step 2: Export Table using Datapump
C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion Export: Release 11.2.0.1.0 - Production on Sun Jan 23 15:54:13 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."TESTVERSION" 5.031 KB 1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: D:\BACKUPNEW\DUMP\TESTVER.DMP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:54:40 Now Go to Target DB 10.2 Version
Step 3: Create Directory for Scott User.
SQL> create or replace directory test_dir as 'd:\newdump'; Directory created. SQL> grant read,write on directory test_dir to scott; Grant succeeded.
Step 3: I just Copied this TESTVER.DMP file into target DB 10.2 Directory and import it
D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf ile=testversion.dmp tables=testversion Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:04: 31 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39143: dump file "d:\newdump\testversion.dmp" may be an original export dump file
It showing error, So you need to export Data in source db 11.2 using Version parameter
Step 4:
C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion version=10.2 reuse_dumpfiles=yes Export: Release 11.2.0.1.0 - Production on Sun Jan 23 16:06:47 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion version=10.2 reuse_dumpfiles=yes Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."TESTVERSION" 4.968 KB 1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: D:\BACKUPNEW\DUMP\TESTVER.DMP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:06:54
Step 5: Again I copied this dump file to target DB 10.2 directory and import it.
D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf ile=testver.dmp tables=testversion remap_tablespace=users_tbs:users Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:08: 37 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/********@ace directory=test_dir d umpfile=testver.dmp tables=testversion remap_tablespace=users_tbs:users Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."TESTVERSION" 4.968 KB 1 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:08:39 D:\oracle\product\10.2.0\db_2\BIN>
Now I successfully import it.
Same Like If you want Import oracle 9i version from export taking of 10g version dumpfile, you just use to export 9.2 export client of Source DB 10.2 data and then import it.
Sourcedb : azardb , Version :11.2
Unfortunately I don’t have Oracle 9i software.So i jsut used 11.2 and 10.2
D:\oracle\product\10.2.0\db_2\BIN>exp scott/tiger@azardb file=d:\newdump\testver sion.dmp tables=test Export: Release 10.2.0.1.0 - Production on Sun Jan 23 15:10:32 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table TEST 1 rows exported Export terminated successfully without warnings. Target DB : ACE Version :10.2 D:\oracle\product\10.2.0\db_2\BIN>set oracle_sid=ace D:\oracle\product\10.2.0\db_2\BIN>imp scott/tiger@ace file=d:\newdump\testversio n.dmp tables=test Import: Release 10.2.0.1.0 - Production on Sun Jan 23 15:11:03 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V10.02.01 via conventional path import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing table "TEST" 1 rows imported Import terminated successfully without warnings.
14 Comments
Post a comment
DBA 
Dear Azar,
Its very clear we can import a dump from a higher version to lower version. thank you for the useful tips you given.
I had a problem to upgrade a older version of database into 11gR2.
Yes my older version of database is 8.0.4.0.0, so there is one and only possiblity to upgrade this db into 11gR2 is Export/Import Utility.
Can u kindly tell me how to export this db dump and import into 11gR2. i am totally confused with the export utlity version. can u tellme how to identify this dbs Export version and how it will support in 11gR2.
Hope you understand my Question.
Expecting your earliest reply
Regards
Hamid
You can use EXP/IMP utility. you can not user datapump utility for oracle 8, because It was supported from 10g release version. so you just take export from oracle 8 using EXP and then import into oracle 11gr2 using IMP utility…
Thank u for your reply, is it possible to import 8.0.4.0.0 Database Dump file into 11gR2 ?
Is there any Issue we need to expect !!?!??
Regards
Hamid
no issues If Use EXP/IMP utility…you don’t have choice to use expdp/impdp.
Thank you Mr.Azar,
Very happy to know this.. very soon i will continue here if i face any Issues in Export/Import Part ..
Thank u again..
Regards
Hamid
Hi Azar,
It is nice thing to learn.
i have problem like client gave me a dump file of higher version than what we have. while client exporting they forgot to
mention the version parameter
is it possible to import the dump file to lower version without the version parameter ?
Thanks
Anand Eppa
There is no need of using version parameter while ur doing import from lower version to higher version
EX : 10g to 11g
Export from 10g and import to 11g
Yes indeed but my post talking about higher to lower
There is a mistake at step 3: the dumpfile name while importing is different.Please look at it carefully.
There is a mistake at step 3: the dump file name while importing is different.Please look at it carefully.
Hi Mohamed Azar,
To import to a lower db version will need to add parameter version. let say if i run export from 11g with parameter version 10.2. Is this copy of export can also be import in 11g?
Many Thanks