> 支援Oracle11.2.0.4 到任意版本（大於源端）。本文件介紹升級到19c的pdb中 --by Firsouler 2021.1.7

#### 1.準備

```
-檢查字符集
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;

col VALUE$ for a20
select name,value$ from PROPS$ where name like '%CHARACTERSET';

--檢查表空間
select distinct tablespace_name from dba_segments where owner in ('','');

select ts#,file#,name,status from v$datafile where status='OFLINE';

--檢查表空間自包含
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS3', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
```

#### 2.初始化

1.檢查表空間資料檔案所在位置

```
select file_name from dba_data_files where tablespace_name in ('MYWORK','MYTEST');
```

2.源端建立相關目錄

```
--源端
create directory sourcedir as '+DATA/mydb/datafile';
export TMPDIR=/home/oracle/xtt
mkdir -p /home/oracle/stage_source

--開啟塊跟蹤
alter database enable block change tracking using file '+data';
```

3.目標端建立相關目錄

```
sqlplus sys/oracle@mypdb as sysdba

create directory destdir as '+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE';
mkdir -p /home/oracle/stage_dest
--建立dblink，需要配置tnsname
create public database link ttslink connect to system identified by oracle using 'mydb';
select * from dual@ttslink;
```

3.解壓xtt檔案，並調整

```
unzip rman_xttconvert_v3.zip -d /home/oracle/xtt

cat xtt.properties #dbms_file_transfer 方式
#tablespaces/srcdir/dstdir 值需要大寫
tablespaces=MYWORK,MYTEST
platformid=13
backupformat=/home/oracle/stage_source
backupondest=/home/oracle/stage_dest
getfileparallel=2
srcdir=SOURCEDIR
dstdir=DESTDIR
stageondest=/home/oracle/stage_dest
srclink=TTSLINK

--複製到目標端
scp -r /home/oracle/xtt 192.168.80.210:/home/oracle/

#參考
tablespaces=          ----需要遷移的表空間
platformid=           ----源 OS 平臺 ID
dfcopydir=            ----源資料庫備份檔案存放目錄
backupformat=         ---源資料庫增備檔案存放目錄
stageondest=          ----目標據庫備份檔案存放目錄
storageondest=        ----目標據庫正式檔案存放目錄
backupondest=         ----目標據庫增備檔案存放目錄
parallel=             ----備份，轉化的並行度
rollparallel=         ----增備的並行度
getfileparallel=      ---- 使用 dbms_file_transfer 方式的並行度

```

#### 4.dbms_file_transfer方式初始化

1 生成相關檔案
```
export TMPDIR=/home/oracle/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -S

--參考
oracle@myasm xtt]$ cat getfile.sql 
0,SOURCEDIR,mytest.270.1025380271,DESTDIR,mytest_270_1025380271
0,SOURCEDIR,mytest.342.1033661031,DESTDIR,mytest_342_1033661031
1,SOURCEDIR,mywork.406.1052385811,DESTDIR,mywork_406_1052385811

[oracle@myasm xtt]$ cat xttnewdatafiles.txt      
::MYWORK
8,DESTDIR:/mywork.406.1052385811
::MYTEST
9,DESTDIR:/mytest.270.1025380271
10,DESTDIR:/mytest.342.1033661031

[oracle@myasm xtt]$ cat xttplan.txt 
MYWORK::::26814044
8
MYTEST::::26814044
9
10
```

2.複製

```
scp xttnewdatafiles.txt 192.168.80.210:/home/oracle/xtt/
scp getfile.sql 192.168.80.210:/home/oracle/xtt/
```

3.目標端執行

```
export TMPDIR=/home/oracle/xtt
$ORACLE_HOME/perl/bin/perl xttdriver.pl -G
```

#### 5.增量備份
> 增量備份會使資料庫重啟，如果是pdb，建議新建立一個例項，nomount狀態即可，執行恢復，可參考現有例項 建立pfile

1.源端執行

