DB Migration Using TTS

haisins

2년 ago

==============================================================================================

순서

1.CRS 및 Oracle install

1.1.CRS requirement

1.1.1.OS requirement
1.1.2.Oracle requirement
1.1.3.CRS install & Oracle install

2.To IBM From HP

2.1.Pre-Migration Check(HP)

2.1.1.HP 서버의 TTS 대상 Tablespace 확인
2.1.2.TTS 대상 Tablespace 의 Violation Check

2.2.TTS Migration On HP

2.2.1.Tablespace Read Only 변경
2.2.2.TTS 이전을 위한 Meta Data Export 실행
2.2.3.TTS plug-in 후 object 생성용 norows Export 실행
2.2.4.Oracle datafile copy

2.3.TTS Migration On IBM

2.3.1.임시 Tablespace 생성
2.3.2.TSD_PROD_IMSI Tablespace 생성
2.3.3.TTS 대상 유저 생성
2.3.4.Meta Data Import 실행
2.3.5.Tablespace Read-Write 모드로 변경

2.4.TTS Migration 마무리

2.4.1.유저정보 변경
2.4.2.TTS 제약사항 Object 등록

3.Tablespace Re-Org

3.1.LOB Table Move

3.1.1.TSD_PROD Rename To LOB_DATA
3.1.2.TSD_PROD_IMSI Rename To TSD_PROD
3.1.3.TSI_PROD_IMG01 Tabelspace Re-size
3.1.4.LOB_DATA 일반테이블을 TSD_PROD 로 이전

3.2.Long Table Move

3.2.1.long type export
3.2.2.drop long type table
3.2.3.create long type table(tablespace 변경)
3.2.4.long type import

3.3.Partition Table Move

3.3.1.LOB_DATA 에 존재하는 partition 테이블 TSD_PROD 로 이전

3.4.Tablespace Re-Org 마무리

3.4.1.logging, noparallel 설정
3.4.2.최종 권한 설정

=====================================================================================

1.CRS및 Oracle install

1.1. CRS requirement

1.1.1.OS requirement

1.1.1.1 /etc/hosts

1.1.1.2 dba, Oracle user add

1.1.1.3 file descriptor 변경

1.1.1.4 Network tunning parameter 변경

1.1.1.5 CRS 용 vote,crs raw device 생성 및 permission 변경

1.1.2. Oracle requirement

1.1.2.1 Oracle 환경변수 설정

1.1.3.CRS install & Oracle install
1.1.2.1 CRS install (한쪽 노드에서만 수행, oracle user)

1.1.2.2 Oracle S/W install (한쪽 노드에서만
수행, oracle user)

2.To IBM From HP

2.1.Pre-Migration Check(HP)

2.1.1.HP 서버의 TTS 대상 Tablespace 확인

( Oracle 10g 에서는 system, undotbs, sysaux, temp 는 TTS 의 기능으로 이전이 불가능 함)

SYS>select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
UNDOTBS2
USERS
TSD_COMM
TSD_MARKET
TSD_PROD
TSD_TRADE
TSD_TNS
TSD_MEMBER
TSD_SYS
TSD_TANAL
CYBER_DATA
TBS_TINSP_DAT
TSI_COMM
TSI_PROD
TSI_TRADE
….

2.1.2.TTS 대상 Tablespace 의 Violation Check

SQL> exec dbms_tts.transport_set_check( ‘TSD_COMM, TSD_MARKET, TSD_PROD,
TSD_TRADE, TSD_TNS, TSD_MEMBER, TSD_SYS, TSD_TANAL, CYBER_DATA,
TBS_TINSP_DAT, TSI_COMM, TSI_PROD, TSI_TRADE, TSI_TNS, TSI_MEMBER, TSI_SYS,
TSI_TANAL, CYBER_INDX, TBS_TINSP_IDX, TSD_MPV2, TSI_MPV2, TSD_SHPLNK,
TSI_SHPLNK, TSI_PROD_01, TSI_MARKET, TSD_2K_BLOCK, TSI_2K_BLOCK,
TSD_PROD_02, TSI_PROD_02, TSI_PROD_03, TSI_PROD_IMG_01, TSI_CUPN_01,
TSD_AMAIL, TSI_AMAIL, TSD_LIVEPROD, TSI_LIVEPROD, TPA_TS’,TRUE);
PL/SQL procedure successfully completed.

