Oracle Data Pump (Data Dump) Error collection, Volume ledump

Oracle Data Pump (Data Dump) Error collection, Volume ledump

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 39065

39065, 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 39097

39097, 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 minutes l, 2012 14:41:48

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-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,200 5, 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,200 5, 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,200 6, Oracle. All Rights Reserved.

Connected:

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 [/etljavascx/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 <= 100 ';

LRM-00101: unknown parameter name '>'

Solution:

Etl @ NMS_ODS_NDB1 [/etljavascx/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 [/etljavascx/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 <= 100 ';

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,200 5, 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 = export rty. 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,200 7, 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 & quot; SYS. KUPV $ FT_INT & quot;, line 600

ORA-39080: failed to create queues "KUPC $ c_1_30714082716" 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,200 7, 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 & quot; SYS. KUPV $ FT & quot;, line 871

ORA-00959: tablespace 'tool' 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.

ORACLE Database Data Pump usage Problems

Exp hr/hr @ ORCL owner = hr file = hr_db_20151117.dmp log = hr_db_20151117.log
Imp hr/hr file = hr_db_20151117.dmp log = hr_db_20151117.log full = y

You do not need to export the data of all accounts in a database. You only need to export the data of one account you need.

How to import data from ORACLE Data Pumps

NNC_DATA01
NNC_DATA02
NNC_DATA03
NNC_INDEX01
NNC_INDEX02
NNC_INDEX03
Nc has a total of six tablespaces. You should create these six tablespaces before importing. You do not need to consider the tablespace settings when importing them.