Datapump Export and Import Usage

I found this article on Datapump useful, just to record it here. Reference: http://www.datadisk.co.uk/html_docs/oracle/data_pump.htm

 

Data Pump Export and Import

Data Pump takes the old export and import utilities one step further, you can have total control over the job running (stop it, pause it, check it, restart it). Data pump is a server side technology and it can transfer large amounts of data very quickly using parallel streams to achieve maximum throughput, they can be 15-45% faster than the older import/export utilities. Advantages using data pump are

• ability to estimate jobs times
• ability to restart failed jobs
• perform fine-grained object selection
• monitor running jobs
• directly load a database from a remote instance via the network
• remapping capabilities
• improved performance using parallel executions

A couple of notes is that you cannot export to a tape device only to disk, and the import will only work with version of oracle 10.1 or greater. Also remember that the expdp and impdp are command line tools and run from within the Operating System.

Data Pump Uses

You can use data pump for the following

• migrating databases
• copying databases
• transferring oracle databases between different operating systems
• backing up important tables before you change them
• moving database objects from one tablespace to another
• transporting tablespace’s between databases
• reorganizing fragmented table data
• extracting the DDL for tables and other objects such as stored procedures and packages

Data Pump components

Data pump technology consists of three major components

• dbms_datapump – the main engine for driving data dictionary metadata loading and unloading
• dbms_metadata – used to extract the appropriate metadata
• command-line – expdp and impdp are the import/export equivalents

Data Access methods

Data pump has two methods for loading data, direct path or external table path you as a dba have no control with what data pump uses, normally simple structures such as heap tables without triggers will use direct path more complex tables will use the external path, oracle will always try and use the direct-path method.

Direct Path	bypasses the database buffer cache and writes beyond the high water mark when finished adjusts the high water mark, No undo is generated and can switch off redo as well, minimal impact to users as does not use SGA. Must disable triggers on tables before use.
External Path	Uses the database buffer cache acts as a SELECT statement into a dump file, during import reconstructs statements into INSERT statements, so whole process is like a normal SELECT/INSERT job. Both undo and redo are generated and uses a normal COMMIT just like a DML statement would.

In the following cases oracle will use the external path if any of the below are in use

• clustered tables
• active triggers in the table
• a single partition in a table with a global index
• referential integrity constraints
• domain indexes on LOB columns
• tables with fine-grained access control enabled in the insert mode
• tables with BFILE or opaque type columns

Data Pump files

You will use three types’s of files when using data pump, all files will be created on the server.

• dump files – holds the data and metadata
• log files – the resulting output from the data pump command
• sql files – contain the DDL statements describing the objects included in the job but can contain data
• Master data pump tables – when using datapump it will create tables within the schema, this is used for controlling the datapump job, the table is removed when finished.

Data Pump privileges

In order to advance features of data pump you need exp_full_database and imp_full_database privileges.

How Data Pump works

The Master Control Process (MCP), has the process name DMnn, only one master job runs per job which controls the whole Data Pump job, it performs the following

• create jobs and controls them
• creates and manages the worker processes
• monitors the jobs and logs the process
• maintains the job state and restart information in the master table (create in the users schema running the job)
• manages the necessary files including the dump file set

The master process creates a master table which contains job details (state, restart info), this table is created in the users schema who is running the Data Pump job. Once the job has finished it dumps the table contents into the data pump file and deletes the table. When you import the data pump file it re-creates the table and reads it to verify the correct sequence in which the it should import the various database objects.

The worker process is named DWnn and is the process that actually performs the work, you can have a number of worker process running on the same job (parallelism). The work process updates the master table with the various job status.

The shadow process is created when the client logs in to the oracle server it services data pump API requests, it creates the job consisting of the master table and the master process.

The client processes are the expdp and impdp commands.

Running Data Pump

You can either run via a command line specifying options or use a parameter file, there are many options to Data Pump so it would be best to check out the Oracle documentation, I have given a few examples below

