Oracle Data Pump — the better export and import

From Oracle 10 onwards, Data Pump supersedes the classic export/import functionality, and it offers considerable advantages over the older tools.

Overview

The Data Pump utility introduced with version 10 has significant advantages over the old export/import tools. The most important features are:

• Exports and imports can be parallelised.
• Exports and imports can be suspended and resumed.
• Imports are possible over database links, which removes the need to write to the file system altogether.
• Data Pump can be driven through the external tools expdp and impdp, or through a PL/SQL API.
• Data Pump can use database links, which simplifies and speeds up moving data between databases.
• Data Pump can remap schemas and tablespaces during import.

Export files are written to an Oracle directory on the server, which needs consideration on RAC systems. Dump files can therefore no longer be read or written from an external server over a SQL*Net connection. This is the price of parallel processing and the substantial gain in throughput, but it can be a problem in certain environments.

Architecture

Master table

The master table is the central element of every Data Pump operation. It is created in the schema of the user who starts the Data Pump job and holds all information about the job:

• the current status of every object
• the user-specific configuration parameters
• the status of the worker processes
• restart information

The master table is created during export and import and can be located with the following SQL.

SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name master_table_name
FROM
dba_objects o,
dba_datapump_jobs j
WHERE
o.owner=j.owner_name AND
o.object_name=j.job_name ;

The master table makes it easy to verify the current status of an import or export. In particular, information about the data export and import — usually the most time-consuming part — is available quickly.

	select * from
	master_table_name
	where
	job_name = 'MY_EXPORT_JOB' and
	object_type = 'TABLE_DATA'

Client process

The client process calls the Data Pump API. Alongside the functionality already present in exp and imp, the new clients expdp and impdp offer a range of additional features.

Server process

This is a dedicated server process that starts as soon as a client connects to the database. After a call to DBMS_DATAPUMP.OPEN, the server process begins to launch the actual job and generates the master table.

Master control process

There is exactly one master control process (MCP) per job. It controls how a Data Pump job is processed and distributed. Among other things, the MCP is responsible for:

File management

maintaining the list of dump files

maintaining the entries in the master table (job status, job description, restart and dump file information)

Worker processes

By calling START_JOB, the MCP starts worker processes in parallel. The degree of parallelism is defined by

	DBMS_DATAPUMP.SET_PARALLEL( handle => h, degree => Anzahl_Paralleler_Prozesse );

	.

Usage

Here we look only at driving Data Pump through the PL/SQL API. That way you do not depend on the client tools being installed and you can develop your own PL/SQL blocks with additional functionality.

Data Pump over database links

The Data Pump API can copy entire schemas or individual objects over a database link. With a loopback link this is an elegant way to duplicate a schema within one database.

In the first step Data Pump is initialised:

create database link my_db_link ...

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
dump_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
scn_number number;

BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => 'my_db_link',
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

dbms_output.put_line(dump_handle);

Once Data Pump is open, individual options can be set:

Flashback SCN

Running Data Pump against a specific SCN is a very useful option, particularly in streaming environments or when recovering individual tables after data loss. What matters is that no DDL statements were issued on the source side after the SCN in question — and that includes truncate.

scn_number := dbms_flashback.get_system_change_number@my_db_link ;
dbms_datapump.set_parameter(h, 'FLASHBACK_SCN', scn_number);
dbms_output.put_line(scn_number);

.

Object filters

Object filters can be used to restrict the export or import to the objects you actually want.

DBMS_DATAPUMP.metadata_filter(
handle => h,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

.

or

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => 'in (''SCHEMA1'',''SCHEMA2'')');

.

and for individual table names:

dbms_datapump.metadata_filter(handle => dump_handle,
´ name => 'NAME_EXPR',
value => 'IN (''TABLE_NAME'')');

.

Parallelism

Data Pump exports and imports can run in parallel, and the degree of parallelism can be changed while the job is running. During online hours you might use a parallelism of 1 or 2, and raise it considerably during quieter periods at night or at the weekend.

DBMS_DATAPUMP.SET_PARALLEL(
handle => dump_handle,
degree => 5);

.

Remap

Remapping changes certain values during import — a clear advantage over the old export/import procedures. It is essential when cloning a schema:

