데이터 이동하기 (Export / Import)

운영서버 -> 개발서버 또는 개발서버 -> 운영서버로의 테이블 이동 등에 사용

그리고 DB가 No Archive log mode 로 운영 중 Open 상태에서 백업을 받아야 하는 경우 데이터를 DB에서 OS 파일로 저장할 떄 사용하는 것이 논리적 백업 툴인 export 이고 export로 백업받은 파일을 서버로 입력하는 툴이 import

중요한 특징 중 한가지는 Export 와 import 유틸리티는 DB가 OPEN 된 상태에서만 사용 가능하다는 것. 또 OS 기종이나 오라클 버전에 달라도 데이터를 이동시킬 수 있다. 데이터가 많을 경우 물리적 백업보다 시간이 많이 걸린다는 단점이 있다.

원리

Export 유틸리티로 데이터와 데이터베이스의 정의를 복사하고 이 내용을 오라클 내부 포맷의 바이너리 파일로 변환해 OS에 저장.

1. Conventional Path Export

Data -> Database buffer cache -> Evaluation Buffer -> Dmp 파일로 변환(binary)

※ 무조건 시작시점의 데이터가 반영되고 중간에 다른 사용자에 의해 변경된 데이터는 반영되지 않는다. 이유는 다른 사용자들은 DB버퍼캐시에서 작업을 하고 exp 작업은 evaluation buffer에서 작업하기 때문이다. 

2. Direct Path Export

Data -> Database buffer cache -> Dmp 파일로 변환

※ 이 작업 중에는 다른 사용자들이 못쓰게 Holding 된다. 잘못쓰면 역적됨. 조심.

Import 유틸리티가 저장된 바이너리 파일을 읽어 데이터베이스에 저장. 아래는 import 순서.

1. create table ~

2. insert into ~

3. create index ~ 

4. constraint ~  ※ 중간에 에러가 나면 이후 모든 작업이 중단된다.

EXPORT 수행하기 

들어가기 앞서..

Data Dictionary Views. 
---------------------- 
EXPORT 또는 IMPORT utility 를 사용하기 전에, 반드시 이 
utilities 에 의해 서용될 Data Dictionary가 준비되어야만 
합니다. 다음 script를 실행하면 됩니다. 

UNIX : SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql 

Windows: SQL> @%ORACLE_HOME%\rdbms\admin\catexp.sql 

이 CATEXP.SQL script는 반드시 SYS user로 실행되어야만 합니다. 
이 script는 CATALOG.SQL script에 의해 자동으로 호출됩니다. 
CATEXP.SQL 와 CATALOG.SQL script는 하나의 database에 단지 
한번만 실행되면 됩니다. 정상적인 상태라면 export나 import 
전에 이 script를 실행해 줄 필요는 결코 없습니다. 

CATEXP.SQL는 다음과 같은 일을 해서 database를 export와 import 
에 대해 준비시켜 줍니다. 

- data dictionary에 필요한 export와 import views들을 생성합니다. 
- EXP_FULL_DATABASE role 과 IMP_FULL_DATABASE role을 생성합니다. 
- EXP_FULL_DATABASE role 과 IMP_FULL_DATABASE role에 
필요한 모든 privileges를 할당합니다. 
- EXP_FULL_DATABASE 과 IMP_FULL_DATABASE 를 DBA role에 할당합니다. 
- sys.props$ table을 export 와 import views의 version으로 update 
합니다. 

출처 : http://www.syhds.com/it_kor/support/date.php?ptype=view&code=pds&idx=32

export 수행 모드

1) full=y 옵션을 사용하여 데이터베이스 전체 exp

2) tablespaces 옵션을 사용하여 특정 tablespace 전체를 exp

3) owner 옵션을 사용하여 특정 schema를 exp    ---> 중요!! 많이 씀!!

4) tables 옵션을 사용하여 원하는 테이블만 골라서 exp 

※ export / import 는 temporary tablespace 공간을 사용하기때문에 용량이 없다는 에러가 뜨면 바로 아래와 같이 다른 temporary tablespace 생성 후 디폴트로 설정해준다.

sql> create temporary tablespace temp2 tempfile '/app/oracle/temp2.dbf' size 20M autoextend on;

sql> alter database default temporary tablespace temp2;

※ SYS 계정으로 작업한건 exp 되지 않는다. 무조건 SYSTEM 계정으로!!

※ EXP-00107: Feature (BINARY XML) of column WAREHOUSE_SPEC in table OE.WAREHOUSES is not supported. The table will not be exported.

exp 중간에 이런 에러가 뜨는 이유는 11g에서 새롭게 추가된 XML 저장기능 때문.

나중에 나오는 Datapump 부터 지원된다.

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

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

Export: Release 11.2.0.2.0 - Production on Fri Apr 17 17:35:58 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

. exporting profiles

(생략)

Export terminated successfully with warnings.

real    3m38.938s

user    0m20.536s

sys     0m11.438s

실습2. Direct path 로 full export

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

Export: Release 11.2.0.2.0 - Production on Fri Apr 17 18:05:26 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

(생략)

real    3m12.066s

user    0m19.754s

sys     0m10.588s

실습3. export를 저장하는 파일을 분할해서 받기 (분할백업) -> SIZE 계산 중요!

[oracle@server128 ~]$ time exp system/oracle full=y file=/data/exp/full04_1.dmp , /data/exp/full04_2.dmp , /data/exp/full04_3.dmp filesize=10M

