WebSphere Issues

28/04/2014

Export and Import schemas from Oracle using Data Pump utility

Filed under: Other — Tags: , , , — Ishtiaque @ 9:55 am

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

Create a free website or blog at WordPress.com.

%d bloggers like this: