You sometimes might face difficulties while export data from higher database version by data pump export and them import the dumpfile into lower version database. It is more appropriate to say it - to export data from a database that has higher compatibility and then import the dumpfile into the database that has lower compatibility settings.
The general guidelines are set in the post http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html.
From the post,
When you decide to use data pump operation from a higher compatibility level database to a lower compatibility level database(i.e expdp from a higher compatibility level database and then impdp to a lower compatibility level database) then always while performig data pump exoprt operation always add VERSION parameter and the value of the VERSION parameter need to match the compatiblity level of the database to which you will perform data pump import.
Below is an example which will take a dump of 11.1.0.6 database using 11g data pump export client and then import that dump into 10.2.0.1 database using 10g data pump client.
In the example I have taken a data pump dumpfile of table test from schema arju of 11g database and then import it to 10g database under the same schema.
Step 01: Connect to 11.1.0.6 database compatibility and create the directory.
C:\>sqlplus arju/a@san
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 22:58:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory e as 'E:\';
Directory created.
This directory e is a name of location E:\ drive on my windows computer. I wanted to put my data pump dumpfile into E:\ drive.
Step 02: Take a dump of 11g database using 11.1.0.6 export data pump client and while export also include version parameter so that dumpfile can be imported into 10g database.
SQL> host expdp arju/a tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSION=10.2
Export: Release 11.1.0.6.0 - Production on Friday, 06 February, 2009 23:00:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/******** tables=test dumpfile=test_table.dmp logfile=test_table.log directory=e VERSI
ON=10.2
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST" 5.007 KB 1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
E:\TEST_TABLE.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 23:01:33
Step 03: Transfer the dumpfile into the D:\ drive of the machine where 10.2g database resides. On windows you can just share your E:\ drive of your 11g machine and from another machine you can just copy the dumpfile. There are many other ways to transfer file using network. If two computers does not have network connection then using pen drive you can transfer the file to the 10.2g machine.
Step 04: Connect to 10.2g database and create a directory d.
C:\>sqlplus arju/a@orcl
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 23:05:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory d as 'D:';
Directory created.
Step 05: Using 10.2g import data pump client just import the dumpfile into 10.2g database.
SQL> host J:\oracle\product\10.2.0\db_1\BIN\impdp arju/a@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Import: Release 10.2.0.1.0 - Production on Friday, 06 February, 2009 23:09:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_TABLE_01": arju/********@orcl tables=test dumpfile=test_table.dmp logfile=test_table.log directory=d
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST" 5.007 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_TABLE_01" successfully completed at 23:10:16
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/how-to-take-data-pump-export-on-another.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html
Great information thanks.
ReplyDelete