使用隱含Trace引數診斷Oracle Data Pump（expdp）故障
使用隱含Trace引數診斷Oracle Data Pump（expdp）故障
Data Pump資料泵是Oracle從10g開始推出的，用於取代傳統exp/imp工具的資料備份還原元件。經過若干版本的演進和修改，Data Pump已經非常成熟，逐漸被越來越多的DBA和運維人員接受。
相對於傳統的exp/imp，Data Pump有很多優勢，也變得更加複雜。資料泵一個最顯著的特點就是Server-Side執行。Exp/Imp是執行在客戶端上面的小工具，雖然使用方便，但是需要處理資料來源端和目標端各自伺服器和客戶端四個版本的差異相容問題。這就是為什麼網路上很多朋友都在糾結如何處理Exp/Imp的版本差異。而且，執行在客戶端上的Exp/Imp受網路影響很大，一旦操作時間較長網路不穩定，操作過程可能就以失敗告終。同時，exp/imp還存在很多效能、穩定性和特性支援上的不足。
Data Pump資料泵是執行在服務端，直接就減少了版本問題出現的可能。即使存在版本問題，使用version引數也可以進行有效的控制。此外單獨的作業執行，可以避免出現意外中斷的情況。
儘管如此，我們還是經常會遇到Data Pump的故障和問題，很多時候僅僅藉助提示資訊不能做到完全的診斷。這個時候，我們可以考慮使用Data Pump的隱藏引數Trace來生成跟蹤檔案，逐步排查錯誤。
1、 Data Pump工作原理和環境準備
Data Pump工作原理有兩個特點：作業排程，多程式配合協作。在Oracle中，Data Pump是作為一個特定的Job來進行處理的，可以進行Job作業的啟動、終止、暫停，而且更重要的是Dump作業的工作過程是獨立於外部使用者的。也就是說，使用者不需要和Exp/Imp一樣“死盯著”介面，也不需要使用nohup &後臺作業化，就可以實現自動的後臺操作。
在工作中，Data Pump是一個多程式配合的工作。我們從工作日誌上就可以看到，每個Data Pump作業在建立的時候，會自動建立一個作業表，其中記錄操作過程。Job工作的時候有兩類Process程式工作，一個是master control process，負責整體過程協調，Work Process池管理，任務分配。實際進行匯入匯出的是Work process，如果設定了parallel引數，就會有多個Work Process進行資料工作。
對Data Pump的診斷本質上就是對各種Process行為的跟蹤。Oracle提供了一個Trace的隱含引數，來幫助我們實現這個目標。
首先，我們準備一下Data Pump工作環境。開始需要準備Directory物件。
[root@SimpleLinux /]# ls -l | grep dumpdata
drwxr-xr-x 2 root root 4096 Sep 11 09:01 dumpdata
[root@SimpleLinux /]# chown -R oracle:oinstall dumpdata/
[root@SimpleLinux /]# ls -l | grep dumpdata
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 09:01 dumpdata
--建立directory物件
SQL> select * from v$version where rownum<2;
BANNER
-----------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Producti
SQL> create directory dumpdir as '/dumpdata';
Directory created
2、隱含引數Trace
Trace引數是Data Pump隱含內部使用的一個引數。使用方法和其他資料泵引數相同，但是使用取值需要有一些注意之處。下面是我們實驗的Trace命令。
[oracle@SimpleLinux dumpdata]$ expdp \"/ as sysdba\" directory=dumpdir schemas=scott dumpfile=scott_dump.dmp parallel=2 trace=480300
Export: Release 11.2.0.3.0 - Production on Wed Sep 11 09:45:07 2013
Trace並不像其他跟蹤過程相同，使用y/n的引數，開啟或者關閉。Data Pump的Trace引數是一個7位十六進位制組成的數字串。不同的數字串表示不同的跟蹤物件方法。7位十六進位制數字分為兩個部分，前三個數字表示特定的資料泵元件，後四位使用0300就可以。
根據Oracle MOS中提供資訊資料，Trace字元遵守如下設定規則：
ü 不要輸入超過7位長度；
ü 不需要使用0X指定十六進位制字元；
ü 不能將十六進位制字元轉化為數字取值；
ü 如果7位字元以0開頭，可以省略0；
ü 輸入字元大小寫不敏感；
各個元件分別使用不同的三位十六進位制數字代表。如下片段所示：
-- Summary of Data Pump trace levels:
-- ==================================
Trace DM DW ORA Lines
level trc trc trc in
(hex) file file file trace Purpose
------- ---- ---- ---- ------ -----------------------------------------------
10300 x x x SHDW: To trace the Shadow process (API) (expdp/impdp)
20300 x x x KUPV: To trace Fixed table
40300 x x x 'div' To trace Process services
80300 x KUPM: To trace Master Control Process (MCP) (DM)
100300 x x KUPF: To trace File Manager
200300 x x x KUPC: To trace Queue services
400300 x KUPW: To trace Worker process(es) (DW)
800300 x KUPD: To trace Data Package
1000300 x META. To trace Metadata Package
--- +
1FF0300 x x x 'all' To trace all components (full tracing)
如果需要同時跟蹤多個元件，需要將目標元件的hex值進行累加，後面四位的300相同。
目標Dump作業生成的Trace檔案，同其他Trace檔案沒有什麼本質差異。預設都是在BACKGROUP_DUMP_DEST目錄。但是注意，Data Pump的Trace過程，會生成多個Trace檔案，而且定位需要知道dm和dw的Process ID資訊。
筆者建議的一種方法是，如果系統業務不是非常繁忙，可以將目錄上的Trc和trm檔案暫時儲存在其他的地方。再進行Trace作業，此時生成的檔案就可以明顯看出是哪些。
對於跟蹤的Trace取值，Oracle建議使用480300就可以應對大部分的情況。480300會跟蹤Oracle Dump作業的Master Control Process（MCP）和Work Process。作為初始化跟蹤的過程，480300基本就夠用了。
3、Expdp Trace過程
我們先從資料匯出Expdp看Trace，匯出一個案例。首先清理一下Trace File目錄。
[oracle@SimpleLinux trace]$ rm *.trc
[oracle@SimpleLinux trace]$ rm *.trm
[oracle@SimpleLinux trace]$ ls -l
total 92
-rw-r----- 1 oracle oinstall 86384 Sep 11 09:37 alert_ora11g.log
呼叫命令，以兩個並行度的方法進行匯出動作。
[oracle@SimpleLinux dumpdata]$ expdp \"/ as sysdba\" directory=dumpdir schemas=scott dumpfile=scott_dump.dmp parallel=2 trace=480300
Export: Release 11.2.0.3.0 - Production on Wed Sep 11 09:45:07 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dumpdir schemas=scott dumpfile=scott_dump.dmp parallel=2 trace=480300
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32.18 MB
Processing object type SCHEMA_EXPORT/USER
. . exported "SCOTT"."T_MASTER":"P1" 42.43 KB 982 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
（篇幅原因，有省略……）
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."T_MASTER":"P2" 88.69 KB 1859 rows
. . exported "SCOTT"."T_SLAVE":"P1" 412.2 KB 11268 rows
. . exported "SCOTT"."T_SLAVE":"P2" 975.7 KB 21120 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/dumpdata/scott_dump.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:45:36
我們從日誌上能看出Parallel的一點不一樣，額外的T_MASTER.P1被提前匯出了。
新生成的Trace檔案目錄。
[oracle@SimpleLinux trace]$ ls -l
total 260
-rw-r----- 1 oracle oinstall 87421 Sep 11 09:45 alert_ora11g.log
-rw-r----- 1 oracle oinstall 40784 Sep 11 09:45 ora11g_dm00_3894.trc
-rw-r----- 1 oracle oinstall 1948 Sep 11 09:45 ora11g_dm00_3894.trm
-rw-r----- 1 oracle oinstall 73971 Sep 11 09:45 ora11g_dw00_3896.trc
-rw-r----- 1 oracle oinstall 1986 Sep 11 09:45 ora11g_dw00_3896.trm
-rw-r----- 1 oracle oinstall 27366 Sep 11 09:45 ora11g_dw01_3898.trc
-rw-r----- 1 oracle oinstall 982 Sep 11 09:45 ora11g_dw01_3898.trm
-rw-r----- 1 oracle oinstall 3016 Sep 11 09:45 ora11g_ora_3890.trc
-rw-r----- 1 oracle oinstall 209 Sep 11 09:45 ora11g_ora_3890.trm
Dm和dw標註的就是MCP和Work Process生成的Trace檔案。同時Parallel設定使得dw有00和01兩個。
在匯出過程中，我們可以看到兩個worker的會話資訊。
SQL> select * from dba_datapump_sessions;
OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE
------------------------------ ------------------------------ ---------- -------- --------------
SYS SYS_EXPORT_SCHEMA_01 1 35EB0580 DBMS_DATAPUMP
SYS SYS_EXPORT_SCHEMA_01 1 35E95280 MASTER
SYS SYS_EXPORT_SCHEMA_01 1 35E8A480 WORKER
SYS SYS_EXPORT_SCHEMA_01 1 35E84D80 WORKER
此時我們可以從Trace檔案中，看到一些Data Pump工作的細節資訊。例如：在MCP的Trace檔案中，我們看到一系列呼叫動作過程，如下片段：
--初始化匯出動作，整理檔案系統；
KUPM:09:45:08.720: ****IN DISPATCH at 35108, request type=1001
KUPM:09:45:08.721: Current user is: SYS
KUPM:09:45:08.721: hand := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA', '', 'SYS_EXPORT_SCHEMA_01', '', '2');
KUPM:09:45:08.791: Resumable enabled
KUPM:09:45:08.799: Entered state: DEFINING
KUPM:09:45:08.799: initing file system
*** 2013-09-11 09:45:08.893
KUPM:09:45:08.893: ****OUT DISPATCH, request type=1001, response type =2041
--日誌寫入
KUPM:09:45:12.135: ****IN DISPATCH at 35112, request type=3031
KUPM:09:45:12.135: Current user is: SYS
KUPM:09:45:12.136: Log message received from worker DG,KUPC$C_1_20130911094507,KUPC$A_1_094510040559000,MCP,3,Y
KUPM:09:45:12.136: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 4
kwqberlst ascn 986758 lascn 0
KUPM:09:45:12.137: ****OUT DISPATCH, request type=3031, response type =2041
在Worker Process中，如下片段看出在匯出資料。
KUPW:09:45:12.153: 1:
KUPW:09:45:12.153: 1:
KUPW:09:45:12.153: 1: TABLE
KUPW:09:45:12.153: 1: SCOTT
KUPW:09:45:12.153: 1: DEPT
KUPW:09:45:12.154: 1: In procedure LOCATE_DATA_FILTERS
KUPW:09:45:12.154: 1: In function NEXT_PO_NUMBER
KUPW:09:45:12.161: 1: In procedure DETERMINE_METHOD_PARALLEL
KUPW:09:45:12.161: 1: flags mask: 0
KUPW:09:45:12.161: 1: dapi_possible_meth: 1
KUPW:09:45:12.161: 1: data_size: 65536
KUPW:09:45:12.161: 1: et_parallel: TRUE
KUPW:09:45:12.161: 1: object: TABLE_DATA:"SCOTT"."DEPT"
KUPW:09:45:12.164: 1: l_dapi_bit_mask: 7
KUPW:09:45:12.164: 1: l_client_bit_mask: 7
KUPW:09:45:12.164: 1: TABLE_DATA:"SCOTT"."DEPT" either, parallel: 1
KUPW:09:45:12.164: 1: In function GATHER_PARSE_ITEMS
KUPW:09:45:12.165: 1: In function CHECK_FOR_REMAP_NETWORK
KUPW:09:45:12.165: 1: Nothing to remap
KUPW:09:45:12.165: 1: In procedure BUILD_OBJECT_STRINGS
KUPW:09:45:12.165: 1: In DETERMINE_BASE_OBJECT_INFO
KUPW:09:45:12.165: 1: TABLE_DATA
KUPW:09:45:12.165: 1: SCOTT
KUPW:09:45:12.165: 1: EMP
4、Impdp匯入過程
在Trace過程中，我們也可以如10046跟蹤過程一樣，新增SQL跟蹤。Data Pump本質上工作還是一系列的SQL語句，很多時候的效能問題根源都是從SQL著手的。
切換到SQL跟蹤模式也比較簡單，一般是在Trace數值後面新增1。我們使用匯入過程進行實驗。
--處理之前
[root@SimpleLinux trace]# ls -l
total 4
-rw-r----- 1 oracle oinstall 552 Sep 11 10:49 alert_ora11g.log
[oracle@SimpleLinux dumpdata]$ impdp \"/ as sysdba\" directory=dumpdir dumpfile=scott_dump.dmp remap_schema=scott:test trace=480301 parallel=2
Import: Release 11.2.0.3.0 - Production on Wed Sep 11 10:50:14 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dumpdir dumpfile=scott_dump.dmp remap_schema=scott:test trace=480301 parallel=2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_MASTER":"P1" 42.43 KB 982 rows
. . imported "TEST"."T_MASTER":"P2" 88.69 KB 1859 rows
. . imported "TEST"."T_SLAVE":"P1" 412.2 KB 11268 rows
. . imported "TEST"."T_SLAVE":"P2" 975.7 KB 21120 rows
. . imported "TEST"."DEPT" 5.929 KB 4 rows
. . imported "TEST"."EMP" 8.562 KB 14 rows
. . imported "TEST"."SALGRADE" 5.859 KB 5 rows
. . imported "TEST"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 10:50:24
檢視跟蹤目錄。
[root@SimpleLinux trace]# ls -l
total 7588
-rw-r----- 1 oracle oinstall 739 Sep 11 10:50 alert_ora11g.log
-rw-r----- 1 oracle oinstall 1916394 Sep 11 10:50 ora11g_dm00_4422.trc
-rw-r----- 1 oracle oinstall 9446 Sep 11 10:50 ora11g_dm00_4422.trm
-rw-r----- 1 oracle oinstall 2706475 Sep 11 10:50 ora11g_dw00_4424.trc
-rw-r----- 1 oracle oinstall 15560 Sep 11 10:50 ora11g_dw00_4424.trm
-rw-r----- 1 oracle oinstall 2977812 Sep 11 10:50 ora11g_ora_4420.trc
-rw-r----- 1 oracle oinstall 12266 Sep 11 10:50 ora11g_ora_4420.trm
-rw-r----- 1 oracle oinstall 29795 Sep 11 10:50 ora11g_p000_4426.trc
-rw-r----- 1 oracle oinstall 526 Sep 11 10:50 ora11g_p000_4426.trm
-rw-r----- 1 oracle oinstall 30109 Sep 11 10:50 ora11g_p001_4428.trc
-rw-r----- 1 oracle oinstall 524 Sep 11 10:50 ora11g_p001_4428.trm
-rw-r----- 1 oracle oinstall 8430 Sep 11 10:50 ora11g_p002_4430.trc
-rw-r----- 1 oracle oinstall 184 Sep 11 10:50 ora11g_p002_4430.trm
-rw-r----- 1 oracle oinstall 8432 Sep 11 10:50 ora11g_p003_4432.trc
-rw-r----- 1 oracle oinstall 204 Sep 11 10:50 ora11g_p003_4432.trm
目錄生成的Trace檔案，都是10046格式的Raw檔案。擷取片段如下：
=====================
PARSING IN CURSOR #13035136 len=51 dep=2 uid=0 ct=3 lid=0 tim=1378867817703043 hv=1523794037 ad='360b079c' sqlid='b1wc53ddd6h3p'
select audit$,options from procedure$ where obj#=:1
END OF STMT
PARSE #13035136:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1637390370,tim=1378867817703039
EXEC #13035136:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1637390370,tim=1378867817703178
FETCH #13035136:c=0,e=53,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=1637390370,tim=1378867817703248
STAT #13035136 id=1 cnt=1 pid=0 pos=1 bj=221 p='TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=0 pw=0 time=53 us cost=2 size=47 card=1)'
STAT #13035136 id=2 cnt=1 pid=1 pos=1 bj=231 p='INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=24 us cost=1 size=0 card=1)'
CLOSE #13035136:c=0,e=7,dep=2,type=1,tim=1378867817703387
=====================
5、結論
Oracle Data Pump已經非常成熟，也越來越多被人們接受。Trace引數尤其存在的歷史背景，相信使用的機會越來越少。不過，作為研究內部機制的用途，還是比較有用的。
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (文件 ID 286496.1)
In this Document
Purpose
Scope
Details
1. Introduction.
2. How to create a Data Pump trace file ?? Parameter: TRACE
3. How to start tracing the Data Pump job ?
4. How are Data Pump trace files named, and where to find them ?
5. How to get a detailed status report of a Data Pump job ?? Parameter: STATUS
6. How to get timing details on processed objects ? Parameter: METRICS
7. How to get SQL trace files of the Data Pump processes ?
8. How to get header details of Export Data Pump dumpfiles ?
9. How to get Data Definition Language (DDL) statements ? Parameter: SQLFILE
10. How to get the DDL both as SQL statements and as XML data ?
Additional Resources
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
PURPOSE
This document describes how to use the TRACE parameter with the Export Data Pump (expdp) and Import Data Pump (impdp) database utilities when diagnosing incorrect behavior and/or troubleshooting Data Pump errors.
SCOPE
The article is intended for users of the database utilities Export Data Pump (expdp) and Import Data Pump (impdp), and who need to troubleshoot the execution of the jobs that are generated by these utilities. These database utilities were introduced with Oracle10g. The article gives detailed information how to use the undocumented parameter TRACE.
DETAILS
1. Introduction.
In Oracle10g, we introduced the new database utilities Export Data Pump and Import Data Pump.
Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export job and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.
Example output when a full database Export Data Pump is running with parallelism 2:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f%U.dmp \
LOGFILE=expdp_f.log FULL=y PARALLEL=2
% ps -ef | grep expdp
oracle 8874 8704 1 07:00 pts/2 00:00:03 expdp DIRECTORY=my_dir ...
% ps -ef | grep ORCL
oracle 8875 8874 4 07:00 ? 00:00:11 oracleORCL (DESCRIPTION=(LOCAL=YES) ...
oracle 8879 1 3 07:00 ? 00:00:08 ora_dm00_ORCL
oracle 8881 1 94 07:00 ? 00:04:17 ora_dw01_ORCL
oracle 8893 1 3 07:00 ? 00:00:09 ora_dw02_ORCL
...
-- Obtain Data Pump process info:
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
DATE PROGRAM SID STATUS
------------------- -------------------------------------- ------- --------
2007-10-19 07:01:03 ude@celclnx7.us.oracle.com (TNS V1-V3) 140 ACTIVE
2007-10-19 07:01:03 oracle@celclnx7.us.oracle.com (DM00) 152 ACTIVE
2007-10-19 07:01:03 oracle@celclnx7.us.oracle.com (DW01) 144 ACTIVE
2007-10-19 07:01:03 oracle@celclnx7.us.oracle.com (DW02) 159 ACTIVE
USERNAME JOB_NAME SPID SERIAL# PID
---------- ------------------------------ ------- ------- -------
SYSTEM SYS_EXPORT_FULL_01 8875 8 18
SYSTEM SYS_EXPORT_FULL_01 8879 21 21
SYSTEM SYS_EXPORT_FULL_01 8881 7 22
SYSTEM SYS_EXPORT_FULL_01 8893 26 23
The Data Pump processes will disappear when the Data Pump job completes or is (temporary) stopped.
Improved tracing capabilities have been implemented with these utilities. The tracing can be controlled with the TRACE parameter.
Data Pump is server based and not client based. This means that most Data Pump specific defects will be fixed on the server side (changes in packages in source and target database) and not on the client side (Export or Import Data Pump client). If a Data Pump defect that occurs during an import job is fixed in a later patchset (e.g. 10.2.0.4.0) and the target database is still on the base release (10.2.0.1.0) then the defect will still occur when importing with a 10.2.0.4.0 Import Data Pump client into this 10.2.0.1.0 target database. The same applies to export jobs. It is therefore recommended that both the source database and the target database have the latest patchset installed. For details, see also:
Note:553337.1 "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions"
2. How to create a Data Pump trace file ? Parameter: TRACE
Tracing can be enabled by specifying an 7 digit hexadecimal mask in the TRACE parameter of Export DataPump (expdp) or Import DataPump (impdp). The first three digits enable tracing for a specific Data Pump component, while the last four digits are usually: 0300.
Any leading zero's can be omitted, and the value specified for the TRACE parameter is not case sensitive.
Example:
TRACE = 04A0300
-- or:
TRACE=4a0300
Some rules to remember when specifying a value for the TRACE parameter:
- do not specify more than 7 hexadecimal digits;
- do not specify the typical leading 0x hexadecimal specification characters;
- do not convert the hexadecimal value to a decimal value;
- omit any leading zero's (not required though);
- values are not case sensitive.
When using the TRACE parameter, an error may occur if the Data Pump job is run with a non-privileged user, e.g.:
% expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=emp TRACE=480300
Export: Release 10.2.0.3.0 - Production on Friday, 19 October, 2007 13:46:33
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-31631: privileges are required
To resolve this problem: ensure that the user that connects to the database with the Export Data Pump or Import Data Pump utility, is a privileged user (i.e. a user who has the DBA role or the EXP_FULL_DATABASE resp. IMP_FULL_DATABASE role), e.g.:
-- run this Data Pump job with TRACE as a privileged user:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=scott.emp TRACE=480300
-- or:
-- make user SCOTT a privileged user:
CONNECT / AS SYSDBA
GRANT exp_full_database TO scott;
% expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=emp TRACE=480300
For details, see also:
Note:351598.1 "Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)"
Also ensure that the init.ora/spfile initialization parameter MAX_DUMP_FILE_SIZE is large enough to capture all the trace information (or set it to unlimited which is the default value), e.g.:
-- Ensure enough trace data can be written to the trace files:
CONNECT / as sysdba
SHOW PARAMETER max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 10M
ALTER SYSTEM SET max_dump_file_size = unlimited SCOPE = both;
SHOW PARAMETER max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED
The majority of errors that occur during a Data Pump job, can be diagnosed by creating a trace file for the Master Control Process (MCP) and the Worker Process(es) only, e.g.:
-- To run a Data Pump job with standard tracing:
-- This results in two trace files in BACKGROUND_DUMP_DEST:
-- Master Process trace file: _dm_.trc
-- Worker Process trace file: _dw_.trc
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott TRACE=480300
Each Data Pump component can be specified explicitly in order to obtain tracing details of that component:
-- Summary of Data Pump trace levels:
-- ==================================
Trace DM DW ORA Lines
level trc trc trc in
(hex) file file file trace Purpose
------- ---- ---- ---- ------ -----------------------------------------------
10300 x x x SHDW: To trace the Shadow process (API) (expdp/impdp)
20300 x x x KUPV: To trace Fixed table
40300 x x x 'div' To trace Process services
80300 x KUPM: To trace Master Control Process (MCP) (DM)
100300 x x KUPF: To trace File Manager
200300 x x x KUPC: To trace Queue services
400300 x KUPW: To trace Worker process(es) (DW)
800300 x KUPD: To trace Data Package
1000300 x META: To trace Metadata Package
------- 'Bit AND'
1FF0300 x x x 'all' To trace all components (full tracing)
Combinations of tracing Data Pump components are possible, e.g.:
-- 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)
------- 'Bit AND'
4C0300 to trace Process services and Master Control and Worker processes
In order to trace all Data Pump components, level 1FF0300 can be specified:
-- Run a Data Pump job with full tracing:
-- This results in two trace files in BACKGROUND_DUMP_DEST:
-- Master Process trace file: _dm_.trc
-- Worker Process trace file: _dw_.trc
-- And one trace file in USER_DUMP_DEST:
-- Shadow Process trace file: _ora_.trc
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log FULL=y TRACE=1ff0300
Note: For initial Data Pump tracing, the value 480300 is sufficient. When value 480300 is specified, we will trace the Master Control process (MCP) and the Worker process(es). When creating a Data Pump trace file, we recommend to use the value 480300 unless a different trace level is requested by Oracle Support.
3. How to start tracing the Data Pump job ?
Depending on how the Export or Import Data Pump job was started, there are several ways to activate tracing of the Data Pump processes.
3.1. Use the TRACE parameter upon the start of the job.
Start tracing by specifying the TRACE parameter and a trace level when the Export Data Pump or Import Data Pump is started. This is the standard way to activate Data Pump tracing.
Example:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=scott.emp TRACE=480300
3.2. Use the TRACE parameter when restarting a job.
If an Export or Import Data Pump job was started without the TRACE parameter, it can be temporary stopped, and restarted with the TRACE parameter.
Example:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f.dmp \
LOGFILE=expdp_f.log FULL=Y
Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 17:11:08
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** DIRECTORY=my_dir
DUMPFILE=expdp_f.dmp LOGFILE=expdp_f.log FULL=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA-- press Control-C to go to the Interactive Command mode,
-- and temporarystop the job with the STOP_JOB command:
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
-- use the system generated job-name SYS_EXPORT_FULL_01 to re-attach to the job
-- and specify the TRACE parameter with a tracing level:
% expdp system/manager ATTACH=sys_export_full_01 TRACE=480300
Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 17:23:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: FALSE
GUID: F4E6BF997DFA46378D543F998E82653E
Start Time: Thursday, 18 October, 2007 17:23:49
Mode: FULL
Instance: m10203wa
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** DIRECTORY=my_dir DUMPFILE=expdp_f.dmp
LOGFILE=expdp_f.log FULL=y
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /usr/DataPump/expdp_f.dmp
bytes written: 4,096
Worker 1 Status:
State: UNDEFINED
-- restart the job and change back from Interactive Command mode to Logging mode
-- with CONTINUE_CLIENT (note that tracing with level 480300 is now active):
Export> cont
Restarting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** DIRECTORY=my_dir
DUMPFILE=expdp_f.dmp LOGFILE=expdp_s.log FULL=y
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
...
3.3. Use a database init.ora/spfile event to trace Data Pump.
Data Pump tracing can also be started with a line with EVENT 39089 in the initialization parameter file. This method should only be used to trace the Data Pump calls in an early state, e.g. if details are needed about the DBMS_DATAPUMP.OPEN API call. Trace level 0x300 will trace all Data Pump client processes.
Example when using init.ora initialization parameter file:
- add the following line to init.ora parameter file:
EVENT="39089 trace name context forever,level 0x300"
- Restart the database.
- Start the Export Data Pump or Import Data Pump job.
Example when using spfile initialization parameter file:
-- when using spfile parameter file:
CONNECT / as sysdba
SHOW PARAMETER event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string
ALTER SYSTEM SET EVENTS = '39089 trace name context forever, level 0x300'
SCOPE = spfile;
SHUTDOWN immediate
STARTUP
SHOW PARAMETER event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 39089 trace name context forev
er, level 0x300
- Start the Export Data Pump or Import Data Pump job.
-- to remove the event(s) again:
ALTER SYSTEM RESET EVENT SCOPE = spfile SID='*';
SHUTDOWN immediate
STARTUP
Altenatively, the event 39089 can also be enabled and disabled at system level using next commands:
-- Enable event
ALTER SYSTEM SET EVENTS = '39089 trace name context forever, level 0x300' ;
-- Disable event
ALTER SYSTEM SET EVENTS = '39089 trace name context off' ;
Note:
Only set this event 39089 if tracing in an early state of job execution is needed. For normal standard tracing, use the TRACE parameter as described in the sections 3.1. and 3.2. above.
For details about setting events, see also:
Note:160178.1 "How to set EVENTS in the SPFILE"
@ For Support:
4. How are Data Pump trace files named, and where to find them ?
Data Pump trace files are written to the directories specified by the init.ora/spfile initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST.
4.1 Data Pump Master Control Process (MCP).
Format : _dm_.trc
Example: ORCL_dm00_2896.trc or: ORCL_dm01_3422.trc (for second active Master Control Process)
Location: BACKGROUND_DUMP_DEST or /trace
4.2. Data Pump Worker Process trace file.
Format : _dw_.trc
Example: ORCL_dw01_2936.trc or: ORCL_dw01_2844.trc and ORCL_dw02_2986.trc (if PARALLEL=2)
Location: BACKGROUND_DUMP_DEST or /trace
4.3. Data Pump Shadow Process trace file.
Format : _ora_.trc
Example: ORCL_ora_3020.trc
Location: USER_DUMP_DEST or /trace
-- determine location of the trace files on disk (Oracle10g):
CONNECT system/manager
SHOW PARAMETER dump
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
background_dump_dest string /oracle/admin/ORCL/BDUMP
user_dump_dest string /oracle/admin/ORCL/UDUMP
...
-- determine location of the trace files on disk (Oracle11g):
CONNECT system/manager
SHOW PARAMETER diag
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
diagnostic_dest string /oracle
Note that in Oracle10g the default location for the trace files is: $ORACLE_HOME/rdbms/log
This location will be used if the initialization parameters BACKGROUND_DUMP_DEST and/or USER_DUMP_DEST are not set.
For Oracle11g, the trace files are written to the /trace
where is: /diag///
where is specified by DIAGNOSTIC_DEST (defaults to: $ORACLE_HOME/log).
E.g.: /oracle/diag/rdbms/ORCL/ORCL/trace
For details, see also:
Note:422893.1 "11g Understanding Automatic Diagnostic Repository"
5. How to get a detailed status report of a Data Pump job ? Parameter: STATUS
If a Data Pump is started, a detailed status report of the job can be obtains with the STATUS parameter in Interactive Command mode.
Example:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f%U.dmp \
LOGFILE=expdp_f.log FILESIZE=2g FULL=y
Export: Release 10.2.0.3.0 - 64bit Production on Friday, 19 October, 2007 14:05:33
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, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** DIRECTORY=my_dir
DUMPFILE=expdp_f%U.dmp LOGFILE=expdp_f.log FILESIZE=2g FULL=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
-- press Control-C to go to the Interactive Command mode,
-- and get a STATUS report of the job:
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /bugmnt7/em/celclnx7/user/expdp/expdp_f01.dmp
bytes written: 4,096
Dump File: /bugmnt7/em/celclnx7/user/expdp/expdp_f%u.dmp
Worker 1 Status:
State: EXECUTING
Object Schema: SCOTT
Object Name: EMP
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 959
Worker Parallelism: 1
-- To get a detailed STATUS report every minute while in Logging mode:
Export> stat=60
Export> cont
Total estimation using BLOCKS method: 8.437 GB
Processing object type DATABASE_EXPORT/TABLESPACE
...
Note that the status information is written only to your standard output device, not to the Data Pump log file.
6. How to get timing details on processed objects ? Parameter: METRICS
With the undocumented parameter METRICS additional information can be obtained about the number of objects that were processed and the time it took for processing them. Objects in this context are for example the exported system GRANT privileges, the imported tablespace quota GRANT statements, the exported CREATE TABLE statements. Note that the METRICS parameter does not create any additional trace files. Instead, the extra details are logged in the logfile of the Export Data Pump or Import Data Pump job.
Example output:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott METRICS=y
Export: Release 10.2.0.3.0 - Production on Thursday, 18 October, 2007 17:05:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=my_dir
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott METRICS=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Completed 2 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Completed 2 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Completed 1 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Completed 1 TABLESPACE_QUOTA objects in 2 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed 1 PROCACT_SCHEMA objects in 5 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 4 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed 2 INDEX objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 2 CONSTRAINT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 1 seconds
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/usr/DataPump/expdp_s.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:06:14
7. How to get SQL trace files of the Data Pump processes ?
For troubleshooting specific situations, it may be required to create a SQL trace file for an Export Data Pump or Import Data Pump job. These SQL trace files can be created by setting Event 10046 for a specific process (usually the Worker process). Note that these SQL trace files can become very large, so ensure that there is enough free space in the directory that is specified by the init.ora/spfile initialization parameter BACKGROUND_DUMP_DEST.
event 10046, level 1 = enable standard SQL_TRACE functionality
event 10046, level 4 = as level 1, plus trace the BIND values
event 10046, level 8 = as level 1, plus trace the WAITs
event 10046, level 12 = as level 1, plus trace the BIND values and the WAITs
Remarks:
· level 1: lowest level tracing - not always sufficient to determine cause of errors;
· level 4: useful when an error in Data Pump's worker or master process occurs;
· level 12: useful when there is an issue with Data Pump performance.
When creating a level 8 or 12 SQL trace file, it is required that the init.ora/spfile initialization parameter TIMED_STATISTICS is set to TRUE before the event is set and before the Data Pump job is started. The performance impact of setting this parameter temporary to TRUE is minimal. The SQL trace files that were created with level 8 or 12 as especially useful for investigating performance problems.
Example:
-- For Event 10046, level 8 and 12: ensure we gather time related statistics:
CONNECT / as sysdba
SHOW PARAMETER timed_statistics
NAME TYPE VALUE
--------------------------------- ----------- ---------------------------
timed_statistics string FALSE
ALTER SYSTEM SET timed_statistics = TRUE SCOPE = memory;
-- Now set the event and start the Data Pump job
-- To set the value back to the default:
ALTER SYSTEM SET timed_statistics = FALSE SCOPE = memory;
7.1. Create a standard SQL_TRACE file (level 1).
If the output of standard SQL_TRACE functionality is sufficient (i.e.: neither bind values nore waits details are needed), then this SQL tracing can be activated with the Data Pump parameter trace. To activate standard SQL tracing, use the value 1.
Example:
-- Trace Worker process (400300) with standard SQL_TRACE functionality (1):
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f.dmp \
LOGFILE=expdp_f.log TABLES=scott.emp TRACE=400301
Note that this level of tracing is usually not sufficient for tracing Data Pump when an error occurs or when there is an issue with Data Pump performance. For tracing Data Pump when an error occurs use level 4, and when there is an issue with Data Pump performance use level 12 (see sections below).
7.2. Activate SQL_TRACE on specific Data Pump process with higher trace level.
If a specific Data Pump process needs to traced, and more SQL_TRACE details are required, and it is not required to trace the start of the job, then the Event 10046 with the desired level can also be set on the process that needs to be traced (usually the Worker process).
Example:
- Start the Data Pump job, e.g.:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f%U.dmp \
LOGFILE=expdp_f.log FILESIZE=2G FULL=y
-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
DATE PROGRAM SID STATUS
------------------- -------------------------------------- ------- --------
2007-10-19 08:58:41 ude@celclnx7.us.oracle.com (TNS V1-V3) 158 ACTIVE
2007-10-19 08:58:41 oracle@celclnx7.us.oracle.com (DM00) 143 ACTIVE
2007-10-19 08:58:41 oracle@celclnx7.us.oracle.com (DW01) 150 ACTIVE
USERNAME JOB_NAME SPID SERIAL# PID
---------- ------------------------------ ------- ------- -------
SYSTEM SYS_EXPORT_FULL_01 17288 29 18
SYSTEM SYS_EXPORT_FULL_01 17292 50 22
SYSTEM SYS_EXPORT_FULL_01 17294 17 23
In the example output above we see that the Data Pump Master process (DM00) has SID: 143 and serial#: 50 and the Data Pump Worker process (DW01) has SID: 150 and serial#: 17. These details can be used to activate SQL tracing in SQL*Plus with DBMS_SYSTEM.SET_EV, e.g.:
-- In SQL*Plus, activate SQL tracing with DBMS_SYSTEM and SID/SERIAL#
-- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')
-- Example to SQL_TRACE Worker process with level 4 (Bind values):
execute sys.dbms_system.set_ev(150,17,10046,4,'');
-- and stop tracing:
execute sys.dbms_system.set_ev(150,17,10046,0,'');
-- Example to SQL_TRACE Master Control process with level 8 (Waits):
execute sys.dbms_system.set_ev(143,50,10046,8,'');
-- and stop tracing:
execute sys.dbms_system.set_ev(143,50,10046,0,'');
The example output of the query above also shows that the Data Pump Master process (DM00) has OS process Id: 17292 and the Data Pump Worker process (DW01) has OS process Id: 17294. With this information, it is also possible to use 'oradebug' in SQL*Plus to activate SQL tracing for those processes, e.g.:
-- In SQL*Plus, activate SQL tracing with ORADEBUG and the SPID:
-- Example to SQL_TRACE Worker process with level 4 (Bind values):
oradebug setospid 17294
oradebug unlimit
oradebug event 10046 trace name context forever, level 4
oradebug tracefile_name
-- Example to SQL_TRACE Master Control process with level 8 (Waits):
oradebug setospid 17292
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name
-- To stop the tracing:
oradebug event 10046 trace name context off
Either DBMS_SYSTEM.SET_EV or 'oradebug' can be used to create a Data Pump trace file.
7.3. Place complete database in SQL_TRACE with specific level.
It is possible that there is not enough time to activate tracing on a specific Data Pump process because an error occurs at an early stage of the job, or that the the Data Pump process needs to be traced from the beginning. In those cases, the Event 10046 with the desired level has to be set in SQL*Plus at database level, and the Data Pump job has to be started afterwards. When the job completes, unset the event again.
Example:
-- Activate SQL tracing database wide,
-- Be careful: all processes will be traced!
--
-- never do this on production unless a maintenance window
-- once issued in PROD you may not be able to stop if load is high
-- careful with directories filling up
--
CONNECT / as sysdba
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';
- Start the Export Data Pump or Import Data Pump job, e.g.:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f.dmp \
LOGFILE=expdp_f.log TABLES=scott.emp
-- Unset event immediately after Data Pump job ends:
ALTER SYSTEM SET EVENTS '10046 trace name context off';
Be careful though: the steps above will result in SQL tracing on all processes, so only use this method if no other database activity takes place (or hardly any other activity), and when the Data Pump job ends relatively quickly.
7.4. Analyze the SQL trace files and create a TKPROF output file.
If the SQL trace files were created with level 1 or 4 then we are usually interested in the statements (and their bind variables). Example scenario: Data Pump aborts with a specific error. When investigating those kind of errors, it makes sense to compress the complete trace file and upload the compressed file.
If the SQL trace files were created with level 8 or 12 then we are usually interested in the timing of the statements (and their wait events). Example scenario: there is an presumptive performance issue during a Data Pump job. These SQL trace files can become very large and the tkprof output after analyzing the files are in most cases more meaningful. When investigating those kind of errors, it makes sense to upload the tkprof output files only.
Example:
-- create standard tkprof output files for Data Pump Master and Worker SQL traces:
% cd /oracle/admin/ORCL/BDUMP
% tkprof orcl_dm00_17292.trc tkprof_orcl_dm00_17292.out waits=y sort=exeela
% tkprof orcl_dw01_17294.trc tkprof_orcl_dw01_17294.out waits=y sort=exeela
For details about Event 10046 and tkprof, see also:
Note:21154.1 "EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:32951.1 "Tkprof Interpretation"
8. How to get header details of Export Data Pump dumpfiles ?
Every export Data Pump dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile. Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO. Header information can be extracted from any export dumpfile, even from dumpfiles created with the classic export client. Example output of a procedure that extracts information from a dumpfile header:
----------------------------------------------------------------------------
...File Version....: 2.1 (Oracle11g Release 1: 11.1.0.x)
...Master Present..: 1 (Yes)
...GUID............: AE9D4A8A85C6444F813600C00199745A
...File Number.....: 1
...Characterset ID.: 46 (WE8ISO8859P15)
...Creation Date...: Wed Mar 19 16:06:45 2008
...Flags...........: 2
...Job Name........: "SYSTEM"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: ORCL
...Language........: WE8ISO8859P15
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.01.00.00.00
...Max Items Code..: 20
----------------------------------------------------------------------------
For more details and an example code of procedure SHOW_DUMPFILE_INFO, see:
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"
Besides the DBMS_DATAPUMP.GET_DUMPFILE_INFO procedure, it is also possible to start an Import Data Pump job with TRACE=100300 in order to create a trace file with the dumpfile header details. The dumpfile header details are written to the Data Pump Master trace file: [SID]dm[number]_[PID].trc which can be found in the directory specified by the init.ora/spfile parameter BACKGROUND_DUMP_DEST.
Example:
-- create a SQL file with TRACE parameter value 100300 (trace file layer);
-- although this impdp job will give an error (ORA-39166: Object ... was not found)
-- a trace file will be written with the file header details we are interested in:
-- On windows, place all expdp parameters on one single line:
D:\DataPump> impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp
NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300
Example output of trace file: orcl_dm00_1696.trc
...
KUPF: 17:14:23.345: newImpFile: EXAMINE_DUMP_FILE
KUPF: 17:14:23.355: ......DB Version = 10.02.00.03.00
KUPF: 17:14:23.355: File Version Str = 1.1
KUPF: 17:14:23.355: File Version Num = 257
KUPF: 17:14:23.355: Version CapBits1 = 32775
KUPF: 17:14:23.355: ......Has Master = 1
KUPF: 17:14:23.355: ........Job Guid = 76DC6D8BC4A6479EADECB81E71FAEF93
KUPF: 17:14:23.355: Master Table Pos = 19
KUPF: 17:14:23.355: Master Table Len = 138856
KUPF: 17:14:23.375: .....File Number = 1
KUPF: 17:14:23.385: ......Charset ID = 46
KUPF: 17:14:23.385: ...Creation date = Thu Oct 18 16:51:36 2007
KUPF: 17:14:23.385: ...........Flags = 0
KUPF: 17:14:23.385: ......Media Type = 0
KUPF: 17:14:23.385: ........Job Name = "SYSTEM"."SYS_EXPORT_SCHEMA_01"
KUPF: 17:14:23.395: ........Platform = IBMPC/WIN_NT-8.1.0
KUPF: 17:14:23.395: ........Language = WE8ISO8859P15
KUPF: 17:14:23.395: .......Blocksize = 4096
KUPF: 17:14:23.405: newImpFile: file; D:\DataPump\expdp_s.dmp, FID; 1
...
For more details and an example, see:
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"
9. How to get Data Definition Language (DDL) statements ? Parameter: SQLFILE
With the Import Data Pump parameter SQLFILE you can specify the name of a file into which all of the SQL DDL is written that Import would have executed, based on other parameters. The SQL is not actually executed, and the target system remains unchanged. The file is written to the directory object specified in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name matching the one specified with this parameter is overwritten.
Syntax: SQLFILE=[directory_object:]file_name
Example:
-- create a SQL file with DDL statements:
% impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
Example output of sqlfile: impdp_s.sql
-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE TABLE TO "SCOTT";
GRANT CREATE SESSION TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "EXP_FULL_DATABASE" TO "SCOTT";
GRANT "IMP_FULL_DATABASE" TO "SCOTT";
...
For more details, see:
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"
10. How to get the DDL both as SQL statements and as XML data ?
With the classic export dumpfiles, the Data Definition Language (DDL) statements were stored inside the export dumpfile as normal (ready-to-use) SQL statements. With Data Pump however, the data to recreate the DDL statements is stored in XML format. This XML data can be obtained together with the actual DDL statements by running an import DataPump job with the SQLFILE and TRACE parameter.
-- create a SQL file with DDL statements and XML data:
% impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y TRACE=2
Example output of sqlfile: impdp_s.sql
-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
10
79SCOTT1
F894844C34402B67USERS
TEMP18-OCT-0718-OCT-07
0DEFAULT1
00
DEFAULT_CONSUMER_GROUP0
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
...
Note: it is not recommended to perform an import Data Pump job with TRACE=2 unless explicitly requested by Oracle Support.
Additional Resources
Community:
Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.
REFERENCES
NOTE:552424.1 - Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?
NOTE:553337.1 - Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
- PRODUCT ENHANCEMENT: EXTEND PARAMETER TRACE TO PROVIDE TIMESTAMP IN DATAPUMP LOG
- EXPDP/IMPDP JOB WITH TRACE AND STARTED WITH NON-DBA USER FAILS: ORA-31631
NOTE:160178.1 - How To Set EVENTS In The SPFILE
NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
NOTE:1053432.1 - ORA-31631 During Export Using DataPump API
NOTE:30762.1 - Init.ora Parameter "MAX_DUMP_FILE_SIZE" Reference Note
NOTE:32951.1 - TKProf Interpretation (9i and below)
NOTE:336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?
NOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects
NOTE:351598.1 - Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)
NOTE:422893.1 - 11g Understanding Automatic Diagnostic Repository.
NOTE:453895.1 - Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp)
NOTE:462488.1 - How to Gather the Header Information and the Content of an Export Dumpfile ?
NOTE:30824.1 - Init.ora Parameter "TIMED_STATISTICS" Reference Note
About Me
...............................................................................................................................
● 本文整理自網路
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2140637/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用隱含Trace引數診斷Oracle Data Pump故障Oracle
- 9 Oracle Data Guard 故障診斷Oracle
- 【Data Pump】Data Pump的並行引數原理並行
- Oracle 隱含引數Oracle
- ORACLE隱含引數Oracle
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- Oracle故障診斷Oracle
- expdp impdp Data Pump（資料泵）使用解析
- oracle獲取隱含引數Oracle
- Oracle的隱含引數(zt)Oracle
- Oracle診斷案例-Sql_traceOracleSQL
- 獲取Oracle隱含引數資訊Oracle
- 獲取oracle的隱含引數Oracle
- oracle的一個隱含引數Oracle
- Oracle 中所有隱含的 引數Oracle
- Oracle隱含引數的查詢Oracle
- oracle data pumpOracle
- Exp和資料泵（Data Pump）的query引數使用
- oracle 檢視隱含引數指令碼Oracle指令碼
- oracle 11g常用隱含引數Oracle
- [zt] 如何檢視Oracle 隱含引數Oracle
- Oracle 12c新特性 - Data Pump (expdp/impdp) 功能增強Oracle
- oracle隱含引數的檢視與修改Oracle
- 【parameter】oracle的隱含引數的檢視Oracle
- Oracle 隱含引數 : _allow_resetlogs_corruptionOracle
- Oracle 隱藏引數使用Oracle
- 部落格連結—Oracle故障診斷Oracle
- 【移動資料】data pump（中） EXPDP 應用
- MySQL故障診斷常用方法手冊（含指令碼、案例）MySql指令碼
- Oracle direct path read相關隱含引數Oracle
- Oracle 隱含引數 _disable_loggingOracle
- Oracle Data Pump 研究(一)Oracle
- 隱含引數的查詢
- Oracle expdp 排除表引數Oracle
- 獲取_字首的oracle hidden parameter隱含引數Oracle
- [zt] Oracle10g 隱含引數 - LOG_PARALLELISMOracleParallel
- CBO，ORACLE，隱含引數，_sort_elimination_cost_ratio的含義Oracle
- Oracle引數-隱藏引數Oracle