Oracle datapump는 oracle 10g 버전부터 등장한 export/import 의 향상된 유틸리티입니다.


1. Datapump의 장점
-작업 관리의 편이성 : 작업 중지가 가능함.(job의 제어가 가능)
-필요한 디스크 공간의 예측 : ESTIMATE 파라미터를 사용하여 해당 작업 시 필요한 디스크 공간 예측
-원격지 DB에 작업 수행 가능 : DBLINK 기능을 통해 원격지에 있는 데이터베이스에 expdp/impdp 수행가능
-remapping 기능 지원 : 스키마 변경이나 테이블 스페이스 변경, 데이터 파일 변경까지 가능합니다.
-dump 작업하면서 압축을 동시에 진행 : 용량이 큰 데이터의 경우 압축을 동시에 진행하므로 dump file 용량을 획기적으로 줄일 수 있습니다.
-아주 빨라진 속도 : 서버의 환경에 따라 다르지만 일반적으로 이전 저번의 exp/imp와 비교해 평균적으로 20배 이상의 성능향상

2. 사용 전 환경 설정하기
datapump는 exp/imp와 다르게 유틸리티가 직접 OS 파일에 I/O를 할 수 없고 오라클에 directory라는 객체를 통해서 간접으로 접근 가능합니다.
그래서 datapump를 사용하려면 미리 directory가 만들어져 있어야 하며 datapump를 수행하는 사용자는 그 directory에 접근할 수 있는 권한이 있어야합니다
이 기능을 통해 DBA는 datapump의 보안관리까지 가능하게 되었습니다.

$mkdir /data/datapump

SQL>create or replace directory datapump as ‘/data/datapump’; <–directory 생성
SQL>grant read,write on directory datapump to scott; <–해당 디렉토리에 read,write 권한 부여
SQL>grant create any directory to scott; <–scott에게 directroy 권한 부여

3. expdp 실행 모드
-full mode : full 파라미터 사용하여 database 전체 export
-schema mode : schemas 파라미터 사용하여 스키마 전체 export
-tablespace mode : tablespaces 파라미터 사용하여 tablespace 전체 export
-table mode : tables 파라미터를 사용하여 테이블을 export

#1 scott 계정의 emp,dept 테이블만 백업받기
$expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp
-dumpfile은 파일명 만 명시해줌.

#2 scott schema 전부 백업 받기
$expdp scott/tiger schemas=scott directory=datapump dumpfile=scott01.dmp

#3 DB 전체 백업 받기
$expdp system/oracle full=y directory=datapump dumpfile=full01.dmp job_name=a

#4 일시 중단 후 다시 작업하기***
$expdp systme/oracle full=y directory=datapump dumpfile=full02.dmp job_name=a <–job_name 필수 지정

CTRL+C 눌러 취소
Export>status <–상태확인
Export>stop job <–현재 job 중지

col owner_name for a10
col job_name for a10
col operation for a10
col job_mode for a10
select owner_name, job_name,operation,job_mode,state from dba_datapump_jobs;   <–datapump job의 상태확인

$expdp system/oracle attache=system.a <–중단된 작업 재시작
Export>start job
Export>status

#5 비정상적으로 종료된 job 취소하기
set line 200
col owner.object for a15
select o.status, o.object_id, o.object_type, o.owner||’.’||object_name “OWNER.OBJECT” from dba_object o, dba_datapump_jobs j where o.owner=j.owner_name and o.object_name=j.job_name and j.job_name not like ‘BIN$%’ order by 4,2;

SQL>drop table system.dp1;
SQL>drop table system.dp2;
SQL>drop table system.dp3;

select owner_name, job_name, operation,job_mode, state from dba_datapump_jobs; <– no rows, 모두 삭제됐다.

#6 여러 사용자의 테이블 한꺼번에 expdp 받기
$expdp system/oracle directory=datapump dumpfile=scott16.dmp tables=scott.emp, hr.departments

#7 병렬로 expdp 작업하기
$expdp system/oracle full=y directory=datapump dumpfile=full04.dmp job_name=a parallel=4 <–top -c로 확인

#8 
$expdp system/oracle full=y parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat filesize=100m
datadir1~4 mkdir및 driectory 추가 및 grant 되어있어야함

#9 파라미터 파일 사용해서 expdp 수행 – 여러 개의 파일로 분할 expdp
$vi expdp_pump.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y
:wq!

$expdp parfile=expdp_pump.par

4. impdp 사용하기

