[Oracle] Export/Import 의 기초

prana·2025년 2월 10일
1

ORACLE

목록 보기
77/96

Data Pump

  • Oracle Database 10g부터 도입된 새로운 유틸리티
  • Oracle Data Pump 기술을 사용하면 Data 및 MetaData를 데이터베이스간 빠른 속도로 이동이 가능하다.

    사용 방법

  • expdp/impdp command
  • Enterprise Manager
  • DBMS_DATAPUMP PL/SQL 패키지
  • 그 외, 외부 테이블 등 engine으로서 내부 이용
  • 관련 메뉴얼

Direct Path API 개선에 의한 처리 속도 향상

  • export: original export에 비해 2배 고속
  • import: original import에 비해 15~40배 고속

Data Pump는, 대량의 데이터를 다루는 큰 작업(Job) 용도

  • Data Pump에서는, Master Table을 생성하기 위한 Overhead에 의해 작은 작업은 늦어지지만, 대량의 데이터를 고속으로 처리하는 것은 중/대규모 작업에서 가장 큰 메리트이다.
  • Direct Path API란?
    Direct Path API는 Oracle 데이터베이스에서 디스크 I/O 작업을 최적화하고, 데이터를 효율적으로 디스크에 쓰고 읽는 방법을 제공하는 고속 데이터 전송 메커니즘

Export/Import - export 디렉토리 지정

  • Datapump에서는 client가 아닌 server에 의해 파일이 쓰여지기 때문에, 처리할 directory를 위치가 특정되어 있을 필요가 있다.
  • Dump File을 출력할 디렉토리에 대해, DB 상에 Directory Object를 생성한다.
  • 실행 시에는, Directory Object를 DIRECTORY 파라미터로 지정한다.
# 디렉토리 오브젝트 생성 
SQL> CREATE DIRECTORY DPUMP_DIR1 AS 'home/oracle/oradata/dpump_dir';
SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO scott; -- 권한 부여
COMMAND> expdp scott/tiger tables=emp,dept directory=dpump_dir1
  • 파라미터로 디렉토리를 지정하지 않은 경우, OS환경변수 DATA_PUMP_DIR, 혹은 Directory Object DATA_PUMP_DIR가 참조된다.
# 환경변수의 설정
COMMAND> set DATA_PUMP_DIR=DPUMP_DIR1; export DATA_PUMP_DIR
COMMAND> expdp scott/tiger schemas=scott

Export (Data Pump)

모드기능 설명파라미터
전체(Full)데이터베이스 전체를 내보내기FULL=y
스키마(Schema)지정된 스키마 전체를 내보내기SCHEMAS=schema[, …]
테이블(Table)지정된 테이블 전체를 내보내기TABLES=table[, …]
테이블스페이스(Tablespace)지정된 테이블스페이스 전체를 내보내기TABLESPACES=tablespace[, …]
ex1> expdp scott/tiger full=y <<------- full mode
ex2> expdp scott/tiger dumpfile=exp.dmp tables=emp,dept <<------- table모드

Filter 처리를 수행

INCLUDE/EXCLUDE

  • 필터 처리를 수행하고, export의 대상이 되는 object를 세밀하게 설정이 가능하다.

  • INCLUDE/EXCLUDE 파라미터는 동시에 지정할 수 없다.

  • export 대상 object를 include 파라미터로 지정한다.

COMMAND> expdp scott/tiger include=table
COMMAND> expdp scott/tiger include=index
  • export 대상으로부터 제외할 오브젝트를 EXCLUDE 파라미터로 지정한다.
  • COMMAND>expdp scott/tiger exclude=index:"LIKE 'EMP%'"

공백 등을 구분자로 하지 않도록 "이 필요하지만, OS에 따라서 escape 문자가 필요하게 된다. 따라서 통상적으론 parameter를 기술한 파일에서 읽는 것이 편리하다.

Flashback Mode를 지정한다

  • 지정된 특정 시점 또는 특정 SCN에서의 데이터 상태를 유지한 채로 내보내는 기능
  • 일관된 데이터 백업을 생성할 때 유용

FLASHBACK_TIME: 특정 시점 기준

  • 특정 날짜 및 시간을 기준으로 데이터 내보내기
  • 이스케이프(") 문자가 필요하므로, 파라미터 파일에서 설정하는 것이 권장됨
COMMAND > cat parfile.txt
FLASHBACK_TIME="TO_TIMESTAMP('2004/03/20 10:00','YYYY/MM/DD HH:MI')"

COMMAND > expdp scott/tiger parfile=parfile.txt
  • parfile.txt 파일에 FLASHBACK_TIME 설정
  • 2004년 3월 20일 오전 10시 기준으로 데이터 내보내기
  • 이후 expdp 실행 시 해당 시점의 데이터를 백업

FLASHBACK_SCN 사용: SCN 기준

  • 특정 SCN에서의 데이터 상태를 유지한 채 내보내기
  • SCN은 Oracle에서 트랜잭션을 추적하는 고유한 번호
  • SCN을 알고 있다면 더 정확한 시점의 데이터를 내보낼 수 있다.
  • 특정 트랜잭션이 완료된 직후의 상태로 데이터를 덤프할 때 유용하다.
COMMAND > expdp scott/tiger flashback_scn=364909
---> scn 번호 기준으로 데이터를 내보내는 명령어

Parallel Export를 지정한다

PARALLEL

  • Export 처리를 Parallel로 수행하는 것이 가능하다.
  • 병렬도를 PARALLEL 파라미터로 지정한다.
  • 출력할 디렉토리를 각기 지정하는 것도 가능하다.
# 병렬도의 지정
COMMAND > expdp scott/tiger parallel=3 dumpfile=dpump_dir1:expdat%U.dmp,
dpump_dir2%U.dmp 
--- %U(치환변수)를 지정하지 않는 경우, dumpfile 파일 수는 parallel 수에 맞출 필요가 있다. 
--- 지정되어 있는 경우, 지정된 파일이 round robin으로 사용된다.

COMMAND > ls –lR
dpump_dir1: --- 생성된 dump file set 확인
…….expdat01.dmp
…….expdat02.dmp
dpump_dir2:
…….expdat01.dmp

Dump File Size를 예측한다.

ESTIMATE

  • 덤프파일을 생성하지 않고, 생성되는 덤프 파일 사이즈를 예상하는 것이 가능하다.
  • 추측모드?를 ESTIMATE 파라미터로 지정한다.
  • BLOCKS 모드 --- 블록 사이즈에 오브젝트의 블록 수를 곱해 예상한다. (정확도는 높지 않음)
  • STATISTICS 모드 --- 기존의 통계정보를 기반으로 크기를 추정한다. DBMS_STATS에서 수집한 정보를 활용한다. 일반적으로 STATISTICS 방식이 더 정확하다.
# 덤프 파일을 생성하지 않고 크기만 출력: ESTIMATE_ONLY=y를 지정한다.
COMMAND> expdp scott/tiger tables=emp,dept estimate=blocks
estimate_only=y

IMPDP

  • expdp에서 사용할 수 있던 파라미터와 대부분 이용할 수 있다.
  • IMPDP에서 파라미터를 지정함에 따라, EXPDP에서 생성한 덤프 파일로부터 더욱이 대상 데이터를 (필요한 것들을 조합해) IMPORT 가능하다.
COMMAND> impdp scott/tiger tables=emp,dept -- dumpfile로부터 emp 테이블과 dept 테이블만을 import
COMMAND> impdp scott/tiger include=index -- dumpfile로부터 index만 import

Network link를 사용한다.

  • 10g 이후 버전에서만 수행 가능 (network link import)
  • 데이터베이스 링크를 사용해서, 원격 데이터베이스로부터 직접 로컬 데이터베이스에 import를 수행할 수 있다.

참고: export의 네트워크 링크 지정은, 원격 데이터베이스의 데이터를 로컬로 export하는 기능이다. import 경우에는 직접 로컬 데이터베이스에 쓰므로, dumpfile을 생성하지 않는다.

Export (네트워크 링크로 데이터 내보내기)

  • expdp 명령에서 NETWORK_LINK를 사용하면 원격 데이터베이스의 데이터를 로컬 시스템에 export(내보내기)하는 작업을 수행할 수 있다.

  • 예를 들어, 원격 데이터베이스에서 데이터를 추출하여 로컬 데이터베이스의 dump 파일로 저장하는 방식

  • 이때 dump 파일이 로컬에 저장되며, 네트워크를 통해 원격 데이터베이스와 연결하여 데이터를 가져온다.

expdp scott/tiger network_link=remote_db_link directory=DATA_PUMP_DIR dumpfile=remote_data.dmp

  • 위의 예시는 원격 데이터베이스와 연결된 remote_db_link를 사용하여,
    원격 데이터베이스에서 데이터를 로컬 dump 파일에 내보내는(export) 작업을 수행하는 명령어

Import (네트워크 링크로 데이터 가져오기)

  • impdp 명령에서 NETWORK_LINK를 사용하면 원격 데이터베이스의 데이터를 직접 로컬 데이터베이스로 가져오는(import) 작업을 할 수 있다. 이때 dump 파일을 생성하지 않는다.

  • 원격 데이터베이스에서 직접 데이터를 로컬 데이터베이스로 삽입하므로, 중간에 dump 파일을 생성할 필요가 없습니다.

    impdp scott/tiger network_link=remote_db_link directory=DATA_PUMP_DIR tables=emp

  • 위의 예시는 원격 데이터베이스에서 emp 테이블을 로컬 데이터베이스로 직접 가져오는(import) 작업을 수행하는 명령어

핵심 차이점

  • Export: NETWORK_LINK를 사용하여 원격 데이터베이스에서 데이터를 로컬 시스템에 dump 파일로 내보낼 수 있다.
    • 즉, 원격 데이터베이스의 데이터를 로컬에 덤프 파일로 저장한다.
  • Import: NETWORK_LINK를 사용하여 원격 데이터베이스에서 데이터를 로컬 데이터베이스로 바로 삽입한다.
    • 이때 덤프 파일이 생성되지 않으며, 네트워크 링크를 통해 원격 데이터베이스에서 직접 데이터를 가져와 로컬 데이터베이스에 기록한다.

여러 개의 테이블을 export하여 데이터만 import하고 싶을 때

  • 여러 개의 테이블을 Export해서 데이터만을 Import하고 싶은데, FK가 많이 있어서 정합성 제약에 위반하게 돼.....
  • 다음과 같이 관련된 참조정합성(pk-fk)을 disable하고, import 후에 참조정합성제약을 enable로 하면, error를 해소할 수 있다.

외래 키 제약조건을 일시적으로 비활성화 한 후, 데이터만 import한 후 다시 활성화한다.

COMMAND> expdp system/manager ;
SQL> alter table emp disable constraint emp_no_fk ; -- 관련된 모든 외래 키 제약조건을 disable로 설정
COMMAND> impdp system/manager ;
SQL> alter table emp enable constraints emp_no_fk ; -- 관련된 모든 외래 키 제약조건을 enable로 설정

Tip

Export 덤프로부터 일부 데이터를 제외하는 방법

  • 실수로 일부 데이터를 delete해서, commit 해버렸어!
    • export의 dumpfile로부터 추출하고 싶어.. (제외하고 싶어)
command> impdp system/manager remap_schema=oradirect:dummy
SQL> insert into oradirect.emp select * from dummy.emp where emp_id=100;
SQL> drop table dummy.emp
  • 주의: export한 시점의 데이터와 삭제한 데이터가 일치하는지는 불명확하기 때문에, 이 예시의 경우 emp_id=100의 데이터에 관해 export할 때랑 실수해서 삭제한 시점이 동일했다는 것을 전제로 한다.

Export File Size을 지정하는 방법

FILESIZE

  • 기본값: 데이터는 최대 사이즈에 도달할 때까지 1개 파일로 쓰여진다.

  • 용도: FILESIZE 파라미터값(BYTE제한)을 지정하면, EXPORT utility 에 의해 각각의 덤프파일에 지정한 byte값만을 쓰게 된다.

  • 예) exp scott/tiger TABLES=emp FILESIZE=2048👉KB, MB, GB를 붙여 지정할 수도 있다.

  • FILESIZE=5MB를 지정한 경우

