OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c to 19c datapump expdp network_link 옵션 사용
본문에서는 dblink를 이용한 expdp 사용 방법에 대해 설명함
dblink를 이용한 datapump expdp이기때문에 로컬에서 소스의 데이터를 바로 export 해올 수 있음
이 경우 로컬서버에 공간이 있는 경우 소스 db서버에 dump 파일을 내릴 공간이 없어도 수행할 수 있음
로컬 db <- 소스 db 서버로 원격 expdp 하는 방식임
network_link 옵션 제약사항
- 소스와 대상 DB 버전 차이는 최대 두 버전까지만 허용함 (예시 : 12c <-> 11g, 10g 가능)
- expdp 실행 계정이 DATAPUMP_EXP_FULL_DATABASE 권한이 있으면, impdp 실행 계정도 DATAPUMP_IMP_FULL_DATABASE 권한 필요함
- Parallel Query(PQ) 및 메타데이터 병렬 처리 불가
- 암호화되지 않은 네트워크 링크 사용 시, 암호화된 데이터도 평문으로 전송됨
이외 제약사항은 공식문서 참고
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-0871E56B-07EB-43B3-91DA-D1F457CF6182
db 정보
소스 데이터베이스 : 192.168.137.19 / sid : ORACLE19FS / port : 1521
로컬 서버 : 192.168.137.100
테스트
소스 데이터베이스 리스너 기동
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
$ lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-OCT-2025 19:50:11
Copyright (c) 1991, 2025, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19fs)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-OCT-2025 08:55:09
Uptime 12 days 7 hr. 55 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19fs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19fs)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19DBFS" has 1 instance(s).
Instance "ORA19DBFS", status READY, has 1 handler(s) for this service...
Service "ORA19DBFSXDB" has 1 instance(s).
Instance "ORA19DBFS", status READY, has 1 handler(s) for this service...
The command completed successfully
|
소스 데이터베이스에서 empmig 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
SQL> create user imsi2 identified by imsi2 quota unlimited on users;
User created.
SQL> grant resource, connect to imsi2;
Grant succeeded.
SQL> conn imsi2/imsi2
SQL> create table empmig as select * from emp;
Table created.
SQL> update empmig set empno = 9999;
14 rows updated.
SQL> commit;
Commit complete.
SQL> set lines 200 pages 1000
SQL> select * from empmig;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9999 KING PRESIDENT 17-NOV-81 5000 10
9999 BLAKE MANAGER 7839 01-MAY-81 2850 30
9999 CLARK MANAGER 7839 09-JUN-81 2450 10
9999 JONES MANAGER 7839 02-APR-81 2975 20
9999 SCOTT ANALYST 7566 13-JUL-87 3000 20
9999 FORD ANALYST 7566 03-DEC-81 3000 20
9999 SMITH CLERK 7902 17-DEC-80 800 20
9999 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
9999 WARD SALESMAN 7698 22-FEB-81 1250 500 30
9999 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
9999 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
9999 ADAMS CLERK 7788 13-JUL-87 1100 20
9999 JAMES CLERK 7698 03-DEC-81 950 30
9999 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
|
샘플 테이블이 정상적으로 생성됨
소스 데이터베이스에 존재하는 테이블 확인
|
1
2
3
4
5
6
7
|
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- ------------- ----------
EMPMIG TABLE
DEPT TABLE
EMP TABLE
|
총 3개가 존재함
해당 테이블이 존재하는 테이블스페이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col segment_name for a10
col tablespace_name for a10
select segment_name, tablespace_name from dba_segments
where segment_name in ('EMP', 'DEPT', 'EMPMIG')
and owner = 'IMSI2';
SEGMENT_NA TABLESPACE
---------- ----------
DEPT USERS
EMP USERS
EMPMIG USERS
|
모두 users 테이블스페이스에 존재함
유저의 quota 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col username for a20
select * from dba_ts_quotas
where username = 'IMSI2';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- -------------------- ---------- ---------- ---------- ---------- ---
USERS IMSI2 327680 -1 40 -1 NO
|
현재 imsi 유저는 users 테이블스페이스에 quota가 존재함
참고 : 오라클 19c 유저별 테이블스페이스 quota 확인 및 변경 ( https://positivemh.tistory.com/1202 )
로컬 데이터베이스에서 tnsnames.ora에 소스 데이터베이스 정보 등록
|
1
2
3
4
5
6
7
8
9
10
|
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
SOURCEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORACLE19FS)
)
)
|
tnsping 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ tnsping sourcedb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-OCT-2025 20:29:18
Copyright (c) 1997, 2025, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.19)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19DBFS)))
OK (0 msec)
|
정상적으로 tnsping이 동작함
소스 데이터베이스 접속 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ sqlplus system/"oracle"@sourcedb
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 28 20:29:54 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Tue Oct 28 2025 20:29:23 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> select instance_name, version, status from v$instance;
INSTANCE_NAME VERSION STATUS
---------------- ----------------- ------------
ORA19DBFS 19.0.0.0.0 OPEN
|
정상적으로 접속됨
로컬 데이터베이스에서 directory 생성
|
1
2
3
|
SQL> create directory mig_dir as '/oradata2/datapump';
Directory created.
|
로컬 데이터베이스에서 db링크 생성
|
1
2
3
4
5
6
7
8
9
|
SQL> create public database link mig_link connect to system identified by "oracle" using 'SOURCEDB';
Database link created.
SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') dt from dual@mig_link;
DT
-------------------
2025/10/28 20:51:41
|
db링크로도 정상 접속됨
dblink를 이용한 datapump expdp이기때문에 소스db서버가 아닌 로컬에 dump 파일이 생성됨
로컬 데이터베이스에서 expdp 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
$ expdp system/oracle network_link=mig_link directory=mig_dir full=y dumpfile=20251028_expdp_full.dmp logfile=20251028_expdp_full.log
Export: Release 19.0.0.0.0 - Production on Tue Oct 28 20:54:47 2025
Version 19.27.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 "SYSTEM"."SYS_EXPORT_FULL_01": system/******** network_link=mig_link directory=mig_dir full=y dumpfile=20251028_expdp_full.dmp logfile=20251028_expdp_full.log
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.632 KB 29 rows
. . exported "SYSTEM"."REDO_DB" 39.22 KB 1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 17.05 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 11.57 KB 97 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 8.648 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 7.773 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 7.570 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 7.648 KB 1 rows
. . exported "WMSYS"."WM$ENV_VARS$" 7 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 6.921 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 6.890 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 7 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 6.523 KB 12 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 6.929 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 8.5 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
. . exported "WMSYS"."WM$EXP_MAP" 9.804 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "SYSTEM"."ALL_TABLE_STATS" 377.9 MB 2814157 rows
. . exported "SYSTEM"."ALL_TABLE_STATS2" 223.5 MB 1529704 rows
. . exported "SYSTEM"."STAT_TAB" 3.632 MB 25700 rows
. . exported "SYSTEM"."SAMPLE_1K" 22.66 KB 1000 rows
. . exported "IMSI"."EMPLOYEE" 20.42 KB 107 rows
. . exported "IMSI"."EMPLOYEES" 20.42 KB 107 rows
. . exported "IMSI"."LOCATION" 10.26 KB 23 rows
. . exported "IMSI"."LOCATIONS" 10.26 KB 23 rows
. . exported "IMSI"."JOB" 8.414 KB 19 rows
. . exported "IMSI"."JOBS" 8.414 KB 19 rows
. . exported "IMSI"."DEPARTMENT" 8.468 KB 27 rows
. . exported "IMSI"."DEPARTMENTS" 8.468 KB 27 rows
. . exported "IMSI"."REGION" 6.242 KB 4 rows
. . exported "IMSI"."REGIONS" 6.242 KB 4 rows
. . exported "IMSI"."CHANNELS" 0 KB 0 rows
. . exported "IMSI"."COUNTRIES" 0 KB 0 rows
. . exported "IMSI"."COUNTRY" 0 KB 0 rows
. . exported "IMSI"."CUSTOMERS" 0 KB 0 rows
. . exported "IMSI"."JOB_HISTORY" 0 KB 0 rows
. . exported "IMSI"."PRODUCTS" 0 KB 0 rows
. . exported "IMSI"."PROMOTIONS" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_1995" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_1996" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_1998" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_1999" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_2000" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_2001" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_1998" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_1999" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_2000" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_2001" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_1998" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_1999" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_2000" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_2001" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_1998" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_1999" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_2000" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_2001" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . exported "IMSI"."SALES":"SALES_Q4_2003" 0 KB 0 rows
. . exported "IMSI"."TIMES" 0 KB 0 rows
. . exported "IMSI2"."DEPT" 7.031 KB 4 rows
. . exported "IMSI2"."EMP" 11.28 KB 14 rows
. . exported "IMSI2"."EMPMIG" 11.28 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/oradata2/datapump/20251028_expdp_full.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Oct 28 20:58:17 2025 elapsed 0 00:03:27
|
명령어 설명 :
system/oracle : 타겟 데이터베이스의 system 유저 이름과 패스워드
network_link=mig_link : 타겟 데이터베이스에서 만든 db링크 이름
full=y : 소스 데이터베이스의 전체 db를 export
로컬 서버에서 dump 파일 확인
|
1
2
3
4
5
6
7
|
$ cd /oradata2/datapump/
$ ls -al
total 623972
drwxr-xr-x 2 oracle oinstall 68 Oct 28 20:54 .
drwxrwxr-x 6 oracle dba 4096 Oct 28 20:54 ..
-rw-r----- 1 oracle oinstall 638926848 Oct 28 20:58 20251028_expdp_full.dmp
-rw-r--r-- 1 oracle oinstall 12852 Oct 28 20:58 20251028_expdp_full.log
|
정상적으로 dump파일이 생성됨
이상태에서 바로 import 할수도 있지만 sqlfile 옵션을 사용해 원하는대로 스크립트를 생성해서 볼수도 있음
metadata만 스크립트로 확인(content=metadata_only)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
$ impdp system/oracle directory=mig_dir dumpfile=20251028_expdp_full.dmp content=metadata_only sqlfile=20251028_metaonly.sql
Import: Release 19.0.0.0.0 - Production on Tue Oct 28 21:03:57 2025
Version 19.27.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
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=mig_dir dumpfile=20251028_expdp_full.dmp content=metadata_only sqlfile=20251028_metaonly.sql
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Oct 28 21:04:05 2025 elapsed 0 00:00:07
|
생성된 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
$ ls -al
total 624164
drwxr-xr-x 2 oracle oinstall 115 Oct 28 20:04 .
drwxrwxr-x 6 oracle dba 4096 Oct 28 20:54 ..
-rw-r----- 1 oracle oinstall 638926848 Oct 28 20:58 20251028_expdp_full.dmp
-rw-r--r-- 1 oracle oinstall 12852 Oct 28 20:58 20251028_expdp_full.log
-rw-r--r-- 1 oracle oinstall 189616 Oct 28 21:04 20251028_metaonly.sql
-rw-r--r-- 1 oracle oinstall 3540 Oct 28 21:04 import.log
$ vi 20251028_metaonly.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
-- new object type path: DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/app/oracle/oradata/ORA19DBFS/undotbs01.dbf' SIZE 1073741824
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER DATABASE DATAFILE
'/app/oracle/oradata/ORA19DBFS/undotbs01.dbf' RESIZE 4622123008;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/app/oracle/oradata/ORA19DBFS/temp01.dbf' SIZE 1073741824
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE TABLESPACE "USERS" DATAFILE
'/app/oracle/oradata/ORA19DBFS/users01.dbf' SIZE 1073741824
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
...
(생략)
|
정상적으로 메타데이터만 확인 가능함
data만 스크립트로 확인(content=data_only)
|
1
2
3
4
5
6
7
8
9
10
|
$ impdp system/oracle directory=mig_dir dumpfile=20251028_expdp_full.dmp content=data_only sqlfile=20251028_dataonly.sql
Import: Release 19.0.0.0.0 - Production on Wed Oct 29 08:10:45 2025
Version 19.27.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-39005: inconsistent arguments
ORA-39208: Parameter CONTENT=DATA_ONLY is invalid for SQL_FILE jobs.
|
참고로 이 작업은 실패함, data_only로 data만 넣겠다는 커맨드인데 datapump sqlfile 옵션에서는 메타데이터만 작성되어 있기 때문임
2개 옵션이 상충되기때문에 에러가 발생함
결론 :
expdp시 network_link 옵션을 사용하면 소스 서버에 dmp 파일을 저장할 공간이 없는 경우 유용하게 사용할 수 있음
하지만 병렬기능이 제대로 동작하지 않기 때문에 일반 expdp, impdp 보다는 많이 느릴 수 있음
대상 세그먼트 용량이 크면 클수록 네트워크 부하 및 시간이 오래 소요됨
참조 :
2807070.1, 1305166.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-0871E56B-07EB-43B3-91DA-D1F457CF6182
https://positivemh.tistory.com/734
https://positivemh.tistory.com/1017
https://positivemh.tistory.com/1202
https://positivemh.tistory.com/1210
https://blog.naver.com/pumba3/220667390732
https://emersontech.com.br/en/network_link/
https://cafe.naver.com/prodba/56084
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 19c 통계정보 export 관련 궁금증 테스트 (0) | 2025.08.11 |
|---|---|
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 (0) | 2025.08.10 |
| 오라클 19c 통계정보 대량 이관시 병렬 처리 (0) | 2025.08.10 |
| 오라클 19c 통계정보 이관 방법 (0) | 2025.08.10 |
| 오라클 11gR2 Raw Device RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |