> 支持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 ```