윈백및업그레이드 Tibero Table Migrator 사용법 2014. 05. 12.
목차 1. TABLE MIGRATOR 란?... 3 2. TABLE MIGRATOR 홖경설정... 3 2.1. TABLE MIGRATOR 바이너리... 3 2.2. Shell 설정... 4 2.3. Migrator.Properterties 파일설정... 4 3. TABLE MIGRATOR 수행... 6 3.1. OBJECT 생성... 6 3.2. TABLE MIGRATOR 수행... 7 4. Data 확인... 9 2
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_140613.zip $ ls log4j-1.2.16.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
2.2. Shell 설정 다음은 Table migration 을실행하기위한 Shell(Unix) 또는 BAT 파일 (Window 용 ) 을확인할수있다. Shell 또는 Bat 파일을확인하여 Tibero 와 Source DB 의 JDBC 를사용할버젂을맞추어수정해주어야한다. $ cat migrator.sh java -classpath migrator_cli.jar:log4j-1.2.16.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
# 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
# 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
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]1409730848322 - STARTED [E0]1409730848346 - TOTAL Extracted ROWS: 19 [L0]1409730848410 Loader started [L1]1409730848410 Loader started [L2]1409730848412 Loader started [L3]1409730848413 Loader started [L1]1409730848503 TOTAL Loaded ROWS: 0 [L2]1409730848643 TOTAL Loaded ROWS: 1 [L3]1409730848660 TOTAL Loaded ROWS: 1 [L0]1409730848660 TOTAL Loaded ROWS: 17 Loading is ended. Elapsed Time (milliseconds) : 1273 ADD BATCH : 2 EXECUTE BATCH : 0 참고사항 Properterties 파일에입력하여사용하는방법이외에명령어수행시속성들을지정해도적용이된다. 7
예시 $ ls log4j-1.2.16.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]1409734694754 - STARTED [E0]1409734694770 - TOTAL Extracted ROWS: 19 [L0]1409734694831 Loader started [L1]1409734694832 Loader started [L2]1409734694833 Loader started [L3]1409734694835 Loader started [L0]1409734694933 TOTAL Loaded ROWS: 3 [L3]1409734694941 TOTAL Loaded ROWS: 2 [L2]1409734694941 TOTAL Loaded ROWS: 7 [L1]1409734694941 TOTAL Loaded ROWS: 7 Loading is ended. Elapsed Time (milliseconds) : 742 ADD BATCH : 1 EXECUTE BATCH : 0 8
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 ---------- -------------------- ------------------------- --------- ---------- 101 Neena Kochhar 21-SEP-89 17000 102 Lex De Haan 13-JAN-93 17000 103 Alexander Hunold 13-JAN-90 9000 104 Bruce Ernst 21-MAY-91 6000 107 Diana Lorentz 07-FEB-99 4200 124 Kevin Mourgos 16-NOV-99 5800 141 Trenna Rajs 17-OCT-95 3500 142 Curtis Davies 29-JAN-97 3100 143 Randall Matos 15-MAR-98 2600 144 Peter Vargas 09-JUL-98 2500 149 Eleni Zlotkey 29-JAN-00 10500 174 Ellen Abel 11-MAY-96 11000 176 Jonathon Taylor 24-MAR-98 8600 178 Kimberely Grant 24-MAY-99 7000 200 Jennifer Whalen 17-SEP-87 4400 201 Michael Hartstein 17-FEB-96 13000 202 Pat Fay 17-AUG-97 6000 205 Shelley Higgins 07-JUN-94 12000 206 William Gietz 07-JUN-94 8300 19 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 ---------- ---------- ---------- ---------- ---------- 101 Neena Kochhar 1989/09/21 17000 102 Lex De Haan 1993/01/13 17000 103 Alexander Hunold 1990/01/13 9000 104 Bruce Ernst 1991/05/21 6000 107 Diana Lorentz 1999/02/07 4200 124 Kevin Mourgos 1999/11/16 5800 141 Trenna Rajs 1995/10/17 3500 142 Curtis Davies 1997/01/29 3100 143 Randall Matos 1998/03/15 2600 144 Peter Vargas 1998/07/09 2500 149 Eleni Zlotkey 2000/01/29 10500 174 Ellen Abel 1996/05/11 11000 176 Jonathon Taylor 1998/03/24 8600 178 Kimberely Grant 1999/05/24 7000 200 Jennifer Whalen 1987/09/17 4400 201 Michael Hartstein 1996/02/17 13000 202 Pat Fay 1997/08/17 6000 205 Shelley Higgins 1994/06/07 12000 206 William Gietz 1994/06/07 8300 19 rows selected. 9
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: +82-31-779-7113 Fax: +82-31-779-7119 Email: info@tmax.co.kr Web (Korean): http://www.tmaxdata.com Technical Support: http://technet.tmaxsoft.com USA TmaxSoft, Inc. 560 Sylvan Avenue Englewood Cliffs, NJ 07632. U.S.A Tel: +1-201-567-8266 Fax: +1-201-567-7339 Email: info@tmaxsoft.com Web (English): http://www.tmaxsoft.com Russia Tmax Russia L.L.C. Grand Setun Plaza, No A204 Gorbunova st.2, Moscow, 121596 Tel: +7(495)970-01-35 Email: info.rus@tmaxsoft.com Web (Russian): http://ru.tmaxsoft.com Singapore Tmax Singapore Pte. Ltd. 430 Lorong 6, Toa Payoh #10-02, OrangeTee Building. Singapore 319402 Tel: +65-6259-7223 Email: 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)20-8481-3776 Email: info.uk@tmaxsoft.com Web (English): http:/www.tmaxsoft.com Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, 3-12-16 Mita, Minato-Ku, Tokyo, 108-0073 Japan Tel: +81-3-5765-2550 Fax: +81-3-5765-2567 Email: info.jp@tmaxsoft.com Web (Japanese): http://www.tmaxsoft.co.jp China TmaxSoft China Co., Ltd. Beijing Silver Tower, RM 1508, 2 North Rd Dong San Huan, Chaoyang District, Beijing, China, 100027. China Tel: +86-10-6410-6145~8 Fax: +86-10-6410-6144 Email: info.cn@tmaxsoft.com Web (Chinese): http://www.tmaxsoft.com.cn Brazil TmaxSoft Brazil Avenida Copacabana, 177-3 andar 18 do Forte Empresarial, Alphaville - Barueri, Sao Paulo, SP-Brasil CEP 06472-001 Email: contato.brasil@tmaxsoft.com TN-TRMI-D0512101 10