the new data pump

8/13/2019 The New Data Pump

1/23

The New Data PumpCaleb Small

[email protected]


2/23

Next generation Import / Export

New features

Better performance

Improved security

Versatile interfaces


3/23

Old Import / Export

Still exists

Installed and enabled by default

Supports features through version 9i

No new releases

Requiredto import pre-10g dump files Dump files & scripts are NOT compatible


4/23

New Features

Data Sampling & filtering

Object filtering

Estimate file size, max file size, multiple files Remap datafile, schema, tablespace

Network mode

Point-in-time export Version conversion

Column data encryption


5/23

Better Performance

Complete re-write

Parallel processing

Parallel data streams to multiple files

Runs within the instance

Uses direct path whenever possible

Restartable

Tunable (auto tuning)

Progress monitoring


6/23

Improved Security

Server based onlyno client side exports!

Based on directory objects within database

Always runs as oracle process on behalf

of invoking database user.


7/23

Versatile Interfaces

Command line

Parameter file

Interactive mode DBMS_DATAPUMP package

DB console (Enterprise manager)

External table

Scheduled job


8/23

Datapump Architecture

Master process Manages and controls the operation

Worker process(es)

Responsible for data movement One for each degree of parallelism

Master table Created in invokers schema at job start

Maintained during job execution Dropped after successfulcompletion

Used to resume a paused/failed job

Control & status queues


9/23

Types of Exports

Table

Schema

Tablespace

Database

Transportable Tablespace (metadata)

INCLUDE / EXCLUDE object filters

QUERY and SAMPLE data filters

CONTENTS = data | metadata | both


10/23

Directory Objects

Created as a database object

Requires CREATE_ANY_DIRECTORYprivilege

Permissions (read, write) granted on the objectto specific user(s)

Not validatedexistence, syntax, OS privilege

Accessed as user oracle at the OS level

Default DATA_PUMP_DIRmaps to


11/23

Data Access

Direct Path Chosen automatically whenever possible

Reads/writes data blocks directly

No undo, redo can be turned off See Utilities Guide for exceptions (eg. active

triggers, clustered tables, BFILE column, etc)

External Table Equivalent to old conventional path

Normal SQL and commit processing, slower

NOTthe same as the external table driver for SQL


12/23

Monitoring Data Pump

STATUS parameter

Detach / re-attach jobs

Stop / start / kill jobs

STATUS command (interactive mode)

Data dictionary views DBA_DATAPUMP_JOBS

DBA_DATAPUMP_SESSIONS

V$SESSION_LONGOPS

Log File


13/23

Interactive Mode

NOT the same as old imp/exp!

Default starts schema mode export

Use command line arguments or par file

logging vsinteractive command mode

Default logging mode logs to terminal

Ctl-C to enter interactive command mode

Job will continue to run even if client disconnects!expdp scott/tiger@fred parfile=myjob.par


14/23

Interactive Mode Commands

STATUS Status of current job

CONTINUE_CLIENT Resume logging mode

EXIT_CLIENT Exit client, leave job running

STOP_JOB Stop current job, do not delete

START_JOB Re-start current job

KILL_JOB Delete current job

PARALLEL Add/remove worker processes

ADD_FILE Add dump file during export

HELP Get help


15/23

Obsolete ParametersExport

BUFFER

COMPRESS

CONSISTENT

DIRECT

RECORD_LENGTH

RESUMABLE

STATISTICS

USERID

VOLSIZE

Import

BUFFER

CHARSET

COMMIT COMPILE

FILESIZE

RECORD_LENGTH

RESUMABLE STATISTICS

Others have changed, see the Utilities Guide!


16/23

Data Pump API

Grant execute on DBMS_DATAPUMP andoptionally DBMS_METADATA

Exec DBMS_DATAPUMP.OPEN

Define parameters (job type, dump file, etc)

Exec DBMS_DATAPUMP.START_JOB

Optionally monitor, detach, re-attach, stop,start or kill the job

Can be scheduled as a recurring job


17/23


18/23

Network Mode

Works across database link

Import reads tables from remote DB and

writes directly to tables in local DBNo dump file created

Directory object still required for logging

Export reads tables from remote DB andwrites to dump file on local server


19/23

SQL File

Import can generate an SQL file instead of

actually performing the import

Contains DDL that would have beenexecuted based on job parameters

Passwords excluded

No change to target DB


20/23

Required Reading

Oracle Database New Features Guide

Oracle Database Utilities

PL/SQL Packages and Types Reference

Oracle Data Pump FAQ on OTN


21/23

Demonstration

Directory object basics

Basic Data Pump unload and load

Query / Sample data filtering

Re-attaching and monitoring a big job

The PL/SQL API

SQL File import

External Table access driver

www.caleb.com/dba

[email protected]


22/23

Tuning Data Pump

PARALLEL is the only DP specific parameter

Set to 2 times number of CPUs

Will cause increased resource consumption

Memory, CPU, I/O bandwidth Do not overload these resources!

Individual worker processes can use parallel query

Use multiple dump files to maximize parallelism

Separate files on separate physical devices/channels Use wildcard in filename eg: dumpfile%u.dmp

Separate device/channel from source tablespace(s)


23/23

Tuning Data Pump

Instance parameters that may affect performance:

DISK_ASYNCH_IO=TRUE

DB_BLOCK_CHECKING=FALSEDB_BLOCK_CHECKSUM=FALSE

PROCESSES

SESSIONS

PARALLEL_MAX_SERVERSSHARED_POOL_SIZE

UNDO_TABLESPACE

the new data pump

Documents