datapump utility
TRANSCRIPT
-
8/2/2019 Datapump Utility
1/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 1
DATAPUMP UTILITY
INTRODUCTION:
The Data Pump Utility runs on the Server Side. Oracle Data Pump technology enablesvery high-speed movement of data and metadata from one database to another.Dump files
generated by the new data pump export utility are not compatible with dump files generated by
the original export utility. Therefore, files generated by the original Export (exp) utility cannot be
imported with the Data Pump Import(impdp) utility.
BENEFITS OF DATA PUMP UTILITY
1) The ability to restart Data Pump Jobs
2) The ability to detach from and reattach to long running jobs without affecting the job
itself.
3) Support for filtering the metadata that is exported and imported based upon objects and
object types.
4) Data Archival
5) Upgrading to new releases.
6) Backing up Oracle Databaes
7) Moving Between from One Datbase to another database.
DATA PUMP EXPORT
With the help of expdp utility of DATAPUMP, We can take export(Logiacal Backup) of
Database. This is similar utility like our old exp utility of previous oracle databases.
There are various modes to take export using Expdp utility.
1) Full Database Export
2) Full Schema Export3) Particualar Table Export
4) Tablespace export
5) Using query option export.
-
8/2/2019 Datapump Utility
2/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 2
HOW TO START DATAPUMP?
1) First Login as sysdba user to sql*plus.
Then go to ORACLE installed drive from MY computer. Create Physically create directory. GiveNamed DATA.
-
8/2/2019 Datapump Utility
3/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 3
Now Again Connect to SQL*PLUS. Create a directory using SQL*PLUS. And give read,write grants to
public.
EXPDP Utiltiy of DATAPUMP
-
8/2/2019 Datapump Utility
4/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 4
With the Help of this utility we can take Logical Backup. Its more faster than previous
exp utility.
Full Export of Database using DATAPUMP.
We can take full database export using Datapump. The Detail step are as Below:
1) Click on Start Menu - go to Run Menu Type cmd click on ok Button. Now Dos
Prompt opened.
2) Now go to our DATA DIRECTORY where we store our Backup.
Now We Want to Perform Full Database Backup - using system or sys user.
-
8/2/2019 Datapump Utility
5/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 5
Command:
Expdp system/x@orcl directory=DATA dumpfile=full_db.dmp logfile=full_db.log
exclude=grants,statistics full=y job_name=full_db (Press Enter Key)
Parameter is: full=y
Our full Export Started Successfully.
-
8/2/2019 Datapump Utility
6/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 6
-
8/2/2019 Datapump Utility
7/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 7
Now our Full export runs successfully.
SCHEMA LEVEL EXPORT
If you Want any specific Users Objects Backup, then you can used Schema Level Export utility of Expdp.
Parameter is: schemas=
-
8/2/2019 Datapump Utility
8/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 8
Command is:
Expdp system/x@orcl directory=DATA dumpfile=scott_schema_bkp.dmp logfile=scott_schema_bkp.log
exclude=grants,statistics schemas=scott
-
8/2/2019 Datapump Utility
9/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 9
Now our Schema Export Runs successfully.
TABLE LEVEL EXPORT
If you Want Specific tables of particular schema, then Used Table Level Export Mode using DATA PUMP
UTILITY.
Parameter is : tables=
-
8/2/2019 Datapump Utility
10/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 10
Command is:
Expdp hr/hr@orcl directory=DATA dumpfile=sp_tab.dmp logfile=sp_tab.log exclude=grants,statistics
tables=regions,jobs
Now our Table Level Backup successful.
-
8/2/2019 Datapump Utility
11/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 11
Multiple Tables Backup using par file Option in DATAPUMP
If you want specific tables backup more than 2 tables then with the help of parfile option in
datapump , We can take tables backup more than once.
Steps to create a parfile in Windows:
1) Create a text file, using Notepad.
2) Specify tables= separated by Comma operator of that particular schemas.
3) Save This File in our DATA DIRECTORY.
-
8/2/2019 Datapump Utility
12/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 12
Command is:
Expdp hr/hr@orcl directory=DATA dumpfile=parfiledemo.dmp logfile=parfiledemo.log parfile=parfile.txt
exclude=grants,statistics
Parameter is: parfile=
-
8/2/2019 Datapump Utility
13/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 13
Our Backup Successful Now.
Tablespace Level Backup
If you Want any particular tablespace backup then, with the help of the parameter of datapump, you
can take Logical backup.
-
8/2/2019 Datapump Utility
14/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 14
First Identify the tablespace that you want to take backup in datapump.
(Press Enter Key)
Parameter Used. Tablespaces=
-
8/2/2019 Datapump Utility
15/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 15
STRUCTURAL LEVEL BACKUP
If you Want any particular tables Structure, then With the Help of this Option, We can take only
structure Backup.
Command is:
Expdp hr/hr@orcl directory=DATA dumpfile=struemp.dmp logfile=struemp.log exclude=grants,statisticscontent=METADATA_ONLY tables=jobs
Parameter is:
1) Content=METADATA_ONLY(always specify this Parameter)
2) Tables=< specify table name that you that tables structure)
-
8/2/2019 Datapump Utility
16/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 16
Now our structure Backup runs successfully.
-
8/2/2019 Datapump Utility
17/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 17
QUERY LEVEL EXPORT BACKUP
If you Want Particular Table Specify data, Then you can used Query Option of DataPump.
PARAMETER is:
1) Query=
2) TABLES=
Now our Backup Successful.
-
8/2/2019 Datapump Utility
18/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 18
HELP of expdp
If you Want Help of expdp then specify expdp help=y you will get all options of expdp utility.
-
8/2/2019 Datapump Utility
19/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 19
IMPORT UTILITY OF DATAPUMP
With the Help of of IMPDP utility we can restore logical Backup from one database toanother, across platforms between different operating systems.
FULL IMPORT OF DATABASE : We can Restore the data to demo user.
Command is:
Impdp system/x@orcl directory=DATA dumpfile=FULL_DB.DMP logfile=imp_FULL_DB.log
remap_schema=system:demo table_exists_action=append
-
8/2/2019 Datapump Utility
20/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 20
Schema level Import
With this option we can restore the schema from one schema to another.
-
8/2/2019 Datapump Utility
21/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 21
Our Import complets successfully.
SPECIFIC TABLES IMPORT
-
8/2/2019 Datapump Utility
22/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 22
Command is:
Impdp system/x@orcl directory=DATA dumpfile=SP_TAB.DMP logfile=imp_SP_TAB.log
remap_schema=hr:hr1 (PRESS ENTER KEY)
Or
Impdp system/x@orcl directory=DATA dumpfile=SP_TAB.DMP logfile=imp_sp_tab.log
tables=jobs,regions (Press Enter Key)
PAR FILE IMPORT
With the Help of Par File, We can restored Multiple tables.
-
8/2/2019 Datapump Utility
23/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 23
Our Import Runs successfully.
Command is:
Impdp system/x@orcl dumpfile=PARFILEDEMO.DMP directory=DATA logfile=impparfile.log
remap_schema=hr:hr1
Or
Impdp hr/hr@orcl dumpfile=PARFILEDEMO.DMP directory=DATA logfile=impparfile.log
(press enter key)
QUERY LEVEL IMPORT
With the Help of this Mode ,we can restore specific tables.
-
8/2/2019 Datapump Utility
24/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 24
Command is:
Impdp system/x@orcl directory=DATA dumpfile=QUERYDEMO.DMP logfile=impquerydemo.log
remap_schema=hr:demo
STRUCTURE TABLE IMPORT : We can restored the only Structure of that Tables from one schema to
another.
-
8/2/2019 Datapump Utility
25/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 25
TABLESPACE LEVEL IMPORT
-
8/2/2019 Datapump Utility
26/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 26
Remap_tablespace option, we can import our tablespace from one schema to another or from
one database to another.
Command is:
Impdp system/x@orcl directory=DATA dumpfile=TABLESPACEDEMO.DMP
LOGFILE=imptablespacedemo.log remap_tablespace=users:demo table_exists_action=truncate
Help of Impdp
If you want any help of impdp then simply type impdp help=y command. Help displayed on your
screen.
-
8/2/2019 Datapump Utility
27/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 27
-
8/2/2019 Datapump Utility
28/28
DOCUMENT PREPARED BY AMEY KULKARNI Page 28
How to Kill the Job?
Type ctrl+c key then at command prompt type kill_job(Press Enter key) type yes to kill the
job.
Version Parameter of Data Pump Utility.
With the Help of Version Parameter, You can take logical backup across different Oracle
Versions;
Expdp Utility from Oracle 10g to 11g
There is No need to specify version parameter in expdp command while exporting from 10g
to11gr2 releases
Impdp Utility from Oracle 11gr2 to 10g
When we take export from 11gr2 version, and We Want to import that Export to 10g Version
that time, You Must specify version parameter.
Important Views of DATAPUMP
1) DBA_DATAPUMP_JOBS
2) USER_DATAPUMP_JOBS
3) DBA_DIRECTORIES.