Oracle DataPump

Master Table

Process Structure

– Worker Process (ora_dw00_)
– Shadow Process
– Client Process
– Master Control Process (MCP) (ora_dm00_)
– Parallel Query (PQ) Process

Data Movement

– Data File Copying (transportable tablespaces)
– Direct Path load and unload
– External Tables
– Conventional Path

Metadata Movement

exec DBMS_METADATA

Interprocess Communication

AQ is used for communication between different Datapump proceses.
– Command and control queue
– Status queue
They have names of form KUPC${C|S}_.

PARAMETERS

compression
table_exists_action
parallel
master_only
metrics
abort_step
keep_master
access_method
attach=
content=
compression=
directory=
dumpfile=
The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99.

filesize=
The minimum size for a file is ten times the default Data Pump block size, which is 4 kilobytes.

transform=
Enables you to alter object creation DDL for specific objects, as well as for all applicable objects being loaded. When the types are exported, we also export the object_identifier (OID) of the types. Within the current archictecture, the object-identifier needs to be unique in the database.

During import (into the same database), we try to create the same object types in the new target schema. As the OID of the types already exists in the source schema, the types cannot be created in the target schema.

failing to perform transform=oid:n will result in below error during impdp

CREATE TYPE “U2″.”MY_TABTYPE”   OID ‘EEC16EAE6DF34B4FA755DBB448EC4F78’ as …
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE “U2″.”MY_RELTAB” …

eg. transform=oid:n
The value N (= No) for the transform name OID implies that a new OID is assigned.

Ref : DataPump Import Of Object Types Fails With Errors ORA-39083 ORA-2304 Or ORA-39117 ORA-39779 [ID 351519.1]



PACKAGES
exec DBMS_DATAPUMP.OPEN
exec DBMS_DATAPUMP.GET_STATUS



GETTING THE MOST FROM THE PARALLEL PARAMETER

Here is a general guideline for what should be considered when using the PARALLEL parameter:

  • Set the degree of parallelism to two times the number of CPUs, then tune from there.
  • For Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of dump files.
  • For Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.
  • A PARALLEL greater than one is only available in Enterprise Edition.


Few Questions answered when performing full database export and import :

(a) tablespaces need to be created
(b) If path/file doesnt exist the import of that tablespace fails
(c) mapping of tablespace has restrictions
two parameters
include=tablespace
remap_tablespace=sourcetbs:newtbs    (make sure the full path exists)
unless you want same path (the files have to exist)

Advertisements

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