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] $