How to take the logical backup of Database?

Simple export and import

EXP/IMP :==> exp –help, imp –help

Data Pump export and import

EXPDP/IMPDP :==> expdp –help, impdp –help

Step-1 Create Logical Directory

CREATE OR REPLACE DIRECTORY DB_DUMP AS '/u01/app/oracle/backup/';
GRANT READ, WRITE ON DIRECTORY DB_DUMP TO username;

Note: You can also use exiting directory if already created
SELECT * FROM V$DBA_DIRECTORIES;

SCHEMA BAKUP

Syntax of Data Pump

expdp username/password@ora_net_string1 schemas=username directory=DB_DUMP dumpfile=exp_username_datetime.dmp logfile=exp_username_datetime.log

impdp username/password@ora_net_string2 schemas=username directory=DB_DUMP dumpfile=exp_username_datetime.dmp 

Example-1: When the source and destination database have same username 

expdp scott/scott@orcl1 schemas=scott directory=DB_DUMP dumpfile=exp_scott_20032012.dmp logfile=log_scott_20032012.log

impdp scott/scott@orcl2 schemas=scott directory=DB_DUMP dumpfile=exp_scott_20032012.dmp

Example-2: When the source and destination database have are not same username

expdp scott1/scott1@orcl1 schemas=scott directory=DB_DUMP dumpfile=exp_scott_20032012.dmp logfile=log_scott_20032012.log

impdp scott2/scott2@orcl2 remap_schemas=scott:scott2 directory=DB_DUMP dumpfile=exp_scott_20032012.dmp

TABLE  BACKUP

Syntax of Data Pump

expdp username/password@ora_net_string tables=table_name directory=DB_DUMP dumpfile=exp_username_datetime.dmp 
logfile=exp_username_datetime.log

impdp username/password@ora_net_string tables=table_name directory=DB_DUMP dumpfile=exp_username_datetime.dmp 

Example:

expdp scott/scott@orcl tables=employees directory=DB_DUMP dumpfile=exp_scott_20032012.dmp    logfile=log_scott_20032012.log

impdp scott/scott@orcl tables=employees directory=DB_DUMP dumpfile=exp_scott_20032012.dmp

More Example

2 thoughts on “How to take the logical backup of Database?

  1. For import database, I think below changes should be taken:

    Instead of REMAP_SCHEMAS, there would be ‘REMAP_SCHEMA’ & we may require to add ‘REMAP_TABLESPACE’.

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.