WHERE절을 지정하여 EXPORT 범위를 제한하는 방법

QUERY

  • 기본값: 없음
  • 용도: export 실행 시에 일련의 테이블로부터 행의 subset을 선택할 수 있도록 한다.
    • query 파라미터 값은 select문의 where절을 포함하는 문자열이다.

예: 직종이 SALESMAN, 급여가 1600보다 적은 종업원만을 export할 경우

exp scott/tiger TABLES=emp QUERY="WHERE job='SALESMAN' and sal <1600"

SQLFILE

  • 기본값: 없음
  • 용도: impdp가 실행할 ddl(테이블 생성, 인덱스 생성 등)의 SQL문을 파일로 저장
  • 즉, 실제 import를 수행하지 않고, 어떤 SQL문이 실행될지를 미리 확인할 수 있다.
  • 사용 예: impdp scott/tiger TABLES=(wendy.emp) 👉SQLFILE='index.txt'

주의

  • password는 SQL File에 포함되지 않는 점에 주의할 것
  • 예를 들면, (CREATE USER 등) 실행한 ddl에 connect 문이 포함되어 있는 경우,
  • 그 문장은 주석처리 되어, schema 명만 표시된다.

TEST_TABLE테이블을 import할 때 SQLFILE을 사용하는 경우

  • impdp oradirect/oradirect dumpfile=test_table.dmp tables=test_table 👉sqlfile=sqlfile.txt
  • 실제 데이터를 import하지 않고, 테이블을 생성하는 SQL문을 미리 확인 가능하다.

Data Pump 압축

  • COMPRESSION={ALL|DATA_ONLY|METADATA_ONLY|NONE}
  • NONE: export 전체에 대해 압축이 무효
  • METADATA_ONLY(default): 전체의 메타데이터가 압축 형식으로 덤프 파일로 생성

여기서부터는 Advanced Compression 필수

  • DATA_ONLY: 모든 데이터가 압축형태로 데이터파일로 생성
  • ALL: 데이터, 메타데이터가 같이 압축

스키마를 지정하고, 메타데이터, 데이터와 같이 압축해서 덤프 파일 scott.dmp로 export

  • expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp COMPRESSION=ALL SCHEMAS=scott

Data Pump 암호화

  • ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY| NONE}
  • ALL: 모든 데이터 및 메타데이터에 대해 암호화가 유효
  • DATA_ONLY: 데이터만
  • ENCRYPTED_COLUMNS_ONLY: 암호화된 열만 암호화됨
  • METADATA_ONLY: 메타데이터만이 암호화됨
  • NONE: 데이터는 암호화되지 않음

Dump File에서 데이터만 암호화되는 export 구문

  • $ expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott_enc.dmp SCHEMAS=scott ENCRYPTION=data_only ENCRYPTION_PASSWORD=tiger

Direct Path

profile
prana
이전 포스트

[Oracle] asmlib

다음 포스트

[Oracle] 성능 분석

0개의 댓글