Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142

Posted on July 16, 2018 by Data Pump Flaws and Pitfalls Patch Recommendation 2

Data Pump 12.1.0.2 - Wrong Dump File Version - ORA-39142Again I’ll have to thank my colleague Roland Gräff from the German ACS Support team in Stuttgart for bringing this into our radar. Roland alerted me a week ago about an issue with exports in Oracle 12.1.0.2 only when you are on a certain patch level. I summarize the issue here under Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142.

In the below blog post you will learn about the actual issue, where it happens and when, and of course how to workaround it.

When does it happen?

The issue I will describe below happens only with Oracle Database 12.1.0.2 Bundle Patches April and July 2018. There are two workarounds available. It does NOT happen with Oracle Database 12.2.0.1 or 18c or any other database release.

Export is working flawless

When you are on Oracle Database 12.1.0.2 with Bundle Patch (BP) of April or July 2018, you will see the following behavior when you export with Data Pump:

expdp hugo/hugo@db12 tables=TAB,OBJ,USR exclude=statistics directory=mydir logfile=hugo_tables.log dumpfile=hugo_tables.dmp logtime=all

Export: Release 12.1.0.2.0 - Production on Fri Jul 13 10:40:49 2018

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

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
13-JUL-18 10:40:50.923: Starting "HUGO"."SYS_EXPORT_TABLE_01":  hugo/********@db12 tables=TAB,OBJ,USR exclude=statistics directory=mydir logfile=hugo_tables.log dumpfile=hugo_tables.dmp logtime=all 
13-JUL-18 10:40:51.457: Estimate in progress using BLOCKS method...
13-JUL-18 10:40:52.573: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
13-JUL-18 10:40:52.645: Total estimation using BLOCKS method: 3.625 MB
13-JUL-18 10:40:56.244: Processing object type TABLE_EXPORT/TABLE/TABLE
13-JUL-18 10:40:57.356: . . exported "HUGO"."OBJ"                                2.031 MB   20071 rows
13-JUL-18 10:40:57.433: . . exported "HUGO"."TAB"                                438.3 KB    1521 rows
13-JUL-18 10:40:57.483: . . exported "HUGO"."USR"                                15.69 KB      19 rows
13-JUL-18 10:40:57.748: Master table "HUGO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
13-JUL-18 10:40:57.754: ******************************************************************************
13-JUL-18 10:40:57.757: Dump file set for HUGO.SYS_EXPORT_TABLE_01 is:
13-JUL-18 10:40:57.761:   /u02/oradata/DB12/mydir/hugo_tables.dmp
13-JUL-18 10:40:57.793: Job "HUGO"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jul 13 10:40:57 2018 elapsed 0 00:00:08

This looks good. And I don’t see anything unusual.

My database is an Oracle 12.1.0.2 database with the April 2018 Bundle Patch:

ACTION_TIME    ACTION	STATUS	   DESCRIPTION				    VERSION	 PATCH_ID BUNDLE_SER
-------------- -------- ---------- ---------------------------------------- ---------- ---------- ----------
2017-06-05     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.170418    12.1.0.2	 25397136 DBBP
2017-07-20     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.170718    12.1.0.2	 25869760 DBBP
2017-10-18     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.171017    12.1.0.2	 26717470 DBBP
2018-01-17     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.180116    12.1.0.2	 26925263 DBBP
2018-04-19     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.180417    12.1.0.2	 27338029 DBBP

I used my simply check_patches.sql for this listing.

Now I will deinstall the April 2018 Bundle Patch.

Experiment only: Remove April 2018 Bundle Patch

Of course I do this step only as I don’t have another 12.1.0.2 Oracle Home in my environment. I just would like to verify what happens when I import the dump now into a 12.1.0.2 database with a lower patch level than April 2018 BP.

At first, I will have to SHUTDOWN IMMEDIATE my database. And as this is my lab environment I’m going straight forward without additional checks to the rollback procedure:

$ $OH18/OPatch/opatch rollback -id 27338029
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0.2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
OPatch version    : 12.2.0.1.13
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-07-13_10-48-07AM_1.log


Patches will be rolled back in the following order: 
   27338029
The following patch(es) will be rolled back: 27338029  
Sub-patches of a composite series are being rolled back. The system will be returned to a state where 20243804,20415006,20594149,20788771,20950328,21125181,21359749,21527488,21694919,21949015,22806133,23144544,24340679,24732088,25397136,25869760,26609798,26717470,26925263 and all its fixes will still remain, because the patch(es) were installed before patch(es) 27338029 were applied.
The following bug fixes will be removed: 6418158,18604493,18774543,19932634,20696223,20844308,21147908,21522582,21913183,21981529,21985256,22232606,22305887,22351495,22495673,22504793,22730454,22864303,23019710,23065489,23197730,23231894,24350620,24563422,24713381,24719799,24737403,24801152,24838599,25058080,25078611,25123585,25357142,25392535,25437699,25444961,25452452,25476149,25489342,25551676,25577309,25633101,25722055,25856821,26170659,26187943,26256131,26388538,26513709,26608137,26658759,26976814,27034890,27060167,27086138,27169796,27213224,27337759,27367194,27401506,27441326,27485863,27548131,27595973,27605624,27620950,27626925

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0.2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y

Rolling back patch 27338029...

RollbackSession rolling back interim patch '27338029' from OH '/u01/app/oracle/product/12.1.0.2'

Patching component oracle.network.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.crs, 12.1.0.2.0...

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.rdbms.util, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.assistants.server, 12.1.0.2.0...

Patching component oracle.has.deconfig, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Deleting "kolaet.o" from archive "/u01/app/oracle/product/12.1.0.2/lib/libgeneric12.a"

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0...

Patching component oracle.sqlplus.rsf, 12.1.0.2.0...
RollbackSession removing interim patch '27338029' from inventory
Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-07-13_10-48-07AM_1.log

OPatch succeeded.

Cool! Worked flawless.

But of course this task is not complete without a datapatch call after I started my database again with STARTUP:

$ $OH18/OPatch/datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Fri Jul 13 10:50:49 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_13611_2018_07_13_10_50_49/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
  ID 180116 in the binary registry and ID 180417 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  The following patches will be rolled back:
    27338029 (DATABASE BUNDLE PATCH 12.1.0.2.180417)
  Nothing to apply

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 27338029 rollback: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27338029/22055339/27338029_rollback_DB12_2018Jul13_10_51_09.log (no errors)
SQL Patching tool complete on Fri Jul 13 10:51:15 2018

Good. This worked fine as well. My database should be now operating with the January 2018 Bundle Patch where the issue is not present. check_patches.sql gives the followingh output:

ACTION_TIME	     ACTION	STATUS	   DESCRIPTION				    VERSION	 PATCH_ID BUNDLE_SER
-------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
2017-06-05	     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.170418    12.1.0.2	 25397136 DBBP
2017-07-20	     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.170718    12.1.0.2	 25869760 DBBP
2017-10-18	     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.171017    12.1.0.2	 26717470 DBBP
2018-01-17	     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.180116    12.1.0.2	 26925263 DBBP
2018-04-19	     APPLY	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.180417    12.1.0.2	 27338029 DBBP
2018-07-13	     ROLLBACK	SUCCESS    DATABASE BUNDLE PATCH 12.1.0.2.180417    12.1.0.2	 27338029 DBBP

Importing into Oracle 12.1.0.2 without April or July 2018 BP

As final step of this experiment I will now import my export dump back into the database:

$ impdp hugo/hugo@db12 tables=TAB,OBJ,USR exclude=statistics directory=mydir logfile=hugo_tables_imp.log dumpfile=hugo_tables.dmp logtime=all

Import: Release 12.1.0.2.0 - Production on Fri Jul 13 10:58:44 2018

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

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.2 in dump file "/u02/oradata/DB12/mydir/hugo_tables.dmp"

Bang! Here it is. ORA-39142: incompatible version number 4.2 in dump file.

Data Pump 12.1.0.2 – Wrong Dump File Version – ORA-39142

This issue is treated as bug 21480031. You can find more information here:

The above bug note gives a clear description why this happens.

Workaround

The workaround is very simple and straight forward: Use VERSION=12.1 when you export from Oracle 12.1.0.2 and plan to import into an 12.1.0.2 database again.

Or apply the fix for bug 21480031 on top of your environment:

Data Pump 12.1.0.2 - Wrong Dump File Version

Then the above ORA-39142: incompatible version number 4.2 in dump file error does not happen when you export from Oracle 12.1.0.2 with Bundle Patches either April or July 2018, and try to import into another Oracle 12.1.0.2 database having a lower (or no) patch bundle applied.

And of course it may be also a workaround to apply the April or July 2018 Bundle Patch to the database you’d like to import into and received the ORA error.

Video

In case you’d like to watch the entire demo in a 10min video, please watch it here:

.
–Mike

Share this:

Tags:

You may also like...

2 Responses

  1. Ravin Maharaj says:
    July 16, 2018 at 07:20

    Hi Mike,
    Please share the script/sql statement which shows this information
    ACTION_TIME ACTION STATUS DESCRIPTION VERSION PATCH_ID BUNDLE_SER

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,850 other subscribers

Follow me on Twitter