티베로 업그레이드, 윈백 (실습)

본 게시글에서는 실제 시나리오를 통해서 이관을 해보는 실습을 해보도록 하겠다.

 
 
tbexport/tbimport와 DB_LINK
 
1. Tibero5 Dummy Data 생성

Tibero5에서 수행 tbsql sys/tibero   create tablespace TBS1 datafile 'tbs1.dtf' size 3g autoextend off; create tablespace TBS2 datafile 'tbs2.dtf' size 3g autoextend off;   create user USER1 identified by "1234" default tablespace TBS1; grant connect, resource to USER1; grant create database link to USER1;   create user USER2 identified by "1234" default tablespace TBS2; grant connect, resource to USER2;     -- table 생성 create table USER1.TBL1( c1 number, c2 date, c3 varchar2(20) );   create table USER1.TBL2( c1 number, c2 date, c3 varchar2(20) );   create table USER1.TBL3( c1 number, c2 number, c3 varchar2(20), c4 varchar2(20) );     -- data insert insert into USER1.TBL1 select level, sysdate+level, dbms_random.string('x',20) from dual connect by level <= 100000;   insert into USER1.TBL2 select level, sysdate+level, dbms_random.string('x',20) from dual connect by level <= 100000;   insert into USER1.TBL3 select level, dbms_random.value(0, 10000), dbms_random.string('x',20), dbms_random.string('x',20) from dual connect by level <= 10000000;     -- index 생성 create unique index USER1.TBL2_PK on USER1.TBL2 (c1); alter table USER1.TBL2 add constraint TBL2_PK primary key (c1);   create unique index USER1.TBL3_PK on USER1.TBL3 (c1); alter table USER1.TBL3 add constraint TBL3_PK primary key (c1); create index USER1.TBL3_IX1 on USER1.TBL3 (c1, c2); create index USER1.TBL3_IX2 on USER1.TBL3 (c1, c2, c3);   -- dblink 생성 CREATE DATABASE LINK DB_LINK1 CONNECT TO SYS IDENTIFIED BY 'tibero' USING 'link';   -- tbdsn.tbr에 link 접속 정보 기입 필요 link=(     (INSTANCE=(HOST=192.168.179.76)               (PORT=7629)               (DB_NAME=tibero5)     ) )

 
As-Is Count, export (Tib5)

