Oracle Data Pump


This chapter gives the original Export and Import utilities a primer on how to move up to the faster, more powerful, and more flexible Data Pump Export and Import utilities.

In this Chapter I going to export and import SALES schema

Export Schema

Step 1 : Login to Source Database server

login as: orapreprod

password:******

$ cd /u03/CRP02/db/tech_st/12.1.0/

$ . CRP2_dev.env

$ sqlplus "/as sysdba"

Step 2 : Connect as system user

SQL> conn system/clonemanager
Connected.

SQL> desc all_directories;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(128)
 DIRECTORY_PATH                                     VARCHAR2(4000)
 ORIGIN_CON_ID                                      NUMBER

Step 4 : Creating Directory Objects If doesn’t exist

If You Want to create New DATAPUMP_DIR DIRECTORY

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u03/CRP02/DATA_PUMP_DIR';

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from ALL_DIRECTORIES;

SYS
DATA_PUMP_DIR
/u03/CRP02/DATA_PUMP_DIR

$ cd /u03/CRP02/

$ mkdir DATA_PUMP_DIR

$ cd /u03/CRP02/DATA_PUMP_DIR/

$ pwd
/u03/CRP02/DATA_PUMP_DIR

$ cd /u03/CRP02/db/tech_st/12.1.0/

$ . CRP2_dev.env

Step 5 : Export shema from source database

$ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u03/CRP02/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:17:58 2017 elapsed 0 00:15:11

$ cd /u03/CRP02/DATA_PUMP_DIR/

$ ls -lrt
-rw-r--r-- 1 orapreprod dba     41016 Aug 30 16:17 30AUG17_SALES_SCHEMA.log
-rw-r----- 1 orapreprod dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

Step 6 : Copy Exported dumpfile to target database

$ scp -pr 30AUG17_SALES_SCHEMA.dmp orauat@172.25.4.50:/u01/UAT/DATA_PUMP_DIR

The authenticity of host '172.25.4.50 (172.25.4.50)' can't be established.
RSA key fingerprint is 49:81:30:ed:2c:17:5c:79:ee:30:79:2d:15:b0:f3:9e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.4.50' (RSA) to the list of known hosts.
orauat@172.25.4.50's password:
30AUG17_SALES_SCHEMA.dmp                      100%  470MB 156.8MB/s   00:03

Import Schema

Step 1 : Login to Target database server

login as : orauat

password : ******

$ cd /u01/UAT/db/tech_st/12.1.0

$ . UAT_ebsuatdb.env

$ sqlplus "/as sysdba"

$ cd /u01/UAT

Step 2 : Creating Directory Objects If doesnot exist

$ mkdir DATA_PUMP_DIR

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/UAT/DATA_PUMP_DIR';

$ cd DATA_PUMP_DIR/

$ pwd
/u01/UAT/DATA_PUMP_DIR

Step 3 : Backup SCHEMA if already exist target database 

$ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA_BACKUP.dmp logfile=30AUG17_SALES_SCHEMA_BACKUP.log

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/UAT/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA_BACKUP.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:04:49 2017 elapsed 0 00:14:20

$ ls -lrt
-rw-r-----. 1 orauat dba 35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba    33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log

$ pwd
/u01/UAT/DATA_PUMP_DIR

Step 4 : Drop Existing Schema

Before drop user connect and check the DEFAULT TABLESPACE and TEMPORARY TABLESPACE of particular schema.

SQL> select * from DBA_USERS where username='SALES';

$ sqlplus "/as sysdba"

SQL> drop user sales cascade;
drop user sales cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

-----------------------------------------------------------------------------------------------------------------

Currently SALES User connected
So disconnect the SALES user by below steps

Kill connected user session
---------------------------------
SQL > select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; 

$ kill -9 26713

$ sqlplus "/as sysdba"

SQL> drop user sales cascade;

User dropped.

Step 5 : Create Schema

SQL>CREATE USER SALES IDENTIFIED BY sales DEFAULT TABLESPACE APPS_POS_DATA TEMPORARY TABLESPACE TEMP1 PROFILE DEFAULT ACCOUNT UNLOCK;

$ ls -lrt

-rw-r-----. 1 orauat dba  35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba     33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log
-rw-r-----. 1 orauat dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

Step 6: Now Import the schema from source dump file.

Copy the source dump file to target database.
And run the below command

$ impdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log remap_schema=SALES:SALES