OS 환경 : AIX 7.2 (64bit), Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c XTTS 마이그레이션 시 데이터파일 추가 케이스
이전 게시글에서 xtts 테스트를 해보았음
참고 : 오라클 19c XTTS(Cross Platform Transport Tablespace) 마이그레이션 방법 ( https://positivemh.tistory.com/1326 )
이때는 작업시 데이터파일이 추가되지는 않았었음
이번 테스트에서는 xtts 이관시 초기 이미지 백업이후 데이터파일을 하나 추가해서 이 파일까지 이관해봄
xtts 설명, 장단점 등은 이전 게시글을 참고하기 바람
테스트
샘플 테이블스페이스 생성
|
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 11-FEB-26
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_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_0a4g5vej.bk tag=FULL_BACKUP RECID=5 STAMP=1224932823
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
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_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_0b4g5veq.bk tag=FULL_BACKUP RECID=6 STAMP=1224932830
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-FEB-26
Starting Control File and SPFILE Autobackup at 11-FEB-26
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260211-00 comment=NONE
Finished Control File and SPFILE Autobackup at 11-FEB-26
|
백업 파일 tobe 서버로 전송
|
1
2
3
4
|
$ cd /u01/single/oracle/base/rman
$ scp /u01/single/oracle/base/rman/rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-*.bk 10.70.101.155:/oraimsi/xtts/
rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_0a4g5vej.bk 100% 500MB 96.2MB/s 00:05
rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_0b4g5veq.bk 100% 500MB 95.3MB/s 00:05
|
tobe에서 파일 확인
|
1
2
3
4
5
6
|
$ ls -al /oraimsi/xtts/
total 1024016
drwxr-xr-x 2 oracle oinstall 168 Feb 12 11:13 .
drwxr-xr-x 8 oracle dba 217 Feb 12 11:10 ..
-rw-r----- 1 oracle oinstall 524296192 Feb 12 11:13 rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_0a4g5vej.bk
-rw-r----- 1 oracle oinstall 524296192 Feb 12 11:13 rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_0b4g5veq.bk
|
tobe 서버에 다른 테이블스페이스 생성 및 유저 생성
*asis와 같은 유저를 생성하고 default 테이블스페이스는 다른 ts로 설정해둠
샘플 테이블스페이스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> create tablespace otherts datafile '/app/oracle/oradata/ORA19DBFS/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)' datafile '/oraimsi/xtts/rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_0a4g5vej.bk' format '/app/oracle/oradata/ORA19DBFS/migts01.dbf';
convert from platform 'AIX-Based Systems (64-bit)' datafile '/oraimsi/xtts/rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_0b4g5veq.bk' format '/app/oracle/oradata/ORA19DBFS/migts02.dbf';
}
Starting conversion at target at 2026-02-12 11:15:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=271 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/oraimsi/xtts/rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-5_0a4g5vej.bk
converted datafile=/app/oracle/oradata/ORA19DBFS/migts01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 2026-02-12 11:16:03
Starting conversion at target at 2026-02-12 11:16:03
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/oraimsi/xtts/rman_level0_20260211_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-6_0b4g5veq.bk
converted datafile=/app/oracle/oradata/ORA19DBFS/migts02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 2026-02-12 11:16:07
|
asis에서 데이터 파일 추가
|
1
2
3
|
SQL> alter tablespace migts add datafile '/u01/single/oracle/base/oradata/SI11G/migts03.dbf' size 500m;
Tablespace altered.
|
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
20
|
RMAN> backup incremental level 1 tag 'lv1_backup' tablespace migts format '/u01/single/oracle/base/rman/rman_level1_%T_%U.inc';
Starting backup at 11-FEB-26
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
input datafile file number=00007 name=/u01/single/oracle/base/oradata/SI11G/migts03.dbf
channel ORA_DISK_1: starting piece 1 at 11-FEB-26
channel ORA_DISK_1: finished piece 1 at 11-FEB-26
piece handle=/u01/single/oracle/base/rman/rman_level1_20260211_0d4g60gi_1_1.inc tag=LV1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 11-FEB-26
Starting Control File and SPFILE Autobackup at 11-FEB-26
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260211-01 comment=NONE
Finished Control File and SPFILE Autobackup at 11-FEB-26
|
증분 백업 파일 tobe 서버로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/rman
$ scp rman_level1_20260211_0d4g60gi_1_1.inc 10.70.101.155:/oraimsi/xtts/
rman_level1_20260111_074dkv0a_1_1.inc 100% 175MB 89.6MB/s 00:00
|
tobe에서 파일 확인
|
1
2
|
$ ls -al /oraimsi/xtts/rman_level1*
-rw-r----- 1 oracle oinstall 367263744 Feb 20 13:53 /oraimsi/xtts/rman_level1_20260211_0d4g60gi_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 => '/oraimsi/xtts/rman_level1_20260211_0d4g60gi_1_1.inc',
fname => '/oraimsi/xtts/rman_level1_20260211_0d4g60gi_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 /oraimsi/xtts/rman_level1_20260211_0d4g60gi_1_1.inc.conv
-rw-r----- 1 oracle oinstall 367263744 Feb 20 13:54 /oraimsi/xtts/rman_level1_20260211_0d4g60gi_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 => '/app/oracle/oradata/ORA19DBFS/migts01.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6, toname => '/app/oracle/oradata/ORA19DBFS/migts02.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece(handle=>'/oraimsi/xtts/rman_level1_20260211_0d4g60gi_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
20
|
RMAN> backup incremental level 1 tag 'lv1_backup' tablespace migts format '/u01/single/oracle/base/rman/rman_level1_%T_%U.inc';
Starting backup at 19-FEB-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 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
input datafile file number=00007 name=/u01/single/oracle/base/oradata/SI11G/migts03.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-26
channel ORA_DISK_1: finished piece 1 at 19-FEB-26
piece handle=/u01/single/oracle/base/rman/rman_level1_20260219_0f4grclp_1_1.inc tag=LV1_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-26
Starting Control File and SPFILE Autobackup at 19-FEB-26
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260219-00 comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-26
|
두번쨰 증분 백업 파일 tobe 서버로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/rman
$ scp rman_level1_20260219_0f4grclp_1_1.inc 10.70.101.155:/oraimsi/xtts/
rman_level1_20260111_094dld3b_1_1.inc 100% 175MB 92.0MB/s 00:01
|
tobe에서 파일 확인
|
1
2
|
$ ls -al /oraimsi/xtts/rman_level1_20260219_0f4grclp_1_1*
-rw-r----- 1 oracle oinstall 269156352 Feb 20 13:58 /oraimsi/xtts/rman_level1_20260219_0f4grclp_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 => '/oraimsi/xtts/rman_level1_20260219_0f4grclp_1_1.inc',
fname => '/oraimsi/xtts/rman_level1_20260219_0f4grclp_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 /oraimsi/xtts/rman_level1_20260219_0f4grclp_1_1.inc.conv
-rw-r----- 1 oracle oinstall 269156352 Feb 20 13:59 /oraimsi/xtts/rman_level1_20260219_0f4grclp_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 => '/app/oracle/oradata/ORA19DBFS/migts01.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6 , toname => '/app/oracle/oradata/ORA19DBFS/migts02.dbf',
fuzziness_hint=>0,max_corrupt =>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.restoreSetPiece(handle=>'/oraimsi/xtts/rman_level1_20260219_0f4grclp_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 10.70.101.155:/oraimsi/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
20
21
22
23
24
25
26
|
SQL> create or replace directory xtts_dir as '/oraimsi/xtts';
Directory created.
$ impdp system/oracle dumpfile=expdp_xtts.dmp logfile=impdp_xtts.log \
directory=xtts_dir transport_datafiles='/app/oracle/oradata/ORA19DBFS/migts01.dbf','/app/oracle/oradata/ORA19DBFS/migts02.dbf','/app/oracle/oradata/ORA19DBFS/migts03.dbf'
Import: Release 19.0.0.0.0 - Production on Fri Feb 20 14:01:08 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=/app/oracle/oradata/ORA19DBFS/migts01.dbf,/app/oracle/oradata/ORA19DBFS/migts02.dbf,/app/oracle/oradata/ORA19DBFS/migts03.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01565: error in identifying file '/app/oracle/oradata/ORA19DBFS/migts03.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Fri Feb 20 14:01:17 2026 elapsed 0 00:00:04
|
migts03.dbf 데이터 파일이 존재하지 않기때문에 impdp가 실패함
migts03.dbf를 이미지 백업으로 받아와야함
asis에서 7번 데이터 파일 이미지 카피 백업
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
RMAN> backup as copy datafile 7 format '/u01/single/oracle/base/rman/new_file_7_%T_%U.bk';
Starting backup at 19-FEB-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=127 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/single/oracle/base/oradata/SI11G/migts03.dbf
output file name=/u01/single/oracle/base/rman/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk tag=TAG20260219T140836 RECID=7 STAMP=1225634917
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 19-FEB-26
Starting Control File and SPFILE Autobackup at 19-FEB-26
piece handle=/u01/single/oracle/product/11.2.0.4/db1/dbs/c-2901833347-20260219-01 comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-26
|
백업 파일 tobe 서버로 전송
|
1
2
3
|
$ cd /u01/single/oracle/base/rman
$ scp /u01/single/oracle/base/rman/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk 10.70.101.155:/oraimsi/xtts/
new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk 100% 500MB 98.5MB/s 00:05
|
tobe에서 파일 확인
|
1
2
|
$ ls -al /oraimsi/xtts/new_file*
-rw-r----- 1 oracle oinstall 524296192 Feb 20 14:06 /oraimsi/xtts/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk
|
tobe 서버에서 인디안 포맷(Endian Format)에 맞게 7번 데이터파일 백업본 Convert 수행
*datafile 부분에 rman datafile copy 백업본 경로 입력, format 부분에 해당 copy 백업본에 맞는 tobe 데이터파일 경로 입력
|
1
2
3
4
5
6
7
8
9
10
11
|
RMAN> convert from platform 'AIX-Based Systems (64-bit)' datafile '/oraimsi/xtts/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk' format '/app/oracle/oradata/ORA19DBFS/migts03.dbf';
convert from platform 'AIX-Based Systems (64-bit)' datafile '/oraimsi/xtts/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk' format '/app/oracle/oradata/ORA19DBFS/migts03.dbf';
Starting conversion at target at 2026-02-20 14:08:28
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/oraimsi/xtts/new_file_7_20260219_data_D-SI11G_I-2901833347_TS-MIGTS_FNO-7_0h4grd34.bk
converted datafile=/app/oracle/oradata/ORA19DBFS/migts03.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 2026-02-20 14:08:32
|
tobe에서 TTS 대상 테이블스페이스 import 재수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ impdp system/oracle dumpfile=expdp_xtts.dmp logfile=impdp_xtts2.log \
directory=xtts_dir transport_datafiles='/app/oracle/oradata/ORA19DBFS/migts01.dbf','/app/oracle/oradata/ORA19DBFS/migts02.dbf','/app/oracle/oradata/ORA19DBFS/migts03.dbf'
Import: Release 19.0.0.0.0 - Production on Fri Feb 20 14:09:44 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_xtts2.log directory=xtts_dir transport_datafiles=/app/oracle/oradata/ORA19DBFS/migts01.dbf,/app/oracle/oradata/ORA19DBFS/migts02.dbf,/app/oracle/oradata/ORA19DBFS/migts03.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 Fri Feb 20 14:09:50 2026 elapsed 0 00:00:05
|
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만건이 확인됨
결론 : xtts 방식을 이용해 db 버전이 다르거나 os가 달라도(크로스 플랫폼) 테이블스페이스 레벨로 이관이 가능함
본문 방식처럼 데이터파일이 추가되는 경우 그에 맞게 데이터파일 이미지 카피 백업을 다시 가져와야하는 번거로움이 있을수 있음
테이블스페이스가 많아지고, 데이터파일이 많은 경우에는 위 방식을 스크립트를 이용해 자동화를 하는것이 편함
본문에서는 프로시저, 트리거 등 세그먼트가 아닌 오브젝트는 이관하지 않았는데 이런 오브젝트는 datapump의 content=metadata_only 옵션을 이용해서 이관해주면됨
참조 :
https://positivemh.tistory.com/1326
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 to 26ai Autoupgrade 이용 업그레이드 (0) | 2026.03.11 |
|---|---|
| 오라클 19c to 26ai Autoupgrade Refreshable Pluggable Database 이용 업그레이드 (0) | 2026.02.25 |
| 오라클 19c XTTS(Cross Platform Transport Tablespace) 마이그레이션 방법 (0) | 2026.01.16 |
| 오라클 19c TTS(Transport Tablespace) 마이그레이션 방법 (0) | 2026.01.13 |
| 오라클 19c to 19c datapump expdp network_link 옵션 사용 (0) | 2025.10.31 |