#1 parameter 파일 이용해서 impdp 작업하기 
$vi impdp.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append
:wq!
$impdp parfile=impdp.par

#2 impdp 병렬 작업하기
$impdp system/oracle parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat table_exists_action=append

#3 import 수행하지 않고 DDL 문장만 추출하기
$impdp system/oracle directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat

#4 작업 예상시간 추출하기
select sid,serial#,sofar,totalwork from v$session_longops where opname=’DATAPUMP’ and sofar !=totalwork;
                                                                                                               job_name 대문자 입력

#5 데이터 펌프 재 설치하기 (10.2 이상 버전)

SQL>@$ORACLE_HOME/rdbms/admin/catdph.sql
SQL>@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
SQL>@$ORACLE_HOME/rdbms/admin/catdpb.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmspump
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql



#6 설정된 directory 경로 확인하기
set line 200
col owner for a10
col directory_name for a25
col directory_path for a60
select * from dba_directories;

 


By 박용석

안녕하세요. 오라클 DBA 박용석 입니다. 궁금하신 사항이 있으시면 haisins@gmail.com 으로 문의 메일 보내주세요.

59 thoughts on “Datapump – expdp, impdp”
  1. hi!,I like your writing so so much! share we
    communicate more about your post on AOL? I need a specialist
    on this house to unravel my problem. May be that’s you! Looking forward to see you.

  2. Hey are using WordPress for your blog platform? I’m new
    to the blog world but I’m trying to get started and set up my own.
    Do you need any html coding expertise to make your own blog?
    Any help would be really appreciated!

  3. Make the most of a cloth dampened using an answer of water as well as some falls of
    meal-washing liquid to fully clean your pearls.

  4. But this could not help keep you off from
    planning a cross social relationship. on Google.
    Plus my own individual web sites are This simple design has save a huge number of individuals
    from losing a hand in these machines. Why would the operator stick their hand in such a dangerous machine?

  5. Thanks for your personal marvelous posting! I seriously enjoyed reading it, you’re a great
    author.I will be sure to bookmark your blog and definitely
    will come back down the road. I want to encourage you to definitely continue your great writing, have a nice weekend!

  6. Howdy! Someone in my Facebook group shared this site with us so I came to look it over.
    I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers!
    Superb blog and terrific design and style.

  7. Mommʏ and DaԀdy hugged thе twins beсause it was getting time to get to bed.
    ?Mommy thinks the most effective thing about God is he ցave me these two little rascals аnd
    they are the best factor in Mοmmy?ѕ world.? She said cuddling and
    tickling both boys. That was the sort of thing mommies аll the time say.
    The giggleԁ and hugged Mommy and were virtᥙally able to
    go to their bunk beds when Lee saіd.

  8. Everyone loves your site.. great colorations & theme. Would anyone style and design this website yourself as well as does you actually hire an attorney to make it happen for you personally? Plz reply as I!|m seeking to style my own, personal weblog and would wish to know where ough obtained that out of. many thanks

  9. This helps make skin look more hydrated and youthful, since dried-out skin can wrinkle
    quicker and may look older far more quickly. They break us down over time by attacking our cells, in fact it
    is only by making use of antioxidants that their damage gets repaired.
    People have theorized when we might eat bee propolis then we will eliminating the germs and things, which try to kill us.

  10. Many of these shows are situated in bigger cities like New York or Los Angeles, which means you get
    to travel at no cost if you achieve in to the finals.
    ” It was President Theodore Roosevelt who had given it the category of White House in 1901. You need a special connector typically termed as a Fire wire or best known as a possible IEEE 1394 high band connector.

  11. We set boundaries on land to create web-sites what-only, typically anyway, we do
    not use piss as markers. Most people believe cats usually do this
    with greater regularity if they are experiencing certain kinds of stress.
    Remember to ensure your cats are sterilized because this
    solves most issues of spraying.

  12. Howdy! Do you know if they make any plugins to assist with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very
    good success. If you know of any please share. Appreciate it!

  13. I’m not sure why but this weblog is loading incredibly slow for me.
    Is anyone else having this issue or is it a issue on my end?
    I’ll check back laater on and see if the problem still exists.

  14. Thank you for some other informative web site.
    Where else could I am getting that type of info written in such a perfect approach?
    I have a venture tha I am just now working on, and I have been att the look out foor such info.

  15. I do not even understand how I finished up right here, but I believed this submit used to
    bbe good. I do not rechognize who you might be but
    certainly you’re going to a well-known blogger when you aren’t already.
    Cheers!

Comments are closed.