Datapump expdp impdp backup in Oracle

Datapump expdp impdp backup in Oracle

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.