Migrating data between 2 different Oracle databases is a very common task for most DBAs. It’s often used to ‘refresh’ test data, or as part of a vendor led upgrade path. It is also common for the 2 databases to either be on the same server, bad practice but still common, or for them to have a network connection between them allowing SQLNET traffic. If that is the case then IMPDP has a largely unknown parameter that can both speed up your migration and reduce your disk space requirements…NETWORK_LINK.
The NETWORK_LINK feature allows data to be pulled from a SOURCE DB to the TARGET directly over a network connection (or locally if on the same machine) using a database link. As a result you do not need to write a dump file to disk which not only saves you space, but can massively increase the speed of the data migration as you only need to write to disk once at the TARGET site.
Here’s the steps of how to do it:
- Create a TNSNAMEs entry for the SOURCE DB on your TARGET host:
TNSNAMES.ORA: SOURCE= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sourcehost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SOURCEDB) ) )
- Create a database link in your TARGET DB referencing this link. Don’t forget to make the link public if you intend to use a different user to do the import:
CREATE <PUBLIC> DATABASE LINK <LINK_NAME> CONNECT AS <SOURCE_USER> IDENTIFIED BY <PASSWORD> USING '<TNSNAMES_NAME>'; CREATE PUBLIC DATABASE LINK migration CONNECT AS source_user IDENTIFIED BY Pa55word USING 'SOURCE';
- Start the import job on the TARGET host using the NETWORK_LINK parameter and the local user credentials and any other parameters you require. You can run the job in parallel as well if appropriate:
IMPDP target_user/Pa55word directory=DATA_PUMP_DIR logfile=networkimport.log network_link=migration schemas=SCOTT parallel=8