Tibero5 접속 정보 ssh tibero5@192.168.179.76 password : tibero5   tbsql sys/tibero   select username from dba_users;   USERNAME -------------------------------------------------------------------------------- SYS SYSCAT SYSGIS OUTLN WMSYS TIBERO TIBERO1 USER1 USER2   9 rows selected.   -- 이관 사이즈 확인 select owner, sum(bytes/1024/1024) as size_mb from dba_segments where owner not in ('SYS', 'SYSCAT', 'SYSGIS', 'OUTLN') group by owner;   OWNER                                                                                                                               SIZE_MB -------------------------------------------------------------------------------------------------------------------------------- ---------- USER1                                                                                                                                  1724 USER2                                                                                                                                  1724   2 rows selected.       -- 이관할 테이블 count col owner for a10 col objec_type for a15 col status for a8 SELECT OWNER, OBJECT_TYPE,STATUS, COUNT(*) FROM DBA_OBJECTS WHERE STATUS='VALID' and OBJECT_TYPE LIKE 'TABLE%' and OWNER not in ('SYS','PUBLIC','XSERVER','SYSGIS','OUTLN','SYSCAT') GROUP BY OWNER, OBJECT_TYPE,STATUS order by owner, object_type desc;   OWNER      OBJECT_TYPE          STATUS     COUNT(*) ---------- -------------------- -------- ---------- USER1      TABLE                VALID             3 USER2      TABLE                VALID             3         -- 이관할 인덱스 count col owner for a10 col objec_type for a15 col status for a8 SELECT OWNER, OBJECT_TYPE,STATUS, COUNT(*) FROM DBA_OBJECTS WHERE STATUS='VALID' and OBJECT_TYPE LIKE 'INDEX%' and OWNER not in ('SYS','PUBLIC','XSERVER','SYSGIS','OUTLN','SYSCAT') GROUP BY OWNER, OBJECT_TYPE,STATUS order by owner;   OWNER      OBJECT_TYPE          STATUS     COUNT(*) ---------- -------------------- -------- ---------- USER1      INDEX                VALID             4 USER2      INDEX                VALID             4     -- 이관할 기타 Object col owner for a10 col objec_type for a15 SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE not in ('INDEX','TABLE') and OWNER not in ('SYS','PUBLIC','XSERVER','SYSGIS','OUTLN','SYSCAT') GROUP BY OWNER, OBJECT_TYPE order by owner, object_type desc;   OWNER      OBJECT_TYPE            COUNT(*) ---------- -------------------- ---------- USER1      DATABASE LINK                 1 WMSYS      TYPE BODY                     1 WMSYS      TYPE                          1 WMSYS      FUNCTION                      1     -- 데이터 count select 'select count(*) from ' || OWNER || '.' || TABLE_NAME || ' union all' from dba_tables where owner='USER1' order by TABLE_NAME;   'SELECTCOUNT(*)FROM'||OWNER||'.'||TABLE_NAME||'UNIONALL' ------------------------------------------------------------------------------------------------------------------------------------------------------ select count(*) from USER1.TBL1 union all select count(*) from USER1.TBL2 union all select count(*) from USER1.TBL3 union all    >> 복사 후 붙여넣기     select count(*) from USER1.TBL1 union all select count(*) from USER1.TBL2 union all select count(*) from USER1.TBL3;     COUNT(*) ----------     100000     100000   10000000     -- db link 조회 select * from dba_db_links;   OWNER -------------------------------------------------------------------------------- DB_LINK -------------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- HOST -------------------------------------------------------------------------------- CREATED -------------------------------- USER1 DB_LINK1 SYS link 2023/02/02 22:44:10   1 row selected.     alter session set current_schema=user1; select * from v$instance@db_link1;   INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------------------------------- DB_NAME ---------------------------------------- HOST_NAME                                                       PARALLEL --------------------------------------------------------------- --------    THREAD# VERSION  STARTUP_TIME                     STATUS ---------- -------- -------------------------------- ---------------- SHUTDOWN_PENDING ----------------               0 tibero5 tibero5 finlin                                                          NO          0 5SP1     2023/02/02 22:11:34              NORMAL NO     1 row selected.     이관할 데이터 사이즈와 카운트가 끝났다면 exit 후 tbexport 수행   tbexport username=sys password=tibero sid=tibero5 port=7629 file=/home/tibero5/work/exp_full.dmp log=/home/tibero5/work/exp_full.log full=y script=y   수행 후 생성되는 exp_full.log 파일에서 DDL 스크립트 확인 가능

 템프는 이관이 안되므로 따로 추가
Target import, count (Tib6)

