목차 1. TABLE MIGRATOR 란? TABLE MIGRATOR 홖경설정 TABLE MIGRATOR 바이너리 Shell 설정 Migrator.Properterties 파일설정 TAB

Size: px

Start display at page:

Download "목차 1. TABLE MIGRATOR 란? TABLE MIGRATOR 홖경설정 TABLE MIGRATOR 바이너리 Shell 설정 Migrator.Properterties 파일설정 TAB"

다해 문
7 years ago
Views:

1 윈백및업그레이드 Tibero Table Migrator 사용법

2 목차 1. TABLE MIGRATOR 란? TABLE MIGRATOR 홖경설정 TABLE MIGRATOR 바이너리 Shell 설정 Migrator.Properterties 파일설정 TABLE MIGRATOR 수행 OBJECT 생성 TABLE MIGRATOR 수행 Data 확인

3 Tibero Table Migrator 사용법 1. TABLE MIGRATOR 란? Table Migrator 는 Tibero 에서제공하는이관을위한 Tool 이다. 보통이관시 tbmigrator1, tbmigrator2 를사용하지만 table 당억단위의 data가있을경우 migrator 로사용하면진행이어려운경우가있다. 이때 Table 단위로이관을하는 Table Migrator 를적젃하게사용하면작업의효율성이높아진다. 단, 테이블에대한데이터의이관만가능하므로 Object 에대한이관은수동으로진행해야한다. 2. TABLE MIGRATOR 환경설정 2.1. TABLE MIGRATOR 바이너리 보통 Migrator 1, Migrator2 는 Tibero 바이너리내에포함되어배포되고있으나, Table migrator 는엔지니어나 TMAXDATA 에문의하여제공받아야한다. 제공되는파일은 table_migrator_yymmdd.zip 파일형태이며, Window/Unix 환경에서모두사용가능하다. 또한 JAVA 설치가되어있어야사용이가능하며, JAVA 6 이상버젂에서지원된다. Zip 파일을풀어보면아래와같은 Table Migrator 를사용하기위해필요한파일들을확인할수있다. $ unzip table_migrator_ zip $ ls log4j jar migrator_cli.jar migrator.sh readme.txt toolcom.jar migrator.bat migrator.properties.eg mlogger.jar tibero5-jdbc.jar JDBC 는 Tibero 서버에있는 JDBC 사용을권장한다. SOURCE DB 의 JDBC 는압축풀어놓은디렉토리에복사해놓아야하며 TIBERO 에서타 DB 의 JDBC 를제공하지않는다. JDBC 종류 > Tibero JDBC : tibero5-jdbc.jar 또는 tibero4-jdbc.jar, Oracle JDBC : ojdbc6.jar 또는 ojdbc5.jar 또는 ojdbc14.jar MSSQL JDBC : sqljdbc.jar 또는 sqljdc4.jar MYSQL JDBC : mysql-connector-java-xxx.jar DB2 : db2jcc.jar 참고사항 - JDBC 버젂확인방법 $ cd $TB_HOME/client/lib/jar $ java -jar tibero5-jdbc.jar 3

