오라클/백업/복구

데이터 이동하기

김포춘 2014. 11. 6. 17:47
반응형


데이터 이동 방법


옛날 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     <-- 로그파일 열어서 편집





반응형

'오라클 > 백업/복구' 카테고리의 다른 글

Flashback  (0) 2014.11.12
Clone DB  (0) 2014.11.10
sql loader  (0) 2014.11.10
Redo log flle 장애 처리방법  (0) 2014.11.05
Log Miner 활용하기  (0) 2014.11.05
control file, data file 연습문제  (0) 2014.11.04
Control file 복구  (0) 2014.11.04