๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/ADMIN

[Oracle] Datapump ๋ฐ์ดํ„ฐ ์ด๋™, expdp impdp ์œ ํ‹ธ๋ฆฌํ‹ฐ ์‚ฌ์šฉ

by ๐ŸŒปโ™š 2020. 7. 16.

Oracle Datapump

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ฆฌ์— ์žˆ์–ด์„œ ๋ฐ์ดํ„ฐ ์ด๋™์€ ํ•„์ˆ˜์ ์œผ๋กœ ๊ผญ ์•Œ์•„์•ผํ•˜๋Š” ๊ธฐ์ˆ ์ž…๋‹ˆ๋‹ค. ์˜ค๋ผํด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ๋Š” Export(exp)์™€ Import(imp) ์œ ํ‹ธ๋ฆฌํ‹ฐ๋ฅผ ์‚ฌ์šฉํ•ด์„œ Data Migration ์ž‘์—…์„ ํ–ˆ์—ˆ๋Š”๋ฐ์š”, 10g๋ถ€ํ„ฐ Datapump๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๊ธฐ์กด ์œ ํ‹ธ๋ฆฌํ‹ฐ๋ณด๋‹ค 20๋ฐฐ๊ฐ€๋Ÿ‰ ๋น ๋ฅด๊ฒŒ ์ž‘์—…์„ ์™„๋ฃŒํ•  ์ˆ˜ ์žˆ๋‹ค๊ณ  ํ•˜๋„ค์š”.

 

 

๊ธฐ์กด exp, imp ์œ ํ‹ธ๋ฆฌํ‹ฐ์™€์˜ ์ฐจ์ด์ 

Datapump๊ฐ€ ๋น ๋ฅด๊ฒŒ ์ž‘์—…์„ ์ง„ํ–‰ํ•  ์ˆ˜ ์žˆ๋Š” ์ด์œ ๋Š” ๋‚ด๋ถ€์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋Š” ๋ฉ”์ปค๋‹ˆ์ฆ˜์ด ๋‹ค๋ฅด๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค. ๊ธฐ์กด์˜ ๋ฐฉ๋ฒ•๊ณผ๋Š” ๋˜‘๊ฐ™์ด ๋ฐ์ดํ„ฐ๋ฅผ ์ด๋™ํ•˜์ง€๋งŒ ์ฒ˜๋ฆฌ ๋ฐฉ์‹์— ๋”ฐ๋ผ ์†๋„๊ฐ€ ๋‹ค๋ฅด๊ฒŒ ์ธก์ •๋ฉ๋‹ˆ๋‹ค. ์ ์€ ์–‘์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ด๋™์‹œํ‚ฌ๋•Œ๋Š” Export์™€ Import ์œ ํ‹ธ๋ฆฌํ‹ฐ๊ฐ€ ๋” ๋น ๋ฆ…๋‹ˆ๋‹ค. ์•„๋ฌด๋ž˜๋„ ๋Œ€๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ทจ๊ธ‰ํ• ๋•Œ Datapump๋ฅผ ์‚ฌ์šฉํ•˜๊ณ  ์•„์ง exp์™€ imp ์œ ํ‹ธ๋ฆฌํ‹ฐ๋Š” ๊ณ„์† ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์œผ๋‹ˆ ์ ์€ ์–‘์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ด๋™ ์‹œํ‚ฌ๋•Œ๋Š” ๊ธฐ์กด ๋ฐฉ๋ฒ•์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ์ข‹์„ ๊ฒƒ ๊ฐ™์Šต๋‹ˆ๋‹ค.

 

์†๋„ ์ด์™ธ๋ฟ๋งŒ ์•„๋‹ˆ๋ผ ์ œ์–ด๋ฐฉ์‹ ๋˜ํ•œ ๋ณ€๊ฒฝ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ๊ธฐ์กด์—๋Š” ํด๋ผ์ด์–ธํŠธ ๋‹จ์—์„œ ์ œ์–ด๊ฐ€๋˜๋Š” ํˆด์ด์—ˆ๋‹ค๋ฉด Datapump๋Š” ์˜ค๋ผํด ์„œ๋ฒ„์—์„œ ์ œ์–ด๊ฐ€ ๋˜๋Š” ํˆด์ž…๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ Datapump์˜ ๊ฒฝ์šฐ Directory ์˜ค๋ธŒ์ ํŠธ๋ฅผ์ƒ์„ฑํ•˜์—ฌ ์‚ฌ์šฉํ•˜๊ณ  export import ์ž‘์—…์„ ์ง„ํ–‰ํ•˜๋Š” ์œ ์ €์—๊ฒŒ ํ•ด๋‹น ์˜ค๋ธŒ์ ํŠธ์— ๋Œ€ํ•ด ์ฝ๊ธฐ, ์“ฐ๊ธฐ ๊ถŒํ•œ์„ ๋ถ€์—ฌํ•ด์ค˜์•ผํ•ฉ๋‹ˆ๋‹ค.

 

 

