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