ORA-39142: incompatible version number 4.2 in dump file impdp

ORA-39142: incompatible version number 4.2 in dump file 

Impdp encountering error ORA-39142

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.2 in dump file

Cause:
The issue is caused due to bug  Metalink Note 
Doc ID (2422236.1)

Workaround:

Use parameter VERSION=12.1 while performing the export.

Issue will be fixed .

ORA-1427 During Importing Statistics

DataPump Import (IMPDP) Fails With Error ORA-1427 During Importing Statistics (Doc ID 1501580.1)

To Bottom

---

In this Document Symptoms Cause Solution APPLIES TO: Oracle Database - Enterprise Edition - Version 10.1.0.2 and later Information in this document applies to any platform. ***Checked for relevance on 06-Nov-2015*** SYMPTOMS During DataPump import, you encounter errors like: ORA-39097: Data Pump job encountered unexpected error -1427 ORA-39065: unexpected master process exception in DISPATCH ORA-01427: single-row subquery returns more than one row Job "SYS"."SYS_IMPORT_FULL_02" stopped due to fatal error at 16:07:30 The import logfile shows: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] ORA-06502: PL/SQL: numeric or value error LPX-00241: entity reference is not well formed ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 9001 CAUSE There is an issue with the statistics in the export dump. SOLUTION Import with EXCLUDE=STATISTICS and gather the statistics after the import.

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS

Error ORA-39126 On Import Using DataPump - Bug 4352110 (Doc ID 749655.1)

To Bottom

---

In this Document Symptoms Changes Cause Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.1.0.4 to 10.2.0.3 [Release 10.1 to 10.2] Information in this document applies to any platform. ***Checked for relevance on 13-Oct-2017*** SYMPTOMS The import log file shows the errors: ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TRIGGER:"APPS"."FND_PROD_LIC_TGR"] ORA-06502: PL/SQL: numeric or value error LPX-00007: unexpected end-of-file encountered ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 7839 CHANGES After applying the interim patch for the Bug 4352110 - IMPORT EXITS WITH ORA-39125 UNEXPECTED FATAL ERROR IN KUPW$WORKER.PUT_DDLS the errors continue. Note the bug has also been fixed in patch set 10.2.0.4. Please refer to: Note 4352110.8 - Bug 4352110 - ORA-39125 from expdp/impdp of triggers with nulls in WHEN clause CAUSE The patch was not properly installed. The catmeta.sql script, as installed with the patch, needs to be run as mentioned in the Patch Readme. SOLUTION 1. Check that the issue is resolved and that the patch has been properly installed. Please make sure the new catmeta.sql script is executed by running the catdph.sql script as instructed in the Patch Readme: #  Patch Special Instructions: #  --------------------------- #  After the patch has been applied please reload the package/s into #  the database. To do this connect as SYS and execute the following; #      SQL> @?/rdbms/admin/catdph.sql #      SQL> @?/rdbms/admin/catdpb.sql 2. Often the error message refers to one trigger (see the example in the reported error) that has NULL characters resolved by the bug fix. a. dbms_metadata.get_ddl package on all triggers can help show what the actual problem trigger is. b. Get the following for one of the problematic triggers: select whenclause, dump (whenclause, 16, 1,  999) from   trigger$ where  obj# = <id_of_problem_trigger>;

ORA-39181: Only partial table data may be exported due to fine grain access control

ORA-39181: Only partial table data may be exported due to fine grain access control

SOLUTION:

Provide the below privilege to the schema which you are trying to export.

grant EXEMPT ACCESS POLICY to <SCHEMA NAME>;

DATA PUMP

Datapump in Oracle
------------------

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions.
The Oracle Data Pump is a feature of Oracle 10g and later databases that enables very fast bulk data and metadata movement between Oracle databases.
The Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp).
Data Pump Export and Import utilities are faster than the original Export and Import Utilities.
A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is several times faster than original Import.

The list of features for both the Data Pump import and export can be displayed using the HELP parameter.
expdp system/password help=y

Requirements
------------

Create Directory Object
-----------------------
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY TO ;

Ensure Oracle user performing Data Pump operation has adequate permissions.
It is common to include a meaningful job name for all Data Pump operations so as a job can be ideally managed.

Export PUMP
------------

expdp user_name/password
  DIRECTORY=dir_obj_name
  DUMPFILE=dump_file_name
  FULL=y|n
  JOB_NAME=job_name
  [COMPRESSION=type]
 
 
expdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump  FULL=y JOB_NAME=expDB LOGFILE=exportDatabase.log

Import
------

impdp user_name/password
  DIRECTORY=dir_obj_name
  DUMPFILE=dump_file_name
  FULL=y|n
  JOB_NAME=job_name

  impdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y  JOB_NAME=impDB LOGFILE=impDatabase.log

  Parameter File (.par)
  ---------------------
 
Export
------
# expdp system/password PARFILE=expDatabase.par COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=expDB
LOGFILE=expDatabase.log

Import
------

# impdp system/password PARFILE=impDatabase.par
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=impDB
LOGFILE=impDatabase.log
TABLE_EXISTS_ACTION=replace



Killing a Data Pump Job
----------------------

Standard Method
--------------
Determine the active Data Pump job name.

Attach to the Data Pump console using the identified jobname.

Issue the command to kill job.

SQL> SELECT j.owner_name, j.job_name,
            j.job_mode, j.state, s.session_type, s.saddr
     FROM dba_datapump_jobs j,dba_datapump_sessions s
     WHERE UPPER(j.job_name) = UPPER(s.job_name);

Owner     JOB_NAME        JOB_MODE     STATE        SESSION_TYPE    SADDR
--------- --------------- ------------ ------------ --------------- --------
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    WORKER          AD09C2D8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    MASTER          AD09E8A8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    DBMS_DATAPUMP   AD0BFA08

OS> expdp system/password attach=EXPORTSCHEMA
Export> kill_job
Export> Are you sure you wish to stop this job ([yes]/no): yes

If this is an import then use impdp correspondingly.

Advanced Method
---------------
If the standard method does not work and the job is no longer active.
Log into SQLPlus as SYS.
Drop the corresponding job table.
DROP TABLE .;

OS> sqlplus sys/password as sysdba

SQL> DROP TABLE SYSTEM.EXPORTSCHEMA;