How to debug Oracle Data Pump (expdp/impdp)

I. Overview

I have written two articles about Oracle Data Pump before:

Oracle 10 gData Pump Expdp/Impdp

Oracleexpdp/impdp example

These two articles describe the use of data pumps.

In actual use, we will also encounter a problem where some ORA-xx errors may occur. For specific errors, we can go to google for analysis, but there are still some situations, that is, it is not wrong. Instead, it stops at a certain step and remains unchanged for a long time.

It is time for us to determine whether the data pump operation is normal.

When the data pump gets stuck, there is a good way to judge:

1. During expdp, We need to observe the changes in the dump file. As long as the size of the dump file changes, it means that expdp is normal.

2. During impdp, we can check the changes in the tablespace in time. As long as the tablespace is changing, our impdp is normal.

If the tablespace and dump remain unchanged during the data pump operation, the data pump operation also stops at a certain step. Then we can only debug the data pump.

2. How to debug the Data Pump?

The file generated by the trace may be large. Therefore, before performing the trace, you must check the size of the dump file: max_dump_file_size.

[Oracle @ asm trace] $ orz param max_dump

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

------------------------------------------------------------

Max_dump_file_size true immediate unlimited

 

If it is not unlimited, modify it:

Alter system SETmax_dump_file_size = unlimited SCOPE = both;

 

2.1 use the TRACE parameter of Data Pump

 

2.1.1 TRACE description

 

To start the trace function, you only need to add a trace parameter after the expdp/impdp command. This parameter consists of a 7-bit hexadecimal data.

 

The code of the first three designated Data Pump components. The last four digits are generally 0300.

 

Trace values starting with 0 are ignored. trace values are case insensitive.

 

For example:

TRACE = 04A0300 or TRACE = 4a0300

 

 

Notes for trace values:

(1) The trace value must not exceed 7 hexadecimal digits.

(2) do not add a hexadecimal 0x symbol.

(3) do not convert hexadecimal to hexadecimal.

(4) The first 0 is ignored, even if the length does not meet 7 characters.

(5) The parameters are case insensitive.

 

 

When using the trace parameter, the user performing the data pump operation needs to have the DBA role or the role of EXP_FULL_DATABASE/IMP_FULL_DATABASE, if the permission is insufficient, an error of the ORA-31631 will be reported.

ORA-31631: privileges are required.

 

Solution: Assign permissions to users. For example:

GRANT exp_full_database TO tianlesoftware;

 

After the operation is complete, revoke the permission:

Revoke exp_full_database from tianlesoftware;

 

Example of using TRACE:

Expdp scott/tiger DIRECTORY = my_dir DUMPFILE = expdp_s.dmp LOGFILE = expdp_s.log TABLES = emptracing = 480300

2.1.2 specific rule for calculating TRACE values

The first three digits of the TRACE value indicate the component code of the Data Pump. The details are as follows:

-- Summary of Data Pump trace levels:

-- ========================================

 

Trace dm dw ora Lines

Level trc in

(Hex) file trace Purpose

------------------------------------------------------------------------

10300 x SHDW: To trace the Shadowprocess (API) (expdp/impdp)

20300 x KUPV: To trace Fixed table

40300 x 'div 'To trace Process services

80300 x KUPM: To trace Master ControlProcess (MCP) (DM)

100300 x KUPF: To trace File Manager

200300 x KUPC: To trace Queue services

400300 x KUPW: To trace Workerprocess (es) (DW)

800300 x KUPD: To trace DataPackage

1000300 x META: To trace Metadata Package

--- +

1FF0300 x 'all' To trace all components (full tracing)

 

To trace all the data pump components, you only need to specify the trace value as 1ff0300.

 

If we want to notify the trace of multiple data pump components, we can overwrite the code of these components, such:

 

-- Example of combination (last 4 digits are usually 0300 ):

 

40300 to trace Process services

80300 to trace Master Control Process (MCP)

400300 to trace Worker process (es)

-- +

4C0300 to trace Process services and MasterControl and Worker processes

 