dbms_datapump.metadata_remap(dump_handle,'REMAP_SCHEMA','SCHEMA1','SCHEMA3');

Als Parameter sind folgende Keywords zulässig:

    REMAP_TABLESPACE
    REMAP_SCHEMA
    REMAP_DATAFILE

Several tablespaces can be remapped as follows:

 define
 type  tbsp_type is table of varchar2(128);
 v_ar_remap_tbsp_rule tbsp_type  := tbsp_type('TBSP_OLD_1:TBSP_NEW_1','TBSP_OLD_2:TBSP_NEW_2');
 i number;
 begin
 IF v_ar_remap_tbsp_rule.COUNT > 0
         THEN
            FOR i IN 1 .. v_ar_remap_tbsp_rule.COUNT
            LOOP
                DBMS_DATAPUMP.METADATA_REMAP (
                   handle    => h,
                   name      => 'REMAP_TABLESPACE',
                   old_value  => substr(v_ar_remap_tbsp_rule(i),1,instr(v_ar_remap_tbsp_rule(i),':') -1 ),
                   value     => substr(v_ar_remap_tbsp_rule(i),instr(v_ar_remap_tbsp_rule(i),':') +1 ,length(v_ar_remap_tbsp_rule(i))  )
                   );
            END LOOP;
    END IF;
end;

Table exists action

This defines how Data Pump behaves when a table already exists.

dbms_datapump.set_parameter (h, 'TABLE_EXISTS_ACTION', 'APPEND');
-- oder
dbms_datapump.set_parameter (h, 'TABLE_EXISTS_ACTION', 'REPLACE');

.

Keep master table

Data Pump drops the master table once the job has finished. If you want to keep it for later analysis, use the following:

  DBMS_DATAPUMP.set_parameter (handle   => h,
                                   name     => 'KEEP_MASTER',
                                   VALUE    => 1);

                                   .

The tables should be dropped manually at a later point.

Once all necessary parameters are set, the job can be started:

DBMS_DATAPUMP.start_job(dump_handle);

.

The easiest way to monitor the job is through the master table:

select * from
master_table_name
where
job_name = 'MY_EXPORT_JOB' and
object_type = 'TABLE_DATA'
;

Export and import through files

Importing and exporting schemas with data

An Oracle directory has to be created first. As mentioned above, this directory needs to be chosen carefully on systems with more than one node. Oracle does not grant a global read privilege (chmod o+r) on the dump file itself; the log file does receive one.

create directory MY_DUMP_DIR as '/opt/app/oracle/something_shared'
grant READ, WRITE on directory MY_DUMP_DIR to operation_user;

.

The export then follows the approach already discussed above:

DECLARE
dump_handle NUMBER;
BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

Since no database link is used this time, the dump and log files have to be declared explicitly.

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE.dmp',
directory => 'MY_DUMP_DIR');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE.log',
directory => 'MY_DUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

For larger schemas and tables it is worth arranging for parallel processing again:

 DBMS_DATAPUMP.SET_PARALLEL(
handle => dump_handle,
degree => 5);

.

All that remains is to start the job:

DBMS_DATAPUMP.start_job(l_dp_handle);

.

Exporting and importing structure only

This produces structure-only exports and imports. The approach is the same as above, except that a data filter with ROWS=0 is added.

DECLARE
dump_handle NUMBER;
BEGIN
dump_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'MY_PUMP_JOB',
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE_STRUCTURE.dmp',
directory => 'MY_DUMP_DIR');

DBMS_DATAPUMP.add_file(
handle => dump_handle,
filename => 'MY_DUMP_FILE_STRUCTURE.log',
directory => 'MY_DUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => dump_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCHEMA1''');

DBMS_DATAPUMP.data_filter(
handle=> dump_handle,
name=> 'INCLUDE_ROWS' ,
value=>0):

DBMS_DATAPUMP.start_job(l_dp_handle);

If an export or import aborts and is terminated, the job itself may already be gone while entries remain in the view dba_datapump_jobs. In these cases the master table could not be dropped during the abort. It can safely be removed manually:

SELECT
'drop table o.owner||'.'||object_name ||';' drop_stmt
FROM dba_objects a, dba_datapump_jobs b
WHERE a.owner=b.owner_name AND a.object_name=b.job_name ;