How to use Exdp and Impdp over Network Link : Oracle DB

We have different enviornment like test , dev , and prod and data on these enviornment is different .Many Times , we want to have exactly the same data of production in to dev enviornment . For this , we need to export the data on production server and import and overwrite the data in development server . Oracle provides expdp and impdp utilities for transferring the data .Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

In this post , we will create a export dump of remote database on our local system . The remote database ,whose data needs to be exported, let's call it targetdb . Here are the steps needs to be taken to create a exported dump file on our local machine .
First login as sysdba on your local database .

#Login in sqlplus 
sqlplus / as sysdba

Then create a public connection link for the remote database .

#Create a connection link for the database which you want to export 
create database link remotelink connect to targetdbuser identified by targetdbpassword using 'hostname:port/sid'
#check connection 
select * from dual@remotelink

Now create a local directory on your file system , and map it to your local oracle db like this :

# create a local directory where the dump file will be stored and map it to your database dir by creating 
create directory dumpdir as 'C:/Users/abhishek/dump';
# give permission to local user by whom we will be running expdp
GRANT read, write ON DIRECTORY dumpdir TO localdb;
# check if the dir is created 
 select directory_name, directory_path from dba_directories ;

You might get following error , if you do not grant read write access of directory to the user .

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name dumpdir is invalid

Now run the expdp command with specifying the directory , network link and other parameters . We are exporting a particular schema here .

expdp userid=localdb/localdb@//localhost:1521/ORCL dumpfile=testdump.dmp logfile=testdump.log SCHEMAS=myschema directory=dumpdir 

You might get error like this :

ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

This error occurs because target database user should have exp_full_database privilege for exporting data over network link.So give the privilege for remote user , by logging in as remote user sysdba and grant access .

GRANT EXP_FULL_DATABASE to targetdbuser;

Now to import the data on your local database . Run the following command :

impdp myschema/mytest@//localhost:1521/orcl schemas=myschema directory=dumpdir dumpfile=testdump.dmp logfile=impdpnewtest1.log 

You might get error like this :

ORA-39154: Objects from foreign schemas have been removed from import
ORA-31655: no data or metadata objects selected for job

And no data imported in your schema . To resolve this , you have to give the following privilege to the local user , where you are importing the schema .

grant imp_full_database to myschema ;

This import will create the schema imported , if it is not available in local database. If the schema is already present in your local db , it will try to import tables in it . If the tables are already present in the schema , it will skip importing those tables . To overwrite all the tables , you have to add paramter TABLE_EXISTS_ACTION=REPLACE in impdp command . It will truncate the tables , and create new one with the dump file .

Here is a nice read comparing different approaches like , exp , expdp and expdp over network link and security issues regarding these.

Source 1
Source 2
Source 3
Source 4



Post Comments and Suggestions !!