When overlapping, the last four digits remain unchanged, and the values of the first three digits are added.

 

Note:

We recommend that you use a trace value of 480300 in Oracle. setting this value will trace Master Control process (MCP) and theWorker process (es ).

 

2.1.3 log location of the TRACE file

 

The two trace files are in the BACKGROUND_DUMP_DEST directory:

Master Process trace file: <SID >_dm <number >_< process_id>. trc
Worker Process trace file: <SID> _ dw <number> _ <process_id>. trc

 

There is also a directory in USER_DUMP_DEST:

Shadow Processtrace file: <SID> _ ora _ <process_id>. trc

 

 

[Oracle @ asm u01] $ orzparam background

 

Session altered.

 

NAME ISDEFAULT SESMO SYSMOD VALUE

--------------------------------------------------------------------------------------

Background_core_dump true false partial

Background_dump_dest true false immediate/u01/app/oracle/diag/rdbms/dave/trace

 

 

[Oracle @ asm u01] $ orzparam user_dump

 

Session altered.

 

NAME ISDEFAULT SESMO SYSMOD VALUE

------------------------------------------------------------------------------------

User_dump_dest true false immediate/u01/app/oracle/diag/rdbms/dave/trace

 

 

 

 

2.1.4 TRACE example

 

SQL> set lin 160 pages 200

SQL> col owner for a10

SQL> col DIRECTORY_PATH for a50

SQL> select * from dba_directories;

 

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------------------------------------------------------------------

SYS ORACLE_OCM_CONFIG_DIR/u01/app/oracle/11.2.0/db_1/Cr/state

SYS DATA_PUMP_DIR/u01/app/oracle/admin/dave/dpdump/

Sys xmldir/u01/app/oracle/11.2.0/db_1/rdbms/xml

 

 

 

SQL>

 

SQL> create directory backup as '/u01/backup ';

Directory created.

 

SQL> select * from dba_directories;

 

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------------------------------------------------------------------

SYS ORACLE_OCM_CONFIG_DIR/u01/app/oracle/11.2.0/db_1/Cr/state

SYS DATA_PUMP_DIR/u01/app/oracle/admin/dave/dpdump/

Sys xmldir/u01/app/oracle/11.2.0/db_1/rdbms/xml

Sys backup/u01/backup

 

 

-- Create a test table: tianlesoftware:

SQL> conn system/oracle;

Connected.

SQL> create table tianlesoftware asselect * from dba_objects;

Table created.

 

-- Use trace: 480300 for export:

[Oracle @ asm u01] $ expdp system/oracleDIRECTORY = backup DUMPFILE = dave. dmp LOGFILE = dave. log TABLES = tianlesoftwareTRACE = 480300

 

Export: Release 11.2.0.3.0-Production onMon May 27 19:50:49 2013

 

Copyright (c) 1982,201 1, Oracle and/or itsaffiliates. All rights reserved.

 

Connected to: Oracle Database 11 gEnterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

And Real Application Testing options

