Export and Import schemas from Oracle using Data Pump utility

1. Create a user “abc” and grant dba privileges for it.

create user abc identified by password
grant dba to abc;

2. Create a directory “export” inside /home/oracle/ directory on the source and target oracle hosts and assign the linux filesystem permission for “dba” group and “oracle” user with “0755” octal onto the “export” directory.

3. Run the following commands on source and target hosts
export ORACLE_SID=MyDB
sqlplus / as sysdba
CREATE DIRECTORY EXP_DIR AS ‘/home/oracle/export’;
GRANT READ, WRITE ON DIRECTORY EXP_DIR TO abc;

4. Create a parfile MyDB.par file with following details inside /home/oracle/export:
USERID=abc/password
DIRECTORY=EXP_DIR
DUMPFILE=exp-MyDB.dmp
LOGFILE=exp-MyDB.log
SCHEMAS=schema1, schema2, schema3

4. switch directory to home/oracle/export and run the following commmand:
expdp parfile=exp-APPDB.par

5. Transfer exp-MyDB.dmp and MyDB.par from source to target DB host

6. Update MyDB.par file with following details:
USERID=abc/password
DIRECTORY=EXP_DIR
DUMPFILE=exp-MyDB.dmp
LOGFILE=imp-APPDB.log
SCHEMAS=schema1, schema2, schema3

7. Run the following command to import the dump
impdp parfile=exp-MyDB.par

Note: If the schema already exisits you can run the following command to drop it:
DROP USER schema1 CASCADE;

Advertisements

About Ishtiaque

I am IBM Certified Infrastructure Systems Architect, Linux Foundation Certified System Administrator, Oracle Certified Programmer in Java and Web Component Developer, and TOGAF 9 certified with over 10 years of support and development experience in IBM middleware software and Java. Additionally, have a sound grip in databases and OpenStack administration. I hold the following certifications: IBM Certified Infrastructure Systems Architect Linux Foundation Certified System Administrator (LFCS) TOGAF 9 Certified Oracle Certified Expert, Java EE6 Web Component Developer Oracle Certified Professional – Java 6 Programmer ITIL v3 Foundation Certified IBM Certified Solution Architect – Cloud Computing Infrastructure V1 IBM Certified System Administrator – WebSphere Portal V8, V7, V6.1, V6 IBM Certified System Administrator – WebSphere Application Server V7, V6.1 IBM Certified System Administrator – AIX V7 IBM Certified System Administrator – WebSphere MQ V7 IBM Certified Deployment Professional – Business Process Manager Advanced V7.5 IBM Certified Solution Advisor – Cloud Computing Architecture V3 IBM Certified Solution Developer – WebSphere Portal V5.1
This entry was posted in Other and tagged , , , . Bookmark the permalink.