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.

About these ads

2 Responses to Oracle 11g Data Pump

  1. [...] 5-How to solve Data Pump “ORA-39021: Database compatibility version XXXX is not supported.” problem? Mike Rothouse-Oracle 11g Data Pump [...]

  2. priya says:

    Thank a lot. This saved lot of my time!!!

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: