[exp / imp]
oracle에서 제공하는 논리 백업 및 적재 방식.
oracle 버전, 플랫폼(서버, os종류)이 다른 경우도 지원 가능
1) exp
– 논리 백업 수행
– 덤프 파일 형태로 백업 (바이너리 파일)
– 백업 방식 : 테이블/스키마/테이블스페이스/전체(full) 단위로 백업 가능하다.
– 전송 모드 : conventional mode / direct mode
2) imp
– exp로 백업받은 덤프파일을 적재하는 기능.
[exp]
논리 백업을 수행하는 명령어.
$ exp -help
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export[imp]
논리 백업을 받은 덤프 파일을 다시 DB에 적재하는 명령어
$ imp -help
Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input files (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output CONSTRAINTS import constraints (Y)
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
FEEDBACK display progress every x rows(0)
TOID_NOVALIDATE skip validation of specified type ids
FILESIZE maximum size of each dump file
STATISTICS import precomputed statistics (always)
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
COMPILE compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION import streams general metadata (Y)
STREAMS_INSTANTIATION import streams instantiation metadata (N)
DATA_ONLY import only data (N)
VOLSIZE number of bytes in file on each volume of a file on tape[table mode export 받은 dump file import 수행]
환경) 12c db1 -> 12c copydb
table mode : 특정 테이블만 논리 백업 받기.
1. 테이블 생성 및 데이터 삽입 – oel7 db1에서
create table scott.exp_test1(no number, name varchar2(10));
insert into scott.exp_test1 values(1, 'aaa');
insert into scott.exp_test1 values(2, 'bbb');
insert into scott.exp_test1 values(3, 'ccc');
commit;2. exp 수행
-- copy 쪽이랑 oel7 쪽 둘 다 mkdir 해주기
mkdir datapump
cd datapump
exp system/oracle file=test1.dmp log=test1.log tables=scott.exp_test1 statistics=none3. dump file 전송 (oel7->copy)
scp test1.* oracle@copy:datapump4. imp 수행 – copy서버에서
imp system/oracle file=test1.dmp log=test1.log full=y
-- warning 뜨는 건 무시해도 됨.
-- 12cR2부터 발생하는 warning으로, re-create 해야하는 object들 중 sys계정을 필요로 하는 쿼리들만 모아서 test1_sys.sql이라는 파일로 만들어줬다라는 의미. sys로 imp 수행하지 않는 이상 항상 뜨는 warning임.
-- 데이터 조회
select * from scott.exp_test1;[table mode exp/imp 시 전송 범위]
전송 범위에 대한 문서 : https://docs.oracle.com/cd/A58617_01/server.804/a58244/ch01.htm#20622
index, constraint -> 전송 가능
view, synonym -> 전송 불가능
1. 테이블 생성 및 데이터 삽입 – oel7 db1에서
create table scott.exp_test2(no number, name varchar2(10));
insert into scott.exp_test2 values(1, 'aaa');
insert into scott.exp_test2 values(2, 'bbb');
insert into scott.exp_test2 values(3, 'ccc');
commit;
-- 1. pk (no) 생성
alter table scott.exp_test2 add constraint exp_test2_pk_no primary key (no);
-- 2. not null (name) 생성
ALTER TABLE scott.exp_test2 MODIFY name NOT NULL;
-- not null 속성은 dba_tab_columns 뷰에 기록된다.
select * from dba_constraints where table_name='EXP_TEST2';
-- not null 속성은 add constraint가 아니라 modify. 근데 강제로 제약조건의 형태로 만들 수는 있다.
alter table scott.exp_test2 add constraint name_not_null check (name is not null);
-- 근데 웬만하면 이렇게 추가하지 말아라. dba_tab_columns 뷰를 조회했을 때 check라서 상태여도 NULLABLE 컬럼이 N이 아닌 Y로 표시되기 때문에 혼동의 여지가 생긴다.
-- 3. index 생성 (name)
create index scott.index_exp_test2 on scott.exp_test2(name);
select * from dba_indexes where table_name='EXP_TEST2';
-- 4. synonym 생성 (public, private)
CREATE PUBLIC SYNONYM pub_synonym_exp_test2 FOR scott.exp_test2;
CREATE SYNONYM synonym_exp_test2 FOR scott.exp_test2;
select * from dba_synonyms where table_name='EXP_TEST2';
-- 5. view 생성
create view scott.view_exp_test2
as
select * from scott.exp_test2;
select * from dba_views where view_name='VIEW_EXP_TEST2';
-- 6. 권한 부여
CREATE USER hr IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA unlimited ON users;
grant select on scott.exp_test2 to hr;2. exp 수행 – table mode
cd datapump
exp system/oracle file=test2.dmp log=test2.log tables=scott.exp_test2 statistics=none3. dump file 전송 (oel7->copy)
scp test2.* oracle@copy:datapump4. imp 수행 – copy서버에서
imp system/oracle file=test2.dmp log=test2.log full=y
-- 기존에 table 존재할 때 실행하려면 truncate table 또는 drop table 후에 ignore=y 붙여서 imp수행.
-- 기존 table에 제약조건이 있어서 넣지 않도록 하려면 constraints=n 붙여서 수행.
imp system/oracle file=test2.dmp log=test2.log full=y ignore=y
-- 데이터 조회
select * from scott.exp_test2;
select * from dba_constraints where table_name='EXP_TEST2';
select * from dba_indexes where table_name='EXP_TEST2';
select * from dba_synonyms where table_name='EXP_TEST2';
select * from dba_views where view_name='VIEW_EXP_TEST2';
select * from dba_tab_privs where table_name='EXP_TEST2';
-- PK, not null, index, 권한은 복사됨.
-- synonym이랑 view는 안 복사됨.[user mode exp imp – 동일한 유저 생성]
** 결과
1. 유저를 새로 생성해주지 않음. user mode로 백업 시 target DB에 해당 user가 존재하지 않는 경우 에러 발생. -> 동일한 user 생성하거나, fromuser/touser 사용하여 다른 user로 imp 수행.
2. user mode 백업 대상 : table, index, constraints, privilege, private synonym, view
– public synonym은 복사 안됨.
1. 새로운 user 생성 – oel7 db1에서
-- 존재하는 user 확인
select username from dba_users;
-- user 생성
create user itwill2 identified by oracle
quota unlimited on users;
grant create session to itwill2;
grant create synonym to itwill2;2. 테이블 생성 및 데이터 삽입 – oel7 db1에서
create table itwill2.exp_test3(no number, name varchar2(10));
insert into itwill2.exp_test3 values(1, 'aaa');
insert into itwill2.exp_test3 values(2, 'bbb');
insert into itwill2.exp_test3 values(3, 'ccc');
commit;
-- 1. pk (no) 생성
alter table itwill2.exp_test3 add constraint exp_teset3_pk_no primary key (no);
-- 2. not null (name) 생성
ALTER TABLE itwill2.exp_test3 MODIFY name NOT NULL;
-- 3. index 생성 (name)
create index itwill2.index_exp_test3 on itwill2.exp_test3(name);
-- 4. synonym 생성 (public, private)
CREATE PUBLIC SYNONYM pub_synonym_exp_test3 FOR itwill2.exp_test3;
-- db1서버에 itwill2로 접속해서 수행하기.
CREATE SYNONYM synonym_exp_test3 FOR itwill2.exp_test3;
-- 5. view 생성
create view itwill2.view_exp_test3
as
select * from itwill2.exp_test3;
-- 6. 권한 부여
grant select on itwill2.exp_test3 to hr;2. exp 수행 – table mode
cd datapump
exp system/oracle file=test3.dmp log=test3.log owner=itwill2 statistics=none3. dump file 전송 (oel7->copy)
scp test3.* oracle@copy:datapump4. imp 수행 – copy서버에서
imp system/oracle file=test3.dmp log=test3.log full=y ignore=y
-- 유저가 없다고 에러 발생
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
-- 동일한 itwill2 유저 생성 후 다시 imp 수행
create user itwill2 identified by oracle
quota unlimited on users;
grant create session to itwill2;
imp system/oracle file=test3.dmp log=test3.log full=y ignore=y
-- 성공
-- 데이터 조회
select * from itwill2.exp_test3;
select * from dba_constraints where table_name='EXP_TEST3';
select * from dba_indexes where table_name='EXP_TEST3';
select * from dba_synonyms where table_name='EXP_TEST3';
select * from dba_views where view_name='VIEW_EXP_TEST3';
select * from dba_tab_privs where table_name='EXP_TEST3';
-- PK, not null, index, private synonym, view, 권한은 복사됨.
-- public synonym은 복사 안됨.
-- public synonym 조회(db1) 및 생성(copydb)
select 'create or replace public synonym '||table_name||' for '||table_owner||'.'||table_name||';'
from dba_synonyms
where table_owner='ITWILL2'
and owner='PUBLIC';
create or replace public synonym EXP_TEST3 for ITWILL2.EXP_TEST3;
-- 위 쿼리는 copydb에서 수행[ exp imp – tablespace 불일치 ]
table/user mode imp 시에 해당 tablespace가 없는 경우 무슨 일이 일어나는지 확인하는 목적.
-> 테이블 소유자의 default tablespace에 저장.
1. test03 tablespace 생성 – oel7에서 수행
CREATE TABLESPACE test03 DATAFILE '/home/oracle/oradata/db1/test03.dbf' SIZE 100M;2. itwill3 user 생성 (default tablespace test)
create user itwill3 identified by oracle
default tablespace test03
quota unlimited on test03;3. itwill3 소유의 table 생성
create table itwill3.exp_test4(no number, name varchar2(10));
insert into itwill3.exp_test4 values(1, 'aaa');
insert into itwill3.exp_test4 values(2, 'bbb');
insert into itwill3.exp_test4 values(3, 'ccc');
commit;4. exp & scp로 dump file 전송
cd datapump
exp system/oracle file=test4.dmp log=test4.log owner=itwill3 statistics=none
-- scp로 dump file 전송
scp test4.* oracle@copy:datapump5. itwill3 user 생성 후 imp
create user itwill3 identified by oracle
quota unlimited on users;
imp system/oracle file=test4.dmp log=test4.log full=y ignore=y6. 데이터 확인
-- 어떤 tablespace에 값들이 입력되었는지 확인
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name = 'EXP_TEST4';
-- USERS tablespace에 들어가졌다. -> 테이블 소유자의 default tablespace로 들어간다.[exp imp – user 불일치 imp]
user 정보 불일치 상황에서 imp 수행.
fromuser/touser 옵션을 사용하여 다른 user에 적재
1. user 생성 (itwill4) – oel7 db1에서
create user itwill4 identified by oracle
quota unlimited on users;2. table 생성
create table itwill4.exp_test5(no number, name varchar2(10));
insert into itwill4.exp_test5 values(1, 'aaa');
insert into itwill4.exp_test5 values(2, 'bbb');
insert into itwill4.exp_test5 values(3, 'ccc');
commit;3. exp
exp system/oracle file=test5.dmp log=test5.log owner=itwill4 statistics=none4. 원격 imp
imp system/oracle@copy file=test5.dmp fromuser=itwill4 touser=scott
-- copy라는게 oel7쪽의 tnsnames.ora에 선언되어 있어야 함.5. 데이터 조회
select owner from dba_tables where table_name='EXP_TEST5';
select * from scott.exp_test5;