4 2.2. Shell 설정 다음은 Table migration 을실행하기위한 Shell(Unix) 또는 BAT 파일 (Window 용 ) 을확인할수있다. Shell 또는 Bat 파일을확인하여 Tibero 와 Source DB 의 JDBC 를사용할버젂을맞추어수정해주어야한다. $ cat migrator.sh java -classpath migrator_cli.jar:log4j jar:ojdbc6.jar:ojdbc14.jar: mlogger.jar:toolcom.jar:tibero5-jdbc.jar com.m.migrator.tablemigrator "$@" 2.3. Migrator.Properterties 파일설정 Migrator.properties 파일은이관을위해속성들을설정하는파일이다. 설정하는 Format 예제를제공하므로아래와같 이 COPY 하여사용하면된다. $ cp migrator.properties.eg migrator.properties $ ls migrator.properties migrator.properties 아래와같이설정 Format 을확인할수있다. SOURCE DB 설정 - Source DB 의홖경설정을하는부분이다 - DBMS 접속계정은 DBA 권한이갖고있는 User 사용을권장하며. 일반유저로사용시에는이관작업을위해사용자별 최소한의권한이필요하다. 타 DB : CONNECT, SELECT ANY TABLE, SELECT ANY DICTIONARY, ALTER SESSION Tibero : CONNECT, SELECT ANY TABLE, RESOURCE, ALTER SESSION $ vi migrator.properties ####################### # Source DB Connection ####################### # Source DB type (Optional, generic default ORACLE) # ORACLE option needs SOURCE_LOGIN_AS property # GENERIC option needs SOURCE_DRIVER property SOURCE_TYPE=ORACLE # Source DB JDBC driver class name # Use with SOURCE_TYPE=GENERIC option SOURCE_DRIVER=oracle.jdbc.OracleDriver # Source DB JDBC connection URL (Optional, Default=jdbc:oracle:thin:@localhost:1521:orcl ) SOURCE_URL=jdbc:oracle:thin:@localhost:1521:orcl # Source DB login ID (Optional, Default=sys) SOURCE_USER=sytem # Source DB login password (Optional, Default=oracle) SOURCE_PASSWORD=oracle # Oracle source DB login option (Optional, Default=sysdba) # Use with SOURCE_TYPE=ORACLE option SOURCE_LOGIN_AS=sysdba 4

5 # Source table schema (Optional) # Default value is the same with SOURCE_USER SOURCE_SCHEMA=SCOTT # Source table name (Mandatory) SOURCE_TABLE=employees TARGET DB 설정 Target DB(TIBERO) 홖경설정을설정하는부분이다. ####################### # Target DB Connection ####################### # Target DB type (Optional, generic DEFAULT) # GENERIC option needs TARGET_DRIVER property TARGET_TYPE=DEFAULT # Target DB JDBC driver class name # Use with TARGET_TYPE=GENERIC option TARGET_DRIVER=com.tmax.tibero.jdbc.TbDriver # Target DB JDBC connection URL (Optional, Default=jdbc:tibero:thin:@localhost:8629:tibero) TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero # Target DB login ID (Optional, Default=sys) TARGET_USER=sys # Target DB login password (Optional, Default=tibero) TARGET_PASSWORD=tibero # Target table schema (Optional) # Default value is the same with TARGET_USER TARGET_SCHEMA=mig_test # Target table name (Optional) # Default value is the same with SOURCE_TABLE TARGET_TABLE=employees 이관옵션설정 다음은이관설정부분으로각각의옵션들을홖경에알맞게설정할수있다. ################# # Data Extractor ################# # Use TSN(or SCN) option (Optional) #SELECT_TSN= # Where conditions (Optional) # ex. column1 > 10 AND column2 = 'abc' #SELECT_CONDITION= # Fetch size of source table cursor (Optional, Default=1024) # Some DB is not supported this feature. #SELECT_FETCH_SIZE=1024 5

6 # Source table partition name (Optional) #SELECT_PARTITION= # Ignore source/target DB character set (Optional, y N ) #SELECT_AS_BYTE=N ############## # Data Loader ############## # Use direct path loading (Optional, cpl DPL) # CPL means conventional path loading # DPL means direct path loading INSERT_METHOD=CPL # Use batch insert (Optional, Y n) # Use with INSERT_METHOD=CPL option INSERT_BATCH=Y # Use parallel loading (Optional, Y n) INSERT_PARALLEL=Y # Number of data loader thread (Optional, Default=4) # Use with INSERT_PARALLEL=Y option INSERT_THREAD_COUNT=4 # Target table partition name (Optional) #INSERT_PARTITION= 사용시에는주석처리를부분을제거해야적용이된다. 파라미터명은대문자로작성되어야한다. INDEX(ex. unique index) 가생성되어있는경우 CPL 로처리된다. 3. TABLE MIGRATOR 수행 3.1. OBJECT 생성 Table migrator 는 DATA 이관에대하서만수행가능하다. 따라서 Target DB 에 TABLESPACE, USER, TABLE 까지미리생성해 놓아야해당 Tool 를사용할수있다. TABLESPACE ->> USER ->> TABLE ->> TABLE MIGRATOR 생성생성생성이용 Tibero 에지정한 User 또는 User 의 table 이없는경우아래와같은에러가발생한다. Data type 또한동일하게맞추어 주어야한다. java.sql.sqlexception: JDBC-8033:Specified schema object was not found. 6

