[테스트 개요]
이 테스트는 타겟 DB에서 DB Link를 통해 소스 DB 테이블
데이터를 직접 읽어와 Data Pump로 이관하는 절차를 검증한 것이다.
또한
스키마/테이블 remap, table_exists_action 옵션에 따른 동작,
LOGTIME 로그 기록 기능을 실습하여 데이터 이관 시 다양한 시나리오를 확인한 테스트다.
[테스트 환경]
<소스 DB>
DB Name : ptdb_act
<타켓 DB>
DB Name : ptdb
1. 타켓DB의 tnsnames.ora 파일에 소스DB의 접속 정보를 저장
1-1. 타켓DB 확인
[PTDB:oracle@ttdb][/home/oracle]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State
details
--------------------------------------------------------------------------------
Local
Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE
ttdb
STABLE
ora.FRA1.dg
ONLINE ONLINE
ttdb
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ttdb
STABLE
ora.asm
ONLINE ONLINE
ttdb
Started,STABLE
ora.ons
OFFLINE OFFLINE
ttdb
STABLE
--------------------------------------------------------------------------------
Cluster
Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE
ONLINE
ttdb
STABLE
ora.diskmon
1 OFFLINE
OFFLINE
STABLE
ora.evmd
1 ONLINE
ONLINE
ttdb
STABLE
ora.ptdb.db
1 ONLINE
ONLINE
ttdb
Open,HOME=/u01/app/o
racle/product/19c/db
_1,STABLE
--------------------------------------------------------------------------------
1-2. tnsnames.ora 설정 및 tnsping 테스트
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1/network/admin]$
vi
tnsnames.ora
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1/network/admin]$
cat tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration
tools.
LISTENER_PTDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = ttdb)(PORT =
1521))
PTDB =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = ttdb)(PORT =
1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
PTDB)
)
)
SRC_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.11)(PORT =
1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
PTDB_ACT)
)
)
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1/network/admin]$ tnsping SRC_DB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-JAN-2025 16:38:19
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the
alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
PTDB_ACT)))
OK (0
msec)
2. 타켓DB에서 소스DB로 붙을 수 있는 DB Link 생성
--데이터를 가져올 스키마 생성
SYS@PTDB> create user tuner_tmp identified by oracle;
User created.
Elapsed: 00:00:00.15
SYS@PTDB> grant connect, resource, dba to
tuner_tmp;
--tuner_tmp 계정으로 접속
[PTDB:oracle@ttdb][/home/oracle]$ sqlplus tuner_tmp/oracle
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18
17:39:18 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sat Jan 18 2025 17:35:16 +09:00
Connected to:
Oracle Database 19c Enterprise Edition
Release 19.0.0.0.0 - Production
Version
19.24.0.0.0
TUNER_TMP@PTDB > create database link SRC_DB_LINK connect to tuner identified by oracle using 'SRC_DB';
Database
link created.
--> 소스DB에 있는 데이터를 읽는 수 있는지 테스트
TUNER_TMP@PTDB> select count(*) as cnt from tb_cust@SRC_DB_LINK;
CNT
----------
40000
1 row selected.
Elapsed: 00:00:00.07
3. 타켓DB에서 디렉토리 생성
[PTDB:oracle@ttdb][/home/oracle]$ mkdir -pv
/home/oracle/datapump_db_link_test
mkdir: created directory
‘/home/oracle/datapump_db_link_test’
[PTDB:oracle@ttdb][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18
16:58:32 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise
Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SYS@PTDB> create or replace directory datapump_db_link_test as '/home/oracle/datapump_db_link_test';
Directory created.
Elapsed:
00:00:00.03
4. 타켓DB에서 impdp 명령으로 소스DB의 tuner.tb_cust 테이블 가져와서 tuner_tmp.tb_cust_tmp로 저장함
< STRONG> < /STRONG>
[PTDB:oracle@ttdb][/home/oracle/datapump_db_link_test]$ impdp tuner_tmp/oracle directory=datapump_db_link_test logfile=datapump_db_link_test.log tables=tuner.tb_cust network_link=SRC_DB_LINK< /FONT> remap_schema= tuner:tuner_tmp remap_table= tuner.tb_cust:tb_cust_tmp exclude= index,constraint< /FONT>
--> remap_schema, remap_Table 옵션 사용, exclude = index, constraint 옵션 사용으로 테이블 및 데이터까지만 가져옴
Import: Release 19.0.0.0.0 - Production on Sat Jan 18
17:54:21 2025
Version 19.24.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 "TUNER_TMP"."SYS_IMPORT_TABLE_01":
tuner_tmp/******** directory=datapump_db_link_test
logfile=datapump_db_link_test.log tables=tuner.tb_cust network_link=SRC_DB_LINK
remap_schema=tuner:tuner_tmp remap_table=tuner.tb_cust:tb_cust_tmp
exclude=index,constraint
Estimate in progress using BLOCKS
method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 6 MB
Processing object type
TABLE_EXPORT/TABLE/TABLE
. . imported
"TUNER_TMP"."TB_CUST_TMP"
40000 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object
type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job
"TUNER_TMP"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 18 17:54:30
2025 elapsed 0 00:00:08
--> 가져온
데이터 확인
[PTDB:oracle@ttdb][/home/oracle/datapump_db_link_test]$ sqlplus tuner_tmp/oracle
SQL*Plus:
Release 19.0.0.0.0 - Production on Sat Jan 18 19:16:14 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sat Jan 18 2025 17:54:21 +09:00
Connected
to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.24.0.0.0
TUNER_TMP@PTDB> select count(*) from tuner_tmp.tb_cust_tmp;
COUNT(*)
----------
40000
1 row selected.
Elapsed:
00:00:00.20
5. table_exists_action=append 옵션 사용
--> 해당 옵션 사용 시 이미 테이블이 존재해도 생성하려고 한 데이터를 append 시킴
SQL> select count(*) from TB_CUST_TMP;
COUNT(*)
---------
40000
1 rows selected.
[PTDB:oracle@ttdb][/home/oracle/datapump_db_link_test]$ impdp tuner_tmp/oracle directory=datapump_db_link_test logfile=datapump_db_link_test.log tables=tuner.tb_cust network_link=SRC_DB_LINK< /FONT> remap_schema= tuner:tuner_tmp remap_table= tuner.tb_cust:tb_cust_tmp exclude= index,constraint table_exists_action= append< /FONT>
Import:
Release 19.0.0.0.0 - Production on Sat Jan 18 21:08:33 2025
Version 19.24.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
"TUNER_TMP"."SYS_IMPORT_TABLE_01": tuner_tmp/********
directory=datapump_db_link_test logfile=datapump_db_link_test.log
tables=tuner.tb_cust network_link=SRC_DB_LINK remap_schema=tuner:tuner_tmp
remap_table=tuner.tb_cust:tb_cust_tmp exclude=index,constraint
table_exists_action=append
Estimate in progress using BLOCKS
method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 6 MB
Processing object type
TABLE_EXPORT/TABLE/TABLE
Table "TUNER_TMP"."TB_CUST_TMP" exists. Data will be
appended to existing table but all dependent metadata will be skipped due to
table_exists_action of append
. . imported
"TUNER_TMP"."TB_CUST_TMP"
40000 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object
type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job
"TUNER_TMP"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 18 21:08:43
2025 elapsed 0 00:00:09
SQL> select count(*) from TB_CUST_TMP;
COUNT(*)
---------
80000
1 rows selected.
만약 table_exists_action 옵션을 truncate로 주면 테이블이 존재하면 truncate후 넣음
replace로 주면 테이블이 존재하면 drop시킨 후 다시 recreate 함
skip로 주면 테이블이 존재하면 아무것도 하지 않음
table_exists_action=APPEND와 DATA_OPTION=SKIP_CONSTRAINT_ERRORS
--> 위 옵션을 함께 사용하면 유니크 제약 조건 에러 발생 시 에러발생된 행을 건너뛰고 발생안하는 행만을 APPEND하게됨
6. LOGTIME=ALL 옵션 사용 시 로그에 대한 시간을 남겨줌 (12c 신기능임)
[PTDB:oracle@ttdb][/home/oracle/datapump_db_link_test]$ impdp tuner_tmp/oracle directory=datapump_db_link_test logfile= datapump_db_link_test.log tables=tuner.tb_cust network_link= SRC_DB_LINK< /FONT> remap_schema= tuner:tuner_tmp remap_table=tuner.tb_cust:tb_cust_tmp exclude= index,constraint table_exists_action=append LOGTIME=ALL
Import: Release 19.0.0.0.0 - Production on Sat Jan
18 21:31:24 2025
Version 19.24.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
18-JAN-25 21:31:26.615: Starting
"TUNER_TMP"."SYS_IMPORT_TABLE_01": tuner_tmp/********
directory=datapump_db_link_test logfile=datapump_db_link_test.log
tables=tuner.tb_cust network_link=SRC_DB_LINK remap_schema=tuner:tuner_tmp
remap_table=tuner.tb_cust:tb_cust_tmp exclude=index,constraint
table_exists_action=append LOGTIME=ALL
18-JAN-25 21:31:27.517: Estimate in
progress using BLOCKS method...
18-JAN-25 21:31:28.430: Processing object
type TABLE_EXPORT/TABLE/TABLE_DATA
18-JAN-25 21:31:28.524: Total estimation
using BLOCKS method: 6 MB
18-JAN-25 21:31:29.233: Processing object type
TABLE_EXPORT/TABLE/TABLE
18-JAN-25 21:31:30.520: Table
"TUNER_TMP"."TB_CUST_TMP" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of
append
18-JAN-25 21:31:31.250: . . imported
"TUNER_TMP"."TB_CUST_TMP"
40000 rows
18-JAN-25 21:31:31.384: Processing object type
TABLE_EXPORT/TABLE/COMMENT
18-JAN-25 21:31:31.754: Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
18-JAN-25 21:31:31.773:
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
18-JAN-25
21:31:32.688: Job "TUNER_TMP"."SYS_IMPORT_TABLE_01" successfully completed at
Sat Jan 18 21:31:32 2025 elapsed 0 00:00:07
'Oracle > DataPump' 카테고리의 다른 글
| [2025-01-18][OL7.9][19c][DATAPUMP] 기존에 export한 dmp 파일을 기준으로 DDL 스크립트 생성하기 (0) | 2025.09.22 |
|---|