Oracle DataPump

Oracle DataPump Features:

Datapump is a server side utility.You initiate datapump jobs from a user process either sql*plus or through EM but all the work is done by server processes.This improves the performance dramatically over the old exp ort/import utilities because the datapump processes running on the server have direct acess to the datafiles and SGA;they don’t have to go via a session.

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.

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 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

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.

Changing from Original Export/Import to Oracle Data Pump:

New concept of 'DIRECTORY' in expdp:

In order to use Data Pump, the database administrator must create a directory object and grant

Privileges to the user on that directory object. If a directory object is not specified, a default

directory object called data_pump_dir is provided. The default data_pump_dir is available only

to privileged users unless access is granted by the DBA.

SQL> Create directory FullDump as ‘C:\FullDump’;

Directory Created

SQL> Create directory SchemaDump as ‘C:\SchemaDump’;

Directory Created

SQL>
SELECT grantee, table_name ,directory_name, PRIVILEGE FROM user_tab_privs;
SELECT * FROM all_directories;

After a directory is created, you need to grant READ and WRITE permission on the directory

to other users. For example, to allow the Oracle database to read and to write to files on behalf

of user scott in the directory named by SchemaDump, you must execute the following

Command:

SQL> GRANT READ, WRITE ON DIRECTORY SchemaDump TO Scott;

Once the directory access is granted, the user scott can export his database objects in that directory using the below command:

expdp scott/tiger schemas=scott directory=SchemaDump dumpfile=scott.dmp logfile=scott.log

If user donot have read-write permission on direcotry then expdp will fail with below error:

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name “SCHEMADUMP” is invalid.

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

• expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
•  impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

•  expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
• impdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

• expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
•  impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

INCLUDE and EXCLUDE

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.

Syntax:

• INCLUDE=object_type[:name_clause] [, ...]
• EXCLUDE=object_type[:name_clause] [, ...]

Example:

• expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
•  expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
• expdp scott/tiger TABLES=SCOTT.EMP EXCLUDE=STATISTICS DIRECTORY=datapump DUMPFILE=EMP.dmp LOGFILE=EMP.log

We can exclude/Include index, constraints, statistics instead of INDEX=y,n or constraints=y,n and statistics=none as in old exp/imp

REMAP_SCHEMA: Fromuser/Touser has been replaced by remap_schema in oracle 10g

Syntax: REMAP_SCHEMA=source_schema:target_schema

If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.

If you do not have enough privileges to perform an import that creates dump files containing the metadata necessary to create a schema, then you must create the target schema before performing the import operation. This is because the dump files do not contain the necessary information for the import to create the schema automatically.

If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:

SQL> ALTER USER [schema_name] IDENTIFIED BY [new_pswd]

Suppose that you want to map HR schema to Scott schema:

• expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
•  impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

NETWORK_LINK

With network mode imports, one doesn't need any intermediate dump files (No more FTP'ing of dump files). Data is exported across a database link and imported directly into the target database.

SQL> create user sigma_local identified by sigma_local;

User created.

SQL> grant connect, resource to sigma_local;

Grant succeeded.

SQL> grant read, write on directory SchemaDump to sigma_local;

Grant succeeded.

SQL> grant create database link to sigma_local;

Grant succeeded.

SQL> create database link old_sigma connect to sigma identified by sigma using 'orcl2';

### Now to import all objects of sigma schema (on remote db) to old_sigma (on local db) use the below command.

impdp sigma_local/sigma_local DIRECTORY=SchemaDump NETWORK_LINK=old_sigma remap_schema=sigma:sigma_local

Stop and restart an expdp/impdp in progress:

· expdp system/sys full=Y exclude=statistics directory=FullDump dumpfile=FullexportDump_stat.dmp logfile=expdpfull_stat.log job_name=SYS_EXPORT_FULL_01

· If you want to pause the job for some time then press CTL+C , type the command given below

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

· Now you want to restart the job then you need to attach the job

1. expdp system/sys attach=SYS_EXPORT_FULL_01

2. Export> START_JOB

3. Export> STATUS=600

4. Export> CONTINUE_CLIENT

The following commands are valid while in interactive mode.

• · ADD_FILE:Add dumpfile to dumpfile set.
• · CONTINUE_CLIENT:Return to logging mode. Job will be re-started if idle.
• · EXIT_CLIENT:Quit client session and leave job running.
• · FILESIZE:Default filesize (bytes) for subsequent ADD_FILE commands.
• · HELP:Summarize interactive commands.
• · KILL_JOB:Detach and delete job.
• · PARALLEL: Change the number of active workers for current job.PARALLEL=.
• · START_JOB: Start/resume current job.
• · STATUS: Frequency (secs) job status is to be monitored where the default (0) will show new status when available.STATUS[=interval]
• · STOP_JOB:Orderly shutdown of job execution and exits the client.
• · STOP_JOB=IMMEDIATE performs an immediate shutdown of the Data Pump job.