━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Datapump
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- 10g 이전 버전에서 export/import는 열악한 속도를 불구하고 마이그레이션을 위해 사용한유틸리티였다.
(일반적으로 insert into 명령을 사용해 대상 테이블에 삽입하기 때문에 import process는 속도가 느렸다.)
- Datapump는 Oracle 10g 유틸리티로 데이터의 이동 기능이 크게 향상되었다.
- Datapump는 일반적인 SQL을 사용하는 대신 API로 load, unload를 한다.
- export/import와 Datapump는 서로 호환되지 않는다.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Datapum export/import의 장점━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1) JOB 컨트롤 가능
- interactive mode로 작업을 통제할수 있다.
- 작업의 중단 및 재시작 할수 있으며 동적으로 dump file을 할당 가능.
- 에러 발생후 작업은 멈추지만 언제든 수정하여 재수행 할수있음.
2) 병렬수행 지원
- parallel 파라메터를 이용하여 datapump작업의 프로세스를 병렬화 할수있음.
- 병렬된 프로세스는 여러개의 데이터 파일에 각각 데이터를 쓰거나
- 여러개의 데이터 파일로부터 데이터를 읽어 데이터베이스에 저장 가능하다.
- parallel 수행이 가능 함으로 더욱 속도가 향상 됨.
3) 작업에 필요한 공간 예측 가능
- estimate 파라메터를 이용하여 작업에 필요한 디스크 공간 예측 가능
4) 원격지 수행
- DB link를 사용하여 원격지에 데이터를 import/export 할수있다.
5) Remapping
- user 스키마, tablespace, datafile 등의 정보를 import/export시 변경 할수 있다.
- 이 기능은 데이터 마이그레이션 시 보다 많은 유연성을 제공한다.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2. data access methods (oracle이 자동으로 판단하여 수행하는 mode)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1) direct-path
메모리를 거치지 않고 파일에 직접 쓰는 방법.
- 메모리 사용이 적고 속도가 빠르며, 데이터 변환에 시간이 걸리지 않는다.
direct-path가 되지 않는 경우에는??
- cluster table인 경우
- table에 active된 triger가 존재 할 경우
- 글로벌 인덱스를 가진 테이블이 하나의 파티션에 존재 할 경우
- LOB 칼럼에 있는 domain index
- insert 모드에서 fine-grained access control이 enable인 경우
- BFILE을 가진 테이블인 경우
2) External table
메타 데이터를 데이터베이스에 저장하고 데이터는 파일시스템에 존재함.
- 대용량 데이터를 export/import 할때 사용함.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
3. Datapump의 사전 설정
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- Directory Object 생성
- 읽기 권한과 쓰기 권한이 필요
- 다양한 옵션과 datapump의 모든 기능을 사용 하려면 EXP_FULL_DATABASE, IMP_FULL_DATABASE role을 부여함.
1) 사용자 생성
create user [ user name ] identified by [ password ]
default tablespace [ tablespace name ]
temporary tablespace temp;
2) 권한 부여
grant connect, resource to [ user name ]
3) 모든 테이블에 대한 select 권한 부여
grant select any table to [ user name ]
4) EXP_FULL_DATABASE, IMP_FULL_DATABASE 권한 부여
grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to [ user name ]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
4. Datapump 파일 오브젝트
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Datapump가 사용하는 파일
- dump file : table로부터 data 또는 meta-data를 load하여 저장된 파일
- log file : datapump 작업 중에 발생하는 메세지나 결과를 기록하는 파일
- SQL file : datapump는 sqlfile이라는 옵션을 사용한다.
이 옵션을 사용할 경우 datapump import 작업이 수행되는 동안
DDL 문을 수행할 수 있게 해주는 옵션이다.
2. Datapump directory object
Datapump는 디렉토리 오브젝트를 참조하여 dump 파일을 쓰게 된다.
Datapump사용시 디렉토리에 대한 사용자의 권한까지 설정할수있다.
1 ) 사용중인 디렉토리 오브젝트의 조회
select * from dba_directories;
2 ) 디렉토리 오브젝트 추가
create directory [ directory_name ] as '/[ 경로명 ]';
3 ) 디렉토리 오브젝트에 대한 권한 설정
grant read,write on directory [ directory_name ] to [ user name ]
// 해당 유저에 대해 해당 디렉토리에 대한 쓰기 및 읽기 권한을 할당.
export 받을때 해당 유저는 해당 경로의 덤프파일에 export된 덤프 파일을 저장.
ex) expdp [ user name ]/[ password ] directory=[ directory_name ] tables=[ table_name ] dumpfile=[ 생성할 덤프 파일명 ]
4 ) default directory 설정
datapump를 사용할대마다 디렉토리 지정을 하지 않고 묵시적으로 사용하려면
운영체제 환경 변수에 DATA_DUMP_DIR을 만들고 그 값으로 디렉토리 오브젝트 명을 입력하면 됨.
$ export DATA_DUMP_DIR [ directory_name ]
설정 했다면 아래와 같이 사용 가능
$ expdp [ user name ]/[ password ] dumpfile=[ dump 파일명 ] tables=[ table_name ]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5. datapump 사용 법
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- commend line
$ expdp [ user name ]/[ password ] directory=[ directory_name ] tables=[ table_name ] dumpfile=[ 생성할 덤프 파일명 ]
- parameter file을 이용 (확장자 상관 없음)
1) parameter file 생성 내용
schemas=[ user_name ]
directory=[ directory_name ]
dumpfile=[ 생성 될 dumpfile 명 ]
logfile=[ 생성 될 logfile 명 ]
2) commend line에서 사용
$ expdp [ user_name ]/[ password ] parfile=[ parameter file ]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
6. Datapump Export Parameter
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- 파일 및 디렉토리 관련 파라메터
1) DIRECTORY : 디렉토리 오브젝트를 참조하는 DIRECTORY 파라메터를 이용해
덤프 파일의 위치 및 로그 파일의 위치를 지정 가능
2) DUMPFILE : EXPORT 받아 파일 시스템에 저장될 덤프파일의 이름을 지정하는 파라메터
- %U 를 사용해서 여러 개의 덤프 파일을 구분 가능
DUMPFILE=AAA_%U.DMP로 파라미터를 정의.
%U 부분이 자동 증가하여 파일을 구분하게 해줌.
%U의 범위는 01~99까지
- ','를 이용하여 여러개의 파일명을 구분
DUMPFILE=AAA_01.DMP, AAA_02.DMP, AAA_03.DMP 과 같이 정의 가능
만약 DUMPFILE 파라메터를 지정하지 않으면 EXPDAT.DMP 라는 파일명으로 ORACLE이 자동 선언 함.
3) FILESIZE : EXPORT 받는 1개 파일의 최대 SIZE를 지정 하는 파라메터.
FILESIZE=N [ BYTES | KILOBYTES | MEGABYTES | GIGABYTES ]
4) PARFILE : 파일에 파라메터를 저장하고 DATAPUMP를 이용 할때 마다 참조 가능.
PARFILE=[ FILENAME.PAR ]
5) LOGFILE AND NOLOGFILE : 로그파일명을 지정하는 파라메터.
LOGFILE=[ LOGFILE_NAME ]
지정 안할시 EXPORT.LOG라는 파일명으로 로그가 남음
로그 파일을 남기고 싶지 않다면 NOLOGFILE 파라메터 사용
6) COMPRESSION : 사용하지 않을 경우 EXPORT 시에 메타데이터를 압축하여 파일에 저장함.
COMPRESSION 파라메터는 METADATA_ONLY 와 NONE 두개의 옵션이 있음
$ EXPDP ~~~~~~~~~~~~~~~ COMPTRESSION=[ NONE, METADATA_ONLY ]
- EXPORT 모드 관련 파라메터
1) full export mode
- full 파라메터를 사용하며 db전체를 export 할수 있다.
- EXP_FULL_DATABASE role이 사용자에게 부여 되어 있어야 한다.
2) schema mode
- schemas 파라메터를 사용하며 하나의 유저가 소유하고 있는 데이터 및 오브젝트 전체를 export 할수 있다.
3) tablespace mode
- tablespace 파라메터를 사용하며 하나의 tablespace에 속한 모든 table을 export 할수있다.
- TRANSPORT_TABLESPACES 파라메터를 이용하면 tablespace의 meta-data까지
export 되어 다른 서버에 import 하게 되면 tablespace 및 table이 자동 생성된다.
4) table mode
- tables 파라메터를 사용하며 하나 이상의 테이블을 export 할때 사용한다.
5) TRANSPORT_FULL_CHECK
- EXPORT 시 TABLESPACE내에 존재하는 테이블과 인덱스의 의존성 검사 유무 설정
- Y 또는 N 만을 허용하는 파라메터
- TRANSPORT_TABLESPACE와 같이 사용 됨.
1. Y 할 경우 반드시 테이블과 인덱스가 같은 TABLESPACE에 존재 해야함.
2. N 일 경우 TABLESPACE 내에 테이블만 있을시 작업은 성공하나,
인덱스만 있고 테이블이 없다면 작업은 실패함.
- EXPORT 필터링 관련 파라메터
: 어떤 데이터를 EXPORT 된 파일에 포함 시킬지 결정 하는 파라메터.
5-1) CONTENT : 3개의 옵션을 가질 수 있으며 옵션들은 다음과 같음.
$ EXPDP ~~~~~~~~~~~~ CONTENT=[ ALL | DATA_ONLY | METADATA_ONLY ]
- ALL : 테이블과 메터데이터를 포함한 모든것을 포함시키는 옵션
- DATA_ONLY : 테이블 데이터만 포함 시키겠다는 옵션
- METADATA_ONLY : 메타 데이터만 포함 시키겠다는 옵션.
이경우 EXPORT 된 파일을 이용해 다른 DB에 IMPORT 할 경우
테이블 구조만 생성되게 함.
5-2) EXCLUDE AND INCLUDE : 원하는 오브젝트를 선택해서 받을수 있음. 조건식으로 사용 가능
$ [ exclude | include ]=object_name:조건 형식
EXCLUDE : 해당 오브젝트를 제외한것들만.
INCLUDE : 해당 오브젝트만.
예1) SCOTT 유저와 관련된 모든것을 EXPORT 하고 싶다. 근데 'BONUS' 테이블은 제외하고 싶다.
$ EXPDP ~~~~~~~~~~~~~~~~~~ schemas=scott exclude=TABLE:"\='BONUS'"
예2) SCOTT 유저와 관련된 모든것을 EXPORT 하고 싶다. 근데 'EMP' 테이블의 인덱스는 싫다.
$ EXPDP ~~~~~~~~~~~~~~~~~~ schemas=scott exclude=INDEX:\"='EMP%'\"
예3) SCOTT 유저와 관련된 모든것을 EXPORT 하고 싶다. 근데 'dept'로 시작하는 인덱스는 제외하고 싶다.
$ EXPDP ~~~~~~~~~~~~~~~~~~ schemas=scott exclude=INDEX:"like 'dept%'"
예4) SCOTT 유저의 'DEPT'와 'EMP' 테이블과 인덱스를 EXPORT 하고 싶다.
$ EXPDP ~~~~~~~~~~~~~~~~~~ schemas=scott INCLUDE=INDEX,TABLE:"IN ('DEPT','EMP')"
예5) SCOTT 유저와 관련된 모든것을 EXPORT 하고 싶다. 근데 FUNCTION은 제외하고 싶다.
$ EXPDP ~~~~~~~~~~~~~~~~~~ schemas=scott exclude=FUNCTION
5-3) QUERY : 테이블 내 데이터중 조건에 만족하는 데이터만 EXPORT 하는 파라메터
예 ) $ expdp ~~~~~~~~~~~~ QUERY=SCHEMA.TABLE:" where sal>1200 "
5-4) SAMPLE : 테이블의 데이터를 EXPORT 할때 %를 정하여
지정된 %만큼 데이터를 샘플링 해서 뽑을때 사용하는 옵션.
입력 가능한 퍼센트 범위 : 0.000001 ~ 100
예 ) $ expdp ~~~~~~~~~~~~~~~~~~~~~ SAMPLE=scott.emp:20
- network_link 파라메터
: 원격지 db에 있는 데이터에 접근해 로컬 db에 export된 덤프 파일을 저장할때 사용
DB_LINK를 통해 export 할수있으며 NETWORK_LINK 파라메터를 사용하려면
원격지 DB의 테이블에 대한 DB_LINK를 만들어 놓아야한다.
예 ) $ expdp ~~~~~~~~~~~~~~~~~~~ NETWORK_LINK[ EMP@orcl ]~~~~~~
- Password 관련 파라메터
: export 데이터중 ENCRYPTION_PASSWORD 파라메터를 이용하여 export시에 암호를 설정 할수있다.
암호 설정 후에 import시 password를 물어보게 된다.
## 전자 지갑 설정을 해줘야 가능하다.
예 ) $ expdp ~~~~~~~~~~~~~~~~~ ENCRYPTION_PASSWORD=[ PASSWORD ]
- JOB 관련 파라메터
1 ) JOB : JOB 파라메터에 주어진 이름으로 등록한다. 작업 마스터 테이블에 작업명이 등록되고
해당 작업의 정보를 JOB 파라메터에 등록된 이름으로 조회 할수있다.
등록을 하지 않으면 오라클에서 자동할당 한다.
2 ) STATUS : export 작업시 작업의 진행상태를 볼때 사용함.
STATUS=10 으로 설정하면 10초 간격으로 갱신하여 보여줌(기본값은 0)
3 ) FLASHBACK_SCN : 파라메터에 설정된 SCN 기준, 이전까지의 상태를 받게 됨
예 ) expdp ~~~~~~~~~~~~~~~~~~~ FLASHBACK_SCN=333444
4 ) FLASHBACK_TIME : SCN 번호 대신에 TIMESTAMP 형식의 값을 가지며 TO_TIMESTAMP 함수를 사용하여 설정함.
파라메터에 지정된 시간까지의 변경사항만 EXPORT 함.
5 ) PARALLEL : 프로세스 숫자를 늘려 수행하여 작업의 속도 향상. 기본 값은 1.
PARALLEL 지정 숫자만큼 dumpfile을 지정 해주어야 하며
%U 사용시 지정된 숫자 만큼 자동으로 dumpfile을 생성함.
예 ) expdp ~~~~~~~~~~~~~~~ dumpfile=export%u.dmp PARALLEL=3
6 ) ATTACH : interactive mode로 들어갈수 있게 함.
interactive mode 진입은 2가지 방법이 있음
관련 parameter : dba_datapump_jobs, user_datapump_jobs, DBA_DATAPUMP_SESSIONS
예1 ) expdp ~~~~~~~~~~~~~~~~~~~~~~~~
작업 로그가 쭉 나올때 Crtl + C 를 눌러주면
export> _ 처럼 프롬프트 상태로 진입.
예2 ) expdp ~~~~~~~~~~~~ ATTACH=scott.JOB
원하는 작업의 interactive mode로 들어감.
위와 같이 현재 수행중인 작업을 ineractive mode로 진입해 컨트롤 할수있음
=======================================================================
명령어 | 설명
=======================================================================
ADD_FILE | 덤프파일을 추가
CONTINUE_CLIENT | interactive mode에서 Logging Mode로 전환
EXIT_CLIENT | Client Session을 종료후 JOB 상태에서 벗어남
HELP | interactive mode 도움말
KILL_JOB | 작업을 삭제
PARALLEL | 현재 수행중인 작업의 프로세스 개수를 조정
START_JOB | 실패 또는 중단된 작업을 재 시작
STATUS | 현재 작업상태를 모니터링 갱신 시간 설정
STOP_JOB | 작업을 중단하고 Client 종료
=======================================================================
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
7. Datapump Import mode
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- dumpfile을 DB내의 테이블로 옮기는 작업
impdp 명령어로 사용하며, export 작업과 마찬가지로 커맨드라인, 파라메터파일, interactive mode로 사용
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
8. Datapump Import Parameter
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1 ) 파일 및 디렉토리 관련 파라메터
impdp [ username/password ] DIRECTORY=[ directory_name ] DUMPFILE=[ dumpfile_name ] SCHEMAS=[ user_name ]
2 ) 필터링 관련 파라메터
필터링 관련 파라메터는 CONTENT, INCLUDE, EXCLUDE, TABLE_EXISTS_ACTION 파라메터가 있다.
이중 TABLE_EXISTS_ACTION 파라메터만 Import 작업시에 사용할수있다.
- CONTENT, INCLUDE, EXCLUDE 는 생략.. ( export의 CONTENT, INCLUDE, EXCLUDE 참고할것 )
- TABLE_EXISTS_ACTION : Import 시 table안의 data는 다르지만 테이블 이름이 같은 테이블이 존재할수있다.
이러한 경우에 사용 되는 파라메터이며
SKIP, APPEND, TRUNCATE, REPLACE의 값을 가질수 있다.
예 ) impdp ~~~~~ TABLE_EXISTS_ACTION=[ SKIP, APPEND, TRUNCATE, REPLACE ]
- SKIP : 같은 테이블을 만나면 지나치고 다음 테이블을 Import (default)
- APPEND : 같은 테이블을 만나면 기존 테이블에 데이터를 추가하여 Import (존재하는 row는 skip)
- TRUNCATE : 같은 테이블을 만나면 기존 테이블을 TRUNCATE 하고 Import (존재하는 row는 delete)
- REPLACE : 같은 테이블을 만나면 기존 테이블을 DROP 하고 테이블을 재생성 한후 데이터를 Import
3 ) JOB 관련 파라메터
: JOB_NAME, STATUS, PARALLEL 파라메타가 있으며 Export와 사용 방법은 동일하다.
4 ) REMAPPING 관련 파라메터
: REMAP_SCHEMA, REMAP_DATAFILE, REMAP_TABLESPACE가 있으며 Import시에 많은 유연성을 제공한다.
- REMAP_SCHEMA : A 유저 스키마로 Export 받은 데이터를 B 유저 스키마로 Import 할때 사용
예 ) impdp ~~~~~~~~~~~~~~~~ SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:HR
명령 수행후 HR 유저 소유로 테이블이 등록된다.
- REMAP_DATAFILE : 전체 데이터베이스가 Export 된 dumpfile에는 Datafile 정보까지 포함된다.
하지만 다른 시스템의 디스크 경로 상에는 존재하지 않는 경로이기 때문에
import에는 실패하는 한다. 이러한 경우에 사용하는 파라메터이다.
Export된 dumpfile이 datafile 정보를 포함한 경우에만 해당된다.
예 ) impdp ~~~~~~~~~~~~~~~~~ REMAP_DATAFILE '/db1/data/lvol01':'/db2/data/lvol01',
'/db1/data/lvol02':'/db2/data/lvol02'
- REMAP_TABLESPACE : Export 받은 데이터가 속한 tablespace에서 다른 tablespace로
REMAPPING 하는 경우에 사용하는 파라메터
예 ) impdp ~~~~~~~~~~~~~~~~~ REMAP_TABLESPACE='scoot_tsb':'hr_tbs'
5 ) NETWORK_LING 관련 파라메터
: Export와 마찬가지로 DB_LINK를 통해 원격지 DB에 대한 import 작업을 수행가능함.
6 ) INTERACTIVE MODE 파라메터
: Export와 마찬가지로 진입하여 작업을 통제 할수 있음.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
9. Datapump Monitering
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
: 작업의 진행 경과와 작업속성들 그리고 얼마나 많은 작업이 존재하는지 파악 가능.
1 ) 관련 조회 테이블 및 VIEW
- DBA_DATAPUMP_JOBS : 실행중인 작업의 속성을 볼수있는 테이블
SQL > SELECT * FROM DBA_DATAPUMP_JOBS; 로 조회 가능
해당 컬럼 - OWNER_NAME : DB 작업 계정
- JOB_NAME : 작업의 명칭
- JOB_MODE : FULL, TABLE, INDEX, TABLESPACE 등이 있음
- STATE : EXECUTING, DEFINING, UNDERFINED, NOT RUNNING 등이 있음
■ JOB이 비정상 작동할때
|
SQL> select owner_name, job_name, state from dba_datapump_jobs;
조회하여 다음 명령을 수행
SQL> drop table [ user_name ].[ job_name ] purge; |
- Pump Session 확인 : SQL > select s.sid, s.serial#
from v$session s, dba_datapump_sessions p
where s.saddr = p.saddr;
로 조회 하면 Datapump를 통해 수행 중인 모든 세션과 상태를 모니터 할수있음
- DATA PUMP의 모니터링 : SQL > select opname, target_desc, sofar,totalwork,(sofar/totalwork*100) percentage
from v$session_longops;
opname : JOB name과 같음
TOTALWORK : 총 수행해야할 용량. MB 단위
sofar : 현재 수행한 용량. MB 단위
targer_desc : 작업의 종류. IMPORT/EXPORT가 값이 됨.