```
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

--複製檔案
scp `cat incrbackups.txt` 192.168.80.210:/home/oracle/stage_dest
scp xttplan.txt 192.168.80.210:/home/oracle/xtt
scp tsbkupmap.txt 192.168.80.210:/home/oracle/xtt

--驗證/生成新的scan
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
```

2.目標端執行

```
--目標端轉換，資料庫例項會重啟
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
```

3.新新增表空間，或資料檔案

```
#按照以下部署執行
13 1 new datafiles added
=============================================================
============================================================
Running prepare cmd for new files my2021.337.106129557.(.*)
=============================================================

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Error:
------
The incremental backup was not taken as a datafile has been added to the tablespace:

Please Do the following:
--------------------------
 1. Copy fixnewdf.txt from source to destination temp dir
 scp fixnewdf.txt 192.168.80.210:/home/oracle/xtt/

2. On Destination, run $ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf

3. Re-execute the incremental backup in source:
    $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

NOTE:  Before running incremental backup, delete FAILED in source temp dir or 
           run xttdriver.pl with -L option
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
```

#### 5.收尾
1.最後增量

```
#源端
alter tablespace mywork read only;
alter tablespace mytest read only;
alter tablespace my2021 read only;
alter tablespace t2021 read only;
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
```
2.最後恢復

```
#目標端
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
```
3.匯入後設資料

```
#生成impdp
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e

#建立相關目錄
create directory TTS_DMP as '/home/oracle';
Grant read,write on directory TTS_DMP to system;

#建立使用者，注意密碼即將過期使用者無法查到（open）
select 'create user '||name||' identified by values '''||password||''';' from user$ where name in ('MYWORK','MYTEST','MY2021','T2021');

#匯入
impdp  system/oracle@mypdb directory=TTS_DMP logfile=tts2021_imp.log \
network_link=ttslink transport_full_check=no \
transport_tablespaces=MYWORK,MYTEST,MY2021,T2021 \
transport_datafiles='+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mywork_406_1052385811','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mywork_339_1061286829','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mytest_270_1025380271','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/mytest_342_1033661031','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/my2021_338_1061286849','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/my2021_337_1061295575','+DATA/TESTDB/9B872C593CBF32F8E053D808A8C0FF07/DATAFILE/t2021_336_1061295991'

#匯入內容參考
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jan 8 13:21:02 2021 elapsed 0 00:08:56

#表空間改為讀寫
alter tablespace mywork read write;
alter tablespace mytest read write;
alter tablespace my2021 read write;
alter tablespace t2021 read write;

#賦權
select 'alter user '||username||' default tablespace '||default_tablespace||' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' from dba_users where username in ('MYWORK','MYTEST','MY2021','T2021');
select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs where grantee in (select username from dba_users where  username  in('MYWORK','MYTEST','MY2021','T2021')); 
select 'grant '||PRIVILEGE||' to '||grantee||';' from DBA_SYS_PRIVS where grantee in (select username from dba_users where username  in('MYWORK','MYTEST','MY2021','T2021'));

```

4.驗證

```
--驗證
validate tablespace TS1, TS2 check logical;

--刪除dblink
drop database link ttslink;

--取消塊跟蹤，根據rman備份情況
alter database disable block change tracking;

#根據要求收集統計資訊，資料驗證等相關工作
```

#### 附
1.nfs配置參考

```
/stage_source 192.168.8.0/24(rw,no_root_squash,no_all_squash,sync)
exportfs -r
service rpcbind start
service nfs start

mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 192.168.8.24:/stage_source /stage_dest

```

2.RMAN 初始化方式參考

```
#如果使用rman 備份，需要定義xtt.properties:dfcopydir,stageondest
#初始化
$ORACLE_HOME/perl/bin/perl xttdriver.pl -p

#掛在的nfs，無需複製
scp /home/oracle/stage_source/* 192.168.80.210:/home/oracle/stage_dest

scp /home/oracle/xtt/rmanconvert.cmd 192.168.80.210:/home/oracle/xtt

#轉換開始，最終儲存位置為storageondest，轉換完成後，可以移除stageondest
$ORACLE_HOME/perl/bin/perl xttdriver.pl -c
```

【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19C

相關文章