데이터 이동하기

데이터 이동 방법

옛날 DB 에서 Export 를 사용해 데이터를 복사해서 dmp file 로 저장 -> Import 유틸리티가 dmp file 을 읽어서 다시 최신 DB 에 저장 해 주는 과정을 데이터 이동이라고 함

데이터 이동시에 DB는 OPEN 상태여야 함.

Conventional Path export & Direct Path export

Conventional Path export : 명령어가 수행되면 export 프로그램이 메모리에 evaluation buffer 라는 곳을 만들어 db buffer cache 에 있는 데이터를 가져와서 이곳이 다 차면 다시 디스크에 파일을 저장.

Direct path export : db buffer cache 에서 바로 dmp file 로 저장

direct path export 는 export 작업중 사용자가 buffer cache 에서 작업을 하게되면 속도저하가 일어나고 hang 이걸려서 db 가 꺼질수 있음

conventional path export 는 evaluation buffer 로 데이터를 복사해서 dmp file 로 저장하기 때문에 direct path export 에서 일어나는 문제를 해결 할 수 있다.

Datapump

Oracle 10g 부터 등장한 export/import 의 향상된 유틸리티. export/import 의 여러 문제점을 해결

1. Datapump 의 장점

1) 작업 관리의 편의성 - exp/imp 의 방법은 작업이 시작되면 끝날 때 까지 제어를 할 수 없었지만 Datapump 는 job의 제어가 가능함.

2) 필요 디스크 공간의 예측 - exp/imp 작업은 작업중 디스크 공간의 부족으로 몇 시간동안 했던 작업을 취소하고 디스크를 추가 or 파일삭제의 방법으로 공간을 확보하고 다시 작업을 수행하던데 비해 datapump 는 ESTIMATE 파라미터를 사용해 해당 작업시 필요 디스크 공간을 미리 알 수 있음

3) 원격지 DB에 작업 수행 가능 - DB Link 라는 기능을 통해 원격지에 있는 db에 expdp / impdp 작업 가능

4) remapping 기능 지원 - 스키마 변경이나 테이블스페이스 변경, 데이터파일 변경이 가능

5) dump 작업을 하면서 압축을 동싱에 진행 - 용량이 큰 데이터의 경우 압축을 동시에 진행해 dump file의 용량을 획기적으로 줄일 수 있음

6) 향상된 작업 속도 - 서버 환경에 따라 다르지만 일반적으로 exp/imp 보다 평균 20배 이상 성능이 향상되었음

2. 사용 전 환경 설정

datapump 는 exp/imp 와 다르게 유틸리티가 직접 OS 파일에 I/O 할 수 없고 오라클에 directory 라는 객체를 통해 간접적으로 접근이 가능. 그래서 datapump 를 사용하려면 미리 directory 를 만들고 directory 접근권한이 있어야 함.

datapump 사용전 디렉토리 생성 후 권한설정하기

[oracle@localhost~]$ mkdir / data/dp

[oracle@localhost~]$ sqlplus / as sysdba

SQL> create or replace directory datapump as '/data/dp';

SQL> grant read, write on directory datapump to scott ;

SQL> grant create any directory to scott ;

3. expdp 실행 모드

1) full 모드 - full 파라미터를 사용해 DB 전체를 export 받을 수 있음. dba권한이나 export_full_database 권한 있어야함

2) schema 모드 - schemas 파라미터를 사용해 특정 스키마의 전체를 export 받을 수 있음

3) tablespace 모드 - tablespace 파라미터를 사용해 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있음

만약 transport_tablespace 파라미터를 사용하면 테이블과 테이블스페이스의 메타 데이터까지 export받게 되어 다른 서버로 테이블스페이스 전체를 이동시킬 때 유용함. 단, 양쪽 db의 os가 같아야 하고 block size 와 characterset 도 같아야함

4) table 모드 - tables 파라미터를 사용, 여러 개의 테이블을 export받으려면 , (콤마) 로 구분

datapump 를 사용하려면 temporary tablespace 설정이 올바르게 되어 있어야 한다.

temporary tablespace 가 없거나 작으면 temporary tablespace is empty 라는 경고가 나옴.

경고가 나올 경우 즉시 temporary tablespace를 크게 만든후 해당 사용자가 사용하게 해주면 됨

SQL> create temporary tablespace temp100

  2    tempfile '/app/oracle/oradata/testdb/temp100.dbf' size 100m ;

SQL> alter user scott temporary tablespace temp100 ;          <-- 해당 사용자만 사용하게

or

SQL> alter database default temporary tablespace temp100 ;    <-- temp100 을 default 로

EXPORT 실습

실습 1. conventional path 로 full export 받기 ( 기본 모드 )

[oracle@localhost~]$ exp system/oracle full=y file=/data/exp/full01.dmp log=/data/exp/full_log01.log

실습 2. direct path 로 full export 받기

[oracle@localhost~]$ exp system/oracle full=y file=/data/exp/full02.dmp log=/data/exp/full_log02.log direct=y

실습 3. export를 저장하는 파일을 분할해서 받기

