오라클 Datapump 비교와 사용법

Datapump

Oracle 10g 이상부터 가능한 export/import의 향상된 유틸리티

Export와 Datapump 비교

	Export	Datapump
속도	진짜 느림	고속 (20배)
시간예측	시간예측 안됨	거의 예측 가능
이어받기	이어받기 불가능  중간에 cancel하면 처음부터 다시	이어받기 가능
병렬받기	불가능	데이터가 클 때 병렬받기로 속도UP!

Datapump 장점

- 작업 관리의 편의성

작업을 일시 중단 시켰다가 다시 시작하는, JOB의 제어가 가능

- 필요한 디스크 공간의 예측

exp/imp 작업도중 디스크 공간 부족으로 작업하던거 취소하고 용량 확보 후 다시 작업 수행해야하지만

datapump는 ESTIMATE 파라미터를 사용해 해당 작업시 필요한 디스크 공간을 미리 알 수 있음

- remapping 기능 지원

스키마 변경이나 테이블 스페이스, 데이터파일 변경까지 가능

expdp 실행 모드

1. Full 모드

full 파라미터를 사용해 데이터베이스 전체를 export 받을 수 있음

하지만 DBA 권한이어야 가능, export_full_database 권한을 가지고 있어야 수행 가능

2. schema 모드

export에서의 owner 파라미터랑 같은

schemas 파라미터를 사용하여 특정 스키마 전체를 export 받음

3. Tablespace 모드

tablespaces 파라미터를 사용해 해당 테이블스페이스에 속한 모든 테이블 받을 수있음

transport_tablespace 파라미터를 사용하면 테이블, 테이블스페이스의 메타 데이터까지 export해

다른 서버로 테이블스페이스 전체를 이동시킬때 아주 유용함 (단, 양쪽 OS, Block size, characterset이 동일해야함)

4. Table 모드

tables 파라미터를 사용, 여러개의 테이블 export도 가능

expdp 파라미터

Parameter	의미
directory	백업받는 디렉토리 이름지정 (오라클에서생성한 directory), 경로는 쓰지 않음
dumpfile	파일 시스템에 저장될 덤프파일의 이름 지정, 역시 경로는 쓰지 않음  파일이 여러개로 나누어질때 %U를 사용해 자동으로 증가(01~99)
filesize	expdp 받을 때 한 개 파일의 최대 크기
parfile	각종 파라미터들을 파일에 저장해놓고 expdp 작업할 때 참조해서 사용  파일은 .par
logfile/nologfile	expdp 작업 내용 저장할 로그파일 지정, 지정하고 싶지 않으면 nologfile
exclude	원하는 오브젝트만 선택해서 작업, exclude=object_name:조건  예) exclude=table:"\='EMP'" --> EMP테이블만 제외       exclude=table:\"IN\(\'EMP\',\'DEPT\'\)\" --> emp,dept 테이블 제외
query	특정 조건에 맞는 데이터만 expdp  예) query=emp:\"'where sal>1000'\"       query=emp:\"where sal\>1000 and job=\'CLERK\'\"
job_name	expdp 작업을 수행할 때 해당 job에 이름 설정,   설정안하면 자동으로 설정하지만 찾기 어려움
parallel	프로세스를 몇개 사용할 것인가를 지정, 속도 upup!  지정된 개수만큼 데이터 파일을 만들어줘야함
attatch	일시 중당된 작업에 다시 접속할 때 사용  add_file: 덤프파일 추가  exit: job 작업에서 빠져나감  parallel: 현재 작업중인 프로세스 개수 조정  status: 현재 작업 상태를 모니터링 하는 갱신 시간 지정  kill_job: 해당 작업 완전히 삭제, 이게 작동 안하면 job_name의 테이블을 drop 해야함  start_job: 중단된 작업 다시 시작  stop_job: 현재 작업 일시 중단

impdp 파라미터

