Data Pump expdp/impdb Scenarios
Overview
Datapump introduce in 10g which is very powerful utility to perform the both load and unload data using external dump files.Oracle Data Pump technology consists of two components: the Data Pump Export utility, to unload data objects from a database, and the Data Pump Import utility, to load data objects into a database. You access the two Data Pump utilities through a pair of clients called expdp and impdp.As their names indicate, the first of these corresponds to the Data Pump Export utility and the latter
to the Data Pump Import utility. You can control both Data Pump Export and Import jobs with the help of several parameters.
In this article we will demonstrate different Data Pump (expdp/impdb) scenarios.
Prerequisites
- Creating a Database Directory
- Create a directory named expdp_dir and specifies that it is to map to the filesystem/location and physical location on disk:
SQL> create directory expdp_dir as ' /orahm/app/oracle/admin/db01/dpdump';
- Granting Access to the Directory
- Grant permissions on the database-directory object to a user that wants to use Data Pump:-
SQL> grant read, write on directory expdp_dir to sys;
Table level
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-----------------------------
-----------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$impdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log
Schema level
Below example to take the schema level export and import.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log
Check invalid object and compile if required.
select object_name from dba_objects where owner=’ABC’ and status='INVALID' ;
compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
Database level
Below example for full DB export and import.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
Check the tablespace name and size to match same on target db
select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
$expdp directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=exp-full.log
scp /orahm/app/oracle/admin/db01/dpdump/ exp-full.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
Make target db contain all the tablespace and available free space to accommodate the source object into target DB
Sql> select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
$impdp directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=imp-full.log
Check the invalid object and compile.
Sql>select object_name,owner,status from dba_objects where status='INVALID';
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select object_name,owner,status from dba_objects where status='INVALID';
Using Parameter file
Below export and import example using parameter file.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
Check the tablespace name and size to match same on target db
select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Create parameter file as below under data pump directory.
vi full_db.par
directory=DATA_PUMP_DIR
full=y dumpfile=exp-full.dmp
logfile=exp-full.log
Take export using par file.
$ expdp parfile=full_db.par
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
Make target db contain all the tablespace and available free space to accommodate the source object into target DB
Sql> select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Create parameter file as below under data pump directory.
vi full_db.par
directory=DATA_PUMP_DIR
full=y dumpfile=exp-full.dmp
logfile=imp-full.log
Perform import using parameter file.
$impdp parfile=full_db.par
Check the invalid object and compile.
Sql>select object_name,owner,status from dba_objects where status='INVALID';
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select object_name,owner,status from dba_objects where status='INVALID';
Data Pump Export/Import more examples
- Using parallel option
Using parallel option we can make export faster. It generate more dump files depends on parallel option during export.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR schemas=ABC dumpfile=exp-schema_%Udmp logfile= exp-schmea.log parallel=2
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema_%U.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema_%U.dmp logfile=imp-schema.log
Check invalid object and compile if required.
select object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
Compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
- Using compress option
Using compress option we can reduce the size of dump files.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log
Check invalid object and compile if required.
select object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
Compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
Creating DDL file
Using Sqlfile option we can only extract the DDL without data.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log sqlfile=ddl-script.sql
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp ddl-script.sql oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
Import only DDL
$impdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log sqlfile=ddl-script.sql
- Cloning user (remap_schema)
Using Remap_schema option we can import the object from one schema to other schema.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR remap_schema=ABC:XYZ dumpfile= exp-schema.dmp logfile=imp-schema.log
Check invalid object and compile if required.
select object_name from dba_objects where owner=’XZY’ and status=‘INVALID’;
Compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
- Cloning table (remap_table)
Remap_table option is to create the table with new name during import to avoid overwriting existing table in target DB.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR tables=ABC.table1 dumpfile=exp-tab.dmp logfile=exp-tab.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
Remap table to new name.
$impdp directory= DATA_PUMP_DIR tables=ABC.table1 remap_table= ABC.table1:table2 dumpfile=exp-tab.dmp logfile=exp-tab.log
- Importing when objects Already Exist
Using the TABLE_EXISTS_ACTION option we can import the object if the object already exist on target.
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
APPEND loads rows from the source and leaves existing rows unchanged.
TRUNCATE deletes existing rows and then loads rows from the source.
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import
$impdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log table_exists_action==[SKIP | APPEND | TRUNCATE | REPLACE]
Check invalid object and compile if required.
select object_name from dba_objects where owner=’ABC’ and status='INVALID' ;
compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
- Exclude option
EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
Check the tablespace name and size to match same on target db
select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Create parameter file as below under data pump directory.
vi full_db.par
directory=DATA_PUMP_DIR
full=y dumpfile=exp-full.dmp
logfile=exp-full.log
exclude=schema:"IN ('SYS','SYSTEM','ANONYMOUS','DBSNMP','DIP','EXFSYS','ORACLE_OCM','OUTLN','WMSYS','XDB')"
Take export using par file.
$ expdp parfile=full_db.par
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
Make target db contain all the tablespace and available free space to accommodate the source object into target DB
Sql> select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Total (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% FREE"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like '%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Create parameter file as below under data pump directory.
vi full_db.par
directory=DATA_PUMP_DIR
full=y dumpfile=exp-full.dmp
logfile=imp-full.log
Perform import using parameter file.
$impdp parfile=full_db.par
Check the invalid object and compile.
Sql>select object_name,owner,status from dba_objects where status='INVALID';
Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Sql>select object_name,owner,status from dba_objects where status='INVALID';
- Include option
INCLUDE parameter is used, only those objects specified by it will be included in the export/import.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR schemas=ABC include=TABLE:”IN(“EMP’,’DEPT’) dumpfile= exp-schema.dmp logfile= exp-schmea.log
scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log
Check invalid object and compile if required.
select object_name from dba_objects where owner=’ABC’ and status='INVALID' ;
compiling invalid object.
ALTER PROCEDURE <PROCEDURE_NAME> COMPILE;
- Creating a Consistent Export
CONSISTENT=Y parameter to indicate the export should be consistent to a point in time
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
$expdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log consistent=y
scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/
Import Target DB
$impdp directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp logfile=exp-tab.log
- Network based import
NETWORK_LINK parameter identifies a database link to be used as the source for a network export/import.
Export Source DB
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------------------------------------------------------------
SYS DATA_PUMP_DIR /orahm/app/oracle/admin/db01/dpdump
Create the db link on on local db which is point to source db for export task.It will export the source db and put the dumpfile in dump directory
sql>CREATE DATABASE LINK remote_expdp CONNECT TO sys IDENTIFIED BY sys123 USING 'DB01';
$expdp directory= DATA_PUMP_DIR tables=owner.table_name network_link=remote_expdp dumpfile=exp-tab.dmp logfile=exp-tab.log
Import Target DB
Create the db link on local DB which is point to source db. Here it will directly import the data using netwok link option without dumpfile of source db.
sql>CREATE DATABASE LINK remote_impdp CONNECT TO sys IDENTIFIED BY sys123 USING 'DB01';
$impdp directory= DATA_PUMP_DIR tables=owner.table_name logfile=exp-tab.log network_link=remote_impdp
No comments:
Post a Comment