▲ TTS 대상 유저의 object 가 TTS 제외 대상의 Tablespace 에 포함된 경우는 에러가 발생!

SQL> select * from sys.transport_set_violation;

no rows selected

2.2.TTS Migration On HP

2.2.1.Tablespace Read Only 변경

SQL> alter tablespace USERS read only;
SQL> alter tablespace TSD_COMM read only;
SQL> alter tablespace TSD_MARKET read only;
SQL> alter tablespace TSD_PROD read only;
SQL> alter tablespace TSD_TRADE read only;
SQL> alter tablespace TSD_TNS read only;
SQL> alter tablespace TSD_MEMBER read only;
SQL> alter tablespace TSD_SYS read only;
SQL> alter tablespace TSD_TANAL read only;
SQL> alter tablespace CYBER_DATA read only;
SQL> alter tablespace TBS_TINSP_DAT read only;
SQL> alter tablespace TSI_COMM read only;
SQL> alter tablespace TSI_PROD read only;
SQL> alter tablespace TSI_TRADE read only;
SQL> alter tablespace TSI_TNS read only;
SQL> alter tablespace TSI_MEMBER read only;
SQL> alter tablespace TSI_SYS read only;
SQL> alter tablespace TSI_TANAL read only;
SQL> alter tablespace CYBER_INDX read only;
SQL> alter tablespace TBS_TINSP_IDX read only;
SQL> alter tablespace TSD_MPV2 read only;
SQL> alter tablespace TSI_MPV2 read only;
SQL> alter tablespace TSD_SHPLNK read only;
….

▲ Tablespace 변경 후에 정상적으로 변경이 되었는지 alert 로그파일을 통해서 확인 !!!

SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
UNDOTBS2 ONLINE
USERS READ ONLY
TSD_COMM READ ONLY
TSD_MARKET READ ONLY
TSD_PROD READ ONLY
TSD_TRADE READ ONLY
TSD_TNS READ ONLY
TSD_MEMBER READ ONLY
TSD_SYS READ ONLY
TSD_TANAL READ ONLY
CYBER_DATA READ ONLY
TBS_TINSP_DAT READ ONLY
TSI_COMM READ ONLY
TSI_PROD READ ONLY
TSI_TRADE READ ONLY
TSI_TNS READ ONLY
TSI_MEMBER READ ONLY
TSI_SYS READ ONLY
TSI_TANAL READ ONLY
CYBER_INDX READ ONLY
TBS_TINSP_IDX READ ONLY
TSD_MPV2 READ ONLY

….

2.2.2. TTS 이전을 위한 Meta Data Export 실행

exp \’sys/oracle as sysdba\’ file=meta.dmp log=meta.log transport_tablespace=y
tablespaces=USERS, TSD_COMM, TSD_MARKET, TSD_PROD, TSD_TRADE, TSD_TNS,
TSD_MEMBER, TSD_SYS, TSD_TANAL, CYBER_DATA, TBS_TINSP_DAT, TSI_COMM,
TSI_PROD, TSI_TRADE, TSI_TNS, TSI_MEMBER, TSI_SYS, TSI_TANAL, CYBER_INDX,TBS_TINSP_IDX, TSD_MPV2, TSI_MPV2, TSD_SHPLNK, TSI_SHPLNK, TSI_PROD_01,
TSI_MARKET, TSD_2K_BLOCK, TSI_2K_BLOCK, TSD_PROD_02, TSI_PROD_02,
TSI_PROD_03, TSI_PROD_IMG_01, SI_CUPN_01, TSD_AMAIL, TSI_AMAIL, TSD_LIVEPROD,
TSI_LIVEPROD, TPA_TS buffer=20480000

2.2.3. TTS plug-in 후object 생성용 norows Export 실행

exp system/oracle#ora2 file=rows_n.dmp log=rows_n.log full=y rows=n buffer=10240000

2.2.4. Oracle datafile copy

remsh(rsh) & dd 명령어를 통한 HP 서버에서 IBM 으로의 rawdevice copy IBM 쪽 rawdevice 는 dd 실행전 생성시켜야 한다.

200 개의 rawdevice 를 8 개의 기가비트라인(노드당 4 개)과 각 라인에 해당하는 script 작성

▲ I/O 병목상태 제거 시간 단축을 위해 IP부여한 랜카드 8장 2 node RAC 1 서버당 4장 씩 병렬 dd 카피

<1node 1script>

dd if=/dev/vg07/rlvol19 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol19
bs=8192k” &
dd if=/dev/vg07/rlvol20 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol20
bs=8192k” &
dd if=/dev/vg01/rlvol32 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg01_rlvol32
bs=8192k”
dd if=/dev/vg20/rlvol10 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg20_rlvol10
bs=8192k” &
dd if=/dev/vg20/rlvol14 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg20_rlvol14
bs=8192k” &
dd if=/dev/vg20/rlvol17 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg20_rlvol17
bs=8192k”

dd if=/dev/vg22/rlvol32 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg22_rlvol32
bs=8192k” &
dd if=/dev/vg22/rlvol10 bs=8192k | remsh 110.110.110.2 “/usr/bin/dd of=/dev/rtd_vg22_rlvol10
bs=8192k” &

……

<1node 2script>
dd if=/dev/vg07/rlvol11 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol11
bs=8192k” &
dd if=/dev/vg03/rlvol3 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg03_rlvol3
bs=8192k” &
dd if=/dev/vg14/rlvol2 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg14_rlvol2
bs=8192k”
dd if=/dev/vg16/rlvol14 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg16_rlvol14
bs=8192k” &
dd if=/dev/vg22/rlvol11 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg22_rlvol11
bs=8192k” &
dd if=/dev/vg22/rlvol2 bs=8192k | remsh 110.110.111.2 “/usr/bin/dd of=/dev/rtd_vg22_rlvol2
bs=8192k”

……

<1node 3script>

dd if=/dev/vg02/rlvol3 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg02_rlvol3
bs=8192k” &
dd if=/dev/vg04/rlvol3 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg04_rlvol3bs=8192k” &
dd if=/dev/vg07/rlvol16 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol16
bs=8192k”
dd if=/dev/vg07/rlvol3 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol3
bs=8192k” &
dd if=/dev/vg10/rlvol1 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg10_rlvol1
bs=8192k” &
dd if=/dev/vg11/rlvol1 bs=8192k | remsh 110.110.112.2 “/usr/bin/dd of=/dev/rtd_vg11_rlvol1
bs=8192k” &

……

<1node 4script>
dd if=/dev/vg07/rlvol21 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol21
bs=8192k” &
dd if=/dev/vg07/rlvol17 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol17
bs=8192k” &
dd if=/dev/vg07/rlvol18 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol18
bs=8192k”
dd if=/dev/vg03/rlvol1 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg03_rlvol1
bs=8192k” &
dd if=/dev/vg07/rlvol4 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol4
bs=8192k” &
dd if=/dev/vg07/rlvol6 bs=8192k | remsh 110.110.113.2 “/usr/bin/dd of=/dev/rtd_vg07_rlvol6
bs=8192k” &

…….중략

<2node 1script>
dd if=/dev/vg16/rlvol17 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg16_rlvol17
bs=8192k” &
dd if=/dev/vg23/rlvol10 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg23_rlvol10
bs=8192k” &
dd if=/dev/vg17/rlvol14 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol14
bs=8192k”
dd if=/dev/vg17/rlvol7 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol7
bs=8192k” &
dd if=/dev/vg08/rlvol1 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg08_rlvol1
bs=8192k” &
dd if=/dev/vg08/rlvol5 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg08_rlvol5
bs=8192k” &
dd if=/dev/vg15/rlvol7 bs=8192k | remsh 120.120.120.2 “/usr/bin/dd of=/dev/rti_vg15_rlvol7
bs=8192k” &

…….중략

<2node 2script>
dd if=/dev/vg15/rlvol4 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg15_rlvol4
bs=8192k” &
dd if=/dev/vg21/rlvol15 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg21_rlvol15
bs=8192k” &
dd if=/dev/vg08/rlvol12 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg08_rlvol12
bs=8192k” &
dd if=/dev/vg17/rlvol2 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol2
bs=8192k” &
dd if=/dev/vg17/rlvol16 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol16
bs=8192k” &
dd if=/dev/vg14/rlvol9 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg14_rlvol9
bs=8192k” &
dd if=/dev/vg21/rlvol11 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg21_rlvol11
bs=8192k” &
dd if=/dev/vg21/rlvol2 bs=8192k | remsh 120.120.121.2 “/usr/bin/dd of=/dev/rti_vg21_rlvol2
bs=8192k” &

……….중략

<2node 3script>
dd if=/dev/vg05/rlvol4 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg05_rlvol4
bs=8192k” &
dd if=/dev/vg12/rlvol6 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg12_rlvol6
bs=8192k” &
dd if=/dev/vg23/rlvol16 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg23_rlvol16
bs=8192k”
dd if=/dev/vg13/rlvol1 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg13_rlvol1
bs=8192k” &
dd if=/dev/vg17/rlvol9 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol9
bs=8192k” &
dd if=/dev/vg12/rlvol4 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg12_rlvol4
bs=8192k” &
dd if=/dev/vg17/rlvol12 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol12
bs=8192k” &
dd if=/dev/vg15/rlvol5 bs=8192k | remsh 120.120.122.2 “/usr/bin/dd of=/dev/rti_vg15_rlvol5
bs=8192k” &

………중략

<2node 4script>
dd if=/dev/vg07/rlvol31 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg07_rlvol31
bs=8192k” &
dd if=/dev/vg07/rlvol24 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg07_rlvol24
bs=8192k” &
dd if=/dev/vg07/rlvol27 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg07_rlvol27
bs=8192k”
dd if=/dev/vg07/rlvol29 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg07_rlvol29
bs=8192k” &
dd if=/dev/vg12/rlvol1 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg12_rlvol1
bs=8192k” &
dd if=/dev/vg17/rlvol17 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol17
bs=8192k”
dd if=/dev/vg13/rlvol5 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg13_rlvol5
bs=8192k” &
dd if=/dev/vg13/rlvol2 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg13_rlvol2
bs=8192k” &
dd if=/dev/vg17/rlvol15 bs=8192k | remsh 120.120.123.2 “/usr/bin/dd of=/dev/rti_vg17_rlvol15
bs=8192k” &

…….중략

2.3.TTS Migration On IBM

2.3.1.임시 Tablespace 생성

TTS 진행시에 쓰여지는 임시 테이블스페이스 생성(Tablespace 명: TTS)

SQL> create tablespace TTS datafile ‘/dev/rdata04_01’ size 100m;

alter database default tablespace TTS;

2.3.2.TSD_PROD_IMSI Tablespace 생성

Re-Org 진행시 TSD_PROD 로 변경될 테이블스페이스 생성

(테이블스페이스의 크기가 크기 때문에 사전에 미리 생성)

SQL> create tablespace TSD_PROD_IMSI datafile
‘/dev/rdata16_01’ size 16000M,
‘/dev/rdata16_02’ size 16000M,
‘/dev/rdata16_03’ size 16000M,
‘/dev/rdata16_04’ size 16000M,
‘/dev/rdata16_05’ size 16000M,
‘/dev/rdata16_06’ size 16000M,
‘/dev/rdata16_07’ size 16000M,
‘/dev/rdata16_08’ size 16000M,
‘/dev/rdata16_09’ size 16000M,
‘/dev/rdata16_10’ size 16000M,
‘/dev/rdata16_11’ size 16000M,
‘/dev/rdata16_12’ size 16000M,
‘/dev/rdata16_13’ size 16000M,
‘/dev/rdata16_14’ size 16000M,
‘/dev/rdata16_15’ size 16000M,
‘/dev/rdata16_16’ size 16000M,
‘/dev/rdata16_17’ size 16000M,
‘/dev/rdata16_18’ size 16000M,
‘/dev/rdata16_19’ size 16000M,
‘/dev/rdata16_20’ size 16000M;

2.3.3. TTS 대상 유저 생성

모든 유저가 대상이 되기 때문에 아래와 같이 생성

TTS 완료전에는 아래와 같이 모든 유저의 default tablespace 를 TTS 로 설정

SQL> create user MPV2 identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user TANALADMIN identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user INSPADMIN identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user TANAL identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user SVCOP identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user SVCADMIN identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user SVCMCHT identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user TPA identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user AMAILADMIN identified by oracle default tablespace TTS temporary tablespace temp;
SQL> create user SHOPLINKER identified by oracle default tablespace TTS temporary tablespace temp;

……..

2.3.4. Meta Data Import 실행

HP 서버에서 실행된 datafile copy 가 종료되면 정상적으로 실행이 되었는지 확인 후에 아래와 같이 meta data 를 import 한다.

$:> imp parfile=meta.parfile

=======meta.parfile =========
USERID=’sys/oracle as sysdba’
file=meta.dmp
log=meta_imp.log
transport_tablespace=y
buffer=102400000
ignore=y
datafiles=(
‘/dev/rtd_vg07_rlvol19’,
‘/dev/rtd_vg07_rlvol20’,
‘/dev/rtd_vg01_rlvol32’,
‘/dev/rtd_vg20_rlvol10’,
‘/dev/rtd_vg20_rlvol14’,
‘/dev/rtd_vg20_rlvol17’,
‘/dev/rtd_vg21_rlvol16’,
‘/dev/rtd_vg20_rlvol4’,
‘/dev/rtd_vg20_rlvol8’,
‘/dev/rtd_vg20_rlvol20’,
‘/dev/rtd_vg22_rlvol18’,
‘/dev/rtd_vg22_rlvol1’,
‘/dev/rtd_vg22_rlvol32’,
‘/dev/rtd_vg22_rlvol10’,
‘/dev/rtd_vg22_rlvol15’,
‘/dev/rtd_vg16_rlvol12′,………중략

’dev/rti_vg11_rlvol5’,
‘/dev/rti_vg21_rlvol17’,
‘/dev/rti_vg21_rlvol3’,
‘/dev/rti_vg21_rlvol9’,
‘/dev/rti_vg21_rlvol20’,
‘/dev/rti_vg21_rlvol4’,
‘/dev/rti_vg21_rlvol5’,
‘/dev/rti_vg08_rlvol4’,
‘/dev/rti_vg08_rlvol3’,
‘/dev/rti_vg07_rlvol2’,
‘/dev/rti_vg12_rlvol3’,
‘/dev/rti_vg21_rlvol13’,
‘/dev/rti_vg21_rlvol19’,
‘/dev/rti_vg23_rlvol19’
)
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
. importing SYS’s objects into SYS
. importing SYS’s objects into SYS
. importing TANAL’s objects into TANAL
. . importing table “TEST”
. importing TANALADMIN’s objects into TANALADMIN
. . importing table “ST_CI_HOUR”
. importing TMALL’s objects into TMALL
. . importing table “TMALL_USER_STATS_BK”
. . importing table “TMALL_USER_STATS”
. . importing table “SE_FEE_STL_OBJ_BK”
. . importing table “BEGIN_SYSTEM_EVENT”
. . importing table “END_SYSTEM_EVENT”

….. 중략

▲ table 단위로 import 를 진행한다.

alert 로그파일의 내용을 확인하면 아래와 같이 plug-in 된 정보의 확인이 가능

=====================================
Sat Mar 28 05:50:53 2009
Plug in tablespace USERS with datafile
‘/dev/rtd_vg07_rlvol34’
‘/dev/rtd_vg01_rlvol33’
Plug in tablespace TSD_COMM with datafile
‘/dev/rtd_vg22_rlvol17’
‘/dev/rtd_vg16_rlvol2’
‘/dev/rtd_vg07_rlvol7’
‘/dev/rtd_vg07_rlvol32’
‘/dev/rtd_vg07_rlvol10’
Plug in tablespace TSD_MARKET with datafile
‘/dev/rtd_vg22_rlvol2’
‘/dev/rtd_vg22_rlvol11’
‘/dev/rtd_vg16_rlvol14’
‘/dev/rtd_vg14_rlvol2’
‘/dev/rtd_vg03_rlvol3’
‘/dev/rtd_vg07_rlvol11’
Plug in tablespace TSD_PROD with datafile
‘/dev/rtd_vg22_rlvol14’
‘/dev/rtd_vg22_rlvol13’ ……..

2.3.5. Tablespace Read-Write 모드로 변경

meta data import 가 정상적으로 종료가 되면 Tablespace 를 Online 상태로 변경

SQL> alter tablespace TSD_TNS read write;
SQL> alter tablespace TSD_MEMBER read write;
SQL> alter tablespace TSD_SYS read write;
SQL> alter tablespace TSD_TANAL read write;
SQL> alter tablespace CYBER_DATA read write;
SQL> alter tablespace TBS_TINSP_DAT read write;
SQL> alter tablespace TSI_COMM read write;
SQL> alter tablespace TSI_PROD read write;
SQL> alter tablespace TSI_TRADE read write;
SQL> alter tablespace TSI_TNS read write;
SQL> alter tablespace TSI_MEMBER read write;
SQL> alter tablespace TSI_SYS read write;
SQL> alter tablespace TSI_TANAL read write;
SQL> alter tablespace CYBER_INDX read write;
SQL> alter tablespace TBS_TINSP_IDX read write;
SQL> alter tablespace TSD_MPV2 read write;
SQL> alter tablespace TSI_MPV2 read write;
SQL> alter tablespace TSD_SHPLNK read write;
SQL> alter tablespace TSI_SHPLNK read write;
SQL> alter tablespace SHAREPLEX read write;
SQL> alter tablespace TSI_PROD_01 read write;

……

▲ Online 상태 확인

SQL> select tablespace_name, status from dba_tablespaces;

2.4. TTS Migration 마무리

TTS 의 마무리 단계에서는 임시로 변경했던 default tablespace 및 temp tablespace 를 HP 서버 와 동일하게 변경을 하고,

TTS 수행중 변경한 패스워드 및 TTS 의 제약사항으로 이전이 되지 않는 procedure, package, db_link, view 등 추가 object 를 생성 해야 한다.

2.4.1. 유저정보 변경

SQL> alter user MPV2 default tablespace TSD_MPV2 temporary tablespace TEMP_BATCH;
SQL> alter user TANALADMIN default tablespace TSD_COMM temporary tablespace TEMP_BATCH;
SQL> alter user INSPADMIN default tablespace TSD_COMM temporary tablespace TEMP_BATCH;
SQL> alter user TANAL default tablespace TSD_COMM temporary tablespace TEMP_BATCH;
SQL> alter user SVCOP default tablespace USERS temporary tablespace TEMP_BATCH;
SQL> alter user SVCADMIN default tablespace USERS temporary tablespace TEMP_BATCH;
SQL> alter user SVCMCHT default tablespace USERS temporary tablespace TEMP_BATCH;
SQL> alter user TPA default tablespace TPA_TS temporary tablespace TEMP;
SQL> alter user AMAILADMIN default tablespace TSD_AMAIL temporary tablespace TEMP;
SQL> alter user SPLEX2 default tablespace SHAREPLEX temporary tablespace TEMP;

……..

2.4.2. TTS제약사항 Object 등록

$:> imp system/oracle file=rows_n.dmp log=imp_obj.log rows=n full=y ignore=y buffer=102400000

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
. importing SYSTEM’s objects into SYSTEM
IMP- 00017: following statement failed with ORACLE error 1119:
“CREATE TABLESPACE “SHAREPLEX” BLOCKSIZE 8192 DATAFILE ‘/dev/vg07/rlvol35’ ”
“SIZE 5120M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE
PERMANENT SEGMENT SPACE MANAGEMENT AUTO”

IMP- 00003: ORACLE error 1119 encountered
ORA- 01119: error in creating database file ‘/dev/vg07/rlvol35’
ORA- 27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 2: No such file or directory
IMP- 00017: following statement failed with ORACLE error 1119:
“CREATE TEMPORARY TABLESPACE “TEMP01″ BLOCKSIZE 8192 TEMPFILE ‘/dev/vg14/rl”
“vol5’ SIZE 10111M , ‘/dev/vg15/rlvol10’ SIZE 10111M , ‘/dev/vg16/”
“rlvol4’ SIZE 10111M , ‘/dev/vg16/rlvol8’ SIZE 10111M , ‘/dev/vg17”
“/rlvol5’ SIZE 10111M , ‘/dev/vg17/rlvol8’ SIZE 10111M , ‘/dev/vg1”
“6/rlvol5’ SIZE 10111M , ‘/dev/vg11/rlvol2’ SIZE 25087M EXTENT MA”
“NAGEMENT LOCAL UNIFORM SIZE 1048576”
IMP- 00003: ORACLE error 1119 encountered
ORA- 01119: error in creating database file ‘/dev/vg14/rlvol5’
ORA- 27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 2: No such file or directory
IMP- 00017: following statement failed with ORACLE error 959:
“ALTER USER “SYS” IDENTIFIED BY VALUES ‘6AE2F634C19CF614’ TEMPORARY TABLESPA”
“CE “TEMP01″”
IMP- 00003: ORACLE error 959 encountered
ORA- 00959: tablespace ‘TEMP01’ does not exist
IMP- 00017: following statement failed with ORACLE error 959:
“ALTER USER “SYSTEM” IDENTIFIED BY VALUES ‘A71C8B67B5BC5635’ TEMPORARY
TABLESPACE “TEMP01″”
IMP- 00003: ORACLE error 959 encountered
ORA- 00959: tablespace ‘TEMP01’ does not exist
. importing TMALL’s objects into TMALL
. importing SYSTEM’s objects into SYSTEM
. importing SYSMAN’s objects into SYSMAN
. importing CYBER’s objects into CYBER
. importing MPV2’s objects into MPV2
. importing INSPADMIN’s objects into INSPADMIN
. importing TANALADMIN’s objects into TANALADMIN
. importing TMALL’s objects into TMALL
. importing SMSMO’s objects into SMSMO
. importing SHOPLINKER’s objects into SHOPLINKER
. importing SPLEX’s objects into SPLEX

중략……

▲ 실행한 후에 각종 object 가 정상적으로 import 가 되었는지 확인

3. Tablespace Re-Org

3.1. LOB Table Move

기존에 lob type 의 Table 과 일반 Table 의 공존으로 인하여 I/O 측면의 문제점과 관리상의 문제 및 Migration 시의 문제점이 있음.

위의 문제로 인하여 TTS Migration 후에 TSD_PROD 에 존재하는 lob type 의 테이블을 별도의 Tablespace 로 이전을 함.

3.1.1. TSD_PROD Rename To LOB_DATA

lob 테이블보다 일반테이블의 이동이 빠르기 때문에 위와 같이 LOB_DATA 로 변경

SQL> alter tablespace TSD_PROD rename to LOB_DATA;

3.1.2. TSD_PROD_IMSI Rename To TSD_PROD

사전에 생성한 TSD_PROD_IMSI 를 TSD_PROD 로 변경

SQL> alter tablespace TSD_PROD_IMSI rename to TSD_PROD;

3.1.3. TSI_PROD_IMG01 Tabelspace Re-size

parallel 실행시 extend 에러 대비 re-size

SQL> alter tablespace TSI_PROD_IMG_01 add datafile ‘/dev/rindex16_01’ size 16000M;

3.1.4. LOB_DATA 일반테이블을 TSD_PROD로 이전

LOB Type 의 테이블의 느린 I/O 문제로 인하여 일반 테이블을 move 함

SQL> alter table TMALL.DP_PRD_CONT_SUMMARY move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.SY_BATCH_APP_LOG move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TEMP_TEST move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.KTW$TBS_REORG_TEMP move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.KTW$SESS_WAIT move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.DP_KN_SHP_RECOM move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.SY_BATCH_APP_LIST move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.PD_BRAND move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.PD_SEL_INFO move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.MT_EVNT_MAIL_CHIP move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.DP_SEMI_EXPRT move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.KW_LOG_INFO move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.PD_AUCT_PRD_HIST move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TEMP_KW_LOG_1208 move tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.DP_COMP_SHPP_ORD_ATTR move tablespace TSD_PROD parallel 8 nologging;