Parameter	의미
include	원하는 오브젝트만 선택해서 작업, include=object_name:조건  예) include=table:\"\=\'emp\'\" --> EMP테이블만 impdp       include=table:\"\in\(\'EMP\',\'DEPT\'\)\" --> emp,dept만 impdp
exclude	특정 테이블만 빼고 전부다 impdp할 때 사용, 방법은 include와 동일
table_exists_action	impdp에만 있는 옵션  동일한 이름의 테이블이 존재할 때 테이블의 데이터를 어떻게 할 것이 지정  skip: 같은 테이블을 만나면 건너뛰고, 다음 테이블 impdp  append: 같은 테이블이 있으면 기존 내용에 데이터 추가 = 기본값  truncate: 기존테이블 truncate하고 새로 impdp  drop: 기존테이블 drop 테이블 새로 만들어서 새로운 내용 impdp
remap_schema	import에서의 fromuser, touser와 동일한 개념  예) remap_schema=scott:hr --> scott으로 받은 테이블을 hr로 impdp
remap_tablespace	기존 테이블스페이스에서 다른테이블스페이스로 테이블을 impdp 시킬 때  예) remap_tablespace='users':'example'

Datapump 작업 관리 및 모니터링 

현재 작업중인 datapump 작업들의 내용을 dba_datapump_jobs 딕셔너리를 통해 확인 가능

SELECT owner_name, job_name, operation, job_mode, state 

FROM dba_datapump_jobs ;

Datapump 작업 예상 시간 조회

SELECT sid, serial#, sofar, totalwork 

FROM v$session_longops

WHERE opname='job_name대문자'

AND sofar != totalwork ;

Datapump 사용 전 환경설정

datapump는 오라클에 directory라는 객체를 통해서 간접적으로 OS파일에 접근

그래서 datapump를 사용하려면 미리 directory가 만들어져 있어야하고 접근 할 수 있는 권한이 필요

예)

$ mkdir /data/datapump

$ sqlplus / as sysdba ;

> create or replace directory datapump as '/data/datapump' ;

> grant read, write on directory datapump to scott ; --> scott에게 datapump 디렉토리에 접근할 수 있는 권한 설정

1. scott 계정의 emp, dept 테이블 백업

tables=테이블명

예)

$ expdp scott/tiger directory=datapump dumpfile=emp_dept job_name=t1 tables=emp, dept

작업내용 보면 

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.617 KB      16 rows

옵션 준대로 scoot의 dept와 emp만 export 되는것을 볼수있음

2. scott schema 전부 백업

schemas=

export에서 owner 옵션이랑 똑같음

예)

$ expdp scott/tiger directory=datapump dumpfile=scott01.dmp schemas=scott

작업내용보면

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  

3. DB 전체를 백업받기

full=y

예)

$ expdp system/oracle directory=datapump dumpfile=full01.dmp job_name=a full=y

4. 일시 중단 후 다시 작업

expdp 수행하다보면 서버에 부하를 많이 주기 때문에 일시 중단했다가 나중에 다시 이어서 작업할 때가 있음

예)

$ expdp system/oracle directory=datapump dumpfile=full02.dmp job_name=a full=y

이 작업중 도중에 컨트롤 c

Export> status --> 현재 작업 상태를 모니터링 하는 갱신시간 지정

Export> stop_job --> 현재 작업 일시 중단

일시 중단한 작업 다시 접속 하려면 attach=작업이름 사용

$ expdp system/oracle attach=system.a

Export> start_job --> 중단된 작업 다시 시작

Export> exit

작업이 되고있는지 조회 가능 

$ sqlplus / as sysdba

> select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs ;

조회했을 때 STATE=EXECUTING 이 나온다. 작업중이라는 얘기

작업이 끝나면 다시 조회했을 때 no rows selected 나옴

해당 작업을 완전히 취소하고 싶으면 

Export> kill_job

5. 비정상적으로 종료된 job 취소

다시 접속을 시도하지만 접속이 안되는 에러가 발생

예)

ORA-39002: invalid operation

ORA-39000: bad dump file specification

ORA-31640: unable to open dump file "/data/dp1/full01.dmp" for read

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

위 문제의 정보를 가진 마스터 테이블을 확인한 후 drop table 명령어로 마스터 테이블 삭제해주면 job 삭제 됨

예) 

1  select o.status, o.object_id, o.object_type,

2  o.owner||'.'||object_name "OWNER.OBJECT"