Exporting
database	expdp vallep/password directory=datapump full=y dumpfile=data.dmp filesize=2G parallel=2 logfile=full.log  Note: increase the parallel option based on the number of CPU’s you have
schema	expdp sys/password schemas=testuser dumpfile=data.dmp logfile=schema.log
table	expdp vallep/password tables=accounts,employees dumpfile=data.dmp content=metadata_only
tablespace	expdp vallep/password tablespaces=users dumpfile=data.dmp logfile=tablespace.log
Importing
database	impdp system/password full=y dumpfile=data.dmp nologfile=y
schema change	impdp system/password schemas=’HR’ remap_schema=’HR:HR_TEST’ content=data_only  impdp system/passwd remap_schema=’TEST:TEST3’ tables=test log=… dumpfile=… directory=…
Other Options
directory	specifies a oracle directory object
filesize	split the dump file into specific sizes (could be used if filesystem has 2GB limit)
parfile	specify the parameter file
content	contents option can be ALL, METADATA_ONLY or DATA_ONLY
compression	compression is used by default but you can stop it
exclude/include	metadata filtering
query	selectively export table data using a SQL statement
estimate	Calculate job estimates where the vaild keywords are blocks and statistics
estimate_only	Calculate job estimates without performing the export
network link	you can perform a export across a network
encryption	you can encrypt data within the data pump file
parallel	increase worker processes to increase throughput, base it on number of CPU’s
remap_schema	move objects from one schema to another
remap_datafile	change the name of the datafile when moving across different systems
remap_tablespace	move from one tablespace to another
Useful Views
DBA_DATAPUMP_JOBS	summary information of all currently running data pump jobs
DBA_DATAPUMP_SESSIONS	displays the user currently running data pump jobs
V$SESSION_LONGOPS	display information like totalwork, sofar, units and opname
Privileges
IMP_FULL_DATABASE	required if using advanced features
EXP_FULL_DATABASE	required if using advanced features

DBMS_DATAPUMP package

The package dbms_datapump can be used for the following

• starting/stopping/restarting a job
• monitoring a job
• detaching from a job

exporting	declare  d1 number; begin d1 := dbms_datapump.open(‘export’,’schema’,null, ‘test1’, ‘latest’); dbms_datapump.add_file(d1, ‘test1.dmp’, ‘dmpdir’); dbms_datapump.metadata_filter(d1, ‘schema_expr’,’in (”OE”)’); dbms_datapump.start_job(d1); dbms_datadump.detach(d1); end;
importing	declare  d1 number; begin d1 := dbms_datapump.open(‘import’,’full’,null, ‘test1’); dbms_datapump.add_file(d1, ‘test1.dmp’, ‘dmpdir’); dbms_datapump.metadata_remap(d1, ‘remap_schema’, ‘oe’, ‘hr’); dbms_datapump.start_job(d1); dbms_datadump.detach(d1); end;

Refreshing development environment using expdp/impdp

Introduction

This note describes the procedure of refreshing a schema in development environment with latest production data.

Environment

Production Database

• machine: 192.168.1.46
• OS user: glporacle
• DB name: GOLDPROD
• user to perform expdp: expuser/expuser
• export directory: /gold/GLP/archive/expdp

Development Database

• Test machine: 192.168.1.42
• OS user: gltoracle
• DB name: GOLDTEST
• user to perform impdp: expuser/expuser
• import directory: /gold/GLT/archive/impdp

Export schema CENPROD from production using expdp

ssh 192.168.70.205
su - glporacle
cd /gold/GLP/archive/expdp

— Verify if there is enough space to hold dump files

The schema CENPROD is currently estimated to occupy10G; ensure there is enough space in current directory to save export dump files.

df -k /gold/GLP/archive

Filesystem    1024-blocks      Free %Used    Iused %Iused Mounted on
/dev/goldglparclv    73400320  <strong>46348548</strong> 37%      961     1% /gold/GLP/archive

— Verify if there is no existing any old dump files *.dmp

if there is any then delete them before executing export pump process.

rm *.dmp

— Run export pump as oracle user ‘expuser’

nohup expdp parfile==expdp_user_cenprod.par &

where parfile has the following contents:

userid="expuser/expuser"
schemas=CENPROD
directory=EXPDP_DIR
parallel=4
dumpfile=user_CENPROD_%U.dmp
logfile=expdp_CENPROD.log

If any errors or warnings shown up, refer to the log file “expdp_CENPROD.log” in current directory for details.

Move dump files to testing environment

Transfer all dump files to development machine.

Import into CENTEST on development database