su - tibero6   vi exp_full.log   테이블스페이스 생성 구문 확인   CREATE TABLESPACE "TBS1" DATAFILE         '/home/tibero5/tibero5/database/tibero5/tbs1.dtf' SIZE 3221225472 EXTENT MANAGEMENT LOCAL AUTOALLOCATE / CREATE TABLESPACE "TBS2" DATAFILE         '/home/tibero5/tibero5/database/tibero5/tbs2.dtf' SIZE 3221225472 EXTENT MANAGEMENT LOCAL AUTOALLOCATE /      >> Target 환경에 맞게 수정 후 sys계정에서 수행   CREATE TABLESPACE "TBS1" DATAFILE         '/tibero/tibero6/database/tibero6/tbs1.dtf' SIZE 3221225472 EXTENT MANAGEMENT LOCAL AUTOALLOCATE / CREATE TABLESPACE "TBS2" DATAFILE         '/tibero/tibero6/database/tibero6/tbs2.dtf' SIZE 3221225472 EXTENT MANAGEMENT LOCAL AUTOALLOCATE /   -- datafile 경로 확인 select FILE_NAME from dba_data_files where TABLESPACE_NAME in ('TBS1', 'TBS2');   FILE_NAME -------------------------------------------------------------------------------- /tibero/tibero6/database/tibero6/tbs1.dtf /tibero/tibero6/database/tibero6/tbs2.dtf   2 rows selected.     --tbimport 수행 tbimport username=sys password=tibero sid=tibero6 port=8629 file=/tibero/work/export/exp_full.dmp log=/tibero/work/export/imp_full.log dpl=y full=y script=y   import 결과는 imp_full.log 파일을 통해 확인 가능     tbsql sys/tibero   select username from dba_users;   USERNAME -------------------------------------------------------------------------------- SYSCAT SYSGIS OUTLN TIBERO TIBERO1 WMSYS USER1 USER2 SYS   9 rows selected.   --data count select count(*) from USER1.TBL1 union all select count(*) from USER1.TBL2 union all select count(*) from USER1.TBL3;     COUNT(*) ----------     100000     100000   10000000       -- 이관된 object 확인 select object_name, object_type from dba_objects where owner='USER1'; OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- OBJECT_TYPE ----------------------- TBL1 TABLE   TBL2 TABLE   TBL3 TABLE   TBL3_IX2 INDEX   TBL3_PK INDEX   TBL2_PK INDEX   TBL3_IX1 INDEX   DB_LINK1 DATABASE LINK   8 rows selected.     -- dblink 조회 tbdsn.tbr에 접속정보 추가 link=(     (INSTANCE=(HOST=192.168.179.76)               (PORT=7629)               (DB_NAME=tibero5)     ) )   tbsql user1/1234 select * from v$instance@DB_LINK1;   INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------------------------------- DB_NAME ---------------------------------------- HOST_NAME                                                       PARALLEL --------------------------------------------------------------- --------    THREAD# VERSION ---------- -------- STARTUP_TIME ---------------------------------------------------------------- STATUS           SHUTDOWN_PENDING ---------------- ----------------               0 tibero5 tibero5 finlin                                                          NO          0 5SP1 2023/02/02 NORMAL           NO

 
dblink 이관

-- 이관을 위해 기존 user drop 후 재생성 tbsql sys/tibero drop user user2 cascade;   create user USER2 identified by "1234" default tablespace TBS2; grant connect, resource to USER2; grant create database link to USER2;     -- 데이터 없이 테이블만 import tbimport username=sys password=tibero sid=tibero6 port=8629 file=/tibero/work/export/exp_full.dmp log=/tibero/work/export/imp_user2.log user=user2 script=y index=n constraint=n rows=n   -- dblink 생성 후 이관 tbsql user2/1234 CREATE DATABASE LINK DB_LINK2 CONNECT TO SYS IDENTIFIED BY 'tibero' USING 'link';   insert into USER2.TBL1 select * from USER2.TBL1@DB_LINK2; insert into USER2.TBL2 select * from USER2.TBL2@DB_LINK2; insert into USER2.TBL3 select * from USER2.TBL3@DB_LINK2; commit;   -- 데이터 외 나머지 object(index, PK) import tbimport username=sys password=tibero sid=tibero6 port=8629 file=/tibero/work/export/exp_full.dmp log=/tibero/work/export/imp_user2_idx.log user=user2 script=y rows=n       tbsql sys/tibero --data count select count(*) from USER2.TBL1 union all select count(*) from USER2.TBL2 union all select count(*) from USER2.TBL3;     COUNT(*) ----------     100000     100000   10000000     -- 이관된 object 확인 select object_name, object_type from dba_objects where owner='USER2'; OBJECT_NAME   OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE ----------------------- TBL3 TABLE   TBL1 TABLE   TBL2 TABLE   TBL3_IX2 INDEX   TBL3_PK INDEX   TBL2_PK INDEX   TBL3_IX1 INDEX   DB_LINK2 DATABASE LINK   8 rows selected.

 
T-UP
T-UP Script 추출 방법

접속 정보 입력 후 Next
 
 
 
 
 
 

 
이관할 Object 선택 후 Refresh
Next
 
 
 