Starting "SYSTEM". "SYS_EXPORT_TABLE_01": system/********* DIRECTORY = backupDUMPFILE = dave. dmp LOGFILE = dave. log TABLES = tianlesoftware TRACE = 480300

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 9 MB

Processing object typeTABLE_EXPORT/TABLE

Processing object typeTABLE_EXPORT/TABLE/PRE_TABLE_ACTION

.. Exported "SYSTEM". "TIANLESOFTWARE" 7.215 MB 74608 rows

Master table "SYSTEM". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

**************************************** **************************************

Dump file set for SYSTEM. SYS_EXPORT_TABLE_01is:

/U01/backup/dave. dmp

Job "SYSTEM". "SYS_EXPORT_TABLE_01" successfully completed at19: 51: 22

 

 

-- View the trace file:

 

-Rw-r ----- 1 oracle asmadmin 1433 May 27 :51 dave_dw00_7486.trm

-Rw-r ----- 1 oracle asmadmin 32109 May 27 :51 dave_dw00_7486.trc

-Rw-r ----- 1 oracle asmadmin 1417 May 27 19:51 dave_dm00_7484.trm

-Rw-r ----- 1 oracle asmadmin 25025 May 27 19:51 dave_dm00_7484.trc

 

-Rw-r ----- 1 oracle asmadmin 2908 May 27 :50 dave_ora_7480.trc

 

 

[Oracle @ asm trace] $ head-50 dave_dw00_7486.trc

Trace file/u01/app/oracle/diag/rdbms/dave/trace/dave_dw00_7486.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0-64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

And Real Application Testing options

ORACLE_HOME =/u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86 _ 64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 42

Unix process pid: 7486, image: oracle @ asm (DW00)

 

 

* ** 2013-05-27 19:50:55. 052

* ** Session id: (24.127) 2013-05-2719: 50: 55.052

* ** Client id :() 19:50:55. 052

* ** Service name :( SYS $ BACKGROUND) 2013-05-2719: 50: 55.052

* ** Module name :() 19:50:55. 052

* ** Action name :() 19:50:55. 052

 

KUPP: 19: 50: 55.051: Current trace/debugflags: 00480300 = 4719360

* ** Module name :( Data Pump Worker) 19:50:55. 054

* ** Action name :( SYS_EXPORT_TABLE_01) 2013-05-27 19:50:55. 054

 

KUPW: 19: 50: 55.054: 0: alter session enableparallel dml called.

KUPW: 19: 50: 55.054: 0: alter session enableparallel dml returned.

KUPC: 19: 50: 55.098: Setting remote flag forthis process to FALSE

Prvquota is-Enter

Prvquota is subtab_name upd

Prvquota is sys table upd

KUPW: 19: 50: 55.665: 0: KUPP $ PROC. WHATS_MY_IDcalled.

KUPW: 19: 50: 55.665: 1: KUPP $ PROC. WHATS_MY_IDreturned.

KUPW: 19: 50: 55.666: 1: worker max messagenumber: 1000

KUPW: 19: 50: 55.668: 1: Full cluster accessallowed

KUPW: 19: 50: 55.669: 1: Original job starttime: 13-MAY-27 07:50:51

KUPW: 19: 50: 55.671: 1: Seqno 16 isTABLE_EXPORT/TABLE/TABLE_DATA

KUPW: 19: 50: 55.671: 1: Seqno 63 isTABLE_EXPORT/TABLE/INDEX/TABLE_DATA

KUPW: 19: 50: 55.671: 1: KUPP $ PROC. WHATS_MY_NAME called.

KUPW: 19: 50: 55.671: 1: KUPP $ PROC. WHATS_MY_NAME returned. Process name: DW00

KUPW: 19: 50: 55.671: 1: KUPV $ FT_INT.GET_INSTANCE_ID called.

KUPW: 19: 50: 55.674: 1: KUPV $ FT_INT.GET_INSTANCE_ID returned. Instance name: dave

KUPW: 19: 50: 55.679: 1: alter session enableresumable called.

KUPW: 19: 50: 55.679: 1: alter session enableresumable returned.

KUPW: 19: 50: 55.682: 1: KUPF $ FILE. INITcalled.

KUPW: 19: 50: 55.883: 1: KUPF $ FILE. INITreturned.

KUPW: 19: 50: 55.886: 1: KUPF $ FILE. GET_MAX_CSWIDTH called.

KUPW: 19: 50: 55.886: 1: KUPF $ FILE. GET_MAX_CSWIDTH returned.

[Oracle @ asm trace] $

 

 

[Oracle @ asm trace] $ head-50 dave_dm00_7484.trc

Trace file/u01/app/oracle/diag/rdbms/dave/trace/dave_dm00_7484.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0-64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

And Real Application Testing options

ORACLE_HOME =/u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86 _ 64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 35

Unix process pid: 7484, image: oracle @ asm (DM00)

 

 

* ** 19:50:50. 817

* ** Session id: (66.5) 2013-05-2719: 50: 50.817

* ** Client id :() 19:50:50. 817

* ** Service name :( SYS $ USERS) 2013-05-2719: 50: 50.817

* ** Module name :() 19:50:50. 817

* ** Action name :() 19:50:50. 817

 

KUPP: 19: 50: 50.816: Current trace/debugflags: 00480300 = 4719360

* ** Module name :( Data Pump Master) 19:50:50. 823

* ** Action name :( SYS_EXPORT_TABLE_01) 19:50:50. 823

 

KUPC: 19: 50: 50.823: Setting remote flag forthis process to FALSE

Prvquota is-Enter

Prvquota is subtab_name upd

Prvquota is sys table upd

KUPM: 19: 50: 50.884: Attached to controlqueue as MCP

KUPM: 19: 50: 50.884: While starting, controlqueue subscriber count is: 2

KUPP: 19: 50: 50.884: Initialization completefor master process DM00

KUPM: 19: 50: 50.916: Entered main loop

KUPM: 19: 50: 50.922: *** in dispatch at71450, request type = 1001

KUPM: 19: 50: 50.922: Current user is: SYSTEM

KUPM: 19: 50: 50.922: hand: = DBMS_DATAPUMP.OPEN ('Port', 'table', '', 'sys _ EXPORT_TABLE_01 ','', '2 ');

 

* ** 19:50:51. 719

KUPM: 19:50: 51.719: Resumable enabled

KUPM: 19: 50: 51.734: Entered state: DEFINING

KUPM: 19: 50: 51.734: initing file system

KUPM: 19: 50: 51.749: *** out dispatch, request type = 1001, response type = 2041

Kwqberlst! Retval block

Kwqberlst rqan-> lagno_kwqiia 5

Kwqberlst rqan-> lascn_kwqiia> 0 block

Kwqberlst rqan-> lascn_kwqiia 5

Kwqberlst ascn 1644468 lascn 22

KUPM: 19: 50: 51.764: *** in dispatch at71451, request type = 1035

 

 

[Oracle @ asm trace] $ head-50 dave_ora_7480.trc

Trace file/u01/app/oracle/diag/rdbms/dave/trace/dave_ora_7480.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0-64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

And Real Application Testing options

ORACLE_HOME =/u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86 _ 64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 36

Unix process pid: 7480, image: oracle @ asm (TNS V1-V3)

 

 

* ** 19:50:49. 871

* ** Session id: (21.359) 2013-05-2719: 50: 49.871

* ** Client id :() 19:50:49. 871

* ** Service name :( SYS $ USERS) 2013-05-2719: 50: 49.871

* ** Module name :( ude @ asm (TNS V1-V3) 19:50:49. 871

* ** Action name :() 19:50:49. 871

 

KUPP: 19: 50: 49.871: Input trace/debug flags: 00480300 = 4719360

KUPP: 19: 50: 49.873: Current trace/debugflags: 00480300 = 4719360

 

* ** 19:50:50. 633

KUPC: 19: 50: 50.633: Setting remote flag forthis process to FALSE

Prvquota is-Enter

Prvquota is subtab_name upd

Prvquota is sys table upd

Prvquota is-Enter

Prvquota is subtab_name upd

Prvquota is sys table upd

Kwqberlst rqan-> lascn_kwqiia> 0 block

Kwqberlst rqan-> lascn_kwqiia 5

Kwqberlst ascn 1644478 lascn 22

Kwqberlst! Retval block

Kwqberlst rqan-> lagno_kwqiia 5

 

* ** 19:50:51. 760

Kwqberlst rqan-> lascn_kwqiia> 0 block

Kwqberlst rqan-> lascn_kwqiia 5

Kwqberlst ascn 1644478 lascn 22

Kwqberlst! Retval block

Kwqberlst rqan-> lagno_kwqiia 5

Kwqberlst rqan-> lascn_kwqiia> 0 block

Kwqberlst rqan-> lascn_kwqiia 5

Kwqberlst ascn 1644478 lascn 22

Kwqberlst! Retval block

[Oracle @ asm trace] $

1 2 Next Page