본문 바로가기
DBMS/ORACLE

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

by 드바 2023. 12. 1.

 
 

OS : oracle linux 7.5
DBMS : oracle 19.3

[목차여기]

- 리모트 DB의 데이터를 expdp로 가져와 로컬(local)에 저장하는 방법
- DB, 스키마, 테이블 별 expdp 방법
- ORA-31631, ORA-39149 에러 발생 시 조치 방법

 

DB LINK 생성

using 부분에 들어가는 값은 tnsnames.ora 에 등록되어 있는 리모트 DB Alias

-- DB LINK 생성
create public database link remote_link connect to skdba identified by "skdba"  using 'ORA19C' ;

SELECT * FROM dba_db_links ;

OWNER |DB_LINK       |USERNAME|HOST    |CREATED                |HIDDEN|SHARD_INTERNAL|VALID|INTRA_CDB|
------+--------------+--------+--------+-----------------------+------+--------------+-----+---------+
SYS   |SYS_HUB       |        |SEEDDATA|2019-04-17 01:12:06.000|NO    |NO            |YES  |NO       |
PUBLIC|REMOTE_LINK   |SKDBA   |ORA19C  |2023-12-01 15:26:50.000|NO    |NO            |YES  |NO       |
PUBLIC|SK_REMOTE_LINK|SK      |ORA19C  |2023-12-01 15:27:00.000|NO    |NO            |YES  |NO       |

 

디렉토리 생성

-- expdp 경로 디렉토리 생성
create  directory  exp_dir  as '/home/oracle/pump' ;

SELECT * FROM DBA_DIRECTORIES ;

OWNER|DIRECTORY_NAME          |DIRECTORY_PATH                                        |ORIGIN_CON_ID|
-----+------------------------+------------------------------------------------------+-------------+
SYS  |EXP_DIR                 |/home/oracle/pump                                     |            0|

/*******************************************************************************
-- expdp 시 에러 메시지 발생한다면 권한이 없으므로 권한 부여
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name EXP_DIR is invalid

-- 권한 부여
grant READ , WRITE ON DIRECTORY exp_dir TO skapp ;

-- 디렉토리 권한 조회
SELECT * FROM DBA_TAB_PRIVS WHERE table_name = 'EXP_DIR' ;

*******************************************************************************/

 

DB LINK 데이터 조회 테스트

select count(*) from sk.t1@remote_link ;

COUNT(*)|
--------+
 2330752|

 

반응형

expdp 실행

DB expdp

[oracle@svr1 pump]$ expdp skdba/oracle NETWORK_LINK=remote_link directory=exp_dir dumpfile=skdba.dmp  logfile=skdba.log

Export: Release 19.0.0.0.0 - Production on Fri Dec 1 15:46:07 2023
Version 19.3.0.0.0

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SKDBA"."SYS_EXPORT_SCHEMA_01":  skdba/******** NETWORK_LINK=remote_link directory=exp_dir dumpfile=skdba.dmp logfile=skdba.log 
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
Master table "SKDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SKDBA.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/pump/skdba.dmp
Job "SKDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Dec 1 15:46:16 2023 elapsed 0 00:00:08


테이블 expdp

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

Export: Release 19.0.0.0.0 - Production on Fri Dec 1 15:42:32 2023
Version 19.3.0.0.0

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 "SKDBA"."SYS_EXPORT_TABLE_01":  skdba/******** NETWORK_LINK=remote_link TABLES=sk.t1 directory=exp_dir dumpfile=skdba.dmp logfile=skdba.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/INDEX/INDEX
. . exported "SK"."T1"                                   306.4 MB 2330752 rows
Master table "SKDBA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SKDBA.SYS_EXPORT_TABLE_01 is:
  /home/oracle/pump/skdba.dmp
Job "SKDBA"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 1 15:42:50 2023 elapsed 0 00:00:17

 
스키마 expdp

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

Export: Release 19.0.0.0.0 - Production on Fri Dec 1 15:44:55 2023
Version 19.3.0.0.0

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SKDBA"."SYS_EXPORT_SCHEMA_01":  skdba/******** NETWORK_LINK=remote_link schemas=sk directory=exp_dir dumpfile=skdba.dmp logfile=skdba.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "SK"."T1"                                   306.4 MB 2330752 rows
. . exported "SK"."T1_1"                                 147.8 MB 2330752 rows
Master table "SKDBA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SKDBA.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/pump/skdba.dmp
Job "SKDBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Dec 1 15:45:23 2023 elapsed 0 00:00:27

 

리모트 expdp 실패 시 확인

ORA-31631, ORA-39149 에러 발생

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

Export: Release 19.0.0.0.0 - Production on Fri Dec 1 16:03:06 2023
Version 19.3.0.0.0

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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user


소스쪽 계정이 exp_full_database 권한을 가지고 있는지 확인
로컬에서 수행하는 것과 다르게 NETWORK_LINK를 사용한 expdp는
테이블 단위 작업에도 EXP_FULL_DATABASE 권한을 필요로 했습니다.

-- EXP_FULL_DATABASE 권한이 없음
SELECT * FROM user_role_privs@sk_remote_link ;

USERNAME|GRANTED_ROLE     |ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|OS_GRANTED|COMMON|INHERITED|
--------+-----------------+------------+---------------+------------+----------+------+---------+
SK      |CONNECT          |NO          |NO             |YES         |NO        |NO    |NO       |

/*****************************************
-- 소스DB에서 권한을 부여해야 함
GRANT exp_full_database TO sk ;
*****************************************/

-- EXP_FULL_DATABASE 권한 부여 확인됨
SELECT * FROM user_role_privs@sk_remote_link ;

USERNAME|GRANTED_ROLE     |ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|OS_GRANTED|COMMON|INHERITED|
--------+-----------------+------------+---------------+------------+----------+------+---------+
SK      |CONNECT          |NO          |NO             |YES         |NO        |NO    |NO       |
SK      |EXP_FULL_DATABASE|NO          |NO             |YES         |NO        |NO    |NO       |


권한 부여 후 장상 작동

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

Export: Release 19.0.0.0.0 - Production on Fri Dec 1 16:06:07 2023
Version 19.3.0.0.0

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 "SKDBA"."SYS_EXPORT_TABLE_01":  skdba/******** NETWORK_LINK=sk_remote_link TABLES=sk.t1 directory=exp_dir dumpfile=skdba.dmp logfile=skdba.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/INDEX/INDEX
. . exported "SK"."T1"                                   306.4 MB 2330752 rows
Master table "SKDBA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SKDBA.SYS_EXPORT_TABLE_01 is:
  /home/oracle/pump/skdba.dmp
Job "SKDBA"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 1 16:06:26 2023 elapsed 0 00:00:18

'DBMS > ORACLE' 카테고리의 다른 글

[oracle] optimizer_adaptive_plans 기능 설명 및 권장값  (0) 2023.12.22
[oracle] datapump 이기종버전 원격지 데이터 로컬로 가져오기  (1) 2023.12.03
ASM DISK 추가/일시중지/재개 시 상태 변화  (0) 2023.10.26
ASM DISK 추가/삭제 성능 테스트(신규추가, 기존삭제)  (0) 2023.10.26
오라클 NTILE 함수 설명 및 사용법  (0) 2023.10.03

관련글

댓글

티스토리툴바