While importing a dump into our 11g database, we got the below error.

 

impdp dumpfile=test1.dmp logfile=imp1.log directory=t

Import: Release 11.2.0.4.0 – Production on Mon Jan 23 08:57:03 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / AS SYSDBA

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 4.1 in dump file “/dmdata07/test/test1.dmp”

 

CAUSE:

The export dump is from oracle 12c database. So while importing the dump into lower version db, it is throwing incompatible version error.

Lets check the header information of the dump. For this we need to create one package . Download show_dumpfile_info

exec sys.show_dumpfile_info(‘T’,’test1.dmp’);

SQL>set serveroutput on 

SQL> exec sys.show_dumpfile_info('T','test1.dmp');
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: test1.dmp
Directory: T
Disk Path: /dmdata07/test
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........:
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x) --------  > DUMP VERSION 
...Creation Date.................: Mon Jan 23 08:55:56 2017
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.:
...Master Piece Number in file...:
...Operating System of source db.:
...Instance Name of source db....:
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.:
...Job Name......................: "SYS"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: 46BDA47EBD1076AEE053941315ACE706
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 22
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Header information shows the the dump version is of release 12c.

SOLUTION:

So to import a dump from higher version(12c) to lower version(11g) , we need to use VERSION=11.2 parameter while taking export( in higher version)

1. Connect to 12c db and take export with version parameter

expdp dumpfile=test1.dmp logfile=test1.log directory=T tables=dbatest.TEST_TABLE version=11.2

Export: Release 12.1.0.2.0 - Production on Mon Jan 23 09:34:01 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=test1.dmp logfile=test1.log directory=T tables=dbatest.TEST_TABLE version=11.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 "DBATEST"."TEST_TABLE"                      12.54 KB      24 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /dmdata07/notu/test1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 23 09:34:28 2017 elapsed 0 00:00:08

2. Now login to 11g and check the dump version from header

SQL> set serveroutput on
SQL> exec sys.show_dumpfile_info('T','test1.dmp');
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: test1.dmp
Directory: T
Disk Path: /dmdata07/notu
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 11.02.00.00.00
...Internal Dump File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x) ---- -->  Dump version 
...Creation Date.................: Mon Jan 23 09:34:28 2017
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: x86_64/Linux 2.4.xx
...Instance Name of source db....: BSDMSIT2
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYS"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: 46BE3078D5008687E053941315ACB981
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 2
...Max Items Code (Info Items)...: 22
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

We can see the now the version is showing as 11gR2 . Now try to import the dump in 11g db, it will work

$ impdp dumpfile=test1.dmp logfile=imp1.log directory=t

Import: Release 11.2.0.4.0 - Production on Mon Jan 23 09:36:43 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  /******** AS SYSDBA dumpfile=test1.dmp logfile=imp1.log directory=t
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBATEST"."TEST_TABLE"                      12.54 KB      24 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_02" successfully completed at Mon Jan 23 09:36:50 2017 elapsed 0 00:00:04

Similarly , for expdp impdp from 11g to 10g, need to use VERSION=10.2

 

HAVE A LOOK –   COLLECTION OF USEFUL DATABASE SCRIPTS

 

SEE NEW FEATURES OF ORACLE 12.2