ssh 192.168.1.42
su - gltoracle
cd /gold/GLT/archive/impdp

$ ls -l

total 19990928
-rw-r--r--    1 gltoracl gloinst         307 Feb 11 15:13 02_grant_java_permission_to_centest.sql
-rw-r--r--    1 gltoracl gloinst        1728 Feb 04 17:02 generate_recreate_script.sql
-rw-r--r--    1 gltoracl gloinst         207 Feb 04 17:31 impdp_user_CENTEST.par
-rw-r--r--    1 gltoracl gloinst         424 Feb 14 10:32 build_replace_synonyms_script.sql
-rw-r-----    1 gltoracl gloinst    23924736 Feb 04 16:56 user_CENPROD_01.dmp
-rw-r-----    1 gltoracl gloinst  3872714752 Feb 04 16:57 user_CENPROD_02.dmp
-rw-r-----    1 gltoracl gloinst  2738532352 Feb 04 16:57 user_CENPROD_03.dmp
-rw-r-----    1 gltoracl gloinst  3600035840 Feb 04 16:58 user_CENPROD_04.dmp

Generate recreation script of schema CENTEST

The file “build_recreate_centest_script.sql” is used to generates a recreation script

set echo off
define username='CENTEST'
set lines 132
set head off
set feedback off
set verify off
set termout on
set long 10000
REM pause "Please give the user that you're going to recreate"
ACCEPT user_name CHAR default &username prompt 'Insert user name:[&username]'

spool 01_recreate_user_&&username\.sql

prompt spool 01_recreate_user_&&username\.log