DDL Script 경로를 지정하지 않으면 실행 파일이 있는 위치에 생성
Next
 
 
 
 
Target DB 접속 정보 입력하고 아래 화면이 나올 때 까지 Next

 
 
DDL Execution, Data Transfer 옵션을 체크하지 않았기 때문에 스크립트만 생성하고 이관되지 않음
DDL_script.sql 파일 생성 확인
 

 
 
 
 
 
 
 
 
 
 
 
 
DDL_script.sql 에서 필요한 내용 Target 환경에 맞게 수정 후 sys 계정에서 수행
 
CREATE TABLESPACE "TBS4" DATAFILE
             '/tibero/tibero6/database/tibero6/TBS4.dtf' SIZE 1g
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/
CREATE TABLESPACE "TBS3" DATAFILE
             '/tibero/tibero6/database/tibero6/TBS3.dtf' SIZE 1g
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/
 
CREATE USER "USER3" IDENTIFIED BY tibero
DEFAULT TABLESPACE "TBS3"
/
CREATE USER "USER4" IDENTIFIED BY tibero
DEFAULT TABLESPACE "TBS4"
/
   >> password tibero로 추출
  
GRANT CONNECT TO "USER3"
/
GRANT RESOURCE TO "USER3"
/
GRANT CONNECT TO "USER4"
/
GRANT RESOURCE TO "USER4"
/
 
CREATE TABLE "USER3"."EXCEPT" (
             "C1" NUMBER,
             "C2" DATE,
             "C3" VARCHAR2(20 BYTE)
)
TABLESPACE "TBS3"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
DISABLE ROW MOVEMENT
NOPARALLEL
/
CREATE TABLE "USER3"."TBL1" (
             "C1" NUMBER,
             "C2" DATE,
             "C3" VARCHAR2(20 BYTE)
)
TABLESPACE "TBS3"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
DISABLE ROW MOVEMENT
NOPARALLEL
/
CREATE TABLE "USER3"."TBL2" (
             "C1" NUMBER NOT NULL,
             "C2" DATE,
             "C3" VARCHAR2(20 BYTE)
)
TABLESPACE "TBS3"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
DISABLE ROW MOVEMENT
NOPARALLEL
/
 
 
CREATE TABLE "USER4"."TBL1" (
                 "C1" NUMBER,
                 "C2" DATE,
                 "C3" VARCHAR2(20 BYTE)
)
TABLESPACE "TBS4"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
DISABLE ROW MOVEMENT
NOPARALLEL
/
CREATE TABLE "USER4"."TBL2" (
                 "C1" NUMBER,
                 "C2" DATE,
                 "C3" VARCHAR2(20 BYTE)
)
TABLESPACE "TBS4"
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
DISABLE ROW MOVEMENT
NOPARALLEL
/
 
 
테이블 생성 후 아래 단계까지 재수행
 
 
 

 
 
 
 
 

 
Data Transfer, DDL Execution 체크 후 이관 진행
 
 
SQL> select count(*) from USER3."EXCEPT";
 
  COUNT(*)
----------
    100000
 
1 row selected.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Table Migrator
 
 
 
 

cd work/migrator/ unzip final_tbmig.zip cd migrator   -- Migrator 설정    아래 설정들 수정 vi CPL.prop   SOURCE_TYPE=ORACLE SOURCE_URL=jdbc:oracle:thin:@192.168.179.76:13232:XE SOURCE_USER=SYSTEM SOURCE_PASSWORD=oracle SOURCE_SCHEMA=USER4   TARGET_URL=jdbc:internal:thin:@localhost:8629:tibero6 TARGET_SCHEMA=USER4       -- 설정 후 이관 대상 테이블에 대해 수행 sh migrator.sh PROPERTY_FILE=CPL.prop SOURCE_TABLE=TBL1 sh migrator.sh PROPERTY_FILE=CPL.prop SOURCE_TABLE=TBL2     인덱스가 있었다면 별도로 생성해야함 DPL 기능 + Shell Script 형태로 작성을 하여 수행하면 속도와 효율이 극적으로 향상