【XTTS】使用XTTS传输表空间将Oracle11.2.0.4数据迁移至Oracle19C

  • xysoul_云龙

    2021-01-08 14:10:55
  • Oracle
  • 原创

支持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
请使用浏览器的分享功能分享到微信等