Tuesday, October 19, 2010

Data Pump with higher version of dump file

Sometimes, we need import a data pump dump file that created with higher version of data pump export. For example, moving production data to a testing database with lower version of database, or getting the dump file from a Vendor or another group within company that run higher version of database. Before Oracle data pump, we can import an Oracle dump file generated by higher version export utility (exp) using the higher version of import utility (imp) that connects to a lower version database. This trick does not work for data pump anymore because the database actually does the heavy lifting for data pump and newer version of data pump binary cannot work with older version of database. The work around is to set VERSION parameter to lower version of database with you do the data pump export. For example: VERSION=10.2 and then you can import it into database with Oracle version 10.2 database using data pump version 10.2. But what about you get a dump file that the version parameter is not being set and it is impossible to ask the provider to regenerate the dump file or it takes too long to do so? You always have option to import the dump file to a higher version of database then export again with version parameter to be set. Then import the regenerated dump file to the target lower version database. Lots of steps and some more additional work, but it can be done. If you like to take a short cut (and most of us do like to take shortcuts), you can skip the data export step by using network_link parameter. Say the importing your data into database orcl11 and the target database is orcl10. Instead of exporting the data with version=10.2 from orcl11g then importing with newly generated dump file to orcl10, you can create a database link db_link_10_to_11 from orcl10g to orcl11g and run 10g data pump directly with network_link=db_link_10_to_11 parameter. You do not need to set dumpfile parameter because orcl11g database generates the dump on fly and export into 10g database. There are few traps and tricks to use this feature. You have to grant the db link user the data pump privilege to make it work and you need to set version=10.2 for the data pump import to work around Oracle bug.