์‹ค์Šตํ™˜๊ฒฝ

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl(export), newdb(import)
host : 601d2fce71dc

 

์‹ค์Šต๋ชฉํ‘œ

-orcl ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค hr ์œ ์ €์˜ employees, departments ํ…Œ์ด๋ธ”์„ export --> newdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค scott ์Šคํ‚ค๋งˆ๋กœ import
-orcl ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค scott ์Šคํ‚ค๋งˆ export --> newdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ import

 

 

Export expdp ์‹ค์Šต

[oracle@601d2fce71dc ~]$ mkdir /opt/oracle/datapump

์ด๋™ํ•  ๋ฐ์ดํ„ฐ์˜ ํŒŒ์ผ ๋””๋ ‰ํ† ๋ฆฌ๋ฅผ ๋งŒ๋“ค์–ด์ค๋‹ˆ๋‹ค.

 

[oracle@601d2fce71dc ~]$ sqlplus / as sysdba
SYS@orcl> create directory pump_dir as '/opt/oracle/datapump';

Directory created.

Elapsed: 00:00:00.04
SYS@orcl> grant read, write on directory pump_dir to hr;

Grant succeeded.

๋””๋ ‰ํ† ๋ฆฌ ์˜ค๋ธŒ์ ํŠธ๋ฅผ ๋งŒ๋“ค์–ด์ฃผ๊ณ  export ์ž‘์—…์„ ํ•  ์œ ์ €์—๊ฒŒ ๊ถŒํ•œ์„ ๋ถ€์—ฌํ•ฉ๋‹ˆ๋‹ค.

 

 

[oracle@601d2fce71dc ~]$ expdp -help

help ๋ช…๋ น์–ด๋ฅผ ํ†ตํ•ด์„œ expdp๊ฐ€ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋Š” ์˜ต์…˜์„ ์ „๋ถ€ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฒˆ ์‹ค์Šต์—์„œ ๊ธฐ๋ณธ์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋Š” ์˜ต์…˜ ๋ช‡๊ฐ€์ง€๋ฅผ ํ™œ์šฉํ•ด์„œ ํ…Œ์ด๋ธ”๊ณผ ์Šคํ‚ค๋งˆ๋ฅผ exportํ•˜๋Š” ์‹ค์Šต์„ ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

table export

[oracle@601d2fce71dc ~]$ expdp hr/hr dumpfile=hr.dmp directory=pump_dir tables=employees,departments

๊ฐ€์žฅ ๊ธฐ๋ณธ์ด๋˜๋Š” export ๋ฐฉ๋ฒ•์ž…๋‹ˆ๋‹ค.

 

SYS@orcl> 
select 
    username
    ,default_tablespace
    ,temporary_tablespace 
from dba_users 
where username = 'HR';

import ์ž‘์—…์‹œ tablespace์˜ ๋ช…์นญ์ด ๋‹ค๋ฅด๋ฉด remap ์ž‘์—…์ด ํ•„์š”ํ•˜์—ฌ ๋ฏธ๋ฆฌ ํ™•์ธํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

schema export

[oracle@601d2fce71dc ~]$ expdp system/oracle dumpfile=scott.dmp directory=pump_dir schemas=scott

์Šคํ‚ค๋งˆ export๋Š” system ๊ณ„์ •์œผ๋กœ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค. ์ฐธ์กฐ๊ณ  expdp์™€ impdp์˜ ๋งˆ์Šคํ„ฐ ๊ณ„์ •์€ sys๊ฐ€ ์•„๋‹Œ system์ž…๋‹ˆ๋‹ค. export ์ž‘์—…์€ ์‚ฌ์šฉํ•˜๋Š” ์˜ต์…˜๋„ ๊ทธ๋ ‡๊ฒŒ ๋งŽ์ง€ ์•Š๊ณ  ํฌ๊ฒŒ ๋ฌธ์ œ๊ฐ€ ๋˜๋Š” ์ผ์„ ์—†์„ ๊ฒ๋‹ˆ๋‹ค.

 

 

import ์ž‘์—…์‹œ ํ…Œ์ด๋ธ” ํ˜น์€ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค์˜ ์ด๋ฆ„์ด ๋‹ค๋ฅด๋ฉด ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค. remap ์ž‘์—…์„ ์œ„ํ•ด ๋ฏธ๋ฆฌ orcl ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ scott ์Šคํ‚ค๋งˆ์— ๋Œ€ํ•œ ์ •๋ณด๋“ค์„ ํ™•์ธํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

SYS@orcl> 
select 
    username
    ,default_tablespace
    ,temporary_tablespace 
from dba_users 
where username = 'SCOTT';

 