Export: Release 11.2.0.2.0 - Production on Fri Apr 17 19:05:50 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

용량은 처음에 맞춰서 줘도 되고 위에서 10M 를 준것은 부족할 때 어떻게 되는지 확인하기위해

Export file: expdat.dmp >  /data/exp/full04_4.dmp

continuing export into file /data/exp/full04_4.dmp 

(이하생략)

이렇게 프롬프트가 뜬다. 여기서 나머지 파일명을 지정해주면 된다. 그러면 이어서 쓴다.

실습4. 특정 Tablespace 만 exp 하기

[oracle@server128 ~]$ time exp system/oracle file=/data/exp/ex_users.dmp tablespaces=example,users

실습5. 특정 Table 만 exp 하기

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

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

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

실습7. evaluateion buffer 값이 export 에 주는 영향 테스트

(버퍼가 크다고 속도가 향상되는건 아님)

SYS>alter database datafile '/data/yeje/users01.dbf' autoextend on;

Database altered.

SYS>alter database datafile '/data/yeje/undotbs01.dbf' autoextend on;

Database altered.

SYS> begin

  2  for i in 1..5000000 loop

  3  insert into scott.test01

  4  values (i, dbms_random.string('A',49) ,

  5             dbms_random.string('Q',49) ) ;

  6  end loop;

  7  commit;

  8  end;

  9  /

SYS>select sum(bytes)/1024/1024 MB from dba_segments where owner='SCOTT' and segment_name='TEST01';

        MB

----------

       606

test1) evaluation buffer 값 설정하지 않고 exp 수행

[oracle@server128 ~]$ time exp scott/tiger file=/data/exp/test01_1.dmp tables=test01

real    0m32.832s

user    0m5.328s

sys     0m3.043s

test2) evaluation buffer 값 1M로 설정 후 exp 수행

[oracle@server128 ~]$ time exp scott/tiger file=/data/exp/test_01_2.dmp tables=test01 buffer=1024000

test3) evaluation buffer 값 10M로 설정 후 exp 수행

[oracle@server128 ~]$ time exp scott/tiger file=/data/exp/test_01_3.dmp tables=test01 buffer=10240000

test4) evaluation buffer 값 20M로 설정 후 exp 수행

[oracle@server128 ~]$ time exp scott/tiger file=/data/exp/test_01_4.dmp tables=test01 buffer=20480000

test5) Direct path로 exp 수행

[oracle@server128 ~]$ time exp scott/tiger file=/data/exp/test_01_5.dmp tables=test01 direct=y

실습8. parameter file 을 이용한 exp

[oracle@server128 ~]$ vi full.dat

file=/data/exp/full02.dmp

full=y

direct=y

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

실습9. 특정 조건만 exp 받기 - query 옵션 사용하기 (escape 문자 주의)

[oracle@server128 ~]$ exp scott/tiger query=\"where ename like \'F%\'\" tables=emp file=/data/exp/test06.dmp

-> 이름 첫글자가 F인 사람만

[oracle@server128 ~]$ exp scott/tiger query=\"where job=\'CLERK\' and sal \>1000\" file=/data/exp/scott2.dmp tables=emp

-> emp 테이블에서 job 이 CLERK 이고 급여가 1000 이상인 사람만

[oracle@server128 ~]$ vi par2.dat

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

file=/data/exp/scott3.dmp

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

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

실습10. schema 별로 자동 exp 백업받는 스크립트

[oracle@server128 ~]$ vi exp_script.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=testdb

sqlplus /nolog<<EOF3

conn / as sysdba

set head off

set time off

set timing off

set feedback off

set echo off

set line 200

col name for a100

spool /app/oracle/exp.tmp

select 'mkdir -p /data/backup/exp/'||to_char(sysdate,'yyyy-mm-dd-hh24-mi') from dual;

select distinct 'exp system/oracle'||' owner='||lower(owner)||' file=/data/backup/exp/'||to_char(sysdate,'yyyy-mm-dd-hh24-mi')||'/'||lower(owner)||'.dmp'||' filesize=100m direct=y' from dba_tables where owner not in ('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS');

spool off

!cat /app/oracle/exp.tmp | egrep -v SQL | egrep -v SYS > /app/oracle/exp.sh

!sh /app/oracle/exp.sh

exit

EOF3

Import 수행하기

imp 실행순서

1) create table ~

2) insert into ~

3) create index ~

4) constraint 생성 ※ 중간과정에서 하나라도 에러가 나면 모든 작업이 캔슬됨.

Import 작업은 DDL과 DML을 수행하는 것이므로 Redo log 와 Undo segment 를 사용한다.

따라서 대량의 데이터를 import 할 때는 반드시 충분한 용량의 Undo tablespace 가 필요하다.

용량이 부족하면 마지막에 에러나면서 전부 rollback 될 수도 있음.

이런 위험을 줄이기 위해서는 import 할 때 commit=y 로 변경하고 수행하면 array 단위로 commit 을 수행하기 때문에 전체가 rollbak 되는 현상은 막을 수 있다.

실습에 앞서 전체 full export 수행

time exp system/oracle file=/data/exp/full11.dmp full=y

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

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

실습2. 특정 사용자의 데이터만 import

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

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

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

SCOTT> begin

for i in 1..1000 loop

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

end loop;

commit;

end;

/

SCOTT> select count(*) from test02;

  COUNT(*)

----------

      1000

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

import 수행하기

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

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

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

[oracle@server128 ~]$ vi test02.log