Oracle

Datapump export/import from Source PDB to Target PDB and with network_link

Bulent Soylu October 8, 2023

Source :

[oracle@node102 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl

SQL> alter session set container=orclpdb;
Session altered.
 
SQL> select username from dba_users where oracle_maintained='N';
USERNAME
--------------------------------------------------------------------------------
PDBADMIN
HR
 
SQL> select table_name from dba_tables where owner='HR';
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES
 
7 rows selected.
 
SQL> select table_name,num_rows from dba_tables where owner='HR';
TABLE_NAME          NUM_ROWS
------------------- ----------
REGIONS             4
LOCATIONS           23
DEPARTMENTS         27
JOBS                19
EMPLOYEES           107
JOB_HISTORY         10
COUNTRIES           25
 
7 rows selected.
 
SQL> select directory_name from dba_directories;
DIRECTORY_NAME
--------------------------------------------------------------------------------
XMLDIR
XSDDIR
ORA_DBMS_FCP_LOGDIR
ORA_DBMS_FCP_ADMINDIR
OPATCH_INST_DIR
ORACLE_OCM_CONFIG_DIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR2
OPATCH_SCRIPT_DIR
OPATCH_LOG_DIR
ORACLE_BASE
ORACLE_HOME
 
1* select directory_name,directory_path from dba_directories
SQL> /
DIRECTORY_NAME         DIRECTORY_PATH
-----------------      --------------------------------------------------------------------------------- 
XMLDIR                 /u01/app/oracle/product/12.2.0.1/rdbms/xml
XSDDIR                 /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema
ORA_DBMS_FCP_LOGDIR    /u01/app/oracle/product/12.2.0.1/cfgtoollogs
ORA_DBMS_FCP_ADMINDIR  /u01/app/oracle/product/12.2.0.1/rdbms/admin
OPATCH_INST_DIR        /u01/app/oracle/product/12.2.0.1/OPatch
ORACLE_OCM_CONFIG_DIR  /u01/app/oracle/product/12.2.0.1/ccr/state
DATA_PUMP_DIR          /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638
ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state
OPATCH_SCRIPT_DIR      /u01/app/oracle/product/12.2.0.1/QOpatch
OPATCH_LOG_DIR         /u01/app/oracle/product/12.2.0.1/rdbms/log
ORACLE_BASE            /u01/app/oracle
ORACLE_HOME            /u01/app/oracle/product/12.2.0.1
 
orclpdb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)
expdp sys/oracle@orclpdb directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log
 
Export: Release 12.2.0.1.0 - Production on Fri Dec 2 00:02:19 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
 
Username: sys/oracle@orclpdb as sysdba
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@orclpdb AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orclpdb/dpdump/EE0955F9C4AB14D0E0536638A8C0EB50/hr_employees.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at ****

target:

[oracle@node101 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value c:\oracle
 
SQL> show pdbs
CON_ID     CON_NAME               OPEN MODE  RESTRICTED
---------- ---------------------- ---------- ----------
2          PDB$SEED               READ ONLY  NO
3          TARGETPDB              READ WRITE NO
SQL> alter session set container=TARGETPDB;
Session altered.
 
1* select directory_name,directory_path from dba_directories
SQL> /
DIRECTORY_NAME                          DIRECTORY_PATH
--------------------------------------- -------------------------------------------------------------------------
XMLDIR                                  /u01/app/oracle/product/12.2.0.1/rdbms/xml
XSDDIR                                  /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema
ORA_DBMS_FCP_LOGDIR                     /u01/app/oracle/product/12.2.0.1/cfgtoollogs
ORA_DBMS_FCP_ADMINDIR                   /u01/app/oracle/product/12.2.0.1/rdbms/admin
OPATCH_INST_DIR                         /u01/app/oracle/product/12.2.0.1/OPatch
ORACLE_OCM_CONFIG_DIR                   /u01/app/oracle/product/12.2.0.1/ccr/state
DATA_PUMP_DIR                           /u01/app/oracle/admin/tgtcdb/dpdump/EE1DA335F2064F71E0536638A8C034E1
ORACLE_OCM_CONFIG_DIR2                  /u01/app/oracle/product/12.2.0.1/ccr/state
OPATCH_SCRIPT_DIR                       /u01/app/oracle/product/12.2.0.1/QOpatch
OPATCH_LOG_DIR                          /u01/app/oracle/product/12.2.0.1/rdbms/log
ORACLE_BASE                             /u01/app/oracle
ORACLE_HOME                             /u01/app/oracle/product/12.2.0.1
 
SQL> select table_name,num_rows from dba_tables where owner='HR';
TABLE_NAME        NUM_ROWS
----------------- -----------------
REGIONS           4
LOCATIONS         23
DEPARTMENTS       27
JOBS              19
EMPLOYEES         107
JOB_HISTORY       10
COUNTRIES         25
 
7 rows selected.
SQL> show pdbs
CON_ID     CON_NAME                   OPEN MODE  RESTRICTED
---------- -------------------------- ---------- ----------
3          TARGETPDB                  READ WRITE NO



tnsnames.ora
TARGETPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TARGETPDB)
)
)


impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace
 
[oracle@node101 admin]$ impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace
Import: Release 19.3.0.0.0 - Production ****
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
UDI-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
 
Username: sys/oracle@TARGETPDB as sysdba
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@TARGETPDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES" 17.08 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at ****

Alternatively direct import with network_link without exporting

in source add pdb record in tnsnames.ora

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB)
    )
  )

add pdb service in listener.ora of source node101

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\bin\oraclr19.dll")
    ) 
    (SID_DESC =
	(ORACLE_HOME = C:\ORACLE)
	(SID_NAME = ORCL)
    )  
    (SID_DESC =
	(ORACLE_HOME = C:\ORACLE)
	(SID_NAME = ORCLPDB)
    ) 
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_PDB_LISTENER=ON 
VALID_NODE_CHECKING_REGISTRATION_PDB_LISTENER=SUBNET

in target add source record in tnsnames.ora of node101



ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB)
    )
  )



targetpdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = targetpdb)
    )
  )

in target node101 add source pdb service in listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\bin\oraclr19.dll")
    )
   (SID_DESC =
	(ORACLE_HOME = C:\ORACLE)
	(SID_NAME = ORCL)
    ) 
   (SID_DESC =
	(ORACLE_HOME = C:\ORACLE)
	(SID_NAME = orclpdb)
    ) 
  )

in target node create source pdb db_link

create database link link_102 connect to system identified by "*****" using 'ORCLPDB';

in target run only impdp 

impdp SYS@targetPDB  network_link=link_102 directory=DATA_PUMP_DIR  tables=hr.BENISIL logfile=BENISIL.log table_exists_action=replace
Import: Release 19.0.0.0.0 - Production on Sat Oct 7 20:17:29 2023
Version 19.3.0.0.0

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

UDI-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: SYS@TARGETPDB AS SYSDBA
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_TABLE_01":  SYS/********@TARGETPDB AS SYSDBA network_link=link_102 directory=DATA_PUMP_DIR tables=hr.BENISIL logfile=BENISIL.log table_exists_action=replace
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 "HR"."BENISIL"                                   2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Oct 7 20:18:03 2023 elapsed 0 00:00:15

Related posts:

  1. Hot Cloning a Remote PDB or Non-CDB
  2. Multitenant : DBCA PDB Remote Clone in Oracle Database 19c
  3. How To Setup Listener For PDBS In Multitenant Database.
  4. Drop Pluggable Database (SQLPLUS command line or DBCA)