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:
- Entry into the tnsnames.ora from the source / destination machine
- Create directory to store the logfile and give the pertinent permits.
- 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:
CREATE DATABASE LINK "SYSLINK" CONNECT TO system IDENTIFIED BY oracle123 USING 'ORACL2' /
CREATE DATABASE LINK "SYSLINK"
CONNECT TO system
IDENTIFIED BY oracle123
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:
expdp \ '/ as sysdba ' network_link = SYSLINK directory = expdp_dir logfile = 20210317_impdp_user_ APLICAUSER.log schemas = APLICAUSER
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