Export Import Over Network Link

<<Back to Oracle DATAPUMP Main Page

How to Use EXPDP/IMPDP NETWORK_LINK Parameter

SOURCE: TEST1T
HOST: test1.oracle.com
TARGET: TEST2T
HOST: test2.oracle.com
Objective: Trigger the Export of Database schema TEST_EXP@TEST1T from test2.oracle.com and write the export dumps on test2.oracle.com
Preparation:
Step1: Add the source database TNS entry in target database tnsnames.ora
TEST1T =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test1.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST1T)
    )
  )
Step2: Create private database link in target database pointing to sourced database
SQL>create database link exp_test connect to TEST_EXP identified by xxxx using 'TEST1T';
Step3: Create directories and grant privilege to the user (supposed to initiate export) in target database
SQL>create directory exp as '/u01/dbatest1/stage';
SQL>GRANT READ,WRITE ON DIRECTORY EXP TO TEST; <= test user exists in TEST2T database and will be used to trigger the export
Step4: Trigger export
EXPDP TEST/xxxxx DIRECTORY=EXP NETWORK_LINK=EXP_TEST DUMPFILE=EXP.DMP LOGFILE=EXP.LOG

Full Database export over NETWORK_LINK

Perform Step1 to Step3
Step4:Grant role DATAPUMP_EXP_FULL_DATABASE to TEST_EXP in source database
SQL>GRANT DATAPUMP_EXP_FULL_DATABASE TO TEST_EXP;
Step5: Grant role DATAPUMP_EXP_FULL_DATABASE to TEST in Target database
SQL>GRANT DATAPUMP_EXP_FULL_DATABASE TO TEST;
Step6: Trigger full database export
$EXPDP TEST/xxxx DIRECTORY=EXP NETWORK_LINK=EXP_TEST DUMPFILE=EXP.DMP LOGFILE=EXP.LOG FULL=Y

Note1:- If you will not grant DATAPUMP_EXP_FULL_DATABASE role you will encounter following error
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
Note2:-If you only grant DATAPUMP_EXP_FULL_DATABASE role to EXP_TEST user in target but not in source you will encounter following error
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

Database export over NETWORK_LINK in Multitenant Environment

You just need to modify the export command slightly like below. Rest everything is same as above
$EXPDP TEST/xxxxx@PDB01 DIRECTORY=EXP NETWORK_LINK=EXP_TEST DUMPFILE=EXP.DMP LOGFILE=EXP.LOG FULL=Y
Where
TEST<= User (in Target Database) initiating export
PDB01<= target Database where from the export is initiated
DIRECTORY<= Location on target server where the dumpfiles and logfiles will be written
NETWORK_LINK<= Database Link connecting to the source database (Database to be exported)
Note:- In addition to the TNS Entry mentioned in Step1 You need TNS Entry for target PDB (ie PDB01)

Using NETWORK_LINK with IMPDP

The data from the source database instance is written directly back to the connected database instance (Target Database Instance). There are no dump files involved.

Preparation:

Perform Step1 to Step3
Step4: grant DATAPUMP_IMP_FULL_DATABASE to the user initiating impdp in target database
SQL> GRANT DATAPUMP_IMP_FULL_DATABASE TO TEST;
Step5: Run Import
$IMPDP TEST/xxx@PDB01 DIRECTORY=EXP NETWORK_LINK=EXP_TEST DUMPFILE=EXP.DMP LOGFILE=EXP.LOG