[OracleDB] Datapump 를 이용한 DB 간 Schema 단위 이관

//-- 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