Export & Import
EXPORT 는 DATA를 ORACLE DATABASE로부터 ORACLE Binary 형태의 OS FILE로 만들고 IMPORT를 이용하여 DATABASE로 LOADING 한다.
Export
ORACLE DATABASE를 OS file로 만드는 작업이다. 이 작업은 DATABASE가 OPEN 되어있는 동안에 실시 할 수 있다.
EXPORT MODE
Export 작업을 수행 할 수 있는 방법으로는 아래의 3가지 방법으로 작업이 가능하다.
USER 어떤 유저소유의 모든 오브젝트를
TABLES 어떤 유저소유의 모든 혹은 특정 테이블만
FULL DATABASE 데이타베이스의 모든 오브젝트를
사용 예>
TABLES=table_lists 또는
OWNER=owner_lists 또는
FULL=Y
EXPORT TYPE
Export Type은 backup되는 양에 따라 3가지 방법이 유용하다. 그러나 가능하면 많은 시간이 소요되지 않는 경우라면 Complete로 작업하는 것이 바람직하다.
COMPLETE : 전체 데이타베이스를 EXPORT 한다.
CUMULATIVE : 마지막 “COMPLETE” 혹은 “CUMULATIVE” EXPORT 이후에 변경된 정보를 EXPORT 한다.
INCREMENTAL : 마지막 “COMPLETE”,”CUMULATIVE” 혹은 “INCREMENTAL” EXPORT 이후에 변경된 정보를 EXPORT 한다.
BACKUP 방법은 매월 1 일에 “COMPLETE”, 주말에 “CUMULATIVE”, 주중에는 “INCREMENTAL”로 하면 가장 적절하다.
INCREMENTAL DAILY
CUMULATIVE WEEKLY
COMPLETE MONTHLY
Export 하기 전에는 DISK용량이 충분한지를 먼저 CHECK 만약 DISK 가 모자라면 WRITE_FAILURE ERROR가 발생한다. TABLE_SIZE는 USER_SEGMENTS view에서 볼 수 있다
주> Export시에는 CLUSTER DEFINETION이나 ROLLBACK SEGMENT의 OPTIMAL option은 Export되지 않는다.
주> SYS 소유의 object는 Export되지 않기 때문에 어떠한 object라도 SYS의 유저로 만들지 말 것
주> sequence number는 Export 도중엔 access되지 않도록 할 것
EXPORT 작업 수행
Export 작업 방법
$ exp keyword=value 형태
ex> exp SCOTT/TIGER GRANTS=Y TABLES=(EMP, DEPT, MGR)
KEYWORD 내용
USERID username/password
BUFFER size of data buffer
FILE outfile(default 는 EXPDAT.DMP)
COMPRESS import into one exetent (Y)
GRANTS export grants(Y)
INDEXES export indexes(Y)
ROWS export data rows(Y)
CONSTRAINTS export constraints(Y)
LOG log file of screen output
FULL export entire file(N)
OWNER list of owner username
TABLES list of tablename
RECORDLENGTH length of IO record
INCTYPE incremental export type
RECORD track incr.export(Y)
PARFILE parameter file name
CONSISTENT cross-table consistent
STATISTICS analyze objects(ESTIMATE)
Parameter file을 이용하는 경우
$ exp username/password PARFILE=filename
위의 OPTION들을 PARAMETER FILE안에 묶어서 간단히 지정
ex>vi filename
SYSTEM/MANAGER
FULL=Y
FILE=DBA.DMP
GRANTS=Y
INDEXES=Y
Export 작업 Test
전체 데이타베이스 EXPORT (Interactive Method)
$ exp system/manager
Connected to: ORACLE7 Server Release 7.0.16.4.0 – Production
With the procedural and distributed options
PL/SQL Release 2.0.18.1.0 – Production
Enter array fetch buffer size : 4096 >(RETURN)
Export file : expdat.dmp >
(1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > e
Export grants (Y/N) : Y > y
Export table data (Y/N) : Y > y
Compress extents (Y/N) : Y > y
About to export the entire database….
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting role
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SYSTEM’s tables …
. about to export SCOTT’s tables …
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
전체 데이타베이스 EXPORT (Command Line Method)
$ exp userid=system/manager full=y compress=y grants=y rows=y
전체 데이타베이스 EXPORT ( Dynamic Method )
EXPORT PARAMETERS 을 다음과 같은 FILE(tusc.par) 형태로 만든다.
system/manager
full=y
compress=y
grants=y
rows=y
$ exp parfile=tusc.par
USER only EXPORT
$ exp system/manager
Connected to: ORACLE7 Server Release 7.0.16.4.0 – Production
With the procedural and distributed options
PL/SQL Release 2.0.18.1.0 – Production
Enter array fetch buffer size : 4096 >(RETURN)
Export file : expdat.dmp >
(1) E(ntire database), (2) U(sers), (3) T(ables) : (2) U > u
Export grants (Y/N) : Y > y
Export table data (Y/N) : Y > y
Compress extents (Y/N) : Y > y
About to export specified users
User to be exported: (RETURN to quit) > scott
. exporting snapshots
. exporting snapshot log
. exporting database links
. exporting sequence numbers
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SCOTT’s tables …
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
USER only EXPORT (Command Line Method)
$ exp userid=system/manager owner=scott compress=y grants=y rows=y
USER ONLY EXPORT ( Dynamic Method )
EXPORT PARAMETERS 을 다음과 같은 FILE(tusc.par) 형태로 만든다.
system/manager
owner=scott
compress=y
grants=y
rows=y
$ exp parfile=tusc.par
TABLES EXPORT
$ exp scott/tiger tables=emp,dept compress=y grants=y rows=y
INCREMENTAL EXPORT
INCREMENTAL EXPORT 는 SYS 혹은 SYSTEM USER로서 EXPORT 한다.만일 FILE 이름을 “expdat.dmp”에서 다른 이름으로 변경하고자 할 때는 COMMAND LINE에서 FILENAME 만 지정하면 된다.
$ exp system/manager inctype=complete file=com0304.dmp
$ exp system/manager inctype=incremental file=inc0305.dmp
주의: INCREMENTAL EXPORT 데이타양은 “NEW 혹은 UPDATE” 된 TABLE의 갯 수에 따라 크게 다를 수 있다. 즉 하나의 큰 TABLE 이 매일 UPDATE 되면 항상 EXPORT 되고 UPDATE 가 안되면 FULL EXPORT 의 1% 정도 될 수 있다. 따라서 이 방법은 모든 TABLE 이 매일 UPDATE 가 발생하면 비효율적인 방법이 된다.
Export/Import Q & A
RDBMS와 Export, Import의 연관관계는 (catexp.sql 이란) ?
Export, Import시 이미 생성된 오브젝트의 정보를 데이타 딕셔너리에서 쿼리를 하는데 이러한 오브젝트의 정보가 데이타 딕셔너리내의 여러 테이블에 나누어져 있다. 필요한 데이타 딕셔너리 정보를 편리하게 이용하기 위하여 여러 가지의 뷰를 catexp.sql에 Script되어 있다. 이 스크립트화일은 $ORACLE_HOME/rdbms/admin에 있으며 Install시 수행되도록 되어 있다.
Export시 오브젝트의 백업 순서는 있는가 ?
Export하는 오브젝트의 순서는 존재하며 이는 Oracle의 Version Up등에 의한 새로운 오브젝트가 만들어지거나 하면 Export되는 오브젝트의 순서는 변할 수 있다.
오브젝트의 Export순서는 다음과 같다.
Tablespaces
Profiles
Users
Roles
System Privilege Grants
Role Grants
Default Roles
Tablespace Quotas
Resource Costs
Rollback Segments
Database Links
Sequences( includes Grants )
Snapshots ( includes grants, auditing )
Snapshot logs
Job Queues
Refresh Groups
Cluster Definitions
Tables(includes grants,column grants,comments,indexes,constraints,auditing)
Referential Integrity
POSTTABLES actions
Synonyms
Views
Stored Procedures
Triggers
Default and System Auditing
Export시 BUFFER와 RECORDLENGTH는 무엇인가?
BUFFER : Export시 오브젝트내에 있는 여러 개의 Row가 한꺼번에 Fetch된다. 디스크에서 Fetch된 정보는 화일에 Write하기 전에 메모리를 거치게 되며, 이때 할당되는 메모리의 양이 Buffer 파라미터의 값이다.
RECORDLENGTH : 메모리에 있는 Export할 자료를 화일에 Write하기 위해 한번에 운반되는 양을 결정하는 파라미터이다.
[주의] 위의 BUFFER와 RECORDLENGTH는 O/S의 Block Size의 배수가 되도록 하는 것이 효율적이다.
다량의 Row를 Export, Import시 어느 정도의 Row가 처리되었는지 알 수 있는가?
알 수 있다. V 7.1까지는 다량의 Row를 Export, Import시 처리된 정도를 알 수가 없어 현재 작업 중인지 시스템이 Hang인지 파악되지 않았으나 V 7.2부터는 FEEDBACK 이라는 옵션을 이용하여 체크가 가능하다.
Export시 한번에 몇 개의 Row가 Fetch되는가?
한번에 Fetch되는 Row의 수는 Buffer Size와 연관 관계가 있다. 하나의 Row가 Export시 차지하는 양은 각 Column Size의 합 + 4 * (Column의 수)로 구할 수 있다. 한번 Fetch 되는 Row의 수는 Buffer Size / 한 Row의 Export시 Size이다. 이를 이용하면 Export된 Output File의 Size는 대략 한 Row의 Export시 Size * Row 수 이다.
Export, Import의 호환성은 어떻게 되는가?
Export, Import의 호환성은 Oracle의 버전과 직접적인 연관 관계를 갖고 있다.
호환성은 4가지로 나누어 설명할 수 있으며 이를 아래의 가정을 이용해 설명하겠다.
가정 A라는 기계에 Oracle V 7.0, B 라는 기계에는 Oracle V 7.1이 설치되어 운영 중이라 가정하자. Oracle V7.0을 X라 하고 Oracle V7.1을 Y라고 하자.
§ Base Compatibility : X의 exp를 이용해 X DB를 export하여 X의 imp를 이용해 X DB에 import하는 것을 말한다. 이는 당연히 지원한다.
§ Upward Compatibility : X의 exp를 이용해 X DB를 export하여 Y DB에 Y의 imp를 이용해 import하는 것을 말한다. 이도 Oracle에서는 지원한다.
§ Downward Compatibility : Y exp를 이용해 Y DB를 export 하여 X DB에 X의 imp로 import하는 것을 말한다. 이는 지원될 수도 안될 수도 있다.
§ Cross Compatibility : X exp를 이용해 Y DB를 export (SQL*Net 이용)하여 X 또는 Y DB에 import(imp는 적정한 것을 활용)하는 것을 말한다. 이는 지원될 수도 안될 수도 있다.
어떤 경우에 Downward Compatibility가 실패하는가?
V7.2에 hash cluster expressions라는 옵션이 있는데, 이를 이용해서 클러스터를 생성하여 사용 후 export한 것을 V7.0 또는 V7.1로 downward시 create cluster문에 옵션이 맞지않아 실패하게 된다.
EXP-37 에러(export views not compatible with database version) 발생의 원인은 무엇인가 ?
이 에러는 Cross Compatibility에서 발생하는 문제로 이는 Export가 이용하는 View(Catexp.sql에 의해 생성된)가 Oracle Version내에 일치하지 않아 발생한 문제로 이를 해결하기 위해 Exp에서 이용 가능한 View를 설치한다.
Full Export는 Dba 권한을 갖고 있는 유저만 할 수 있는가 ?
Version 6에서는 DBA권한을 갖고 있는 이만 Full Export를 할 수 있으며, V7에서는 DBA가 아니더라도 EXP_FULL_DATABASE Role이 Grant되면 Full Export가 가능하다.
테이블 Import 시에 디폴트 테이블스페이스가 아닌 곳으로 들어가는 경우는 왜 발생하는가?
예를 들어서 scott 유저의 디폴트 테이블 스페이스가 users 인데 임포트를 해보면 tools 테이블 스페이스에 테이블이 만들어졌다고 하자. 그 이유는 다음과 같다. 즉, 임포트 하는 테이블이 원래 tools 테이블 스페이스에 있었고 scott가 현재 tools 테이블스페이스에 대한 Quota 를 가지고 있거나 아니면 Unlimited Tablespace 권한(Resource Role 에 포함)을 부여 받았기 때문이다.
Import시에 테이블을 디폴트 테이블스페이스에 만들려면 디폴트 테이블 스페이스 외의 테이블 스페이스에 대한 모든 Quota를 0로 만들고 Unlimited Tablespace 권한을 Revoke 시킨 다음에 임포트를 수행해야 한다. 그리고, 디폴트 테이블스페이스에 대한 Quota만 Unlimited로 한다. 예를 들면 다음과 같다.
$ sqlplus system/manager
SQL> alter user scott
quota 0 on system
quota 0 on tools
…….
quota 0 on data
quota unlimited on users;
SQL>revoke unlimited tablespace from scott;
이렇게 한 다음 Import를 수행하면 된다. 물론 유저를 만들 때 quota를 주지 않은 테이블스페이스는 상관 없으며 Unlimited Tablespace 권한(또는 Resource Role) 을 주지 않았다면 Revoke 명령도 사용할 필요가 없다.
Import 시에 Core Dump/Segmentation Fault 가 발생하는 경우
오라클에는 Character Set 이라고 하는 부분이 있다. 국내에서는 US7ASCII 또는 KO16KSC5601 을 주로 사용하는데 Export 받은 곳과 Import 하는 곳의 Character Set 이 다르면 Import 시에 Core Dump 가 발생하거나 원인 불명의 에러가 발생하면서 임포트가 중단되는 경우가 발생한다.
이 경우에는 Export 받은 dump file 을 convert 프로그램을 이용하여 Import 하는 곳의 Character Set으로 변환시킨 다음 임포트를 하는 방법이 있고, 아니면 어느 한 쪽 DB 의 Character Set 자체를 바꿔서 동일하게 맞춘 다음 Export/Import 하는 방법이 있다. 이중에서 Convert 프로그램을 이용하는 방법이 간단한데 이 프로그램은 Unix 상에서 cc로 컴파일 하여서 사용하면 된다. 프로그램 소스는 천리안에서 go oracle 하여 정보보물창고에서 구할 수 있다.
IMPORT시 Table과 Index를 분리하여 저장하는 방법
Export/Import를 이용하여 Data를 옮기거나 Space를 정리할 때 Table과 Index를 각각 다른 Tablespace에 분리하여 저장하기도 한다. 그러한 방법은 Oracle의 속도를 향상시키는 데 많은 도움을 준다.
Import할 때 Table과 Index를 생성시켰던 Script를 가지고 있으면 편리하다.
먼저 Import할 때 Indexes=N option을 사용하여 Table만 Import하고, Index는 Script에서 Tablespace를 지정하고 SQL file을 실행시켜서 생성하면 된다. 그러나 Index Script를 가지고 있지 않을 때는 문제가 복잡해진다.
그러한 경우에 Import Option중에서 Index file Option을 사용하여 Index script를 만들어 낼 수가 있다. 이 방법은 Index file만 만들기 때문에 속도가 빠르고, Index 뿐만이 아니라 Table Script도 만들어 지기 때문에 유용하다.
다음 작업은 Scott User의 모든 Data를 Test User로 옮기기 위해서 Export/Import를 이용하려고 한다. Import시에 Table과 Index는 Tablespace를 다르게 하여 저장한다.
1. 다음과 같이 Scott User를 Export 받는다.
$ exp scott/tiger file=scott.dmp
2. Export받은 File에서 Index를 제외한 나머지만 Import한다.
$ imp system/manager file=scott.dmp fromuser=scott touser=test indexes=n commit=y
위와 같이 실행하면 Test User에는 Index를 제외한 나머지 Object만 Import된다.
3. 그리고 다음과 같이 Indexfile Option을 이용하여 Index Script를 만든다.
$ imp system/manager file=scott.dmp indexfile=index.sql
위와 같이 명령을 실행하면 index.sql이라는 File이 만들어진다. 그리고 그 화일을 VI 등으로 열어서 확인해 보면 create table 문장과 create index문장이 있고 Create Table 문장은 REM으로 막혀 있어서 결과적으로 Create Index문만 실행할 수 있도록 되어 있다.
이 화일에서 Create Index문의 Tablespace만 바꾸어서 다음과 같이 SQL*Plus에서 실행시킨다.
$ sqlplus test/test
SQL> @index
이렇게 하여 Table과 Index를 다른 Tablespace로 분리하여 저장할 수 있다.
이 Indexfile option은 Index만이 아니라 Table 생성 문장이 필요할 때도 유용하게 사용하여 활용할 수 있다.
Export시에 Export File 압축하는 방법
1. Root에서 먼저 NAMED PIPE를 생성합니다.
# mknod /dev/PIPE.dmp p
2. Oracle Home으로 login하여 Export Script를 생성합니다.
$ vi compress_exp
nohup exp system/manager full=y compress=y file=/dev/PIPE.dmp &
compress < /dev/PIPE.dmp > /user/expfull.z &
3. Export 실행과 Log 확인
$ compress_exp
$ tail – f nohup.out
4. Import Script 를 생성합니다.
$ vi uncompress_exp –
uncompress < /user/expfull.z > /dev/PIPE.dmp &
nohup imp system/manager file=/dev/PIPE.dmp commit=y &
5. Import 실행과 Import Log의 확인
$ uncompress_exp 실행
$ tail -f nohup.out
Import
DATA IMPORT 는 EXPORT된 file을 이용하여 database를 복구하는 작업을 말한다. 항상 EXPORT 된 FILE 로만 IMPORTING이 된다.
Import 작업 Test
전체 데이타베이스 EXPORT FILE로부터 SINGLE TABLE 을 IMPORT (INTERACTIVE MODE)
$ imp system/manager
Connected to: ORACLE7 Server Release 7.0.16.4.0 – Production
With the procedural and distributed options
PL/SQL Release 2.0.18.1.0 – Production
Import file: expdat.dmp > (RETURN)
Enter insert buffer size (minimum is 4096) 30720 > (RETURN)
Export file created by EXPORT:V07.00.16
List contents of import file only (yes/no) : no > “yes” 이면 data는 import 되지않고 Display 만 됨
Ignore create errors due to object existence (yes/no) : yes > (“yes” : 만일 table이 존재하면
record 가 추가된다. “no” : 이미 존재한
Object에 대해서 import를 하지않는다.)
Import grants (yes/no) : yes >
Import table data (yes/no) : yes >
Import entire export file (yes/no) : yes > no
User name : SCOTT
Enter table name. Null list name all tables for user
Enter table name or . if done : TEST
importing SCOTT’s objects into SCOTT
importing table TEST 900 rows imported
Import terminated successfully.
주의 : IMPORT 작업은 보통 생성되는 INDEX 수에 따라 1.5 – 4 배의 시간이 걸리는데 EXPORT 는 “CREATE INDEX ….” 문만을 FILE에 쓰고 IMPORT 시에는 INDEX를 생성하기 때문이다.
전체 데이타베이스 EXPORT FILE 로부터 SINGLE TABLE 을 IMPORT (OMMAND LINE MODE)
$ imp system/manager full=n grants=y rows=y tables=test commit=y
주의 : IMPORT 시는 데이타를 DB 에 WRITE 하기 때문에 ROLLBACK SEGMENT가 사용된다. commit=y 는 row 만큼 데이타를 LOAD 하고 나서 COMMIT 을 하기 때문에 ROLLBACK SEGMENT 가 커지는 것을 피할 수 있다. commit=n 이면 TABLE 단위로 IMPORT 하고 나서 COMMIT 이 되기 때문에 ROLLBACK SEGMNET 확장 ERROR 가 발생 할 수 있다.
INCREMENTAL IMPORTING
EXPORT 된 FILE 이 다음과 같다고 하면
F : full C : cumulative I : incremental
F1, I1, I2, C1, I3, I4, C2, I5, I6
1. 데이타베이스를 다시 생성한다.(CREATE DATABASE 문장을 사용)
2. IMPORT 하기 위해 충분한 ROLLBACK SEGMENT를 추가한다.
3. 가장 최근 KEY 정보를 IMPORT 한다.
$ imp system/manager inctype=SYSTEM full=y file=I6
4. 가장 최근 COMPLETE로 EXPORT 한 FILE 을 IMPORT 한다.
$ imp system/manager inctype=RESTORE full=y file=F1
5. 모든 CUMULATIVE IMPORT를 순서적으로 실행한다.
$ imp system/manager inctype=restore full=y file=C1
$ imp system/manager inctype=restore full=y file=C2
6. INCREMENTAL IMPORT를 순서적으로 실행한다.
$ imp system/manager inctype=restore full=y file=I5
$ imp system/manager inctype=restore full=y file=I6