Oracle Data Pump (Data Dump) often encounters some strange error cases during use.

Oracle Data Pump (Data Dump) often encounters some strange error cases during use.

Oracle Data Pump (Data Dump) often encounters some strange error cases during use. The following summarizes some problems encountered during the use of Data pump (Data Dump) and their solutions. All problems encountered during use will be added here.

Case 1:

ORA-39065: Unexpected master process Exception error in DISPATCH; ORA-44002: the object name is invalid

Windows platform error prompt:

Linux platform error message

Solution:

[oracle@DB-Server admin]$ oerr ora 3906539065, 00000, "unexpected master process exception in %s"// *Cause: An unhandled exception was detected internally within the master// control process for the Data Pump job. This is an internal error.// messages will detail the problems.// *Action: If problem persists, contact Oracle Customer Support.[oracle@DB-Server admin]$ oerr ora 3909739097, 00000, "Data Pump job encountered unexpected error %s"// *Cause: An unexpected, potentially non-fatal error occurred while// processing a Data Pump job.// *Action: Contact Oracle Customer Support.

Run catmet2. SQL and utlrp. SQL in the $ ORACLE_HOME/rdbms/admin directory to solve this problem.

[oracle@DB-Server ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@DB-Server admin]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 7 08:24:23 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> @catmet2.sql  SQL> @utlrp.sql 

Case 2:

ORA-39006: internal error; ORA-39213: Metadata processing is notavailable error when using expdp/impdp

[oracle@DB-Server backup]$ impdp frnt/frnt directory=dum_dir dumpfile=20120420.FRNT_02.dmp tablespaces=TBS_TR_DATA;Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 25 April, 2012 14:41:48Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39006: internal errorORA-39213: Metadata processing is not available

Solution:

The error cause is as follows, because Data Pump cannot use the Metadata API because XSL stylesheets is not correctly set. Run dbms_metadata_util.load_stylesheets with SYSDBA

[oracle@DB-Server admin]$ oerr ora 39213 39213, 00000, "Metadata processing is not available" // *Cause: The Data Pump could not use the Metadata API. Typically, // this is caused by the XSL stylesheets not being set up properly. // *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets // to reload the stylesheets.

SQL> exec dbms_metadata_util.load_stylesheets

Case 3:

The error is as follows:

etl@NMS_ODS_NDB1[/jkfile/klbtmp]#expdp userid=username/password@tnsname dumpfile=ref.dmp directory=DUMP_TEST schemas=ref content=all logfile=ref.log Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:28:25 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation 

Solution:

This error occurs. Check the DIRECTORY. When creating a DIRECTORY, ORACLE does not check whether the operating system DIRECTORY already exists.

First, check the DIRECTORY DUMP_TEST's DIRECTORY_PATH, and then check the operating system to verify whether the DIRECTORY exists.

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DUMP_TEST'; OWNER              DIRECTORY_NAME      DIRECTORY_PATH ------------------------ ------------------------------ ----------------- SYS                DUMP_TEST       /jkfile/klbtmp/ SQL> 

If the directory is deleted, the problem can be solved by creating the directory.

In another case, if a line break occurs during DIRECTORY creation, the above error message will also appear, which is difficult to find and difficult to find. So you need to be very careful.

SQL> create directory DUMP_TEST as '/jkfile/klbtmp/

';

Case 4:

As follows:

etl@NMS_ODS_NDB1[/jkfile/work/klb]#expdp userid=etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR schemas=ref content=all logfile=ref.log Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:50:25 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORA-31631: privileges are required ORA-39109: Unprivileged users may not operate upon other users' schemas 

Solution:

This problem is mainly solved when the etl account lacks the exp_full_database permission and grants the exp_full_database permission to the etl account.

# su - oracle $ sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 2 10:58:37 2013 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> grant exp_full_database to etl; Grant succeeded. 

Case 5:

etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query='DATE_CD >=20121201 AND DATE_CD <=20130131'; LRM-00101: unknown parameter name '>' 

Solution:

etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query="DATE_CD \>\=20121201 AND DATE_CD \<\=20130131"; 

Case 6:

LRM-00121: 'Data _ only' is not an allowable value for 'compression '. As follows:

etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR dumpfile=DM.TM_ALARM_LOG201212.dmp tables=DM.TM_ALARM_LOG COMPRESSION=DATA_ONLY query='DATE_CD >=20121201 AND DATE_CD <=20130131'; LRM-00121: 'DATA_ONLY' is not an allowable value for 'compression' 

Solution:

First, check the EXPDP tool version, as shown below:

etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp version Export: Release 10.2.0.3.0 - 64bit Production on Monday, 04 March, 2013 14:46:47 Copyright (c) 2003, 2005, Oracle. All rights reserved. Password: 

Note that in ORACLE 10g, COMPRESSION only has two options: METADATA_ONLY and NONE, and DATA_ONLY is available in ORACLE 11g. Therefore, the above error is reported. Before using EXPDP, pay attention to the EXPDP tool version.

Case 7:

[oracle@DB-Server]$ expdp system/***** TABLES=INVENRTY.INV_STK_HD dumpfile=INV_STK_HD.dmp logfile=1.log DIRECTORY=CUR_DUMP_DIR Export: Release 10.2.0.4.0 - Production on Sunday, 14 July, 2013 8:27:16 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production ORA-31626: job does not exist ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user SYSTEM ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT_INT", line 600 ORA-39080: failed to create queues "KUPC$C_1_20130714082716" and "KUPC$S_1_20130714082716" for Data Pump job ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPC$QUE_INT", line 1606 ORA-00832: no streams pool created and cannot automatically create one 

Solution:

This case I have introduced before, For details refer to my blog Expdp derivative error ORA-00832

Case 8:

[oracle@testlnx01 u03]$ cd tmp/ [oracle@testlnx01 tmp]$ expdp system/***** directory=DUMPDIR dumpfile=ESCMUSER.dmp schemas=ESCMUSER logfile=ESCMUSER.log Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 27 August, 2014 16:30:46 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production ORA-31626: job does not exist ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 871 ORA-00959: tablespace 'TOOLS' does not exist 

Solution:

This case is a bit special. When I first met it, it was a bit confusing. after hard work and proof, I found that there was a table space for TOOLS, and I don't know which of them was a hot-headed person, the SYSTEM user's default tablespace is specified as TOOLS, but the tablespace is deleted again. So there is such a case.

SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM; User altered. 

After modifying the default tablespace of the user SYSTEM, the problem is solved.