ORA-39142: incompatible version number 4.2 in dump file - Bug 21480031
This morning start with the bug, unfortunately :(
I have to export one schema from one place and import to other place but when i try to import schema i face the below error
Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.2 in dump file "dbabg.dmp"
Patch information
Source database
PATCH_ID VERSION ACTION STATUS ACTION_TIME
---------- -------------------- --------------- --------------- ---------------------------------------------------------------------------
27475603 12.1.0.2 APPLY SUCCESS 27-JUN-18 11.56.04.965095 PM
27338029 12.1.0.2 APPLY SUCCESS 27-JUN-18 11.56.08.135793 PM
Destination database
PATCH_ID VERSION ACTION STATUS ACTION_TIME
---------- -------------------- --------------- --------------- ---------------------------------------------------------------------------
24006101 12.1.0.2 APPLY SUCCESS 17-JUL-18 02.44.47.978528 AM
In order to extract more information from the dump file you can create procedure show_dumpfile_info
Dump from source database
SQL> exec show_dumpfile_info_pi(p_dir=> 'DATA_PUMP_DIR', p_file=> 'dbabg.dmsp')
----------------------------------------------------------------------------
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.: cfg_schema.dmp
Directory: DATA_PUMP_DIR
Disk Path: /u01/oracle/admin/DBABG/dpdump/
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.02.00
...Internal Dump File Version....: 4.2
...Creation Date.................: Tue Jul 17 09:43:35 2018
...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....: DBABG
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYS"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 712D2C1B342C3F41E0537E04030A1272
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...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)...: 23
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
test dump on the destination database
SQL> set serveroutput on
exec show_dumpfile_info_pi(p_dir=> 'DATA_PUMP_DIR', p_file=> 'dest_db.dmp')SQL>
----------------------------------------------------------------------------
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.: cfg_schema_as_backup.dmp
Directory: DATA_PUMP_DIR
Disk Path: /u01/oracle/admin/DBABG/dpdump/
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.02.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Tue Jul 17 02:09:56 2018
...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.: IBM AIX64/RS6000 V4 - 8.1.0
...Instance Name of source db....: DBABG
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYS"."SYS_EXPORT_SCHEMA_01"
...GUID (unique job identifier)..: 712D69443FDB01E0E0530A0A240DEC3E
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...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)...: 23
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
As you can from the above information we have differences in the version
Based on the information in Oracle Support this is due to changes on the version from 4.1 to 4.2 with patch 27338029
Issue:
With April 2018 DBBP (Patch 27338029), the Internal Dump File version has been changed from 4.1 to 4.2.
The issue is still under investigation in Bug 28185363 - ORA-39142: INCOMPATIBLE VERSION NUMBER 4.2 IN DUMP FILE AFTER APRIL 2018 BP, current status 11.
Solution
Until the bug is fixed, execute the export using VERSION=12.1.
for more information, you can check this Metalink Bug 21480031 -
MOS Note: 21480031.8
Bug 21480031 – Wrong Dump file Version If Compatible 12.1.0.2 and Hadoop Trailer Event Not Set
MOS Note: 2422236.1
You can check patch id 21480031 because there are a different version depending on the base patch version
Patch 21480031: WRONG DUMP FILE VERSION IF COMPATIBLE 12.1.0.2 AND HADOOP TRAILER EVENT NOT SET
Alert – Regression in DataPump After Applying 12.1.0.2.180417DBBP or 12.1.0.2.180717DBBP
Have fun!