- 20 Dec, 2023
- 0 Comments
- 2 Mins Read
ORA-39142: Incompatible Version Number 4.1 in Dump File.
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 2023 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 2023 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 2023
...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 2023 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 2023 elapsed 0 00:00:04Similarly , for expdp impdp from 11g to 10g, need to use VERSION=10.2