Killing a Data Pump Job

Another lesson learned in using Oracle 11g Data Pump was being able to attach and kill an existing Data Pump job using the ATTACH command.  This is done either during the existing Data Pump job by using Ctrl-C to get the prompt and executing the KILL_JOB command:

% expdp / full=y dumpfile=dp_test.dmp logfile=dp_test.log directory=expdumpdir job_name=dp_fullexp


Export: Release 10.2.0.4.0 - 64bit Production on Saturday, 16 January, 2010 13:36:18

Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
Starting "OPS$ORACLE"."DP_FULLEXP":  /******** full=y dumpfile=dp_test.dmp logfile=dp_test.log directory=expdumpdir job_name=dp_fullexp
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

^C
Export>  kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Or you can attach from a separate session and kill the job from there by executing the KILL_JOB command:

% expdp / attach=ops\$oracle.dp_fullexp

Export: Release 10.2.0.4.0 - 64bit Production on Saturday, 16 January, 2010 13:54:00

Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Job: DP_FULLEXP
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: FALSE
GUID: 7D4BF0976ED665ABE04400144FA165B2
Start Time: Saturday, 16 January, 2010 13:52:07
Mode: FULL
Instance: d105
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND     /******** full=y dumpfile=dp_test.dmp
logfile=dp_test.log directory=expdumpdir
job_name=dp_fullexp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /disk01/oraexport/dp_test.dmp
bytes written: 4,096

Worker 1 Status:
State: EXECUTING

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

In your original Data Pump session, you will see the job has been terminated:

% expdp / full=y dumpfile=dp_test.dmp logfile=dp_test.log directory=expdumpdir job_name=dp_fullexp

Export: Release 10.2.0.4.0 - 64bit Production on Saturday, 16 January, 2010 13:52:03

Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
Starting "OPS$ORACLE"."DP_FULLEXP":  /******** full=y dumpfile=dp_test.dmp logfile=dp_test.log
directory=expdumpdir job_name=dp_fullexp
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Job "OPS$ORACLE"."DP_FULLEXP" stopped due to fatal error at 13:54:18

Oracle 11g Data Pump

Along with installing Oracle 11g for the first time, I want to utilize Oracle 11g Data Pump to replace the prior exp/imp utilitiy in my Solaris 10 environment.

Setup is quite simple to get started.  First, create a directory object in the database:

CREATE DIRECTORY expdumpdir AS '/u01/oraexport';

Then grant the approriate privileges on that directory object:

GRANT READ,WRITE ON DIRECTORY expdumpdir TO mydba;

One difference encountered between Oracle 10g and Oracle 11g is the compression variable.  In Oracle 10g you can compress metadata only, which is the default option:

COMPRESSION = (METADATA_ONLY | NONE)


However in Oracle 11g, you have the added options of compressing data, metadata, or both:

COMPRESSION = (ALL | DATA_ONLY | METADATA_ONLY | NONE

In my search for additional compression information, I found some debate on the better process when comparing the Data Pump compression approach with the expdp/gzip approach.

A nice comparison example of Oracle 11g Data Pump vs. OS compression utility (gzip) can be found on RajaBaskar Thangaraj’s blog.

Also encountered an error based on the value of my database COMPATIBLE parameter.  After installing Patch 8833297 – 11.1.0.7.1 Patch Set Update (October 2009 PSU), this value was set to 11.1.0.7.1.

% expdp / dumpfile=my11gdb.dmp logfile=my11gdb.log full=y directory=expdumpdir job_name=my11gdb_fullexp compression=all

Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, 13 January, 2010 11:42:09

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39021: Database compatibility version 11.1.0.7.1 is not supported.

By default, Data Pump uses the value set in your COMPATIBLE parameter.  The value I have set, 11.1.0.7.1, is obviously not valid.  To correct the problem, I simply added the VERSION=LATEST parameter to the command line and the process completed successfully.

% expdp / dumpfile=my11gdb.dmp logfile=my11gdb.log full=y directory=expdumpdir job_name=my11gdb_fullexp compression=all version=latest


My usage of Oracle Data Pump at the moment is very basic.  However I have attended at least one presentation by Arup Nanda showing additional uses of Data Pump which is quite versatile depending upon your needs.

First Oracle 11g Installation

As I write this, I am quite aware it is 2010 and Oracle 11g Release 2 is available.  However, I am just getting around to working with Oracle 11g Release 1 which is based on a requirement for our ECM product at the moment.

While installing Oracle Database 11g Release 1 for the first time, I encountered some differences from Oracle 10g.

• Deprecation of BACKGROUND_DUMP_DEST and USER_DUMP_DEST which have been replaced by DIAGNOSTIC_DEST.  This area is now known as the Automatic Diagnostic Repository (ADR).
• The ANALYZE...COMPUTE STATISTICS and ANALYZE...ESTIMATE STATISTICS clauses are no longer supported and using them causes errors.
• The default value of the UNDO_MANAGEMENT parameter is AUTO so that automatic undo management is enabled by default.

Additional Compatibility and Interoperability Issues in Oracle Database 11g Release 1 are found in Appendix A of the Oracle Database 11g Release 1 Upgrade Guide.

From the time I began learning Oracle back in 1995, the Oracle documentation library has been the core requirement over all other references.  I was required to read the Administrator’s  Guide and Concepts manuals from cover to cover.  With my managerial responsibilities over-shadowing DBA responsibilities these days, finding time to read the manuals cover to cover is quite difficult.

In my endeavor to quickly complete the required Oracle 11g installation, I did discover some documentation I have never referenced before.

Descriptions of the SQL scripts stored in ORACLE_HOME/rdbms/admin can be found in Appendix B of the Oracle Database Reference 11g Release 1.

Nothing earth-shattering, but a nice little discovery worth noting for future reference.