Enabling tracing on a running DATAPUMP job

Hi guys,

In the last article I’ve mentioned about how to enable tracing on a datapump (export/import) job.

What if I already triggered my datapump job and it’s in the middle of doing it’s task? Shall I kill the entire job intermittently and restart it by enabling the tracing again? That’s really a bad idea  if we’re dealing with GBs/TBs of data.

Thanks to the datapump team at Oracle; They made it much more simpler to the users.

Please follow the below procedure to enable tracing on an existing datapump job.

% 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 temporarily stop 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
...


Use a database init.ora/spfile event to trace Data Pump (Not in a case of a job  that’s already running).

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. Note that this event cannot be enabled through an ALTER SYSTEM command, only a restart of the database will enable the event.
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

Source: Oracle Metalink.

Advertisements

One Response to Enabling tracing on a running DATAPUMP job

  1. Pingback: Undocumented DATAPUMP parameters « queryCSM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: