DBA의 정석

DB Admin Knowhow

Oracle Up,Mig

DB Migration Using TTS

Byhaisins

4월 20, 2023 , , , , , ,

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

순서

 

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;

중략…

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

Related Post

Oracle Up,Mig

데이터 펌프 걸어 놓고 모니터링 하는 방법 정리

11월 4, 2021 haisins
Oracle Up,Mig

이기종 마이그레이션 TOOL

2월 1, 2019 haisins
Oracle Up,Mig

ORACLE EXP , IMP

3월 19, 2018 haisins

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다

You missed

Oracle News

[NEWS] 오라클, ‘오라클 데이터베이스 23c 개발자용 무료 버전’ 공개

5월 8, 2023
Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023
Oracle Up,Mig

DB Migration Using TTS

4월 20, 2023
DB 설치 & 구축

Windows 용 Oracle Database 19c 설치 필요 조건

4월 19, 2023