12c, data pump

Oracle 12c – Network-based Data Pump operations

Leave a comment

NETWORK_LINK parameter is used with impdp as part of import operation.  Data is moved directly using SQL.  Select statement moves data from remote database over the link and INSERT clause inserts into local database.  No dump files are involved.  Current user database links are not supported.   Only PUBLIC, FIXED user or connected user Database Links can be used.

 

Restrictions of Network Link:

  1. Tables with LONG or LONG RAW that reside in admin tablespaces are not supported.
  2. Auditing cannot be enabled on admin tablespaces when performing full transportable export.
  3. Source and Target database cannot be more than two versions off.

 

This article will give a basic demonstration of Network-based Data Pump operations by extracting the scott.emp table from a database ORCL and importing it into a pluggable database PDB1.

 

  1. Logon to your Pluggable database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 14:15:49 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

2

[oracle@linux2 ~]$

 

  1. Verify that you can reach the remote database ORCL via the network with tnsping command.

 

[oracle@linux2 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 11-JAN-2016 14:43:20

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.98)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

[oracle@linux2 ~]$

 

  1. Logon local PDB1 database as sysdba.

 

[oracle@linux2 ~]$ sqlplus / as sysdba@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 15:12:43 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

  1. Change container to pdb1.

 

SQL> alter session set container=pdb1;

Session altered.

SQL>

 

  1. Create user pdb1_user with dba and create session privileges.

 

SQL> create user pdb1_user identified by password;

User created.

SQL> grant create session to pdb1_user;

Grant succeeded.

SQL> grant dba to pdb1_user;

Grant succeeded.

SQL>

 

  1. Connect to pdb1 as pdb1_user.

 

SQL> connect pdb1_user/password@pdb1;

Connected.

SQL>

 

  1. Create a public database link to ORCL

 

create public database link orcl_db

connect to scott identified by tiger

using ‘orcl’;

 

SQL> create public database link orcl_db

connect to scott identified by tiger

using ‘orcl’;

SQL>   2    3

 

Database link created.

 

SQL>

 

  1. Create a directory object dmpdir for any necessary data pump files. In this example the log file will be placed in this directory object.

 

SQL> create directory dmpdir as ‘/home/oracle’;

Directory created.

SQL>

 

  1. Test the database link to the database ORCL by querying the table scott.emp.

 

SQL> select * from scott.emp@orcl_db fetch first 2 rows only;

     EMPNO ENAME                          JOB                                MGR

———- —————————— ————————— ———-

    DEPTNO HIREDATE         SAL       COMM

———- ——— ———- ———-

      1111 DOE                            SALES                                1

        10 01-JAN-17       1000       1000

      7369 SMITH                          CLERK                             7902

        20 17-DEC-80        800

SQL>

 

  1. Now exit out of pdb1.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

 

  1. Due to new security restriction you may have to raise the privileges of the user scott in extraction database. Logon to database orcl as the sys and grant scott dba privilege.

 

[oracle@linux2 ~]$ sqlplus sys/password@orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 07:58:33 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> grant dba to scott;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

 

  1. Now from the command prompt, execute the impdp utility

 

impdp pdb1_user/password@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

 

[oracle@linux2 ~]$ impdp pdb1_user/password@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

Import: Release 12.1.0.2.0 – Production on Thu Jan 12 07:59:02 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

FLASHBACK automatically enabled to preserve database integrity.

Starting “PDB1_USER”.”SYS_IMPORT_SCHEMA_01″:  pdb1_user/********@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported “SCOTT”.”BONUS”                                 14 rows

. . imported “SCOTT”.”DEPT”                                   4 rows

. . imported “SCOTT”.”EMP”                                   15 rows

. . imported “SCOTT”.”EMP2″                                  15 rows

. . imported “SCOTT”.”FB_TEST”                                1 rows

. . imported “SCOTT”.”SALGRADE”                               5 rows

. . imported “SCOTT”.”TEST”                                   3 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job “PDB1_USER”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Thu Jan 12 08:16:20 2016 elapsed 0 00:17:15

 

[oracle@linux2 ~]$

 

  2. Now logon to pdb1 as the user pdb1_user and query the new table emp.

 

sqlplus pdb1_user/password@pdb1

select * from pdb1_user.emp fetch first 2 rows only;

[oracle@linux2 ~]$ sqlplus pdb1_user/password@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 08:49:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 12 2016 07:59:02 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> select * from scott.emp fetch first 2 rows only;

 

     EMPNO ENAME      JOB              MGR     DEPTNO HIREDATE         SAL

———- ———- ——— ———- ———- ——— ———-

      COMM

———-

      1111 DOE        SALES              1         10 01-JAN-17       1000

      1000

      7369 SMITH      CLERK           7902         20 17-DEC-80        800

SQL>

 

  1. This completes using Network based data pump.

 

Larry Catt

OCP

Leave a Reply