[논리적 백업/복구] IMPORT/EXPORT & DATAPUMP

특징 비교

• EXPORT
  • temporary table space 공간 사용
• IMPORT
  • redolog, undosegmet 사용 (대량작업시 undotablespace 확보 필요)
  • DBA로 EXPORT 받은 dmp는 DBA로 IMPORT 가능
• DATAPUMP
  • export/import와 다르게 직접 OS파일에 IO를 할 수 없음
  • 오라클 내 directory라는 객체를 통해 간접접근 (사전생성 필요)
  • EXPORT/IMPORT 대비 속도가 빠르고 높은 수준의 작업시간 예측 가능
• 공통
  • 제약조건에 의한 에러 발생 가능  (타겟에 동일 테이블이나 데이터가 존재시 추가하는 형태)

 

 스크립트 샘플

• EXPORT

exp 계정/PW file=/backup/EHR_full_exp_2.dmp log=/backup/log/EHR_full_exp_2.log FULL=Y direct=y
exp 계정/PW file=/backup/EHR_full_exp_2.dmp query=\”where ename like\’F:\’\” tables=emp buffer=1024000 
exp 계정/PW file=/backup/EHR_full_exp_2.dmp  owner=(scott,jyoh)

• IMPORT

  imp 계정/PW file =/backup/EHR_full_exp_2.dmp buffer=15847680 commit=Y IGNORE=Y FROMUSER=jyoh TOUSER=jyoh index=no row=no
   
  imp 계정/PW file =/backup/EHR_full_exp_2.dmp log=/backup/log/EHR_full_imp_2.log full=Y show=y FROMUSER=jyoh TOUSER=jyoh

   
• DATAPUMP

expdp dba_jyoh/'AAaa!!11' tables=emp,dept directory=datapump job_name=test1 dumpfile=emp_dept.dmp
expdp dba_jyoh/'AAaa!!11' schemas=jyoh directory=datapump dumpfile=sch_jyoh.dmp
expdp dba_jyoh/'AAaa!!11' full=y directory=datapump dumpfile=sch_jyoh.dmp job_name=a parallel=4
 
impdp dba_jyoh/'AAaa!!11' paraller=4 dumpfile=DATADIR1:full1%U.dat,DATADIR2:full2%U.dat,Table_exists_action=append
impdp dba_jyoh/'AAaa!!11' directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat
 
--작업예상시간 추출
select sid,serial#,sofar,totalwork from v$session_longops where opname in ('TEST1','A') and sofar!=totalwork;
 
--디렉토리 생성 및 권한 설정
create or replace directory datapump as '/data/datapump';
grant read,write on directory datapump to dba_jyoh;
grant create any directory to dba_jyoh;