# Export와 Import 유틸리티는 DB가 Open된 상태에서만 사용 가능합니다.
그리고 export와 import는 OS 기종이나 오라클 버전이 달라도 데이터를 이동시킬수 있기 때문에 이식성이 아주 좋습니다.
단 export와 import는 데이터가 많을 경우 물리적인 백업보다 시간이 많이 걸릴 수 있습니다.
실제로 export의 단점 중 하나가 시간이 많이 소요된다는 것입니다.
##### EXPORT ###### 우리가 full export를 수행하면 수많은 오브젝트를 export 하게 되는데 아래의 순서로 해당 object를 export 합니다.
1. Tablespaces
2. Profiles
3. Users
4. Roles
5. System Privilege Grants
6. Role Grants
7. Default Roles
8. Tablespace Quotas
9. Resource Costs
10. Rollback Segments
11. Database Links
12. Sequences (includes Grants)
13. Snapshots (includes grants, auiting)
14. Snapshot logs
15. Job Queues
16. Refresh Groups
17. Cluster Definitions
18. Tables(includes grants, column grants, comments, indexes, constraints, auditing)
19. Referential Integrity
20. POSTTABLES actions
21. Synonyms
22. Views
23. Stored Procedures
24. Triggers
25. Default and System Auditing
# Conventional Path export 와 Direct Path export
- Exports는 conventional path export와 direct path export로 구분되며 기본값은 conventional Path export입니다.
- 위 그림에서 알 수 있듯이 conventional path export 는 export 명령어가 수행되면 Export client가 메모리에 evaluation buffer라는 곳을 만들고 DB buffer cache 에 있는 데이터를 evaluation buffer로 가졍ㄴ 후 이곳이 ㄷ 차면 다시 디스크에 파일로 저장합니다.
이렇게 하는 이유는 direct path load는 원본 테이블에 여러 process가 동시에 접근을 못하는 단점이 있는데 conventional path load는 그 부부늘 해결할 수 있습니다.
그 외에도 동시성 문제 등에서 direct path가 문제가 있어서 오라클은 기본적으로 여러 사람이 동시에 사용하는 DB이기 때문에 conventional path 모드로 작동하게 되어 있습니다.
그러나 conventional path는 direct path에 비해 과정이 다소 복잡하기 때문에 속도가 저하 됩니다.
1. conventional Path로 Full export 받기(기본모드)
# time exp system/admin full=y log=/export/home/oracle/data/backup/dmp/full_log.log file=/export/home/oracle/data/backup/dmp/full_test01.dmp
2. Direct Path로 Database Full export 받기
# time exp system/admin full=y log=/export/home/oracle/data/backup/dmp/full_log.log file=/export/home/oracle/data/backup/dmp/full_test02.dmp direct=y
3. export를 저장하는 백업파일을 분할해서 받기
- Export를 수행하다 보면 백업파일 하나가 너무 크게 받아져서 후속 작업이 어려운 경우가 종종 있습니다.
여기서는 큰 파일 하나를 여러 개의 작은 파일로 분할해서 받도록 합니다.
# time exp system/admin full=y
file=(/export/home/oracle/data/backup/dmp/full_test03_1.dmp ,\
/export/home/oracle/data/backup/dmp/full_test03_2.dmp ,\
/export/home/oracle/data/backup/dmp/full_test03_3.dmp ,\
/export/home/oracle/data/backup/dmp/full_test03_4.dmp )\
filesize=100M direct=y
4. 특정 테이블 스페이스만 받기
# time exp system/admin file=/export/home/oracle/data/backup/dmp/full_test04.dmp tablespaces=(scott_data, undotbs1);
5. 특정 사용자 백업 받기(여러 사용자를 동시에 백업)
# time exp system/admin file=/export/home/oracle/data/backup/dmp/scott_hr.dmp owner=(scott,sms);
6. evaluation Buffer 값을 조정하면서 특정 테이블만 export 수행
1) evaluation Buffer 값을 설정하지 않고 export 수행
# time exp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_01.dmp tables=test;
2) evaluation Buffer 값을 1M로 설정 후 export 수행
# time exp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_01.dmp tables=test buffer=1024000;
7. parameter file을 이용한 export 수행
# vi par_full.dat
file=/export/home/oracle/data/backup/dmp/scott_par.dmp
full=y
direct=y
# exp system/admin parfile=par_full.bat
8. 특정 조건만 export 받기 - query 옵션 사용하기(8i부터 사용 가능)
- emp 테이블에서 첫글자가 F인 사람만 export 받기
(OS에서 사용하는 ', ", < 등의 문자를 쓸 경우 \(escape 문자)를 꼭 써줘야 합니다.)
# exp scott/tiger query=\"where ename like \'F%\'\" tables=emp file=/export/home/oracle/data/backup/dmp/scott_emp.dmp
- parameter file에서 query 옵션을 사용하기 - escape 문자 안 써도 됨
# vi par_q.dat
table=emp query="where job='CLERK' and sal > 1000"
:wq!
# exp scott/tiger parfile=par_q.dat
## 일반 사용자 scott 가 어떤 롤을 가지고 있는지 확인
SQL> SELECT *
2 FROM DBA_ROLE_PRIVS
3 WHERE GRANTEE='SCOTT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT RESOURCE NO YES
SCOTT DBA NO YES
SCOTT TROLE NO YES
SCOTT CONNECT NO YES
SQL> GRANT EXP_FULL_DATABASE TO SCOTT;
SQL> SELECT *
2 FROM DBA_ROLE_PRIVS
3 WHERE GRANTEE='SCOTT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT RESOURCE NO YES
SCOTT DBA NO YES
SCOTT TROLE NO YES
SCOTT EXP_FULL_DATABASE NO YES
SCOTT CONNECT NO YES
##### IMPORT #####- Import는 export를 수행해서 만든 파일을 다시 데이터베이스로 넣는 작업을 수행합니다.
그래서 export와 거의 옵션이 비슷합니다.
- import 수행
Import 작업은 DDL과 DML을 수행하는 것이므로 Redo log와 undo segment를 사용하게 됩니다.
따라서 대량의 데이터를 import 할 때는 반드신 큰 undo tablespace를 준비해서 작업하여야 합니다.
만약 undo tablespace의 용량이 부족할 경우에는 마지막에 에러가 나면서 전부 rollback 될 수도 있습니다.
이런 위험을 줄이려면 import 할 때 commit=y로 변경하고 import를 수행하면 array 단위로 commit을 수행하기 때문에 전체가 rollback되는 현상을 막을 수 있습니다.
또한 DBA로 export를 받은 파일은 반드시 DBA로 import 해야 합니다.
- 특정 사용자의 데이터만 import 수행
# imp system/admin file=/export/home/oracle/data/backup/dmp/scott.dmp fromuser=scott tables=emp ignore=y
- scott 사용자의 test02 테이블을 hr 사용자 소유로 변경하기
* 테스트 테이블 생성
SQL> create table test02(no number, addr varchar2(10));
Table created.
SQL>
SQL> 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 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test02;
COUNT(*)
----------
1000
SQL>
exp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_test02.dmp tables=test02
imp system/oracle file=/export/home/oracle/data/backup/dmp/scott_test02.dmp fromuser=scott touser=hr ignore=y
- 실제 데이터는 import 하지 않고 DDL 문장만 추출하기
두 가지 방법이 있는데 한 가지는 show=y 옵션을 사용하는 것이고, 한가지는 indexfile 옵션을 사용하는 방법입니다.
import 옵션 중에 show=y 라고 주면 export 파일의 모든 내용들이 보여지는데 log 옵션을 주면 그 내용들이 전부 로그 파일에 저장됩니다.
그 후에 그 로그 파일을 편집하면 됩니다.
# imp system/admin file=/export/home/oracle/data/backup/dmp/scott_test02.dmp full=y show=y log=/export/home/oracle/data/backup/dmp/scott_test02.log
- import 할 때 테이블과 index를 분리해 내기
오라클 권장 사항은 테이블과 인덱스는 서로 다른 테이블 스페이스에 저장해야 성는이 좋아집니다.
그런데 그렇지 못한 경우가 많아서 export 후 import 할 때 같은 테이블 스페이스에 있던 테이블과 인덱스를 다른 테이블 스페이스로 분리하려고 합니다.
만약 인덱스 생성 스크립트가 있다면 import 할 때 indexes=n 옵션으로 import를 해서 import한 후 나중에 인덱스 스크리브를 수정해서 생성될 테이블 스페이스로 변경하고 실행하면 인덱스가 간단히 분리될 수 있습니다.
그러나 인덱스 생성 스크립트가 없다면 좀 더 복잡해집니다.
이럴 경우는 import 옵션 중에 indexfile이라는 옵션을 사용하면 인덱스 생성 스크립트를 만들어 줍니다.
이 스크립트에는 테이블 생성 스크립트까지 포함되어 있습니다.
아래 스크립트에서 테이블 생성하는 부분의 REM은 주석이란 뜻이며 필요하면 REM을 삭제한 후 사용하면 됩니다.
SQL> create index idx_test02_addr on test02(addr);
Index created.
SQL>
# exp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_test02_idx.dmp tables=test02
# imp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_test02_idx.dmp indexfile=/export/home/oracle/data/backup/dmp/scott_test02_idx.sql full=y
# cat scott_test02_idx.sql
REM CREATE TABLE "SCOTT"."TEST02" ("NO" NUMBER, "ADDR" VARCHAR2(10))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM "SCOTT_DATA" LOGGING NOCOMPRESS ;
REM ... 1000 rows
CONNECT SCOTT;
CREATE INDEX "SCOTT"."IDX_TEST02_ADDR" ON "TEST02" ("ADDR" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE "SCOTT_DATA" LOGGING ;
-- 위 내용에서 원하는 테이블 스페이스로 수정해서 실행하면 됩니다.
# export와 import에 대한 일반적인 굼금증들
Q1) Export 시 화면에 내용이 안 나오게 하는 방법 -- 여기서는 RedHat Linux를 기준으로 함
A1) exp scott/tiger file=/export/home/oracle/data/backup/dmp/scott_test02_idx.dmp tables=test02 > /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로 가져온 후 다른 부가 작업을 해서 OS 파일로 저장을 하게 됩니다.
여기서 Buffer라 evaluation Buffer크기를 결정하는 파라미터이고 recordlength는 buffer의 내용을 OS 파일로 내려쓸 때 사용하는 레코드의 크기를 결정하는 파라미터 입니다.
이 두가지 파라미터의 크기는 OS 블록의 배수로 설정하시는 것이 좋습니다.
그리고 DB bufer cache의 내용을 evaluation bufferㄹ 가져올 때 한 건씩 가져오는 것이 아니라 여러 건의 데이터를 한꺼번에 가져오게 되는데 그것을 Array fetch라고 합니다.
Q3) 큰 데이터를 export 또는 import 할 때 화면이 정지된 것 같이 보이는데 입력되고 있는지 아닌지 알 수 있는 방법
A3) export / import 옵션중에 feedback이라는 옵션이 있습니다.
feedback=정수값 이렇게 사용하며 진행되는 과정을 눈에 보여줍니다.
Q4) export 할 때 한 건씩이 아니라 Array fetch로 추출된다고 하는데 한 번에 몇 개의 Row 가 추출되나요?
A4) 이 값은 Bufer(evaluation buffer) 크기와 관련이 있습니다.
하나의 Row가 export 시 차지하는 양은 각 column 크기의 합 + 4 X (column 개수)로 구해집니다.
이 자료를 기준으로 해서 한 번 Fetch될 때 Row 수는 Buffer size / 1 row size 입니다.
이를 이용하면 export 된 파일의 총 크기는 1row size X row 수 입니다.
Q5) import 를 수행하다 보면 가끔 core dump/segmentation fault가 발생합니다.
원인은 무엇이며 해결은 어떻게 하나요?
A5) 오라클은 데이터를 저장하기 때문에 character set 이라는 것이 있습니다
국내는 주로 US7ASCII 또는 KO16KSC5601, KO16MSWIN949를 사용하는 데 export 받은 DB와 import 하는 DB의 캐릭터 셋이 다를 경우 위와 같은 에러가 발생합니다.
해결방법은 캐릭터 셋을 일치시킨 후 export를 받는게 정석이지만 이미 받아버린 덤프파일이라면 convert 프로그램을 이용하여 import 하는 곳의 캐릭터 셋과 동일하게 변경한 후 import를 수행하면 됩니다.
참고) 오라클 캐릭터 셋 종류
한글을 지원하는 캐릭터 셋은 아래와 같이 4가지가 있습니다. 각각의 특생이 다르므로 잘 확인하고 사용해야 합니다.
1. KO16KSC5601 : 완성형 한글. 한글 2350 글자, 한자: 4888 글자, 히라카나, 카티카나, 영문, 특수기호
2. KO16MSWIN949 : 확장완성형. 위 1번을 모두 포함하고 8822 글자 추가됨.
3. UTF8 / AL32UTF8 : 가변길이 한글. 현대 한글 11172 글자 표현 가능함.
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 시에도 한번에 1row씩 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) 에러 납니다.
자료 출처 : 서진수 지음 - 원리부터 실무까지 오라클 백업과 복구
p4198954_40_LINUX.zip
openmotif21-2.1.30-11.rhel4.6.i386-ppolok81.rpm