Datapump Utilities

                                       Datapump Utilities

 

 

Utility     : FROM (Oracle 10g)

Expdp (export datapump)

Impdp (import datapump)

 

Creating Directory for Storing .dmp files (generated by exp)

At Source database:

1. Create a directory at source database for backup files (dump files).

$mkdir exp

2. create logical directory and map the physical directory(exp) with it

Sql>create directory exp as ‘/u02/haszone/exp’;

 

At target database:

1. Create a directory at target database for .dmp files to be placed here (dump files).

$mkdir imp

2. create logical directory and map the physical directory(hasimp) with it

Sql>create directory imp as ‘/u02/haszone/imp’;

 

Levels of logical backups

Export & Import:

 

1.        Table level:

Exporting a table

$expdp dumpfile=emp.dmp directory=exp tables=has.emp logfile=demo.log

Username: / as sysdba

(If user is sys otherwise specify db username and password)  (copy the dump file)

Importing table in a particular schema

$impdp dumpfile=emp.dmp directory=imp remap_schema=has:zone

 

2.       Schema Level

$expdp dumpfile=has.emp directory=exp schemas=has

(copy the dump file)

Importing schema:

Create a schema (ex: zone)

$impdp dumpfile= has.dmp directory=imp remap_schema = has: zone

 

3.        Tablespace level:

Export

$expdp dumpfile=hastbs.dmp directory=exp tablespaces=hastbs

(copy the dump file)

Import:

$impdp dumpfile=hastbs.dmp directory=imp remap_tablespace=hastbs: sampletbs

 

4.        Transporting tablespace from one database to another database:

Export

Steps:

1.        Make the Tablespace Read only

2.        Create .dmp file (metadata)

SQL>select * from v$transportable_platform;

SQL>exec dbms_tts.transport_set_check(‘TABLESPACENAME’,true);

                      $expdp dumpfile=hastbs.dmp directory=exp transport_tablespaces=hastbs

 

                           Copy the dump file and datafile/s to destination database

Import the tablespace:

 

$impdp dumpfile=hastbs.dmp directory=imp transport_datafiles =’/u02/haszone/prod/data/hastbs01.dbf’

 

                5. Full database level

                 Export

                     $expdp dumpfile=full.dmp directory=exp full=y     

 Import

                    $impdp dumpfile=full.dmp directory=imp full=y