[oracle] datapump 이기종버전 원격지 데이터 로컬로 가져오기

 

OS : oracle linux 7.5
DBMS : oracle 19.3

[목차여기]

- 서로 다른 버전간 expdp 실행
- 원격 데이터 -> 로컬DB (dump 파일 생성 없이 DB에 바로 테이블 생성)

 
참고: oracle expdp 원격지(remote) 데이터 로컬로 가져오기

19c(리모트) -> 11g(로컬) 원격 데이터 expdp 에러

[oracle@svr1 ~]$ expdp skdba/oracle NETWORK_LINK=remote_link TABLES=sk.t1 directory=exp_dir dumpfile=skdba.dmp  logfile=skdba.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39169: Local version of 11.2.0.1.0 cannot work with remote version of 19.0.0.0.0.

---

11g(리모트) -> 19c(로컬) 원격 데이터 expdp 정상 작동

[oracle@svr1 pump]$ expdp \'sys/oracle as sysdba\' NETWORK_LINK=LN_ORA11G TABLES=sk.t1_11g directory=exp_dir dumpfile=skdba.dmp  logfile=skdba.log

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/******** AS SYSDBA" NETWORK_LINK=LN_ORA11G TABLES=sk.t1_11g directory=exp_dir dumpfile=skdba.dmp logfile=skdba.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SK"."T1_11G"                               6.964 MB   72003 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/pump/skdba.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 3 13:31:54 2023 elapsed 0 00:00:03

---

리모트(11g) -> 로컬(19c) 덤프파일 생성없이 DB에 바로 테이블 생성

소스 테이블에 생성되어 있던 PK, 인덱스 생성됨

[oracle@svr1 pump]$ impdp \'sys/oracle as sysdba\' NETWORK_LINK=LN_ORA11G TABLES=sk.t1_11g directory=exp_dir logfile=skdba.log

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/******** AS SYSDBA" NETWORK_LINK=LN_ORA11G TABLES=sk.t1_11g directory=exp_dir logfile=skdba.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 832 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SK"."T1_11G"                                 2737 rows
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
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sun Dec 3 13:59:18 2023 elapsed 0 00:00:02