08 export / import
conventional path export - export 명령어가 수행되면 export client 가 메모리에 evaluation buffer 라는 곳을 만들고
db buffer cache 에 있는 데이터를 evaluation buffer 로 가져온 후 이곳이 다 차면 다시 디스크에 파일로 저장합니다.
direct path export - 여러 process 가 동시에 접근해서 사용할 경우 속도 저하가 심해지는 단점이 있음
전체 데이터베이스를 export 받으려면 export 를 수행하는 사용자가 DBA 권한을 가지고 있거나
exp_full_database 라는 role 을 가지고 있어야 합니다.
export 와 import 는 temporary tablespace 공간을 사용한다
export
1) conventional path Full export 받기
$ time exp system/oracle full=y file=/data/exp/full01.dmp \
> log=/data/exp/full_log01.log
2) direct path 로 Database Full export 받기
$ time exp system/oracle full=y file=/data/exp/full02.dmp \
> log=/data/exp/full_log02.log direct=y
3) export 를 저장하는 백업 파일을 분할해서 받기
$ time exp system/oracle full=y file=/data/exp/full03_1.dmp, \
> /data/exp/full03_2.dmp, \
> /data/exp/full03_3.dmp \
> filesize=100M
만약 용량 산정을 잘못하여 파일 개수를 부족하게 지정할 경우 아래와 같이 파일 이름을 추가로 입력하도록 프롬프트가 나오며
관리자가 다음 파일명을 입력해 주면 계속 진행 됩니다.
Export file: expdat.dmp > /data/exp/full03_4.dmp <----- 파일명을 직접 입력해 주세요.
4) 특정 테이블스페이스만 받기
$ time exp system/oracle file=/data/exp/ex_users.dmp \
> tablespaces=example,user
5) 특정 Table 만 exp
$ exp scott/tiger tables=emp,deptfile=emp_dept.dmp
6) 여러 사용자를 동시에 백업 받기
$ time exp system/oracle file=/data/exp/scott_hr.dmp owner=scott,hr
7) evaluation Buffer 값을 조정하면서 export 수행
실습용 테이블 생성
SYS> create table scott.test01
2 ( no number,
3 name varchar2(50),
4 address varchar2(50)
5 ) tablespace users
SYS>alter database datafile '/app/oracle/oradata/testdb/users01.dbf'
2 autoextend on;
SYS>alter database datafile '/app/oracle/oradata/testdb/undotbs01.dbf'
2 autoextend on;
SQL> 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 /
test 1) evaluation Buffer 값을 설정하지 않고 export 수행
$ time exp scott/tiger file=/data/exp/test01_1.dmp tables=test01
Test 2) evaluation Buffer 값을 1M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_2.dmp tables=test01 \
> buffer=1024000
Test 3) evaluation Buffer 값을 10M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_3.dmp tables=test01 \
> buffer=10240000
Test 4) evaluation Buffer 값을 20M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_4.dmp tables=test01 \
> buffer=20480000
Test 5) Direct Path 로 export 수행
$ time exp scott/tiger file=/data/exp/test_01_5.dmp tables=test01 \
> direct=y
8) 일반 사용자로 Full export 수행
SYS> grant exp_full_database to scott ;
$ time exp scott/tiger full=y \
> file=/data/backup/export/test7.dmp buffer=10240000
9) parameter file 을 이용한 export 수행하기
$ exp system/oracle parfile=full.dat
10) 특정 조건만 export 받기 - query 옵션 사용하기
Test1) emp 테이블에서 이름 첫 글자가 F인 사람만 export 받기
(OS에서 사용하는 ‘,”,< 등의 문자를 쓸 경우 \(escape 문자)를 꼭 써줘야 합니다
$ exp scott/tiger query=\"where ename like \'F%\'\" tables=emp\
> file=/data/exp/test06.dmp
Test2) emp 테이블에서 job 이 CLERK 이고 급여가 1000 이상인 사람만 export 받기
$ exp scott/tiger query=\"where job=\'CLERK\' and sal \> 1000\"
> file=/data/exp/scott2.dmp tables=emp
Test 3) parameter file 에서 query 옵션 사용하기 – escape 문자 안 써도 됨.
$ vi par2.dat
tables=emp query="where job='CLERK' and sal > 1000"
file=/data/exp/scott3.dmp
:wq!
$ exp scott/tiger parfile=par2.dat
10) schema 별로 자동 export 백업 받는 스크립트
$ 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-SS') from dual;
select distinct 'exp system/oracle'||'owner='||lower(owner)||' file=/data/backup/exp/'
||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS')||'/'||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','L
OADER','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
$ sh exp_script.sh
import
1) DBA 로 전체 데이터 import 수행하기
$ imp system/oracle file=/data/exp/full11.dmp ignore=y full=y
2) 특정 사용자의 데이터만 import 수행하기
$ imp system/oracle file=/data/exp/full11.dmp fromuser=scott \
> tables=test01 ignore=y
3) SCOTT 사용자의 test02 테이블을 HR 사용자 소유로 변경하기
$ imp system/oracle file=/data/exp/test02.dmp \
> fromuser=scott touser=hr ignore=y
4) 실제 데이터는 import 하지 않고 DDL 문장만 추출하기
show=y 옵션 사용하는 것과 indexfile 옵션을 사용하는 방법
$ imp system/oracle file=/data/backup/export/test02.dmp full=y \
> show=y log=test02.log
$ vi test02.log
log 파일을 열어서 편집
export 와 import 에 대한 궁금증 모음
Q1) Export 시 수행과정을 화면에 안 나오게 하는 방법은 ?
A1) 이 방법은 OS 별로 조금 다른데 여기서는 Linux 를 기준으로 설명하겠습니다.
$ exp scott/tiger file=/data/backup/export/test02.dmp \
> tables=test01 > /dev/null 2>&1
- CShell : exp …… > &/dev/null
- Ksh , Bsh , Bash : exp ….. >/dev/null > 2>&1
Q2) export 시 Buffer 와 Recordlength 의 차이는 무엇인가요?
A2) export 시 Conventional Path 를 이용할 경우 (기본값) 메모리에 evaluation Buffer 를 생성하고
DB Buffer Cache 에 있는 내용을 evaluation buffer 로 가져 온 후 Binary 로 변환 작업을 해서 OS 파일로 저장을 하게 됩니다.
여기서 Buffer 란 evaluation Buffer 크기를 결정하는 파라미터이고
recordlength 는 buffer 의 내용을 OS 파일로 내려쓸 때 사용하는 레코드의 크기를 결정하는 파라미터입니다.
이 두 가지 파라미터의 크기는 OS 블록의 배수로 설정하시는 것이 좋습니다.
그리고 DB Buffer Cache의 내용을 evaluation buffer 로 가져올 때 한 건씩 가져오는 것이 아니라
여러 건 의 데이터를 한꺼번에 가져오게 되는데 그것을 Array fetch 라고 합니다.
Q3) 큰 데이터를 export 또는 import 할 때 화면이 정지된 것 같이 보이는데 입력되고 있는지 아닌지 알 수 있는 방법이 있을까요?
A3) export / import 옵션 중에 feedback 이라는 옵션이 있습니다 . feedback=정수값 이렇게 사용하며 진행되는 과정을 눈에 보여줍니다.
Q4) export 할 때 한 건씩이 아니라 Array Fetch 로 추출된다고 하는데 한번에 몇 개의 Row가 추출되나요?
A4) 이 값은 Buffer(evaluation buffer) 크기와 관련이 있습니다.
하나의 row가 export 시 차지하는 양은 각 column 크기의 합 + 4 X (column 개수)로 구해집니다.
이 자료를 기준으로 해서 한번 Fetch될 때 Row 수는 Buffer size / 1 row size 입니다.
이를 이용하면 export 된 파일의 총 크기는 1 row size X row 수 입니다.
Q5) import 를 수행하다 보면 가끔 core dump/segmentation fault 가 발생합니다. 원인은 무엇이며 해결방법은 어떻게 하나요?
A5) 오라클은 데이터를 저장하기 때문에 character set 이라는 것이 있습니다.
국내에서는 주로 US7ASCII 또는 KO16KSC5601 , KO16MSWIN949 를 사용하는 데
export 받은 DB와 import 하는 DB의 캐릭터 셋이 다를 경우 위와 같은 에러가 발생합니다.
해결방법은 캐릭터 셋을 일치 시킨 후 export 를 받는 것이 정석이지만
이미 받아 버린 덤프 파일이라면
convert 프로그램을 이용하여 import 하는 곳의 캐릭터 셋과 동일하 게 변경한 후 import 를 수행하면 됩니다.
참고로 현재 데이터베이스의 캐릭터 셋을 확인하시려면 아래의 SQL을 사용하시면 됩니다.
SYS> select value from nls_database_parameters w here parameter='NLS_CHARACTERSET';
Q6) Array Fetch 란 무엇인가요?
A6) export 는 select 문장을 만들어서 DB Buffer Cache 에 있는 데이터를 evaluation buffer 가져옵니다.
만약 한번에 한 문장씩 가져와서 파일로 내려쓴다면 시간이 너무 오래 걸리고 파일에 I/O 하는 회수도 아주 많아 질 것입니다.
그래서 export 는 evaluation Buffer 에 일정량의 데이터가 쌓이게 되면 한꺼번에 파일에 내려씁니다.
즉 만약 10개의 row 를 파일에 export 해야 한다면 1번에 1개씩 하면 10번 I/O가 발생하지만
10개를 한번에 내려쓴다면 I/O는 한번만 일어나면 되겠죠.
이렇게 해서 I/O를 줄여서 속도를 높이는 방법이 array fetch라는 기법입니다.
Q7) import 시에 array insert 는 무엇인가요?
A7) export 와 마찬가지로 import 시에도 한번에 1 row 씩 insert 를 하게 되면 너무 비효율적 입니다.
그래서 import 할 때도 여러 건의 데이터를 한꺼번에 insert 하게 되는데 이것이 array insert 입니다.
Q8) import 시에 “ABNORMAL END OF FILE” 라는 에러메시지는 어떤 의미인가요?
A8) 이 에러는 export 받은 파일에 문제가 있음을 의미합니다. 다시 export 받아야 합니다.
Q9) sys 소유자의 객체는 왜 export 가 안되나요?
A9) sys 계정은 dictionary 객체들을 소유하고 있습니다.
새로운 데이터베이스는 이미 고유의 딕셔너리를 가지고 있을 것입니다.
Sys 계정은 DB를 관리하는 계정으로 sys계정의 객체를 export 하고 import 한다는 것은
아주 큰 위험 부담이 있고 부하도 많이 걸리는 작업이라
sys계정은 export 를 수행할 수 없도록 되어 있습니다.
또 개인적인 업무를 하기 위해 sys 계정으로 접속하시는 것은 권장사항이 아니므로 주의해야 합니다.
Q10) A 사용자 소유의 테이블에 B 사용자가 인덱스를 만들었다면
A사용자가 export 받을 때 B 사용자의 인덱스도 함께 export 받을 수 있나요?
A10) A 사용자 계정으로 export 를 수행하면 받을 수 없으나 DBA 권한으로 받으면 받을 수 있습니다.
Q11) offline 상태의 테이블스페이스도 export 받을 수 있나요?
A11) 아닙니다. Exp 는 select 와 동일한 원리이므로 offline 이나 shutdown 상태에서는 사용할 수 없습니다.
심화1 tables 옵션으로 exp 받은 dmp 파일 분석하기
exp 수행한 후 생성되는 dmp 파일 절대 수정하시면 안됩니다
$ exp scott/tiger file=scott.dmp tables=emp,dept
$ vi scott.dmp
위 파일에서 많은 정보를 얻을 수 있는데 중요한 부분들만 발췌해서 살펴보겠습니다.
1 ^C^CNEXPORT:V11.02.00
2 USCOTT
3 RTABLES
4 8192
우선 1번줄은 export 버전이고 2번 줄에 보면 exp 를 수행한 사람이 나옵니다.
3번줄은 table 을 export 한 것이고 4번줄은 블록사이즈가 됩니다.
Sat Apr 23 0:31:0 2013scott.dmp
그 다음 아랫줄에 보면 exp 를 수행한 시간 날짜 정보와 파일이름이 나옵니다.
12 TABLE "EMP"
13 CREATE TABLE "EMP"("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB"VARCHAR2(9),
"MGR" NUMB ER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2),
"DEPTNO" NUMBER(2, 0)) P CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS"LOGGING NOCOMPRESS
14 INSERT INTO "EMP"("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM",
"DEPTNO") VALUE
13번 째 줄에 보면 CREATE TABLE “EMP”.. 라는 부분이 나오면서 table 을 생성합니다.
그리고 14번 줄에 보면 데이터를 입력합니다.
18 CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO") PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE( INITIAL 65536 NEXT 1048576MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TA BLESPACE "USERS" LOGGING
19 ANALSTATS IS "EMP"
20 Y^@BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"PK_EMP"',NULL,NULL,NULL,14,1,14,1,1,1,0,6);
END;
21 ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING
INDEX PCTFREE 10 INITR ANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUP S 1 BUFFER_POOL DEFAULT) TABLESPACE
"USERS" LOGGING ENABLE
13번, 14번 줄에서 테이블 생성 후 데이터까지 입력 완료하고 나면 18번 줄에서 인덱스를
생성합니다. 그리고 21번 줄에 제약조건을 추가합니다.
그리고 각종 통계정보를 생성하게 됩니다.
Dept 테이블도 이 과정을 반복하게 됩니다.
우리가 여기서 알 수 있는 사실은 exp 받은 dmp 파일은 나중에 imp 를 하기 위해서 바이너리
파일로 변환되어서 만들어 진다는 것과 내부 순서를 보면 Create Table - > insert -> Create index
-> add constraints 과정으로 진행된다는 것입니다.
심화2 tablespaces 옵션으로 exp 받은 dmp 파일 분석하기
$ exp system/oracle file=users.dmp tablespaces=users
$ vi users.dmp
1 ^C^CNEXPORT:V11.02.00
2 DSYSTEM
3 RTABLES
4 8192
가장 앞부분 1번 줄은 export 버전이며 2번 줄은 exp 를 수행한 계정이고 3번 줄은 table 들을
exp 받았다는 뜻이며 4번 줄은 block 의 크기입니다.
11 CONNECT SCOTT
12 TABLE "BONUS"
13 CREATE TABLE "BONUS"
특이한 점은 11번 줄에 SCOTT 로 접속한 부분이 나옵니다. Export 를 수행할 때 분명히 system
계정으로 수행했지만 exp 유틸이 자동으로 USERS Tablespace 를 default tablespace 로 사용하는
schema가 scott 임을 찾아서 해당 계정으로 로그인 한 것입니다.
그 후에 해당 사용자가 USERS Tablespace 에 생성한 테이블들 단위로 exp 받게 됩니다.
여기서부터는 위에서 Tables 옵션으로 exp 받은 것과 동일한 순서로 진행이 됩니다.
즉 TABLESPACES 옵션을 주었다 하더라도 dmp 파일에서 확인되는 것과 같이 imp 할 때
Tablespace 를 생성하고 Table 을 만드는 것이 아니라는 뜻이며 imp 하길 원하시는 서버에
Tablespace 를 먼저 만들어 두고 imp 를 해야 한다는 뜻입니다.
심화3 clob data export/import 수행하기
Step 1. Clob 컬럼을 포함한 테스트 테이블 생성
SQL> create tablespace ts_lob
2 datafile '/home/oracle/oradata/testdb/ts_lob01.dbf' size 1M
Tablespace created.
SQL> create table scott.clobtest
2 (no number , name varchar2(10), contents clob)
3 lob(contents) store as (tablespace ts_lob);
Table created.
SQL> insert into scott.clobtest
2 values (1,'AAA','BBBBB');
-- 현재 LOB 목록 확인하는 방법
SQL> col owner for a10
SQL> col table_name for a10
SQL> col column_name for a10
SQL> col segment_name for a30
SQL> col tablespace_name for a10
SQL> select owner,table_name,column_name,segment_name,tablespace_name
2 from dba_lobs
3 where table_name='CLOBTEST'
OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE
---------- ------------------ -------------------- ----------------------------------- ----------------
SCOTT CLOBTEST CONTENTS SYS_LOB0000054504C00003$$ TS_LOB
Step 2. Export 수행
SQL> !
[oracle@localhost ~]$ exp scott/tiger tables=clobtest file=clobtest.dmp
Step 3. Import 수행
Import 를 수행하기 위해 기존 테이블과 테이블 스페이스(ts_lob)를 먼저 삭제하겠습니다.
테이블 스페이스 (ts_lob)를 삭제하는 이유는 해당 테이블스페이스가 없을 경우 import 시에 에러
가 발생함을 보여주기 위해서입니다.
SQL> drop table scott.clobtest purge;
Table dropped.
SQL> drop tablespace ts_lob including contents anddatafiles;
Tablespace dropped.
SQL>!
[oracle@localhost ~]$ imp scott/tiger file=clobtest.dmp fromuser=scott ignore=y
]$ exit
exit
SQL> create tablespace ts_lob
2 datafile '/home/oracle/oradata/testdb/ts_lob01.dbf' size 1M;
!
[oracle@localhost ~]$ imp scott/tiger file=clobtest.dmp fromuser=scott ignore=y
Import: Release 10.2.0.5.0 - Production on Tue Mar 1 07:41:33 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
import server uses KO16KSC5601 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "CLOBTEST" 1 rows imported
Import terminated successfully without warnings.
에러 없이 잘 수행됩니다.
이상으로 예전 버전부터 데이터 이동에 널리 사용되고 있는 exp/imp 에 대해서 살펴보았습니다