수행 예제
Table Migrator 유틸리티로 Oracle(Source DB)에서 Tibero(Target DB)로 DBMS 전환을 수행하는 예제입니다.
1. 테스트 환경 구성
Oracle에 테스트 Table, 데이터, 접속할 계정을 생성합니다.
--DBTECH 계정에 Tablespace, User, Table, Table Data 생성
SQL> create tablespace DBTECH_TBS
2 datafile 'dbtech_tbs01.dtf' size 50M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 5G
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.
SQL> create user dbtech identified by dbtech
2 default tablespace DBTECH_TBS;
User created.
SQL> grant connect, resource to dbtech;
Grant succeeded.
CONNECT dbtech/dbtech;
SQL> create table board (no number,
2 empno number(4),
3 title varchar(20),
4 contents clob,
5 regdate date default sysdate,
6 hit number default 0)
7 PCTFREE 10
8 INITRANS 2;
Table created.
SQL> INSERT INTO BOARD VALUES
(1, 7369, 'introduction', 'Hello, my name is SMITH.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(2, 7499, 'introduction', 'Hello, my name is ALLEN.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(3, 7521, 'introduction', 'Hello, my name is WARD.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(4, 7566, 'introduction', 'Hello, my name is JONES.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(5, 7654, 'introduction', 'Hello, my name is MARTIN.', sysdate, 0);
1 row created.
SQL> commit;
Commit complete.
--Migration할 때 접속할 계정(DBTECH_TEST) 생성
SQL> CONNECT /as sysdba
Connected.
SQL> create user dbtech_test identified by dbtech_test
2 default tablespace DBTECH_TBS;
User created.
--DBTECH_TEST 계정에 권한 부여(connect)
SQL> grant connect to dbtech_test;
Grant succeeded.
--DBTECH_TEST 계정에 Table 조회 권한 부여(select on dbtech.board)
SQL> grant select on dbtech.board to dbtech_test;
Grant succeeded.
--DBTECH_TEST 계정에 권한 부여(select any dictionary)
SQL> grant select any dictionary to dbtech_test;
Grant succeeded.2. 전환 수행
Oracle에 테스트 Table, 데이터, 접속할 계정을 생성합니다.
SQL> create tablespace DBTECH_TBS
2 datafile 'dbtech_tbs01.dtf' size 50M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 5G
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace 'DBTECH_TBS' created.
SQL> create user dbtech identified by dbtech
2 default tablespace DBTECH_TBS;
User 'DBTECH' created.
SQL> grant connect, resource, select any dictionary to dbtech;
Granted.
SQL> conn dbtech/dbtech
Connected to Tibero.
SQL> create table board (no number,
2 empno number(4),
3 title varchar(20),
4 contents clob,
5 regdate date default sysdate,
6 hit number default 0)
7 PCTFREE 10
8 INITRANS 2;
Table 'BOARD' created.
SQL> exit
Disconnected.
$ ls -lrt
-rw-r--r--. 1 tibero dba 1340431 Nov 15 15:29 antlr-4.3-complete.jar
-rw-r--r--. 1 tibero dba 1376 Nov 15 15:29 ANTLR4_License.txt
-rw-r--r--. 1 tibero dba 1604783 Nov 15 15:29 internal-jdbc-16.jar
-rw-r--r--. 1 tibero dba 167 Nov 15 15:29 migrator.bat
-rw-r--r--. 1 tibero dba 8313 Nov 15 15:29 migrator.properties.eg
-rw-r--r--. 1 tibero dba 170 Nov 15 15:29 migrator.sh
-rw-r--r--. 1 tibero dba 167137 Nov 15 15:29 migrator_cli.jar
-rw-r--r--. 1 tibero dba 352917 Nov 15 15:29 msllogger-14.jar
-rw-r--r--. 1 tibero dba 1320 Nov 15 15:29 pgjdbc_License.txt
-rw-r--r--. 1 tibero dba 850563 Nov 15 15:29 postgresql-42.2.27.jre6.jar
-rw-r--r--. 1 tibero dba 4054 Nov 15 15:29 readme.txt
-rw-r--r--. 1 tibero dba 123556 Nov 15 15:29 toolcom.jar
-rwxr-xr-x. 1 tibero dba 4210517 Nov 16 13:54 ojdbc6.jar
$sh migrator.sh SOURCE_TYPE=ORACLE SOURCE_URL=jdbc:oracle:thin:@localhost:1525:oraclesb SOURCE_USER=dbtech_test SOURCE_PASSWORD=dbtech_test SOURCE_LOGIN_AS=NORMAL SOURCE_SCHEMA=dbtech SOURCE_TABLE=board TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB TARGET_USER=dbtech TARGET_PASSWORD=dbtech TARGET_SCHEMA=dbtech TARGET_TABLE=board
SOURCE_USER=dbtech_test SOURCE_PASSWORD=dbtech_test SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech SOURCE_TABLE=board
TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB TARGET_USER=dbtech
TARGET_PASSWORD=dbtech TARGET_SCHEMA=dbtech TARGET_TABLE=board
INFO[16:51:45,676][main]TableMigrator.initAndDoMigrate()(1429)
********************************************************************************
*
* Start Log for table migrator.
* Vendor: tibero
* Product: table migrator
* Version: 263750
*
* Trace is started on 16:51:45.675
* Log level: INFO
*
********************************************************************************
INFO[16:51:45,679][main]TableMigrator.initAndDoMigrate()(1431)
=== Parameters ===
SOURCE_TYPE=ORACLE
SOURCE_DRIVER=oracle.jdbc.OracleDriver
SOURCE_URL=jdbc:oracle:thin:@localhost:1525:oraclesb
SOURCE_USER=DBTECH_TEST
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_SCHEMAORIGN=dbtech
SOURCE_TABLE=board
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
USE_OBJECT_PARALLEL_HINT=N
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB
TARGET_USER=DBTECH
TARGET_PASSWORD=************
TARGET_SCHEMA=dbtech
TARGET_TABLE=board
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=dpl
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
DPL_LOG_UNIT=500
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N
==================
INFO[16:51:45,784][TabMig[16:51:45.680]]TbCmdConnection.connect()(58) Connect To The Source- URL : jdbc:oracle:thin:@localhost:1525:oraclesb ID : DBTECH_TEST
INFO[16:51:45,983][TabMig[16:51:45.680]]TbCmdConnection.connect()(58) Connect To The Target- URL : jdbc:internal:thin:@localhost:6213:JSH_T7_DB ID : DBTECH
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(256) SOURCE_TYPE : ORACLE
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(257) SOURCE_SCHEMA : dbtech
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(275) SOURCE_TABLE : board
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(277) TARGET_TYPE : DEFAULT
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(278) TARGET_SCHEMA : dbtech
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(293) TARGET_TABLE : board
INFO[16:51:46,236][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(52) SELECT column_name, data_type, virtual_column, hidden_column FROM DBA_TAB_COLS WHERE OWNER=? AND TABLE_NAME=? order by column_id
INFO[16:51:46,292][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(55) setString(1) : DBTECH
INFO[16:51:46,292][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(58) setString(2) : BOARD
INFO[16:51:46,308][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(125) SELECT iot_type FROM dba_tables WHERE OWNER = ? and TABLE_NAME = ?
INFO[16:51:46,309][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(128) setString(1) : DBTECH
INFO[16:51:46,309][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(131) setString(2) : BOARD
INFO[16:51:46,311][TabMig[16:51:45.680]]TableMigrator.migrate()(375) start extracting thread...
INFO[16:51:46,315][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(86) run
INFO[16:51:46,315][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(531) Extract Information
INFO[16:51:46,316][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(532) table name : DBTECH.BOARD
INFO[16:51:46,316][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(538) columns [6] - "NO" "EMPNO" "TITLE" "REGDATE" "HIT" "CONTENTS"
INFO[16:51:46,318][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(93) TabMig[16:51:45.680].E0 - sql : SELECT '0', t."NO", t."EMPNO", t."TITLE", t."REGDATE", t."HIT", t."CONTENTS" FROM DBTECH.BOARD t
INFO[16:51:46,327][TabMig[16:51:45.680]]TableMigrator.migrate()(536) start loading thread...
INFO[16:51:46,328][TabMig[16:51:45.680]]DataLoader.setRemainThr()(85) Set Remain Threads [0], to [1]
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(525) DPL Loading Information
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(526) table name : dbtech.board
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(532) columns [6] - "NO" "EMPNO" "TITLE" "REGDATE" "HIT" "CONTENTS"
INFO[16:51:46,337][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(282)
Total Extract Time : 20ms
Time For Putting Row on Queue : 0ms
Time For Executing Query Time : 12ms
Time For Executing Fetch Time : 4ms
Percentage of Query and Fetch => 80.00%
INFO[16:51:46,337][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(283) TOTAL Extracted ROWS: 5 (source schema = DBTECH, source table = BOARD)
INFO[16:51:46,338][TabMig[16:51:45.680]]TableMigrator.migrate()(580) Extractor termFlag : [true], isError : [false]
INFO[16:51:46,338][TabMig[16:51:45.680].E0]DataExtractor.setTermFlag()(543) Set termFlag and isError : [false]
INFO[16:51:46,338][TabMig[16:51:45.680].E0]DataExtractor.setTermFlag()(544) complete
INFO[16:51:46,354][L0.TbDPLDataLoader0]DataLoaderManager.run()(47) run
INFO[16:51:46,361][L0.TbDPLDataLoader0]DataLoaderManager.run()(240) Total Insert Time : 29ms
Time For Taking Row From Queue: 0ms
Time For Executing Batch : 4ms
Time For Executing Commit : 2ms
Percentage of Batch and Commit Time => 20.69%
INFO[16:51:46,361][L0.TbDPLDataLoader0]DataLoaderManager.run()(242) [L0.TbDPLDataLoader0]1700121106361 TOTAL Loaded ROWS: 5 (target schema = dbtech, target table = board)
INFO[16:51:46,374][TabMig[16:51:45.680]]TableMigrator.migrate()(613) Loader remainThr : [0], isError : [false]
INFO[16:51:46,374][L0.TbDPLDataLoader0]DataLoader.finish()(80) Decrement remainThr : [0] and isError : [false]
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.resetThrs()(1359) terminate extract thread...1
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.resetThrs()(1373) terminate loader thread...1
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.migrate()(627) [VERIFICATION] Source Info : dbtech.board : 5 Rows, Target Info : dbtech.board : 5 Rows [OK]
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.migrate()(686) extract conneciton close
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(695) loader connection close
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(705) Elapsed Time (milliseconds) : 697
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(707) End MigrationLast updated