Datapump – expdp, impdp


 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;

 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

Datapump – expdp, impdp”의 59개의 댓글

  • 2018-07-19 10:11 오후
    Permalink

    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.

  • 2018-07-20 12:41 오전
    Permalink

    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!

  • 2018-07-20 10:05 오후
    Permalink

    Thanks. Argh! I want more designers made bigger earrings. And as
    soon as harvested, gold is among the most versatile metals on earth.

  • 2018-07-21 3:09 오전
    Permalink

    Additionally, the type of changes needed in loosening
    too-tight clip on earrings relies on the type of attachment on the earring.

  • 2018-07-21 3:12 오전
    Permalink

    The form of our fingers can be a factor as some individuals have extra prominent knuckles.
    The official Prague beer!

  • 2018-07-21 2:14 오후
    Permalink

    Properly, aren’t these just cute as a button! Enamel bangle bracelets and
    brooches are created by esteemed designers. Really nice ruby and gold
    necklace.

  • 2018-07-21 2:15 오후
    Permalink

    These 5 things will definitely improve your brain.

  • 2018-07-21 2:20 오후
    Permalink

    Albert confirmed some discomfort. It’s a leafy and inexperienced space of Central London and is picturesque in the summertime time.

  • 2018-07-21 2:22 오후
    Permalink

    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.

  • 2018-07-21 2:24 오후
    Permalink

    Decision making on your self is wonderful as a result of it
    suits your wants. Choices made by others for you aren’t at
    all times in your finest pursuits.

  • 2018-07-21 3:13 오후
    Permalink

    Here on my Handmade Beaded Jewelry web site I supply individuals unique and one-of-a-type necklace and earring units!

  • 2018-07-21 3:18 오후
    Permalink

    The woodland is generally coniferous as it is a commercial plantation, however this area is combined, so
    attracts a wide variety of birds.

  • 2018-07-21 3:19 오후
    Permalink

    Maintaining prior to the war on Search Engine
    Optimization search engine marketing, Googles Rules and what realy works getting greater regarding the machines.

  • 2018-07-21 3:35 오후
    Permalink

    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?

  • 2018-07-21 4:17 오후
    Permalink

    To those who live near the Seaside Line in Yokohama, Kangawa-ken, check
    out ideas to be more spendthrift.

  • 2018-07-21 4:38 오후
    Permalink

    Wow, I didn’t know there’re a lot of benefits to playing stupid!

  • 2018-07-21 5:18 오후
    Permalink

    The fact of matter is, this documentary ended up being just average PBS quality, and it
    was not stellar.

  • 2018-07-21 6:10 오후
    Permalink

    We use Grapeshot as a contextual targeting technology, enabling united states generate custom sets
    of stories outside out of our usual site navigation.

  • 2018-07-21 11:44 오후
    Permalink

    , the place the place Jesus was crucified. Take two earring mounts with as massive mounting pads as possible and select two items of creste di galli
    pasta.

  • 2018-07-21 11:53 오후
    Permalink

    If you are a picky buyer, you’ll certainly love the offerings acquired.
    Now I’m going to let you in on what I have found.

  • 2018-07-22 12:06 오전
    Permalink

    If you’re beginning to study jewellery making, you would possibly postpone your start date as you consider you need
    a whole lot…

  • 2018-07-22 12:48 오전
    Permalink

    As an example, if the partner does not wish for one to
    pry to their past, cannot get behind their straight back and hire a detective to sniff around.

  • 2018-07-22 1:33 오전
    Permalink

    The simplest and most controllable heat supply to work
    with is a butane torch, which is similar to the kind utilized in kitchens.

  • 2018-07-22 8:51 오전
    Permalink

    Ahaa, its nice discussion concerning this paragraph here at this website, I have read all that, so now me also commenting here.

  • 2018-07-22 10:41 오전
    Permalink

    An eight hour change can become a nine hour change as most side
    work wasn’t permitted throughout the shift like
    rolling silverware.

  • 2018-07-22 10:46 오전
    Permalink

    I don’t understand… easily had been being chased I would personallyn’t lock myself in a room upstairs, i might run outside!

  • 2018-07-22 10:54 오전
    Permalink

    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!

  • 2018-07-22 10:56 오전
    Permalink

    Whereas on retail version you will have the commentary individuals
    are familiar with getting on the DVD’s and a host of other features.

  • 2018-07-22 11:03 오전
    Permalink

    It had been like the claims he made had been true
    simply because he said them.

  • 2018-07-22 11:26 오전
    Permalink

    Online is enjoyable, addicting and packed with things
    you didn’t know existed!

  • 2018-07-31 4:48 오후
    Permalink

    What’s up, for all time i used to check web site posts here in the early hours in the daylight,
    as i like to learn more and more.

  • 2018-08-03 7:09 오후
    Permalink

    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.

  • 2018-08-04 7:09 오전
    Permalink

    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.

  • 2018-08-06 8:45 오전
    Permalink

    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

  • 2018-08-14 3:49 오후
    Permalink

    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.

  • 2018-08-15 2:13 오전
    Permalink

    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.

  • 2018-08-15 5:05 오전
    Permalink

    I’m gone to say to my little brother, that he should also go to see this web site on regular basis
    to get updated from most up-to-date news.

  • 2018-08-15 11:12 오전
    Permalink

    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.

  • 2018-08-16 5:34 오전
    Permalink

    I am delighted that I detected this website, exactly the right info that
    I was looking for!

  • 2018-08-19 3:20 오후
    Permalink

    This is a good tip especially to those fresh to the blogosphere.
    Brief but very accurate information… Appreciate your sharing this
    one. A must read post!

  • 2018-08-20 7:48 오후
    Permalink

    This excellent website truly has all of the info I needed about this subject
    and didn’t know who to ask.

  • 2018-08-22 1:56 오후
    Permalink

    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!

  • 2018-08-22 10:38 오후
    Permalink

    I am really grateful to the holder of this web page who has shared this wonderful piece of writing at at this time.

  • 2018-08-22 10:46 오후
    Permalink

    Wow, that’s what I was exploring for, what a material! present here
    at this webpage, thanks admin of this website.

  • 2018-08-24 4:37 오전
    Permalink

    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.

  • 2018-08-24 11:52 오전
    Permalink

    Hurrah! After all I got a blog from where I be capable of truly get valuable information concerning
    my study and knowledge.

  • 2018-08-24 6:11 오후
    Permalink

    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.

  • 2018-08-25 6:46 오전
    Permalink

    Amazing! Its genuinely amazing paragraph, I have got much
    clear idea regarding from this piece of writing.

  • 2018-08-25 1:29 오후
    Permalink

    Hi to every body, it’s my first pay a quick visit of
    this blog; this web site carries awesome and really good material for readers.

  • 2018-08-27 12:40 오전
    Permalink

    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!

  • 2018-08-27 1:11 오전
    Permalink

    Hi there, I would like to subscribe for this
    weblog to obtain most recent updates, therefore where can i do it please help.

댓글이 닫혀있습니다.