//-- Oracle DB 의 Datapump 를 이용한 DB 간 Data 이관
//-- Draft 버전
1. 개요
-- 필요한경우
> Oracle DB 간의 Data Migration
> 사례1) 개발DB에 있는 특정한 Schema의 모든 Object들을 운영DB로 모두 옮기는 경우 등
-- 제약사항
> Oracle DB 10.2.0.X 이상 (11.1.0.X 이상 권고) => 해당 버전 이상에서 제공하는 datapump 를 사용
2. 전체적인 가이드
A. Datapump 를 떨어뜨릴 Directory를 소스/타켓 양쪽에 생성
B. Datapump 명령어로, 해당 Data 를 OS File 형태로 추출
C. FTP 로 타켓서버로 전송
D. Datapump 명령어로, 해당 OS File 을 DB에 적재
3. 세부방법
A. (소스/타켓 양쪽 DB에) Directory 설정
-- 기존 Directory 중 적당한 것을 사용해도 되고, 새로 만들어 사용해도 됨
-- DB 관리user 인 system 을 사용하는 것이 무난함 => 그렇지 않은 경우, 일반 User 에 필요한 권한을 설정 필요
-- 기존 Directory 조회
-- 아래의 경우, DATA_PUMP_DIR 가 사용하기에 무난
SQL> set linesize 350 SQL> set pagesize 100 SQL> col owner format a15 SQL> col directory_path format a80 SQL> select * from dba_directories ;
OWNER DIRECTORY_NAME DIRECTORY_PATH ---------- ------------------------------ ---------------------------------------------------------------------------- SYS SUBDIR /app/oracle/product/11.2.0/db/demo/schema/order_entry/2002/Sep SYS SS_OE_XMLDIR /app/oracle/product/11.2.0/db/demo/schema/order_entry/ SYS JOOYH_DIR /home/oracle/data_pump SYS LOG_FILE_DIR /app/oracle/product/11.2.0/db/demo/schema/log/ SYS MEDIA_DIR /app/oracle/product/11.2.0/db/demo/schema/product_media/ SYS XMLDIR /app/oracle/product/11.2.0/db/rdbms/xml SYS DATA_FILE_DIR /app/oracle/product/11.2.0/db/demo/schema/sales_history/ SYS DATA_PUMP_DIR /app/oracle/admin/ogg25/dpdump/ SYS ORACLE_OCM_CONFIG_DIR /app/oracle/product/11.2.0/db/ccr/state 9 rows selected. SQL> |
-- 새로운 Directory 생성 (tran_pump 로 생성) 및 필요권한 부여
-- system 에는 이미 권한이 있으나, 셈풀Query를 수행
[/home/oracle]$ id uid=401(oracle) gid=512(dba) groups=512(dba) [/home/oracle]$ cd /app/oracle/admin/ogg25/ [/app/oracle/admin/ogg25/]$ mkdir tran_pump -- dpdir 정도의 이름으로 만들어진 것을 사용하면 됨 |
-- 일반 User로 Directory 생성시 필요 (system 은 기 보유) SQL> GRANT CREATE ANY DIRECTORY TO SYSTEM ; -- 원하는 Directory 생성 SQL> CREATE DIRECTORY tran_pump AS '/app/oracle/admin/ogg25/tran_pump' ; -- DIRECTORY 에 개별권한 부여 SQL> GRANT READ, WRITE ON DIRECTORY tran_pump TO SYSTEM ; -- 일반 User로 추출/적재 시 필요 (system 은 이미 권한이 있음} SQL> grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to SYSTEM ; |
B. Datadump 명령어로, 해당 Data 를 OS File 형태로 추출
-- 추출대상 Object 확인 : 하단 참조
-- 새로 생성한 tran_pump 에 Data 를 추출/적재
-- tran_pump 이름의 directory 에 dumpfile 이 생성됨
[/home/oracle]$ expdp system/oracle directory=tran_pump schemas=BIGDATA01 job_name=bigdata01_expdp dumpfile=src_schema_bigdata01.dmp logfile=src_schema_bigdata01.expdp.log |
[/app/oracle/admin/ogg25/tran_pump]$ du -hs * 5.1G src_schema_bigdata01.dmp -> 14:13:13 소요됨, tran_pump 란 directory 에 생성됨 12K src_schema_bigdata01.expdp.log [/app/oracle/admin/ogg25/tran_pump]$ |
C. FTP 로 타켓서버로 전송 : 생략
D. Datadump 명령어로, 해당 OS File 을 DB에 적재
** 주의사항
-- 사전에 적재 될 Schema 는 이미 만들어 두어야 함
-- 이미 생성하지 않으면, 적재 시 생성하나 (동일 DB서버에 복구 하는 경우가 아니라면) 이미 생성해 두어야 함
-- 적재된 Tablespace 에도 충분한 공간이 확보되어 있어야 함
SQL> drop user bigdata02 cascade ; -- 해당 Schema 가 이미 있는 경우, 삭제하고 재생성 필요 SQL> create user bigdata02 identified by bigdata02 default tablespace tmp_data temporary tablespace temp ; SQL> grant resource, connect to bigdata02 ; SQL> grant dba, sysdba to tranm ; -- TRANManager DB User 에는 dba, sysdba 권한도 추가로 부여 |
** 만약 impdp 가 퍼미션 문제로 실행되지 않는다면 SYSDBA 로 로그인한 후, 아래 Query 를 실행한 후 재시도.
SQL> execute dbms_metadata_util.load_stylesheets ; |
-- 아래 명령어로 적재
-- 추출하는 Schema 와 적재되는 Schema 가 다른 경우 REMAP_SCHEMA 사용해야 함
-- 소스DB 의 Tablespace 명이 타켓DB에서 달라지는 경우, REMAP_TABLESPACE 를 사용해야 함
[/home/oracle]$ impdp system/oracle directory=tran_pump job_name=bigdata01_impdp dumpfile=src_schema_bigdata01.dmp logfile=src_schema_bigdata01.impdp.log REMAP_SCHEMA=bigdata01:bigdata02 |
8. 관련 Query
A. 추출 대상 Schema 의 Object 정보 사전 확인
SQL> set linesize 350 SELECT DB.NAME DBNAME , USERNAME , NVL(ROUND(SUM(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN NULL ELSE BYTES END)/1024/1024/1024,1),-1) TABLE_SIZE , NVL(ROUND(SUM(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE NULL END)/1024/1024/1024,1),-1) INDEX_SIZE , COUNT(B.SEGMENT_NAME) SEGMENT_COUNT , MAX(C.OBJ_CNT) OBJECT_COUNT FROM DBA_USERS A INNER JOIN (SELECT NAME FROM V$DATABASE) DB ON 1=1 LEFT OUTER JOIN DBA_SEGMENTS B ON A.USERNAME=B.OWNER AND SEGMENT_NAME NOT LIKE 'BIN%' LEFT OUTER JOIN (SELECT OWNER OWNER2,COUNT(*) OBJ_CNT FROM DBA_OBJECTS GROUP BY OWNER) C ON A.USERNAME = C.OWNER2 WHERE A.USERNAME IN ('BIGDATA01') GROUP BY DB.NAME,USERNAME ORDER BY USERNAME
SQL> /
DBNAME USERNAME TABLE_SIZE INDEX_SIZE SEGMENT_COUNT OBJECT_COUNT --------- ------------------------------ ---------- ---------- ------------- ------------ OGG25 BIGDATA01 4.7 1.4 126 134
SQL>
select owner, object_type, count(1) from dba_objects where owner in ('BIGDATA01') group by owner, object_type order by owner, object_type
OWNER OBJECT_TYPE COUNT(1) --------------- ------------------- ---------- BIGDATA01 INDEX 42 BIGDATA01 PACKAGE 1 BIGDATA01 PACKAGE BODY 1 BIGDATA01 PROCEDURE 3 BIGDATA01 SEQUENCE 1 BIGDATA01 TABLE 84 BIGDATA01 VIEW 2
7 rows selected. SQL> |
9. 참조 사례/셈풀
A. 추출 하지 않고, 생성될 dump file 의 Size 만 계산 : estimate_only 사용
-- 다음 옵션으로 expdp에 사용되는 공간을 추정할 수 있다.
$ expdp dpumpuser/dpumpuser full=y estimate_only=Y
B. 추출/적재 시 병렬처리 하는 경우 : %U 를 사용
-- parallel 파라메터는 몇 개의 스레드로 병렬 처리 할 것 인지 지정한다.
# expdp hr/hr tables=paratest directory=data_pump_test dumpfile=paratest%U.dmp parallel=4 job_name=parr_test;
-- paratest01.dmp, paratest02.dmp, paratest03.dmp paratest04.dmp => 4 개의 파일이 생성된다.
# impdp hr/hr directory=data_pump_test dumpfile=paratest%U.dmp job_name=parr_test;
C. 추출 시, 특정 Schema 데에터만 추출
-- 특정 스키마의 구조와 데이터 추출 : schema 를 사용
$expdp dpumpuser/dpumpuser schema=PORTAL directory=data_pump dumpfile=portal_schema_20081031.dmp
D. 추출 대상 정하기 - Metadata 만을 추출하거나, Table 의 Data 만 추출하는 경우 : content 를 사용
-- content를 사용해 특정 스키마의 데이터만 추출 (all 전체 | metadata_only 껍데기만 | data_only table의Data만 )
$expdp dpumpuser/dpumpuser schemas=PORTAL content=data_only directory=data_pump dumpfile=portal_schema_data_20081031.dmp
E. 적재 시, Schema 및 Tablespace 가 변경되는 경우 : REMAP_XXXXX 사용
-- 데이터 파일, 테이블 스페이스, 유저는 각각 다음의 옵션으로 변경할 수 있다.
REMAP_DATAFILE='C:\user01.dbf':'/usr/data/user01.dbf'
REMAP_TABLESPACE='users':'user'
REMAP_SCHEMA=scott:stralth
-- TABLESPACE 변경이 여러개인 경우, "," 를 구분자로 필요한 만큼 기술 가능
-- ex) impdp system/oracle directory=datapump dumpfile=scott.dmp remap_tablespace =users1_data:users2_data, users1_index:users2_index schemas=scott;
F. 적재 시 이미 Table 이 존재하는 경우 선택 가능 : table_exists 사용
$ impdp dpumpuser/dpumpuser table_exists_action=truncate directory=data_pump dumpfile=portal_schema_data_20081031.dmp
-- table_exists_action 옵션 사용, 기존의 데이터를 truncate 하도록 (skip | append | truncate | replace)
skip : 존재하는 오브젝트에 대해 임포트 스킵
append : 기존 오브젝트에서 업는 행만을 임포트
truncate : 기존 테이블 truncate
replace : drop & recreate
