OS 환경 : AIX 7.2 (64bit), Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c XTTS(Cross Platform Transport Tablespace) 마이그레이션 방법
본문에서는 XTTS(Cross Platform Transport Tablespace) 방식으로 데이터를 이관하는 방법을 설명함
AIX 환경의 11gR2 db에서 Linux 환경의 19c 버전의 다른 db로 일부 테이블스페이스만 이관하는 시나리오임
TTS가 동일 플랫폼 이관이고(Linux to Linux) XTTS는 다른 플랫폼간(다른 인디안간) 이관하는 방식임
참고 : 오라클 19c TTS(Transport Tablespace) 마이그레이션 방법 ( https://positivemh.tistory.com/1325 )
TTS란?
Transport Tablespace는 오라클 데이터베이스에서 테이블스페이스 단위로 데이터를 이관하는 기능임
데이터를 논리적으로 Export/Import 하지 않고, 데이터파일 자체를 다른 데이터베이스로 전달하여 메타데이터만 Import하는 방식임
XTTS(Cross Platform Transport Tablespace)는 크로스 플랫폼간 TTS를 사용하는것을 말함
특징
- 테이블스페이스에 포함된 데이터파일을 그대로 복사하여 사용함
- 데이터 자체는 이동하지 않고 메타정보만 Import 하므로 속도가 매우 빠름
- 대용량 데이터 이관, DB 통합, 환경 이전 시 주로 사용됨
- 동일하거나 호환 가능한 플랫폼, 인디안 방식이 필요함
동작 방식 요약
1. 소스 db에서 이동할 테이블스페이스를 read only로 변경
2. 해당 테이블스페이스의 데이터파일을 타겟 서버로 복사
3. expdp/impdp를 이용해 tts 메타데이터만 import
4. 타겟 db에서 테이블스페이스를 read write로 전환
장점
- Export/Import 대비 매우 빠른 이관 속도 제공
- 대용량 테이블 이동 시 부하 최소화
- 네트워크 트래픽 감소
- XTTS(Cross Platform Transport Tablespace)시 os(플랫폼)이 달라도 이관 가능
- Rman 사용시 증분 데이터 이관 가능
제약 사항
- SYSTEM, SYSAUX 테이블스페이스는 이관 불가
- 블록 사이즈가 동일해야함
- 소스와 타겟db의 캐릭터셋 호환이 맞아야함
- 이관 대상 테이블스페이스는 Self-Contained 조건을 만족해야 함(TRANSPORT_SET_CHECK로 사전 확인 가능)
(옮기려는 테이블스페이스에 데이터가 완전히 독립적으로 존재해야 한다는 뜻, 특정 오브젝트가 TTS 비대상 테이블스페이스에 걸쳐 있으면 안됨)
Self-Contained가 깨지는 대표적인 경우
- 테이블은 A TS에 있는데 인덱스는 B TS에 있음 => 불가
- 외래키(FK)가 다른 테이블스페이스의 테이블을 참조함 => 불가
- LOB 데이터는 LOB TS에 있고 테이블은 DATA TS에 있음 => DATA_TS만 옮기면 불가
Self-Contained가 되는 예
- 테이블, 인덱스, LOB 세그먼트가 전부 같은 테이블스페이스이거나
- 아예 같이 옮길 테이블스페이스 목록에 모두 포함되어 있음
소스 db 정보 : OS : AIX, SID : SI11G, 버전 : 11gR2, IP : 192.168.137.50
타켓 db 정보 : OS : Linux, SID : BIGDB, 버전 : 19c, IP : 192.168.137.60
테스트
샘플 테이블스페이스 생성
|
1
2
3
|
SQL>
create tablespace migts datafile '/u01/single/oracle/base/oradata/SI11G/migts01.dbf' size 500m;
alter tablespace migts add datafile '/u01/single/oracle/base/oradata/SI11G/migts02.dbf' size 500m;
|
유저생성 및 권한 부여
|
1
2
3
4
5
6
7
|
SQL> create user miguser identified by miguser account unlock default tablespace migts quota unlimited on migts;
User created.
SQL> grant resource, connect, dba to miguser;
Grant succeeded.
|
테이블 생성
|
1
2
3
4
5
6
7
|
SQL>
conn miguser/miguser
create table migtable(cola varchar2(20), colb number, colc number,
cold varchar2(30), cole varchar2(30), colf varchar2(30),
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30));
Table created.
|
샘플 데이터 삽입(DECLARE문 1번당 197mb) 총 5번 반복 => 약 880mb
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF MIGTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).COLA :=i;
w_ins(i).COLB :=300000;
w_ins(i).COLC :=999;
w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
w_ins(i).COLG :=9999999;
w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
FORALL i in 1..1000000 INSERT INTO MIGTABLE VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from miguser.migtable;
COUNT(*)
----------
1000000
|
asis 서버 플랫폼 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select platform_name from v$database;
PLATFORM_NAME
---------------------
AIX-Based Systems (64-bit)
SQL>
set lines 200 pages 1000
col platform_name for a30
select platform_id, platform_name, endian_format
from v$transportable_platform
where platform_name = 'AIX-Based Systems (64-bit)';
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
6 AIX-Based Systems (64-bit) Big
|
AIX에 platform_id는 6이고 endian_format은 Big임
Violation Check(tts전 유효성 검사)
|
1
2
3
4
5
6
7
8
9
|
SQL>
conn / as sysdba
exec dbms_tts.transport_set_check('MIGTS', TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
|
결과가 안나옴, 만약 결과가 나오는 경우 해당 데이터를 tts 대상 ts로 이동시켜줘야함
asis Block Change Tracking 설정(선택)
*필수 사항은 아님(증분 백업 향상을 위해 설정)
|
1
2
3
|
SQL> alter database enable block change tracking using file '/home/oracle/bct/bct.txt';
Database altered.
|
asis에서 db 이관 대상 테이블스페이스 백업
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ rman target /
RMAN> backup as copy incremental level 0 tag 'full_backup' tablespace migts format '/u01/single/oracle/base/rman/rman_level0_%T_%U.bk';
Starting backup at 2026/01/11 22:30:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/single/oracle/base/oradata/SI11G/migts01.dbf
output file name=/u01/single/oracle/base/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_034dlfsb.bk tag=FULL_BACKUP RECID=3 STAMP=1222295436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/single/oracle/base/oradata/SI11G/migts02.dbf
output file name=/u01/single/oracle/base/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_044dlfse.bk tag=FULL_BACKUP RECID=4 STAMP=1222295440
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2026/01/11 22:30:41
Starting Control File and SPFILE Autobackup at 2026/01/11 22:30:41
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260111-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2026/01/11 22:30:42
|
백업 파일 tobe 서버로 전송
|
1
2
3
4
|
$ cd /u01/single/oracle/base/rman
$ scp /u01/single/oracle/base/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-*.bk 192.168.137.60:/oradata1/rman/
rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_034dlfsb.bk 100% 500MB 96.2MB/s 00:05
rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_044dlfse.bk 100% 500MB 95.3MB/s 00:05
|
tobe에서 파일 확인
|
1
2
3
4
5
6
|
$ ls -al /oradata1/rman/
total 1024016
drwxr-xr-x 2 oracle oinstall 168 Jan 11 22:00 .
drwxr-xr-x 8 oracle dba 83 Jan 11 17:30 ..
-rw-r----- 1 oracle oinstall 524296192 Jan 11 22:00 rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_034dlfsb.bk
-rw-r----- 1 oracle oinstall 524296192 Jan 11 22:00 rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_044dlfse.bk
|
tobe 서버에 다른 테이블스페이스 생성 및 유저 생성
*asis와 같은 유저를 생성하고 default 테이블스페이스는 다른 ts로 설정해둠
샘플 테이블스페이스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create tablespace otherts datafile '/oradata2/BIGDB/otherts01.dbf' size 100m;
Tablespace created.
SQL> create user miguser identified by miguser account unlock default tablespace otherts quota unlimited on otherts;
User created.
SQL> grant resource, connect, dba to miguser;
Grant succeeded.
|
tobe 서버 플랫폼 확인
|
1
2
3
4
5
|
SQL> select platform_name from v$database;
PLATFORM_NAME
---------------------
Linux x86 64-bit
|
리눅스임
tobe 서버에서 인디안 포맷(Endian Format)에 맞게 풀백업본 Convert 수행
*datafile 부분에 rman datafile copy 백업본 경로 입력, format 부분에 해당 copy 백업본에 맞는 tobe 데이터파일 경로 입력
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
$ rman target /
RMAN>
run
{
convert from platform 'AIX-Based Systems (64-bit)' to 'Linux x86 64-bit' datafile '/oradata1/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_034dlfsb.bk' format '/oradata2/BIGDB/migts01.dbf';
convert from platform 'AIX-Based Systems (64-bit)' to 'Linux x86 64-bit' datafile '/oradata1/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_044dlfse.bk' format '/oradata2/BIGDB/migts02.dbf';
}
Starting conversion at target at 2026-01-11 22:02:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/oradata1/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_034dlfsb.bk
converted datafile=/oradata2/BIGDB/migts01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2026-01-11 22:02:11
Starting conversion at target at 2026-01-11 22:02:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/oradata1/rman/rman_level0_20260111_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_044dlfse.bk
converted datafile=/oradata2/BIGDB/migts02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2026-01-11 22:02:12
|
asis에서 증분 데이터 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
conn miguser/miguser
insert into miguser.migtable select * from miguser.migtable where rownum <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
|
테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from miguser.migtable;
COUNT(*)
----------
2000000
|
100만건이 추가됨
asis에서 증분 백업 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> backup incremental level 1 tag 'lv1_backup' tablespace migts format '/u01/single/oracle/base/rman/rman_level1_%T_%U.inc';
Starting backup at 2026/01/11 22:33:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=121 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/single/oracle/base/oradata/SI11G/migts01.dbf
input datafile file number=00006 name=/u01/single/oracle/base/oradata/SI11G/migts02.dbf
channel ORA_DISK_1: starting piece 1 at 2026/01/11 22:33:47
channel ORA_DISK_1: finished piece 1 at 2026/01/11 22:33:50
piece handle=/u01/single/oracle/base/rman/rman_level1_20260111_064dlg2b_1_1.inc tag=LV1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2026/01/11 22:33:50
Starting Control File and SPFILE Autobackup at 2026/01/11 22:33:50
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260111-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2026/01/11 22:33:52
|
증분 백업 파일 tobe 서버로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/rman
$ scp rman_level1_20260111_064dlg2b_1_1.inc 192.168.137.60:/oradata1/rman/
rman_level1_20260111_074dkv0a_1_1.inc 100% 175MB 89.6MB/s 00:00
|
tobe에서 파일 확인
|
1
2
|
$ ls -al /oradata1/rman/rman_level1*
-rw-r----- 1 oracle oinstall 183762944 Jan 11 22:03 /oradata1/rman/rman_level1_20260111_064dlg2b_1_1.inc
|
tobe 서버에서 인디안 포맷(Endian Format)에 맞게 첫번째 증분 백업본 Convert 수행
*bpname, fname, pltfrmfr 이름만 수정하여 사용
*bpname에는 증분백업 경로를, fname에는 tobe에서 증분 백업이 convert 될 위치, pltfrmfr에는 asis의 platform_id를 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
SQL>
set serveroutput on;
set termout on;
set verify off;
DECLARE
handle varchar2(512) ;
comment varchar2(80) ;
media varchar2(80) ;
concur boolean ;
recid number ;
stamp number ;
platfrmto number;
same_endian number := 1;
devtype VARCHAR2(512);
BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(
bpname => '/oradata1/rman/rman_level1_20260111_064dlg2b_1_1.inc',
fname => '/oradata1/xtts/rman_level1_20260111_064dlg2b_1_1.inc.conv',
handle => handle, media => media, comment => comment,
concur => concur, recid => recid, stamp => stamp, check_logical => FALSE,
copyno => 1, deffmt => 0, copy_recid => 0, copy_stamp => 0,
npieces => 1, dest => 0,
pltfrmfr => 6);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line ('ERROR IN CONVERSION ' || SQLERRM);
END ;
sys.dbms_backup_restore.deviceDeallocate;
dbms_output.put_line('CONVERTED BACKUP PIECE');
END;
/
CONVERTED BACKUP PIECE
PL/SQL procedure successfully completed.
|
convert된 파일 확인
|
1
2
|
$ ls -al /oradata1/xtts/rman_level1_20260111_064dlg2b_1_1.inc.conv
-rw-r----- 1 oracle oinstall 183762944 Jan 11 17:30 /oradata1/xtts/rman_level1_20260111_064dlg2b_1_1.inc.conv
|
ts 크기가 작아서 그런지 convert 후에도 용량이 동일함
tobe에서 첫번째 증분 백업본 recover 수행
*dfnumber, toname, handle 이름만 수정하여 사용, dbf가 여러개인 경우 applyDatafileTo 구문이 있는 라인을 여러번 사용하면됨
*dfnumber에는 데이터파일 번호를, toname에는 tobe에서 데이터파일 경로를, handle에는 convert된 증분 백업본 경로를 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
set serveroutput on
DECLARE
d varchar2(512);
h varchar2(512);
t varchar2(30);
b1 boolean;
b2 boolean;
DONE boolean;
FAILOVER boolean;
BEGIN
d := sys.dbms_backup_restore.deviceAllocate;
dbms_output.put_line(d);
sys.dbms_backup_restore.applysetdatafile(check_logical=>false, cleanup=>false) ;
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>5, toname => '/oradata2/BIGDB/migts01.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6, toname => '/oradata2/BIGDB/migts02.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece(handle=>'/oradata1/xtts/rman_level1_20260111_064dlg2b_1_1.inc.conv',
tag=>null,fromdisk=>true,recid=>0,stamp=>0) ;
sys.dbms_backup_restore.restoreBackupPiece(done=>DONE, params=>null, outhandle=>h, outtag=>t, FAILOVER=>FAILOVER);
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
PL/SQL procedure successfully completed.
|
asis에서 두번째 증분 데이터 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
conn miguser/miguser
insert into miguser.migtable select * from miguser.migtable where rownum <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
|
테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from miguser.migtable;
COUNT(*)
----------
3000000
|
100만건이 추가됨
asis 테이블스페이스를 read only 모드로 변경(down time 시작)
|
1
2
3
|
SQL> alter tablespace MIGTS read only;
Tablespace altered.
|
asis에서 두번째 증분 백업 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> backup incremental level 1 tag 'lv1_backup' tablespace migts format '/u01/single/oracle/base/rman/rman_level1_%T_%U.inc';
Starting backup at 2026/01/11 22:36:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=124 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/single/oracle/base/oradata/SI11G/migts01.dbf
input datafile file number=00006 name=/u01/single/oracle/base/oradata/SI11G/migts02.dbf
channel ORA_DISK_1: starting piece 1 at 2026/01/11 22:36:20
channel ORA_DISK_1: finished piece 1 at 2026/01/11 22:36:21
piece handle=/u01/single/oracle/base/rman/rman_level1_20260111_084dlg74_1_1.inc tag=LV1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2026/01/11 22:36:21
Starting Control File and SPFILE Autobackup at 2026/01/11 22:36:21
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260111-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2026/01/11 22:36:22
|
두번쨰 증분 백업 파일 tobe 서버로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/rman
$ scp rman_level1_20260111_084dlg74_1_1.inc 192.168.137.60:/oradata1/rman/
rman_level1_20260111_094dld3b_1_1.inc 100% 175MB 92.0MB/s 00:01
|
tobe에서 파일 확인
|
1
2
|
$ ls -al /oradata1/rman/rman_level1_20260111_084dlg74_1_1*
-rw-r----- 1 oracle oinstall 183197696 Jan 11 22:06 /oradata1/rman/rman_level1_20260111_084dlg74_1_1.inc
|
tobe 서버에서 인디안 포맷(Endian Format)에 맞게 두번째 증분 백업본 Convert 수행
*bpname, fname, pltfrmfr 이름만 수정하여 사용
*bpname에는 증분백업 경로를, fname에는 tobe에서 증분 백업이 convert 될 위치, pltfrmfr에는 asis의 platform_id를 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
SQL>
set serveroutput on;
set termout on;
set verify off;
DECLARE
handle varchar2(512) ;
comment varchar2(80) ;
media varchar2(80) ;
concur boolean ;
recid number ;
stamp number ;
platfrmto number;
same_endian number := 1;
devtype VARCHAR2(512);
BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(
bpname => '/oradata1/rman/rman_level1_20260111_084dlg74_1_1.inc',
fname => '/oradata1/xtts/rman_level1_20260111_084dlg74_1_1.inc.conv',
handle => handle, media => media, comment => comment,
concur => concur, recid => recid, stamp => stamp, check_logical => FALSE,
copyno => 1, deffmt => 0, copy_recid => 0, copy_stamp => 0,
npieces => 1, dest => 0,
pltfrmfr => 6);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line ('ERROR IN CONVERSION ' || SQLERRM);
END ;
sys.dbms_backup_restore.deviceDeallocate;
dbms_output.put_line('CONVERTED BACKUP PIECE');
END;
/
CONVERTED BACKUP PIECE
PL/SQL procedure successfully completed.
|
convert된 파일 확인
|
1
2
|
$ ls -al /oradata1/xtts/rman_level1_20260111_084dlg74_1_1.inc.conv
-rw-r----- 1 oracle oinstall 183197696 Jan 11 22:06 /oradata1/xtts/rman_level1_20260111_084dlg74_1_1.inc.conv
|
ts 크기가 작아서 그런지 convert 후에도 용량이 동일함
tobe에서 두번째 증분 백업본 최종 recover 수행
*dfnumber, toname, handle 이름만 수정하여 사용, dbf가 여러개인 경우 applyDatafileTo 구문이 있는 라인을 여러번 사용하면됨
*dfnumber에는 데이터파일 번호를, toname에는 tobe에서 데이터파일 경로를, handle에는 증분 백업 경로를 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
set serveroutput on
DECLARE
d varchar2(512);
h varchar2(512);
t varchar2(30);
b1 boolean;
b2 boolean;
DONE boolean;
FAILOVER boolean;
BEGIN
d := sys.dbms_backup_restore.deviceAllocate;
dbms_output.put_line(d);
sys.dbms_backup_restore.applysetdatafile(check_logical=>false, cleanup=>false);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>5 , toname => '/oradata2/BIGDB/migts01.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6 , toname => '/oradata2/BIGDB/migts02.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece(handle=>'/oradata1/xtts/rman_level1_20260111_084dlg74_1_1.inc.conv',
tag=>null,fromdisk=>true,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreBackupPiece( done=>DONE, params=>null, outhandle=>h, outtag=>t, FAILOVER=>FAILOVER);
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
PL/SQL procedure successfully completed.
|
asis에서 TTS 대상 테이블스페이스 export
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL> create or replace directory xtts_dir as '/u01/single/oracle/base/xtts';
Directory created.
$ expdp system/oracle dumpfile=expdp_xtts.dmp logfile=expdp_xtts.log directory=xtts_dir transport_tablespaces=MIGTS
Export: Release 11.2.0.4.0 - Production on Sun Jan 11 22:37:58 2026
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdp_xtts.dmp logfile=expdp_xtts.log directory=xtts_dir transport_tablespaces=MIGTS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/single/oracle/base/xtts/expdp_xtts.dmp
******************************************************************************
Datafiles required for transportable tablespace MIGTS:
/u01/single/oracle/base/oradata/SI11G/migts01.dbf
/u01/single/oracle/base/oradata/SI11G/migts02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 11 22:38:10 2026 elapsed 0 00:00:10
|
dump 파일 tobe로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/xtts
$ scp expdp_xtts.dmp 192.168.137.60:/oradata1/xtts
expdp_xtts.dmp 100% 100KB 36.4MB/s 00:00
|
tobe에서 TTS 대상 테이블스페이스 import
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> create or replace directory xtts_dir as '/oradata1/xtts';
Directory created.
$ impdp system/oracle dumpfile=expdp_xtts.dmp logfile=impdp_xtts.log \
directory=xtts_dir transport_datafiles='/oradata2/BIGDB/migts01.dbf','/oradata2/BIGDB/migts02.dbf'
Import: Release 19.0.0.0.0 - Production on Sun Jan 11 22:08:03 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expdp_xtts.dmp logfile=impdp_xtts.log directory=xtts_dir transport_datafiles=/oradata2/BIGDB/migts01.dbf,/oradata2/BIGDB/migts02.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 11 22:08:05 2026 elapsed 0 00:00:02
|
tobe에서 tts 대상 테이블스페이스 read write 모드로 변경(down time 끝)
|
1
2
3
|
SQL> alter tablespace MIGTS read write;
Tablespace altered.
|
tobe에서 임시로 지정했던 default tablespace 변경
|
1
2
3
|
SQL> alter user miguser default tablespace migts;
User altered.
|
tobe에서 데이터 건수 확인
|
1
2
3
4
5
6
7
|
SQL>
conn miguser/miguser
select count(*) from miguser.migtable;
COUNT(*)
----------
3000000
|
정상적으로 300만건이 확인됨
참고용. tts impdp 시 수행되는 ddl 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
$ impdp system/oracle dumpfile=expdp_xtts.dmp logfile=impdp_xtts_ddl.log \
directory=xtts_dir transport_datafiles='/oradata2/BIGDB/migts01.dbf','/oradata2/BIGDB/migts02.dbf' sqlfile=xtts_impdp_ddl.sql
$ cat xtts_impdp_ddl.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
ALTER SESSION SET EVENTS '44405 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '44406 TRACE NAME CONTEXT FOREVER, LEVEL 1';
SYS.DBMS_PLUGTS.PLTS_NEWDATAFILE('/oradata2/BIGDB/migts01.dbf');
SYS.DBMS_PLUGTS.PLTS_NEWDATAFILE('/oradata2/BIGDB/migts02.dbf');
-- new object type path: TRANSPORTABLE_EXPORT/PLUGTS_BLK
BEGIN
sys.dbms_plugts.beginImport ('11.2.0.4.0',846,'2000',6,'AIX-Based Systems (64-bit)',14565,250251,1,0,0,0);
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.checkCompType('COMPATSG','11.2.0.4.0');
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.checkUser('MIGUSER');
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.beginImpTablespace('MIGTS',5,'SYS',2,0,8192,1,208887,1,2147483645,8,128,8,0,1,2147483645,8,2901833347,1,4129,206925,NULL,0,0,NULL,NULL);
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.checkDatafile(NULL,2901833347,5,64000,5,5,0,0,206933,208887,1,20971522,NULL,NULL,NULL);
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.checkDatafile(NULL,2901833347,6,64000,5,6,0,0,207201,208887,1,25165826,NULL,NULL,NULL);
COMMIT;
END;
/
BEGIN
sys.dbms_plugts.commitPluggable;
COMMIT;
END;
/
-- new object type path: TRANSPORTABLE_EXPORT/TABLE
CREATE TABLE "MIGUSER"."MIGTABLE"
( "COLA" VARCHAR2(20 BYTE),
"COLB" NUMBER,
"COLC" NUMBER,
"COLD" VARCHAR2(30 BYTE),
"COLE" VARCHAR2(30 BYTE),
"COLF" VARCHAR2(30 BYTE),
"COLG" NUMBER,
"COLH" VARCHAR2(30 BYTE),
"COLI" VARCHAR2(30 BYTE),
"COLJ" VARCHAR2(30 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(SEG_FILE 6 SEG_BLOCK 130 OBJNO_REUSE 14460
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MIGTS" ;
BEGIN
SYS.DBMS_PLUGTSP.PATCHTABLEMETADATA('MIGUSER','MIGTABLE',:1,:2,:3);
END;
/
BEGIN
SYS.DBMS_PLUGTSP.PATCHDICTIONARY('MIGUSER','MIGTABLE',536870912,'unused','unused','unused','unused',2);
END;
/
BEGIN
SYS.DBMS_PLUGTSP.PATCHDICTIONARY('MIGUSER','MIGTABLE',736,'unused','unused','unused','unused',3);
END;
/
-- new object type path: TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
BEGIN
sys.dbms_plugts.endImport;
COMMIT;
END;
/
|
결론 : xtts 방식을 이용해 db 버전이 다르거나 os가 달라도(크로스 플랫폼) 테이블스페이스 레벨로 이관이 가능함
테이블스페이스가 많아지고, 데이터파일이 많은 경우에는 위 방식을 스크립트를 이용해 자동화를 하는것이 편함
본문에서는 프로시저, 트리거 등 세그먼트가 아닌 오브젝트는 이관하지 않았는데 이런 오브젝트는 datapump의 content=metadata_only 옵션을 이용해서 이관해주면됨
참조 :
https://positivemh.tistory.com/1325
How to Fix Transport Set Check Violations (DBMS_TTS.TRANSPORT_SET_CHECK)(ORA-39908, ORA-39910, ORA-39932, ORA-39921, etc.)(KB109933)
Primary Note for Transportable Tablespaces (TTS) -- Common Questions and Issues(KB134974)
Best Practices for Using Transportable Tablespaces (TTS)(KB130949)
트랜스포터블 테이블스페이스에 대한 프라이머리 노트 - 일반적인 질문 및 이슈들(KB134974)
트랜스포터블 테이블스페이스 (TTS) 제약사항, 제한: 세부사항, 참조, 그리고 적용 가능한 버젼(KB86037)
이동가능한 테이블스페이스(Transportable Tablespaces-TTS)를 사용하기 위한 모범기준(KB130949)
V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup(KB147608)
M5 Cross Endian Platform Migration using Full Transportable Data Pump Export/Import and RMAN Incremental Backups(KB144840)
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/transporting-data.html#GUID-BF7A7711-499D-4E54-A3E9-D51950E562B2
https://docs.oracle.com/ko/learn/ocidb-xtts/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmdu/general-limitations-on-transporting-data.html#GUID-28800719-6CB9-4A71-95DD-4B61AA603173
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmdu/task-1-pick-a-self-contained-set-of-tablespaces.html#GUID-BF7A7711-499D-4E54-A3E9-D51950E562B2
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/CONVERT.html#GUID-AD252F46-DD9E-4E2C-BF0E-DF2A4C7590C9
Migrate Database From 11g To 19c Using TTS With Less Storage In Target Database (Doc ID 2955663.1)
https://blog.goodusdata.com/190
https://bae9086.tistory.com/7
https://medium.com/garimoo/tts-transportable-tablespace-%EA%B8%B0%EB%8A%A5%EC%9D%84-%EC%9D%B4%EC%9A%A9%ED%95%9C-%EC%98%A4%EB%9D%BC%ED%81%B4db-%EB%B2%84%EC%A0%84-%EC%97%85%EA%B7%B8%EB%A0%88%EC%9D%B4%EB%93%9C-c1203004612c
https://jack-of-all-trades.tistory.com/534
https://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/migrate-bulky-oracle-databases/migrate-bulky-oracle-databases.pdf
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 19c TTS(Transport Tablespace) 마이그레이션 방법 (0) | 2026.01.13 |
|---|---|
| 오라클 19c to 19c datapump expdp network_link 옵션 사용 (0) | 2025.10.31 |
| 오라클 19c 통계정보 export 관련 궁금증 테스트 (0) | 2025.08.11 |
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 (0) | 2025.08.10 |
| 오라클 19c 통계정보 대량 이관시 병렬 처리 (0) | 2025.08.10 |