SYS@orcl> select table_name from dba_tables where owner = 'SCOTT';

 

 

Import impdp ์‹ค์Šต

Import ์ž‘์—…์€ ๊ธฐ์กด์— ๋งŒ๋“  newdb ์—์„œ ์ž‘์—…ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋Š” dbca๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ƒˆ๋กœ ๋งŒ๋“ค๊ฑฐ๋‚˜ ์ปค๋งจ๋“œ๋กœ ์ˆ˜๋™์œผ๋กœ ์ƒ์„ฑํ•˜๋Š” ์ž‘์—…์„ ์ง„ํ–‰ํ•˜๋ฉด ๋ฉ๋‹ˆ๋‹ค. ์ˆ˜๋™ ์ƒ์„ฑ์ž‘์—…์€ ์•„๋ž˜ ๋งํฌ ์ฐธ์กฐ.

 

[Oracle] ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ SID ์ถ”๊ฐ€ CREATE DATABASE ๋ช…๋ น์–ด ์‚ฌ์šฉ

OS : Red Hat Enterprise Linux Server 6.10 DB : Oracle 11.2.0.1 Oracle Database์—์„œ DBCA๋ฅผ ์ด์šฉํ•˜์ง€ ์•Š๊ณ  ์ˆ˜๋™์œผ๋กœ CREATE DATABASE ๋ช…๋ น์–ด๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•˜๋Š” ์‹ค์Šต์„ ํ•˜๋ ค๊ณ ํ•ฉ๋‹ˆ๋‹ค. ์ƒˆ๋กœ..

myjamong.tistory.com

 

 

[oracle@601d2fce71dc ~]$ . oraenv
ORACLE_SID = [orcl] ? newdb
The Oracle base for ORACLE_HOME=/opt/oracle/app/product/11.2.0/dbhome_1 is /opt/oracle/app
[oracle@601d2fce71dc ~]$ sqlplus / as sysdba

ํ™˜๊ฒฝ๋ณ€์ˆ˜ ๋ณ€๊ฒฝ ์ž‘์—…๊ณผ sys ๊ณ„์ •์œผ๋กœ ๋กœ๊ทธ์ธํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

 

SYS@newdb> select username from dba_users;

์ˆ˜๋™์œผ๋กœ ์ƒ์„ฑํ•œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ผ ์Šคํ‚ค๋งˆ๊ฐ€ ๊ฑฐ์˜ ์—†์Šต๋‹ˆ๋‹ค. ๊ทธ๋Ÿผ exportํ–ˆ๋˜ scott schema๋ถ€ํ„ฐ import ์ž‘์—…์„ ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

SYS@newdb> create directory pump_dir as '/opt/oracle/datapump';

newdb ์ชฝ์—์„œ๋„ ๋””๋ ‰ํ† ๋ฆฌ ์˜ค๋ธŒ์ ํŠธ๋ฅผ ์ƒ์„ฑํ•ด์ค๋‹ˆ๋‹ค.

 

impdp ์œ ํ‹ธ๋ฆฌํ‹ฐ๋ฅผ ์‚ฌ์šฉํ• ๋•Œ expdp์™€๋Š” ๋‹ฌ๋ฆฌ ์ž์ฃผ ์‚ฌ์šฉํ•˜๊ฒŒ๋˜๋Š” remap ์˜ต์…˜์ด ์žˆ์Šต๋‹ˆ๋‹ค. export ํ•œ์ •๋ณด๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ import๋ฅผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์— tablespace๋‚˜ schema์˜ ๋ช…์นญ์ด ๋‹ค๋ฅผ ๋•Œ remap ์ž‘์—…์„ ๊ผญ ํ•ด์ค˜์•ผํ•ฉ๋‹ˆ๋‹ค.

 

 

schema import

SYS@newdb> select tablespace_name, contents from dba_tablespaces;

Schema import ์ž‘์—…์‹œ export ํ–ˆ์„๋•Œ์˜ ์ •๋ณด๋“ค๊ณผ ๋ช…์นญ์ด ๋‹ค๋ฅด๋ฉด remap ์ž‘์—…์„ ํ•ด์ค˜์•ผํ•ฉ๋‹ˆ๋‹ค. scott ๊ณ„์ •์„ import ํ•˜๊ธฐ์œ„ํ•ด์„œ  default tablespace์™€ default temporary tablespace์˜ ๋ช…์นญ์„ ๋งž์ถฐ์ค˜์•ผํ•˜๋‹ˆ ๋ฏธ๋ฆฌ ํ™•์ธํ•ด๋ดค์Šต๋‹ˆ๋‹ค. ๊ธฐ๋ณธ์œผ๋กœ ์‚ฌ์šฉํ•   tablespace๋„ ๋งŒ๋“ค์–ด์ฃผ๊ฒ ์Šต๋‹ˆ๋‹ค.

 