7 3.2. TABLE MIGRATOR 수행 Shell 을수행하여 migration 을수행한다. Shell 을수행하면 Properterties 에설정한값들이화면에표시되면서이관이수행된다. $ sh migrator.sh === Parameters === SOURCE_TYPE=ORACLE SOURCE_DRIVER=oracle.jdbc.OracleDriver SOURCE_URL=jdbc:oracle:thin:@localhost:1521:orcl SOURCE_USER=system SOURCE_PASSWORD=************ SOURCE_LOGIN_AS=normal SOURCE_SCHEMA=SCOTT SOURCE_TABLE=employees TARGET_TYPE=DEFAULT TARGET_DRIVER=com.tmax.tibero.jdbc.TbDriver TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero TARGET_USER=sys TARGET_PASSWORD=************ TARGET_SCHEMA=mig_test TARGET_TABLE=employees SELECT_FETCH_SIZE=256 INSERT_METHOD=CPL INSERT_BATCH=Y INSERT_PARALLEL=Y INSERT_THREAD_COUNT=4 ================== [E0] STARTED [E0] TOTAL Extracted ROWS: 19 [L0] Loader started [L1] Loader started [L2] Loader started [L3] Loader started [L1] TOTAL Loaded ROWS: 0 [L2] TOTAL Loaded ROWS: 1 [L3] TOTAL Loaded ROWS: 1 [L0] TOTAL Loaded ROWS: 17 Loading is ended. Elapsed Time (milliseconds) : 1273 ADD BATCH : 2 EXECUTE BATCH : 0 참고사항 Properterties 파일에입력하여사용하는방법이외에명령어수행시속성들을지정해도적용이된다. 7

8 예시 $ ls log4j jar migrator_cli.jar migrator.properties.eg mlogger.jar readme.txt toolcom.jar migrator.bat migrator.properties migrator.sh ojdbc6.jar tibero5-jdbc.jar $sh migrator.sh SOURCE_URL=jdbc:oracle:thin:@localhost:1521:orcl SOURCE_USER=SYSTEM SOURCE_PASSWORD=oracle SOURCE_LOGIN_AS=NORMAL SOURCE_SCHEMA=scott SOURCE_TABLE=employees TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero TARGET_USER=sys TARGET_PASSWORD=tibero TARGET_SCHEMA=mig_test === Parameters === SOURCE_TYPE=ORACLE SOURCE_DRIVER=oracle.jdbc.OracleDriver SOURCE_URL=jdbc:oracle:thin:@localhost:1521:orcl SOURCE_USER=SYSTEM SOURCE_PASSWORD=************ SOURCE_LOGIN_AS=normal SOURCE_SCHEMA=SCOTT SOURCE_TABLE=employees TARGET_TYPE=DEFAULT TARGET_DRIVER=com.tmax.tibero.jdbc.TbDriver TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero TARGET_USER=sys TARGET_PASSWORD=************ TARGET_SCHEMA=mig_test TARGET_TABLE=employees SELECT_FETCH_SIZE=256 INSERT_METHOD=CPL INSERT_BATCH=Y INSERT_PARALLEL=Y INSERT_THREAD_COUNT=4 ================== [E0] STARTED [E0] TOTAL Extracted ROWS: 19 [L0] Loader started [L1] Loader started [L2] Loader started [L3] Loader started [L0] TOTAL Loaded ROWS: 3 [L3] TOTAL Loaded ROWS: 2 [L2] TOTAL Loaded ROWS: 7 [L1] TOTAL Loaded ROWS: 7 Loading is ended. Elapsed Time (milliseconds) : 742 ADD BATCH : 1 EXECUTE BATCH : 0 8