[oracle@localhost~]$ exp system/oracle full=y file=/data/exp/full03_1.dmp, \

      full03_2.dmp, \

      full03_3.dmp file size=100m

만약 용량 산정을 잘못하여 파일 개수를 부족하게 지정할 경우 파일을 추가하도록 프롬포트가 나옴, 그럼 그때 등록하면 됨

....

....

Export file : expdat.dmp > /data/exp/full04_4.dmp        <-- 이런식으로 추가해주면 됨

실습 4. 특정 tablespace 만 export 하기

[oracle@localhost~]$ exp system/oracle file=/data/exp/ex_user.dmp tablespaces=example,users

실습 5. 특정 table 만 exp 하기

[oracle@localhost~]$ exp scott/tiger tables=emp,dept file=emp_dept.dmp

실습 6. 여러 사용자를 동시에 exp 하기

[oracle@localhost~]$ exp system/oracle file=/data/exp/scott_hr.dmp owner=scott,hr

실습 7. evaluation buffer 값이 크면 대체로 export 값이 빠른데 크다고 무조건 빨라지는 것은 아님

temporary tablespace 의 크기 또한 exmp/imp 에 영향을 많이 주니 작업전에 큰 temporary tablespace를 확보

실습 8. parameter file을 이용한 export 수행

[oracle@localhost~]$ vi full.dat

file=/data/exp/full.dmp

full=y

direct=y

~

:wq!

[oracle@localhost~]$ exp system/oracle parfile=full.dat

실습 9. 특정 조건만 export 받기 - query 옵션 사용

step 1. emp 테이블에서 이름 첫 글자가 F 인 사람만 export 받기

( os 에서 사용하는 ' , " , < 등의 문자를 쓸 경우 \ 꼭 써야함 )

[oracle@localhost~]$ exp scott/tiger query= \ " where ename like \ ' F% ' \ ' \ " tables=emp \

> file=/data/exp/test06.dmp

step 2. emp 테이블에서 job 이 CLERK 이고 급여가 1000이상인 사람만 export 받기

[oracle@localhost~]$ exp scott/tiger query= \ "where job= \ 'CLERK\ ' and sal= \ >1000\ " \

> file=/data/exp/scott2.dmp tables=emp

step 3. parameter file 에서 query 옵션 사용하기 - escape 문자 안써도 됨

[oracle@localhost~]$ vi par2.dat

tables=emp query= "where job='CLERK' and sal > 1000 "

file = /data/exp/scott3.dmp

:wq!

[oracle@localhost~]$ exp scott/tiger parfile=par2.dat

IMPORT 실습

import 작업은 DDL과 DML 을 수행하는 것이므로 Redo log 와 Undo Segment 를 사용

대량의 데이터를 import 할 경우 충분한 용량의 Undo tablespace 를 준비해놓고 작업해야 함.

만약 import 중 Undo tablespace의 용량이 부족할 경우 마지막에 에러나면서 전부 rollback 될수도 있다.

이러한 위험을 줄이기 위해 import 할때 commit=y 옵션을 사용하면 array 단위로 commit 하기에 전체가 rollback 되는걸 방지한다.

실습 11. 전체 데이터 import 수행

[oracle@localhost~]$ imp system/oracle file=/data/exp/full01.dmp ignore=y full=y

전체 데이터를 import 할때 A 서버에서 B 서버로 이동시킬 때 B서버에 같은 테이블이나 데이터가 존재하면 추가해줌

만약 B서버에 제약조건이나 index 가 존재하면 unique index 나 primary key 등이 존재할 경우 데이터 추가 안되고 에러 발생

실습 12. 특정 사용자의 데이터만 import 하기

[oracle@localhost~]$ imp system/oracle file=/data/exp/full01.dmp ignore=y fromuser=scott tables=test01

실습 13. scott 사용자의 test02 테이블을 hr 사용자 소유로 변경

테스트 테이블 생성후 export

SCOTT> create table test02 (no numberm addr varchar2(10)) ;

SCOTT> begin

2    for i in 1..1000 loop

3      insert into test02 values (i,dbms_random.string('a',10)) ;

4    end loop ;

5    commit ;

6    end ;

7    /

SCOTT> select count(*) from test02 ;

COUNT(*)

--------

1000

[oracle@localhost~]$ exp scott/tiger file=/data/exp/test02.dmp tables=test02

import

[oracle@localhost~]$ imp system/oracle file=/data/exp/test02.dmp fromuser=scott touser=hr ignore=y

실습 14. 실제 데이터는 import 하지 않고 DDL 문장만 추출

두가지 방법이 있는데 한가지는 show=y 옵션, 다른 한가지는 indexfile 옵션 사용

show=y 옵션 : export 파일의 모든 내용들이 보여지는데 log 옵션을 주면 그 내용들이 전부 로그 파일에 저장됨, 그후 로그파일 편집

[oracle@localhost~]$ imp scott/tiger file=/data/exp/test02.dmp show=y log=test02.log

[oracle@localhost~]$ vi test02.log     <-- 로그파일 열어서 편집