Datapump
10g부터는 Datapump를 사용해서 기존 보다 빠르게 작업을 할 수 있으나
적은 양의 데이터를 이동시킬때는 Export와 Import 유틸리티를 이용하는게 더 빠름.
Datapump는 Directory 객체를 생성하여 사용하고(OS파일에 I/O 할 수 없슴)
export/import 작업을 진행하는 유저에게 해당 객체에 대해 읽기, 쓰기 권한을 부여 해야한다(필수!!!)
expdp : 디렉터리에 dmp 파일에 저장
impdp : 디렉터리에서 dmp 파일을 읽어 데이터베이스에 생성
◈ DATAPUMP 필요 권한
. create session
· create table
· create directory
· read, write on directory
◈ DATAPUMP 장점
. 필요한 디스크 공간 예측 : estimate 파라미터를 사용하여 필요한 디스크 공간 예측 가능
expdp system/password full=y estimate_only=y : 디스크 공간 예측
. 원격지 DB에 작업 수행 가능 : db link를 통해 원격지 db에 expdp/impdp 수행 가능
. REMAPPING 기능 지원 : datafile, tablespace, 스키마 변경 가능
◈ EXPDP 실행 모드
. full : DB 전체 export
. tablespace
. schema
. table
◈ DATAPUMP 설정 순서
1) 소스/타겟 서버에 directory 생성 및 권한 부여
mkdir data/datapump
chown -R oracle:dba /data/datapump
create directory DATAPUMP as '/data/datapump' ;
grant read,write on directory DATAPUMP to scott;
grant create any directory to scott;
2) expdp 명령어로 dmp 파일 생성
3) ftp로 타겟 서버로 dmp 파일 전송
- 타겟서버의 disk 용량 확인!!!용량이 부족하면 에러남!!!
4) impdp 명령어로 dmp파일 내용 db에 적재
- permission에러나면 dmp 파일의 권한을 확인한다
chown -R oracle:dba backup.dmp : oracle이 해당 dump파일을 읽을 수 있게
- tablespace 사이즈 확인!!
[ DATAPUMP 설정 ]
1) directory 조회
SELECT * FROM dba_directories; . DATAPUMP : datapump를 위해 사용자가 만든 것
. DATA_PUMP_DIR : 오라클 기본 directory
(데이타펌프시 사용 안함->사용자 생성한 directory로 사용)
설정 전 초기 화면
2) source/target 서버에 directory 생성
2-1) unix 일때
① 파일 디렉토리 생성 및 권한 부여
mkdir /data/datapump
② 폴더 권한 주기(권한을 oracle오너랑 그룹으로 맞춰준다) : chown으로
chown -R oracle:dba /data/datapump
③ 데이타펌프 alias 생성
su - oracle : oracle로 접속
서버 하나에 DB가 여러개 생성되어 있으면 해당 DB로 접속(SID입력) : erpdbtest
sqlplus / as sysdba ;
CREATE DIRECTORY DATAPUMP AS '/data/datapump';: /data/datapump에 대한 alias 생성(DATAPUMP)
④ expdp 할 유저에게 해당 디렉토리 접근권한 부여
- scott user에 datapump 디렉토리에 접근할 수 있는 권한 설정
grant read, write on directory datapump to scott( or public);
grant create any directory to scott ; // scott가 directory 생성 권한 없을때 사용
⑤ datapump 디렉터리 생성 확인
2-2) WINDOWS 서버일때 : DIRECTORY 생성 및 권한주기
CREATE DIRECTORY DATAPUMP AS 'E:\db_backup\datapump';
GRANT READ, WRITE ON DIRECTORY DATAPUMP TO SYSTEM;
GRANT CREATE ANY DIRECTORY TO SYSTEM;
3) EXPDP
① 특정 테이블 expdb 할때
expdp scott/tiger directory=DATAPUMP dumpfile=backup.dmp
logfile=backup.log tables=emp,dept
② 유저별로 expdb 할때
expdp system/password directory=DATAPUMP dumpfile=backup.dmp
logfile=backup.log schemas=scott
③ Full로 받을 때
expdp system/password directory=DATAPUMP dumpfile=backup.dmp
logfile=backup.log job_name=backup full=y
④ 특정 사이즈로 받을때
expdp system/password directory=DATAPUMP dumpfile=backup%U.dmp
logfile=backup.log job_name=backup full=y filesize=1g
⑤ EXCLUDE/INCLUDE 사용법
INCLUDE = object_type[:name_clause] [, ...]※ 주의사항
▶ name_clause에 제공하는 이름은 데이터베이스의 기존 개체인
대소문자를 포함하여 정확히 일치해야 합니다
▶ exclude/include 옵션 사용시 모든 문자에 \(역슬래쉬)를 입력해야
syntax 오류 발생하지 않음
예제)
◈ TABLE중 이름이 TB_%는 제외하고 EXPDP 하기(LIKE사용해서)
expdp scott/tiger directory=DATAPUMP dumpfile=etc.dmp
logfile=etc.log exclude=TABLE:\"LIKE \'TB_%\'\"
◈ 특정 TABLE은 제외하고 EXPDP 하기(IN사용해서)
expdp scott/tiger directory=DATAPUMP dumpfile=etc.dmp
logfile=etc.log exclude=TABLE:\"IN\(\'DEPT\',\'EMPLOYEE\'\)\"
◈ package, function, procedure만 expdp 하기
expdp scott/tiger directory=DATAPUMP dumpfile=etc.dmp
logfile=etc.log include=PACKAGE,FUNCTION,PROCEDURE
⑥ QUERY조건 사용법
◈ TB_DATA 테이블의 YYMM <= '202112' 조건만 expdp 하기)
expdp scott/tiger directory=DATAPUMP
dumpfile=etc.dmp logfile=etc.log
tables=TB_DATA query='TB_DATA:"where yymm <= '202112'"'
(full export 를 하기 위해서는 DATAPUMP_EXP_FULL_DATABASE 롤을 부여 받아야함)
▶ 옵션 설명
. derectory : dmp파일이 저장되어여 있는 directory
. logfile : 로그파일 저장 이름
. full=y : db 전체 export
. tablespaces : 지정된 테이블스페이스에 저장된 object export
. schemas : 지정된 user가 소유한 object export
. tables : 지정된 테이블 export(여러개일 경우 콤마(,)로 구분)
. exclude : 작업에서 제외 될 object 종류와 이름을 지정
exclude=table:\"=emp\'\" : emp 테이블만 제외
. include : 작업에 포함 될 object 종류와 이름을 지정
include=table(object type):"dept"(object_name) : dept 테이블만 포함(대소문자 주의)
. parfile : 파라미터를 미리 지정하여 저장후 참조해서 사용(확장자는 상관없음)
. content=[all | data_only | metadata_only]
all : 테이블 데이타와 메타데이타 포함
data_only : 테이블 데이타만
metadata_only : 메타 데이타만
. remap_tablespace : 타겟 서버에서 object가 저장될 테이블스페이스 변경
remap_tablespace='users':'temp'
. remap_schema : 타겟 서버 db에서 object user 변경
remap_schema=scott:user1
. remap_datafile : 타겟 서버 db의 데이터파일 변경
remap_datafile= 'c:\user1.dbf':'c:\data\user2.dbf'
. estimate_only : 내보내기 수행하지 않고 작업 추정
. filesize : 덤프파일 크기 지정
. flashback_scn : 세션 스냅 샷을 다시 설정 하는데 사용되는 scn
. flashback_time : 지정된 시간과 가장 가까운 scn을 가져 오는데 사용
. job_name : 작업 이름
. network_link : 타켓 서버 db link 이름
network_link=dept@db_link명
. parallel : 병렬 처리 프로세스 갯수
. query
. status : 작업시 갱신 내용을 status에 설정된 시간 간격으로 진행상태를 보여줌
. transport_full_check : 테이블스페이스에 존재하는 테이블과 인덱스 의존성 검사 유무
Y : 테이블과 인덱스가 동일한 테이블스페이스에 존재해야 함
N : 테이블과 인덱스가 다른 테이블스페이스에 존재 해야 함
. attach : 중단된 작업에 다시 접속할때 사용
attach scott/tiger job_name=backup
. reuse_dumpfiles : 저장할 덤프파일이 있으면 덮어씀
reuse_dumpfiles=Y
▶ 작업 확인 쿼리
select owner_name, job_name, operation, job_mode, state
from dba_datapump_jobs ;
. owner_name : 작업 계정
. job_name : 작업 명칭
. job_mode : full, tablespace, table..
. state = executing : 작업중
no rows selected : 작업완료
not running
undefined
defining
▶ 작업예상시간 조회
SELECT SID, SERIAL#, SOFAR, TOTALWORK
FROM V$SESSION_LONGOPS
WHERE OPNAME = 'job_name’(대문자로입력)
AND SOFAR != TOTALWORK;
4) IMPDP
: expdp로 만든 dmp 파일을 타켓 서버의 /data/datapump(설정한 datapump directory)로 이동한다
- datapump를 사용하려면 미리 directory가 만들어져 있어야하고 접근 할 수 있는 권한이 필요
① 특정 테이블
impdp scott/tiger directory=DATAPUMP dumpfile=backup.DMP
TABLE_EXISTS_ACTION=TRUNCATE logfile=backup.log tables=emp,dept
② User별
impdp system/password directory=DATAPUMP dumpfile=backup.dmp
logfile=backup.log schemas=scott
③ Full
impdp system/password directory=DATAPUMP dumpfile=backup.dmp
logfile=backup.log full=y
▶ 옵션 설명
. table_exists_action
truncate : 동일한 이름의 테이블이 존재할 때 truncate 하고 import
skip : 동일 테이블 존재시 skip
append : 동일 테이블 존재시 데이터 추가
drop : 기존 테이블 drop 후 새로만들어서 import
. remap_schema : import의 from_user, to_user와 동일
remap_schema=scott:scott
. remap_tablespace : 기존 users 테이블스페이스에서 example 테이블스페이스로 import
remap_tablespace='users':'example'
. remap_datafile : 기존 서버의 datafile에서 타겟 서버 db의 datafile로 매핑
. remap_table
remap_table=emp:employee
. include : 특정 object 나 테이블 포함
. exclude : 특정 object 나 테이블 제외
. network_link
. partition_options : 파티션 테이블을 단일테이블로 import
partition_option=merge
. data_options=skip_constraint_errors : 제약조건 위배에도 무시하고 진행
▶ impdp시 에러 발생 대응
1. ORA-31694: master table "SYS_IMPORT_FULL_01" failed to load/unload
1) datapump로 export 하지 않았을때
2) dmp파일을 ftp로 이동할때 binary로 지정하지 않았을때
3) dmp 파일 손상되었을때 -> expdp로 dmp 다시 생성 후 impdp 한다
2. ORA-31644 unable to position to block number string in dump file " string "
Export or Import was unable to position its file pointer to a specific block within the dump file.
(내보내기 또는 가져오기에서 파일 포인터를 덤프 파일 내의 특정 블록에 배치할 수 없습니다)
This message is usually followed by device messages from the operating system.
(이 메시지 다음에 일반적으로 운영 체제의 장치 메시지가 옵니다)
★ 처리방법
expdp로 dmp 다시 생성 후 impdp 한다
3. ORA-39001 : invalid argument value
★ 처리방법
권한 문제인줄 알았으나 dmp 파일이 정상적이지 않은 문제 였음
expdp로 만든 dmp파일과 ftp로 올린 dmp 파일 사이즈 비교
=> 타겟 서버에 해당 파일이 저장될 폴더의 disk용량이 확보되어 있어야 함
용량 확보 되고도 위의 에러가 나면 파일을 읽을수 있는 권한을 확인해야 함
아래처럼 dmp파일이 system 그룹으로 되어 있으면 oracle이 읽을 수 없으므로
해당 파일에 대해서 아래와 같이 작업 후 진행 한다
chown -R oracle:dba swg_2207270.dmp 후에 다시 impdp 실행한다
★ IMPDP 작업 중단(완전 삭제는 kill_job)
CTRL + C를 눌러도 실제로 작업이 중단된것은 아님
stop_job을 입력하여 작업을 중단 시킨다 => 재가동 하려면 start_job을 입력한다
system계정으로 impdp의 상태를 확인한다
★ IMPDP 실행중 삭제(kill_job)
impdp scott/tiger attach=job명
imp> kill_job 입력
EX) impdp scott/tiger attach=SYS_IMPORT_TABLE_02
imp>kill_job