9 4. Data 확인 --ORACLE DATA 확인 SQL> select EMP_NO, FIRST_NAME,LAST_NAME,HIRE_DATE, SALARY DEPT_ID from scott.employees ; EMP_NO FIRST_NAME LAST_NAME HIRE_DATE DEPT_ID Neena Kochhar 21-SEP Lex De Haan 13-JAN Alexander Hunold 13-JAN Bruce Ernst 21-MAY Diana Lorentz 07-FEB Kevin Mourgos 16-NOV Trenna Rajs 17-OCT Curtis Davies 29-JAN Randall Matos 15-MAR Peter Vargas 09-JUL Eleni Zlotkey 29-JAN Ellen Abel 11-MAY Jonathon Taylor 24-MAR Kimberely Grant 24-MAY Jennifer Whalen 17-SEP Michael Hartstein 17-FEB Pat Fay 17-AUG Shelley Higgins 07-JUN William Gietz 07-JUN rows selected. --TIBERO DATA 확인 SQL> select EMP_NO, FIRST_NAME,LAST_NAME,HIRE_DATE, SALARY DEPT_ID from mig_test.employees EMP_NO FIRST_NAME LAST_NAME HIRE_DATE DEPT_ID Neena Kochhar 1989/09/ Lex De Haan 1993/01/ Alexander Hunold 1990/01/ Bruce Ernst 1991/05/ Diana Lorentz 1999/02/ Kevin Mourgos 1999/11/ Trenna Rajs 1995/10/ Curtis Davies 1997/01/ Randall Matos 1998/03/ Peter Vargas 1998/07/ Eleni Zlotkey 2000/01/ Ellen Abel 1996/05/ Jonathon Taylor 1998/03/ Kimberely Grant 1999/05/ Jennifer Whalen 1987/09/ Michael Hartstein 1996/02/ Pat Fay 1997/08/ Shelley Higgins 1994/06/ William Gietz 1994/06/ rows selected. 9

10 Copyright 2014 TmaxData Co., Ltd. All Rights Reserved. Trademarks Tibero RDBMS is a registered trademark of TmaxData Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Contact Information TmaxData can be contacted at the following addresses to arrange for a consulting team to visit your company and discuss your options. Korea TmaxData Co., Ltd 5, Hwangsaeul-ro 329beon-gil, Bundang-gu, Seongnam-si, Gyeonggi-do. South Korea Tel: Fax: info@tmax.co.kr Web (Korean): Technical Support: USA TmaxSoft, Inc. 560 Sylvan Avenue Englewood Cliffs, NJ U.S.A Tel: Fax: info@tmaxsoft.com Web (English): Russia Tmax Russia L.L.C. Grand Setun Plaza, No A204 Gorbunova st.2, Moscow, Tel: +7(495) info.rus@tmaxsoft.com Web (Russian): Singapore Tmax Singapore Pte. Ltd. 430 Lorong 6, Toa Payoh #10-02, OrangeTee Building. Singapore Tel: info.sg@tmaxsoft.com United Kingdom TmaxSoft UK Ltd. Surrey House, Suite 221, 34 Eden Street, Kingston-Upon- Thames, KT1 1ER United Kingdom Tel: + 44-(0) info.uk@tmaxsoft.com Web (English): Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, Mita, Minato-Ku, Tokyo, Japan Tel: Fax: info.jp@tmaxsoft.com Web (Japanese): China TmaxSoft China Co., Ltd. Beijing Silver Tower, RM 1508, 2 North Rd Dong San Huan, Chaoyang District, Beijing, China, China Tel: ~8 Fax: info.cn@tmaxsoft.com Web (Chinese): Brazil TmaxSoft Brazil Avenida Copacabana, andar 18 do Forte Empresarial, Alphaville - Barueri, Sao Paulo, SP-Brasil CEP contato.brasil@tmaxsoft.com TN-TRMI-D

개발및운영 Tibero DB Link (Tibero To Oracle) - Local 방식

개발및운영 Tibero DB Link (Tibero To Oracle) - Local 방식 Tibero DB Link (Tibero To Oracle) - Local 방식 2014. 04. 16. 목차 1. 구성환경... 3 2. 환경설정... 3 2.1. Tibero 서버 (AIX) 에 Oracle instance Client 파일을업로드... 3 2.2. Oracle Instance Client에대한환경설정등록 (.profile)... 4 2.3.