3  from dba_objects o, dba_datapump_jobs j

4  where o.owner=j.owner_name

5  and o.object_name=j.job_name

6  and j.job_name not like 'BIN$%'

7* order by 4,2

> drop table system.db1 ;

6. 여러 사용자의 테이블 한꺼번에 expdp 받기

tables= 옵션, 콤마로 이어서 써줌

유저.테이블명 쓰면 된다.

예)

$ expdp system/oracle directory=datapump dumpfile=scott16.dmp tables=scott.emp, hr.employees

but 10g는 에러 발생! 따로따로 해줘야함

7. 병렬 expdp + impdp 작업

parallel=n

dumpfile=n개만큼

fillsize=

주의! parallel 지정할 개수만큼 미리 데이터파일 만들어줘야함

그리고 병렬로 작업하다보면 temp tablespace 에러가 뜰 수 있는데 사이즈 늘려주기

예)

> create directory datadir1 as '/data/datadir1' ;

> create directory datadir2 as '/data/datadir2' ;

> create directory datadir3 as '/data/datadir3' ;

> create directory datadir4 as '/data/datadir4' ;

$ expdp system/oracle full=y parallel=4 \

> dumpfile=datadir1:full1%U.dat, \

> datadir2:full2%U.dat, \

> datadir3:full3%U.dat, \

> datadir4:full4%U.dat, \

> filesize=100M

프로세스 몇개 쓰고있는지 확인하려면 $ top 명령어로 가능 

imp작업은 exp 작업과 동일

예)

$ impdp system/manager parallel=4 \

> dumpfile=datadir1:full1%U.dat, \

> datadir2:full2%U.dat, \

> datadir3:full3%U.dat, \

> datadir4:full4%U.dat, \

> table_exists_action=append

8. 파라미터 파일 사용하기 - 여러개 파일로 분할 expdp + 특정 테이블 impdp 작업

parfile=

예)

$ vi expdp_pump.par

userid=system/oracle

directory=datapump

job_name=datapump

full=y

dumpfile= expdp_%U.dmp

filesize=100M

$ expdp parfile=expdp_pump.par 

예)

$ vi impdp_pump.par

userid=scott/tiger

directory=datapump

job_name=datapump

dumpfile=expdp_%U.dmp

tables=TT700

table_exists_action=append

$ impdp parfile=impdp.par

9. import 수행하지 않고 DDL 문장만 추출

sqlfile=

예) 

$ impdp system/oracle directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat

10. 일자별 schema 별로 자동 백업 받는 스크립트

[스크립트]

접기

$ vi expdp_script.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

sqlplus /nolog << E0F3

conn / as sysdba

set head off

set time off

set timing off

set feedback off

set echo off

set line 200

col name for a100

spool /home/oracle/expdp.tmp

select '!mkdir -p /data/backup/expdp/'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual ;

select 'create or replace directory datapump_'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' as '||''''||'/data/backup/expdp/'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||''''||';' from dual ;

select distinct 'grant read, write on directory '||' datapump_'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' to '||lower(owner) from dba_tables where owner not in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','OM','EXFSYS','CTXSYS','OLAPSYS',

'MDSYS','SYSMAN','LOADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS') ;

select distinct '!expdp system/oracle'||' schemas='||lower(owner)||job_name='||lower(owner)||'_datapump_'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' directory=datapump_'||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' dumpfile='||lower(owner)||'_%U.dmp'||' logfile='||lower(owner)||'.log'||' filesize=100M ' from dba_tables where owner not in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','OM','EXFSYS','CTXSYS','OLAPSYS',

'MDSYS','SYSMAN','LOADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS') ;

spool off

!cat /home/oracle/expdp.tmp|grep -v SQL|grep -v SYS>/home/oracle/expdp.sh

@/home/oracle/expdp.sh

exit

E0F3

$ sh expdp_script.sh

접기

★ 11g New Feature, 데이터 펌프 수행 시 암호화 작업

encryption=

encryption_password=암호

encryption 값들은 all, data_only, encrypted_columns_only, metadata_only, none(기본값)

별로 권장하지는 않음!

출처: https://hayleyfish.tistory.com/99 [DB네 생선가게]