select 'create user '||USERNAME||' identified by values '''||PASSWORD||''''||chr(10)|| -
'temporary tablespace '||TEMPORARY_TABLESPACE||chr(10)|| -
'default tablespace '||DEFAULT_TABLESPACE||chr(10)|| -
'profile '||PROFILE||';' -
from dba_users where username='&&user_name';

select 'alter user '||username||' quota '||decode(MAX_BYTES,-1,'unlimited',MAX_BYTES)||' on '||tablespace_name||';'
from dba_ts_quotas where username='&&user_name';

select 'grant '||PRIVILEGE||' to '||GRANTEE||decode(ADMIN_OPTION,'YES',' with admin option',null)||';'
from dba_sys_privs where grantee='&&user_name';

select 'grant '||GRANTED_ROLE||' to '||GRANTEE||decode(ADMIN_OPTION,'YES',' with admin option',null)||';'
from dba_role_privs where grantee='&&user_name';

select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE|| -
decode(GRANTABLE,'YES',' with grant option',null)||decode(HIERARCHY,'YES',' with hierarchy option',null)||';' -
from dba_tab_privs where grantee='&&user_name' -
 and table_name not in (select DIRECTORY_NAME from dba_directories);

select 'grant ' || PRIVILEGE || ' on directory ' || DIRECTORY_NAME || ' to CENTEST;' -
  from dba_tab_privs a,  -
       dba_directories b  -
 where a.grantee='CENTEST' -
   and a.TABLE_NAME=b.DIRECTORY_NAME;

prompt spool off
prompt quit
spool off

$sqlplus expuser/expuser @build_recreate_centest_script.sql

A script named 01_recreate_user_CENTEST.sql is generated in current directory;

drop user centest

sqlplus expuser/expuser
SQL> select sid, serial#, username, machine, program, status from v$session where username= 'CENTEST';

If it returns any sessions of ‘CENTEST’, kill them

SQL> alter system kill session 'sid, serial#';

Run the following command to drop user until there is no any session of CENTEST returned by the above query.

SQL> drop user centest cascade;

Recreate CENTEST user structure

SQL> @01_recreate_user_CENTEST.sql

Check log file “01_recreate_user_CENTEST.log” in current directory and if any errors correct them before proceeding ahead.

Import data into centest

parameter file “impdp_user_CENTEST.par” has the following contents

userid="expuser/expuser"
REMAP_SCHEMA=CENPROD:CENTEST
REMAP_TABLESPACE=CENPROD_DATA:CENTEST_DATA,CENPROD_IDX:CENTEST_IDX
DIRECTORY=IMPDP_DIR
EXCLUDE=GRANT
PARALLEL=4
DUMPFILE=user_CENPROD_%U.dmp
LOGFILE=impdp_CENPROD_to_CENTEST.log

Launch export pump import process:

nohup impdp expuser/expuser parfile=impdp_user_CENTEST.par &

Master table "EXPUSER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EXPUSER"."SYS_IMPORT_FULL_01":  expuser/******** parfile=impdp_user_CENTEST.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CENTEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CENTEST"."STOCOUCH"                        262.3 MB 1813316 rows
......

Check log file impdp_CENPROD_to_CENTEST.log in current directory and if any errors occurred, correct them before proceeding ahead.

Replace synonyms references

Run the following script as ‘expuser’ user to build a replacing script.

spool 03_replace_synonyms.sql
set lines 200 pages 0 echo off feedback off
prompt spool 03_replace_synonyms\.log
select 'CREATE OR REPLACE SYNONYM ' || owner ||'.' || SYNONYM_NAME || ' FOR ' ||
       decode(TABLE_OWNER,
              '&fromuser','&touser' || '.',
              null, '',
              TABLE_OWNER || '.') || TABLE_NAME || decode(TABLE_OWNER, null, '@'|| db_link || ';', ';')
from dba_synonyms where OWNER='STKTEST';
prompt spool off
prompt quit
spool off
quit

sqlplus expuser/expuser @build_replace_synonyms_script.sql

A script named “03_replace_synonyms.sql” is created in current directory. Run it as ‘expuser’ user:

sqlplus expuser/expuser @03_replace_synonyms.sql

Check log file “03_replace_synonyms.log ” for execution results.

Recompile invalid objects of centest

sqlplus expuser/expuser

SQL> exec dbms_utility.COMPILE_SCHEMA(‘CENTEST’);

After a compilation of schema CENTEST, verify if there is any remaining invalid object

select object_name, object_type, status from dba_objects where owner='CENTEST' and status ='INVALID';

Estimate export dump size

1) if the database is 10g or upper, we can use “expdp ESTIMATE_ONLY=Y ESTIMATE={BLOCKS | STATISTICS}

expdp schema=OWNER estimate_only=Y estimate=BLOCKS

where
BLOCKS – The estimate is calculated by multiplying the number of database blocks used by the source objects, times the appropriate block sizes.
STATISTICS – The estimate is calculated using statistics for each table. For this method to be as accurate as possible, all tables should have been analyzed recently.

2) If the database version is 9i or 10g, we can estimate export dump file size by querying on “dba_segments”

e.g. to estimate size of exporting a schema

select sum(bytes)
  from dba_segments
where owner=''
  and segment_type not in (
        'CACHE',
        'ROLLBACK',
        'TYPE2',
        'UNDO'
   );

Caveat: the actual size of the dump file can be very different from what it’s estimated if tables are very fragmented.

3) If the database version is 9i or 10g, we can get a PRESICE dump size without generating it (refer to ). To achieve it, we need to create a pipe, use ‘dd’ to read from it, redirect exp to the pipe and at the end dd reports actual blocks passing through the pipe.

3.1) Create a pipe called exp.pipe in /tmp directory
(syntax may differ depending on platform)

% cd /tmp
% mknod exp.pipe p

3.2) Start reading from exp.pipe with dd,
dump output to bit bucket (/dev/null),
set blocksize to 1k and execute this process in background

% dd if=/tmp/exp.pipe of=/dev/null bs=1024 &

3.3) Start the export, setting file=/tmp/exp.pipe

% exp user/pwd schema=OWNER file=/tmp/exp.pipe &

3.4) At the end of exp, look for numbers of records written

Export terminated successfully without warnings.
1131864+0 records in.
1131864+0 records out.
[1] +  Done                    dd if=/tmp/exp.pipe of=/dev/null bs=1024 &

– ‘1131864+0 records out’ shows 1131864 records of 1024 bytes were written to the exp dumpfile.
– Step 2 specifies record size(bs) of 1024.
– Size of actual dumpfile would be 1024*1131864 = 1159028736 bytes
– Format of ‘records out’ is f+p, f=full blocks, p=partial block
– For example, if step 4 returns ‘1131864+1 records out’
Your actual dumpfile size will be between 1159028736 bytes(1024*1131864) and 1159029760 bytes(1024*(1131864+1))

Cavert: we have to go through a whole export process to get the a precise dump size.