Creation of a Network Link in Oracle.

Hello again friends! This week we are going to see how to make an expdp / impdp through the creating a network link . With this, we will reduce the space we need as well as the time to do it. It is one of the Oracle best practices , so what, let’s see it !!

First, we have 2 ways to do it. Both are totally valid:

• Expdp: From origin we send to destination
• Impdp: From destination we read and write.

In both cases, we will need to carry out a previous configuration that we must adapt to the requirements of our environment.

Configuration required to create a Network Link

In order to carry out our expdp / impdp we need to have the structure created:

1. Entry into the tnsnames.ora from the source / destination machine
2. Create directory to store the logfile and give the pertinent permits.
3. Create dblink

We start from the following scenario:

• Machine 1: oraclehost1.domain.com
  • SID = ORACL1

• Machine 2: oraclehost2.domain.com
  • SID = ORACL2

1- tnsnames.ora entry.

If we launch impdp , we add this entry in the file tnsnames.ora ORACL2 machine

ORACL1 = 
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP) (HOST = oraclehost1.domain.es)
(PORT = 1533)))
 (CONNECT_DATA = (SERVICE_NAME = ORACL1))) 

ORACL2 = 
(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP) (HOST = oraclehost2.domain.es)
(PORT = 1533)))
 (CONNECT_DATA = (SERVICE_NAME = ORACL2))) 

2- Create the folder that will contain the log.

We must modify the clause USING specifying the Dblink to use:

For expdp:

CREATE DATABASE LINK "SYSLINK"
 CONNECT TO system
 IDENTIFIED BY oracle123
 USING 'ORACL2'
  /

For impdp:

CREATE DATABASE LINK "SYSLINK"
 CONNECT TO system
 IDENTIFIED BY oracle123
 USING 'ORACL1'
  /

Directory creation.

This step will be required for both runs

CREATE OR REPLACE DIRECTORY
expdp_dir AS '/ backup / datapump';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO system;

Execution of expdp / impdp command with network link:

For expdp:

expdp \ '/ as sysdba ' network_link = SYSLINK
directory = expdp_dir logfile = 20210317_impdp_user_ APLICAUSER.log schemas = APLICAUSER

For impdp:

impdp \ '/ as sysdba '
network_link = SYSLINK directory = expdp_dir logfile = 20210317_impdp_user_ APLICAUSER.log schemas = APLICAUSER

Then we can see in the following image, it is executed correctly:

Also, if you want us to help you with your environment, whether for administration or consulting tasks, do not hesitate to contact us !! See you next time!!

Subscribe to our newsletter monthly so you don’t miss any of our publications with just one email per month.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

You can see more information in this Oracle document: https://www.oracle.com/a/tech/docs/19c-oracle-data-pump-whats-new.pdf