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:
- Entry in the tnsnames.ora of the source/destination machine
- Create directory to store the logfile and give the appropriate permissions.
- 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:
CREATE DATABASE LINK "SYSLINK" CONNECT TO system IDENTIFIED BY oracle123 USING 'ORACL2' /
CREATE DATABASE LINK "SYSLINK"
CONNECT TO system
IDENTIFIED BY oracle123
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:
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 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.
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