Creation of a Network Link in Oracle.

DatabasesOracle

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

First of all, we have 2 ways to do it. Both are fully valid:

  • Expdp: From origin we ship 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 for the creation of a Network Link

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

  1. Entry in the tnsnames.ora of the source/destination machine
  2. Create directory to store the logfile and give the appropriate permissions.
  3. Create dblink

We start from the following scenario:

  • Machine 1: oraclehost1.dominio.es
    • SID= ORACL1
  • Machine 2: oraclehost2.dominio.es
    • SID= ORACL2

1- Entry tnsnames.ora.

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

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

2- Create the folder that will contain the log.

We must modify the USING clause by 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'
  /

Creation of the directory.

this step will be required for both executions

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

GRANT READ,WRITE ON DIRECTORY expdp_dir TO system;

Running the 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 runs correctly:

Also, if you want us to lend you a hand with your environment either for administration or consulting tasks do not hesitate to contact us! See you in the next one!!

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

Still don’t know Query Performance? Find out how it can help you in your Oracle environment. Learn more on their LinkedIn page.

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

Comments are closed.