SYS@newdb> create tablespace dump datafile '/opt/oracle/app/oradata/newdb/dump01.dbf' size 200m;

์Šคํ‚ค๋งˆ์˜ ๊ธฐ๋ณธ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค๋ฅผ ๋ฏธ๋ฆฌ ๋งŒ๋“ค์–ด์คฌ์Šต๋‹ˆ๋‹ค. ์ด์ œ import ์ž‘์—…์„ ์ง„ํ–‰ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

[oracle@601d2fce71dc ~]$ impdp system/oracle dumpfile=scott.dmp directory=pump_dir remap_tablespace=users:dump

์Šคํ‚ค๋งˆ๋ฅผ importํ•˜๋Š” ์ž‘์—…์œผ๋กœ system ๊ณ„์ •์œผ๋กœ ์ ‘์†ํ•ด์„œ ์ž‘์—…ํ–ˆ์Šต๋‹ˆ๋‹ค. ๊ธฐ๋ณธ ์˜ต์…˜์œผ๋กœ directory์™€ dumpfile์„ exportํ•œ ํŒŒ์ผ์˜ ์ •๋ณด๋กœ ์„ค์ •ํ•˜๊ณ  default temporay tablespace์˜ ๋ช…์นญ์€ ๊ฐ™์•„ ๊ทธ๋Œ€๋กœ ์ง„ํ–‰ํ•˜๊ณ  default tablespace์˜ ์ •๋ณด๊ฐ€ ๋‹ฌ๋ผ remap ์ž‘์—…์„ ํ•ด์คฌ์Šต๋‹ˆ๋‹ค.

 

SYS@newdb> alter user scott identified by tiger account unlock;

User altered.

Elapsed: 00:00:00.01
SYS@newdb> conn scott/tiger
Connected.

์Šคํ‚ค๋งˆ๊ฐ€ unlock๋˜์–ด ์žˆ๋Š” ์ƒํƒœ์—์„œ export ํ–ˆ์Šต๋‹ˆ๋‹ค. account unlockํ•˜๊ณ  ์ ‘์†ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

SCOTT@newdb> select table_name from tabs;

TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT

Elapsed: 00:00:01.36
SCOTT@newdb> select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

์ด์ƒ์—†์ด ๋ฐ์ดํ„ฐ๊ฐ€ ์ด๋™๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

 

table import

[oracle@601d2fce71dc ~]$ impdp system/oracle dumpfile=hr.dmp directory=pump_dir remap_schema=hr:scott remap_tablespace=examples:dump

ํ…Œ์ด๋ธ”๋งŒ importํ•˜๊ฒŒ ๋˜๋ฉด ํ•ด๋‹น ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ๊ถŒํ•œ์„ ์—†๋Š” ์œ ์ €์—๊ฒŒ ๋ถ€์—ฌํ•˜๊ฑฐ๋‚˜ ์—†๋Š” ํ…Œ์ด๋ธ”๊ณผ์˜ ์ œ์•ฝ์กฐ๊ฑด ๋“ฑ๋“ฑ์˜ ์ด์œ ๋กœ ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ๋Š” ๋ชจ๋‘ ๋“ค์–ด๊ฐ€๋”๋ผ๋„ ๋‹ค๋ฅธ ์˜ค๋ธŒ์ ํŠธ์™€์˜ ๊ด€๊ณ„์— ์˜ํ•œ ์˜ค๋ฅ˜๋Š” ์ƒํ™ฉ์— ๋งž๊ฒŒ ๋กœ๊ทธํ™•์ธํ•˜๋ฉด์„œ ํ•ด๊ฒฐํ•ด์ฃผ๋ฉด ๋ฉ๋‹ˆ๋‹ค.

 

 

SCOTT@newdb> select table_name from tabs;

ํ…Œ์ด๋ธ”์ด ๋ชจ๋“œ import๋˜์–ด ๋“ค์–ด๊ฐ„๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

 

 

 

[Oracle] Transportable Tablespace ๊ฐ€์žฅ ๋น ๋ฅธ ๋ฐ์ดํ„ฐ ์ด๋™ ๋ฐฉ๋ฒ•

Transportable Tablespace Transportable Tablespace๋Š” ์˜ค๋ผํด์—์„œ ์˜ค๋ผํด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ์ด๋™ํ• ๋•Œ ๊ฐ€์žฅ ๋น ๋ฅธ๋ฐฉ๋ฒ•์ด๋ผ๊ณ  ํ•ฉ๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐํŽŒํ”„๋‚˜ Export, Import ์œ ํ‹ธ๋ฆฌํ‹ฐ๋ฅผ ์‚ฌ์šฉํ• ๋•Œ๋Š” ๊ทธ๋ƒฅ insert์ž‘์—…..

myjamong.tistory.com

 

 

๋Œ“๊ธ€