How to Use Data Pump Network Mode

Data pump has a very nice feature since 10g, which can make data imported directly from source to target via network connection without any intermediate dump files. The best thing is that it saves IO and space during export and import dump files.

If you are the first time to use impdp network mode, you should know all the following preconditions before using it. For more operable parameters of impdp, please refer to: impdp Parameter for All Releases of Oracle Database.

Grant EXP_FULL_DATABASE role to the export user in source database.

SQL> conn / as sysdba
Connected.
SQL> grant EXP_FULL_DATABASE to export_user;
...

Grant IMP_FULL_DATABASE role to the import user in target database.

SQL> conn / as sysdba
Connected.
SQL> grant IMP_FULL_DATABASE to import_user;
...

In the target database server, add a tns entry which points to source database before creating a database link.

SOURCE_DATABASE =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (Host=10.1.1.1)
        (Port=1521)
      )
    )
    (CONNECT_DATA=
      (SID=SOURCE_DATABASE)
    )
  )

Create a database link in target database, which can connect to the export user in source database.

SQL> conn import_user/password@target_database
Connected.
SQL> CREATE DATABASE LINK "SOURCE_LINK" CONNECT TO export_user IDENTIFIED BY password USING 'SOURCE_DATABASE';
...

Test the database link. Here is an example:

SQL> select sysdate from dual@source_link;
...

Grant read/write on a directory object DATA_PUMP_DIR to the import user in target database in order to log the process.

SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMPORT_USER;
...

Now, you can begin to use impdp network mode. The following is an example of data migration in network mode.

$ impdp import_user/password NETWORK_LINK=SOURCE_LINK FULL=YES TABLE_EXISTS_ACTION=REPLACE STATUS=300
...

The jobs of data pump could take a very long time to complete, you may refer to my post about speeding up the jobs: Tips to Speed Up Data Pump Export or Import.