………

3.2. Long Table Move

long type 의 테이블은 exp/imp 유틸리티를 사용해서 이전

3.2.1. long type export

$:/>exp tmall/oracle file=tmall_long.dmp log=tmall_long.log tables=’SY_SECURE_KEY’,’TEMP1′,’PLAN_TABLE_OLD’,’TKPROF_TABLE’ buffer=1024000

3.2.2. drop long type table

SQL> drop table tmall.SY_SECURE_KEY;
SQL> drop table tmall.TEMP1;
SQL> drop table tmall.PLAN_TABLE_OLD;
SQL> drop table tmall.TKPROF_TABLE;

3.2.3.create long type table(tablespace변경)

CREATE TABLE TMALL.SY_SECURE_KEY
(
KEY LONG RAW
)
TABLESPACE TSD_PROD
PCTFREE 10
INITRANS 1
MAXTRANS 255

STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED

BUFFER_POOL DEFAULT
)
LOGGING
DISABLE ROW MOVEMENT ;
GRANT SELECT ON TMALL.SY_SECURE_KEY TO SVCOP;
GRANT DELETE ON TMALL.SY_SECURE_KEY TO SVCADMIN;
GRANT INSERT ON TMALL.SY_SECURE_KEY TO SVCADMIN;
GRANT SELECT ON TMALL.SY_SECURE_KEY TO SVCADMIN;

GRANT INSERT ON TMALL.SY_SECURE_KEY TO SVCESC;
GRANT SELECT ON TMALL.SY_SECURE_KEY TO SVCESC;
GRANT UPDATE ON TMALL.SY_SECURE_KEY TO SVCESC;
GRANT SELECT ON TMALL.SY_SECURE_KEY TO SVCFRONT;

……. 중략

3.2.4. long type import

$:/>imp tmall/oracle file=tmall_long.dmp log=imp_tmall_long.log tables=’SY_SECURE_KEY’,’TEMP1′,’PLAN_TABLE_OLD’,’TKPROF_TABLE’ ignore=y buffer=1024000

3.3. Partition Table Move

3.3.1. LOB_DATA에 존재하는 partition 테이블 TSD_PROD 로 이전

SQL> alter table TMALL.PD_PRD move partition P_PD_PRD_SEL tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TB_IF_SMS_TRANSFER move partition P_MAXVALUE tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TB_IF_SMS_TRANSFER move partition P_2009_12 tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TB_IF_SMS_TRANSFER move partition P_2009_11 tablespace TSD_PROD parallel 8 nologging;
SQL> alter table TMALL.TB_IF_SMS_TRANSFER move partition P_2009_10 tablespace TSD_PROD parallel 8 nologging;

…..

3.4. Tablespace Re-Org 마무리

3.4.1. logging, noparallel 설정

SQL> alter index TANALADMIN.IDX_QRTZ_T_VOLATILE noparallel;
SQL> alter index TMALL.PK_CM_UNITY_INFO_CONT noparallel;
SQL> alter index TMALL.PK_MT_MT_ISLND_TMPLT noparallel;
SQL> alter index TMALL.IX99_MT_ISLND_TMPLT_IMG noparallel;
SQL> alter index TMALL.PK_SELLER_SELL_SUMMARY noparallel;
SQL> alter index TMALL.PK_MT_SELLERSHP noparallel;

중략…

3.4.2. 최종 권한 설정

App 실행시 권한으로 생기는 오류를 방지하기 위해서 모든 유저의 권한을 마지막에 실행

SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_CUPN_ORD TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_CUPN_ORD_GA TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_CUPN_USE TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_CUPN_USE_GA TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_DISP_ITM_FEE TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_DLVCOST_MNBD TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_DLV_CLAIM TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_DVLTYPE_STATUS TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_ITEM_ATTR TO YMLEE75;
SQL>GRANT SELECT ON TANALADMIN.ST_TOTZ_TR_ORD_CN TO YMLEE75;

중략…