Oracle 18c Datapump-Network Link
- In this article we are going to see the Oracle 18c Datapump-Network Link
- we can directly import to target using network_link
- This option uses network link methodology and uses DBLINK and TNS service to fetch the data rows.
Prerequisites:-
- Both Source & target databases must be in open mode.
- we have two databases testdb and devdb. We need to restore table chennai form testdb to devdb.
- Source and Destination database must be connected through the network.
- Source database (testdb) tns detail must exist at destination (devdb).
- Create datapump directory in TARGET
Let’s start the Demo:-
Step1:-Check the schema and tables in source database(TestDB).
[oracle@testdb ~]$ sqlplus hari/hari@pdb1
SQL*Plus: Release 18.0.0.0.0 Production on Fri Jul 13 03:50:35 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Fri Jul 13 2018 03:49:44 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> select count(*) from chennai;
COUNT(*)
----------
107
Grant the privilege to hari user:-
SQL> conn system/Chennai#123@pdb1
Connected.
SQL> grant imp_full_database,exp_full_database to hari;
Grant succeeded.
Step 2: Copy the TNSNAMES entry from source database to target database.
[oracle@devdb ]$ tnsping pdb1
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 13-JUL-2018 03:56:03
Copyright (c) 1997, 2017, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/18.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testdb.compute-604179528.oraclecloud.internal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.604179528.oraclecloud.internal)))
OK (30 msec)
Step 3: Create Database link on target database
[oracle@devdb ~]$ sqlplus system/Chennai#123@devdb SQL*Plus: Release 18.0.0.0.0 Production on Fri Jul 13 04:00:31 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> create database link chennai_link connect to hari identified by "hari" using 'pdb1'; Database link created. SQL> select count(*) from chennai@chennai_link; COUNT(*) ---------- 107
Step4:-Create datapump directory in target side
[oracle@devdb ~]$ mkdir -p /u01/datapump [oracle@devdb ~]$ sqlplus system/Chennai#123@devdb SQL*Plus: Release 18.0.0.0.0 Production on Fri Jul 13 04:10:07 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Fri Jul 13 2018 04:00:32 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> create directory dump_dir as '/u01/datapump'; Directory created. SQL> grant read,write on directory dump_dir to public; Grant succeeded.
Step5:-execute the impdp job on target side using network_link parameter
[oracle@devdb ~]$ impdp system/Chennai#123@devdb directory=dump_dir network_link=chennai_link tables=chennai remap_schema=hari:system LOGFILE=delhi.log
Import: Release 18.0.0.0.0 - Production on Fri Jul 13 04:27:37 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@devdb directory=dump_dir network_link=chennai_link tables=chennai remap_schema=hari:system LOGFILE=delhi.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SYSTEM"."CHENNAI" 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Fri Jul 13 04:27:58 2018 elapsed 0 00:00:20
Step6:-Check the table
[oracle@devdb ~]$ sqlplus system/Chennai#123@devdb SQL*Plus: Release 18.0.0.0.0 Production on Fri Jul 13 04:31:18 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Fri Jul 13 2018 04:27:38 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> select count(*) from chennai; COUNT(*) ---------- 107
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba