Mostly I have done database export from either same version (between production, UAT, test or development), or to a higher one in the upgrade scenario.
When doing an export from a higher version and import into a lower one:
I got an import error ORA-39142: incompatible version number when attempting to import the 11.2 export data dump file into a 11.1.0.7 database.
Import: Release 11.1.0.7.0 – 64bit Production
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file
This error is because the database version is not compatible. I’m not sure which Oracle version you’re using but I’m on 11.1.0.7.0 and the error is due to difference in version.
You can specify VERSION option when importing data dump.
Per the help page of the expdp, here is the description of VERSION.
VERSION={COMPATIBLE | LATEST | version_string}
VERSION Version of objects to export where valid keywords are: (COMPATIBLE), LATEST, or any valid database version.
The legal values for the VERSION parameter are as follows:
COMPATIBLE – This is the default value. The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher.
LATEST – The version of the metadata corresponds to the database release.
version_string – A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2.
It is interesting that the Oracle’s document states that the VERSION can be set as low as 9.2 since the data pump only works with Oracle 10.1 or above. This is probably for backward compatibility.
Sample here is to expdp from 11.2 in order to import into 11.1
$ expdp system directory=EXPDIR dumpfile=t.dmp version=11.1.0
Sample here is to expdp from 11.2 in order to import into 10.2
$ expdp system directory=EXPDIR dumpfile=t.dmp version=10.2
Example:
Export from 11.2.0.2 database without version parameter
Export: Release 11.2.0.2.0 – Production
nohup expdp \”/ as sysdba\” schemas=TESTSCHEMA directory=DATA_PUMP_DIR dumpfile=ORCL_TESTSCHEMA_022213.dmp logfile=expdpORCL_TESTSCHEMA_022213.log job_name=expdpORCL_TESTSCHEMA_022213 PARALLEL=2 &
Import into 11.1.0.7 database
nohup impdp \”/ as sysdba\” schemas=TESTSCHEMA directory=DATA_PUMP_DIR dumpfile=ORCL_TESTSCHEMA_022213.dmp logfile=impdpORCL_TESTSCHEMA_022213.log job_name=impdpORCL_TESTSCHEMA_022213 &
Import: Release 11.1.0.7.0 – 64bit Production
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file
Now using the VERSION parameter in export dump.
Export from 11.2.0.2 database with version parameter
Export: Release 11.2.0.2.0 – Production
nohup expdp \”/ as sysdba\” schemas=TESTSCHEMA directory=DATA_PUMP_DIR dumpfile=ORCL_TESTSCHEMA_022613.dmp logfile=expdpORCL_TESTSCHEMA_022613.log job_name=expdpORCL_TESTSCHEMA_022613 PARALLEL=2 VERSION=11.1.0 &
Import into 11.1.0.7 database
nohup impdp \”/ as sysdba\” schemas=TESTSCHEMA directory=DATA_PUMP_DIR dumpfile=ORCL_TESTSCHEMA_022213.dmp logfile=impdpORCL_TESTSCHEMA_022213.log job_name=impdpORCL_TESTSCHEMA_022213 &
Successfully Imported without any errors
source: http://sunkupuli.com/2013/02/26/ora-39142-incompatible-version-number-3-1-in-dump-file/
Posting Komentar