SOLVED
ORA-39142: Incompatible Version Number 4.1 in Dump File.
Asked by OracleDba••13 views•oracle
#oracle#error
Solutions(1)
Accepted Solution
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147
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:04
Similarly , for expdp impdp from 11g to 10g, need to use VERSION=10.2OracleDba•