Mastering Oracle Datapump: Backup and Import Techniques

Datapump is introduced in Oracle 10g. It is also used to take logical backup of the database.
Working of Datapump: Every datapump job created a table in schema who is running job. This is called Master table.
Master table is keeping track of job and help to make datapump job resumable. When job finished table is dropped.
Note: If table is already present in database with same name then job is not started.

Datapump is Server based job, it created output on Server pointing to the Directory object of Oracle.
For Start any job first we need to create the Directory for the location.

Steps for Datapump started

1. Create the Directory

Create directory dbpump as 'E:\dbbackup';

2. Grant the user to have read and write permission on directory

Grant read,write on directory dbpump to Scott,HR;

3. Make directory at Operating System level:

mkdir E:\dbbackup

4. Example of EXPDP Backup as follows:

EXPDP system/manager directory=dbpump dumpfile=full.dmp logfile=full.log parallel=4 job_name=full_system

Note: A table is created as name JOB_NAME paraemter value full_system for EXPDP job. Its dropped automatic when job completed.

5. Stop or start expdp job

EXPDP system/manager directory=dbpump dumpfile=full.dmp logfile=full.log parallel=4 job_name=full_system

-- Ctrl+c for export prompt
Ctrl+C

--Stop the current job
export> stop_job

-- come out from export prompt
export> Exit

-- Start the job
export>start_job

6. If you exit from the export prompt then need to attach the job again

expdp system/manager ATTACH=full_system
export> START_JOB

7. Perform Schema export

Option 1: Run with that schema user like HR

expdp HR/HR directory=dbpump dumpfile=hr.dmp logfile=hr.log parallel=4 job_name=hr_dump

Option 2: Run with DBA user
expdp sys directory=dbpump dumpfile=hr.dmp logfile=hr.log parallel=4 job_name=hr_dump schemas=HR

8. Import the one schema into another schema remap it

impdp system/manager directory=dbpump dumpfile=hr.dmp logfile=imphr.log job_name=HR_TO_SCOTT remap_schema=HR:SCOTT

9. Export a table

expdp HR/HR directory=dbpump dumpfile=table.dmp logfile=table.log parallel=4 job_name=table_job tables=hr.dept

10. Estimate the space utilization without executing the EXPDP job

expdp system/manager directory=dbpump extimate_only=Y logfile=full.log

11. Monitoring query for datapump job

select owner_name, job_name ,operation, job_mode ,state , degree ,attached_sessions from dba_datapump_jobs;

select a.owner,a.job_name,b.osuser from dba_datapump_sessions a, v$session b where b.saddr = a.saddr;