前言:
Oracle11G迁移到12c之后的PDB升级方式主要有expdp/impdp,TTS以及XTTS，其中，expdp/impdp的迁移方式适用于小数据量的迁移，停机窗口较长，TTS的迁移方式使用于中小数据量的迁移，停机窗口长，而XTTS由于支持增量同步，跨平台迁移，所以适用于大数据量的迁移，并且停机窗口相对较短。
本文接下来将演示的迁移案例是通过XTTS方式将Oracle11g迁移到Oracle12.2上的pdb数据库。
环境信息:
迁移案例:
将Oracle11G上的数据迁移到Oracle12.2上的pdb，主要的涉及用户为ocs，bss，采用xtts的方式进行，使用rman_xttconvert_VER4脚本
源端安装xtts工具
[oracle@source xtts]$ unzip /tmp/rman_xttconvert_VER4.3.zip Archive: /tmp/rman_xttconvert_VER4.3.zipinflating: xtt.newproperties inflating: xtt.propertiesinflating: xttcnvrtbkupdest.sql inflating: xttdbopen.sql inflating: xttdriver.pl inflating: xttprep.tmpl extracting: xttstartupnomount.sql
源端确认需要迁移用户涉及的表空间
select DEFAULT_TABLESPACEfrom dba_userswhere username in ('OCS','BSS')unionselect distinct tablespace_namefrom dba_segmentswhere owner in ('OCS','BSS')union select distinct tablespace_namefrom dba_tableswhere owner in ('OCS','BSS')union select distinct tablespace_namefrom dba_indexeswhere owner in ('OCS','BSS')union select distinct tablespace_namefrom dba_lobswhere owner in ('OCS','BSS');---涉及的表空间DEFAULT_TABLESPACE------------------------------TICKET_DATA1TICKET_DATA2TICKET_INDEX1TICKET_INDEX2
查询表空间里面是否涉及其他用户对象
select owner,table_name,tablespace_namefrom dba_tableswhere tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in ('OCS','BSS');select owner,table_name,tablespace_namefrom dba_indexeswhere tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in ('OCS','BSS');select owner,segment_name,tablespace_namefrom dba_segmentswhere tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in ('OCS','BSS');select owner,table_name,tablespace_namefrom dba_lobswhere tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in ('OCS','BSS');
表空间自包含检查
--严格检查:full_check=true --要去表关联的约束，索引，lob字段等对象都需要包含在整个传输表空间集里面--执行自包含检查exec dbms_tts.transport_set_check('TICKET_DATA1,TICKET_DATA2,TICKET_INDEX1,TICKET_INDEX2',TRUE,TRUE);--查询是否有自包含对象，对于出现的对象，都需要进行处理，迁移到这些表空间里面select * from transport_set_violations;
目标端创建配置空PDBOCS
通过dbca或者create pluggable database命令行创建
源端配置xtt.properties文件
#tablespace要迁移的表空间名称#平台的ID，LINUX为13 通过可以V$TRANSPORTABLE_PLATFORM查询确认#src_scratch_location源端备份存放路径#dest_scratch_location目标端备份存放的路径#目标端数据文件恢复路径#usermantransport=1 - It is recommended this be set if the source database is running 12c or higher. This causes new 12c (and higher) functionality to be used when this parameter is setcat /tmp/xtts/xtt.propertiestablespaces=TICKET_DATA1,TICKET_DATA2,TICKET_INDEX1,TICKET_INDEX2platformid=13src_scratch_location=/tmp/backupdest_scratch_location=/tmp/backupdest_datafile_location=+mgmt/cdbocs/pdbocsparallel=8
将源端的脚本以及配置文件复制到目标端
scp -rp /tmp/xtts/ oracle@dest:/tmp
数据初始化:xtts源端执行表空间全量备份
--设置xtts的输出文件路径,跟xtts安装目录在相同的路径下export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --backup --debug 3
将xtts备份文件以及res.txt文件拷贝到目标端
#res.txt为表空间的数据文件信息,备份的scn号scp -rp /tmp/backup/ oracle@dest:/tmp/scp -rp /tmp/xtts/res.txt oracle@dest:/tmp/xtts/
目标端执行xtts恢复
export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --restore --debug 3#会将文件转化成指定的平台，并恢复到数据文件目录里面RMAN> convert from platform 'Linux x86 64-bit' datafile '/tmp/backup/TICKET_INDEX2_10.tf' format '+mgmt/TICKET_INDEX2_10.dbf' ;2>
源端备份增量数据
--备份之前，我们创建测试数据，验证增量备份有效alter tablespace TICKET_DATA1 add datafile size 100M autoextend on next 100M;create table ocs.test as select * from dba_objects;create table bss.test as select * from dba_objects;--备份增量数据export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --backup --debug 3--会备份新添加的数据文件============================================================1 new datafiles added=============================================================TICKET_DATA1,/tmp/backup/TICKET_DATA1_17.tf============================================================--增量备份/ as sysdbasize of tablespace 8No. of tablespaces per batch 1TABLESPACE STRING :'TICKET_DATA1'Prepare newscn for Tablespaces: 'TICKET_DATA1'TABLESPACE STRING :'TICKET_DATA2'Prepare newscn for Tablespaces: 'TICKET_DATA2'TABLESPACE STRING :'TICKET_INDEX1'Prepare newscn for Tablespaces: 'TICKET_INDEX1'TABLESPACE STRING :'TICKET_INDEX2'Prepare newscn for Tablespaces: 'TICKET_INDEX2'TABLESPACE STRING :''''''''Prepare newscn for Tablespaces: ''''''''New /tmp/xtts/xttplan.txt with FROM SCN's generated
将xtts增量备份文件以及res.txt文件拷贝到目标端
--增加的数据文件scp TICKET_DATA1_17.tf oracle@dest:/tmp/backup/--增量备份(incrbackups.txt存放增量备份的路径)scp 'cat incrbackups.txt' oracle@dest:/tmp/backup/--res文件scp -rp /tmp/xtts/res.txt oracle@dest:/tmp/xtts/
目标端执行xtts增量恢复
注意:增量恢复会重启实例，如果上面有跑生产，可以通过指定中间实例进行增量恢复cnvinst_sid################################################--------------------------------------------------------------------Start rollforward--------------------------------------------------------------------ROLLFORWARD: Starting DB in nomount modeORACLE instance started.Total System Global Area 1845493760 bytesFixed Size 8793976 bytesVariable Size 620757128 bytesDatabase Buffers 1207959552 bytesRedo Buffers7983104 bytesrdfno 6,11,17#################################################--执行增量恢复export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --restore --debug 3#################################################执行完成CONVERTED BACKUP PIECE/tmp/backup/xib_0u1ckvan_1_1_9_14_10PL/SQL procedure successfully completed.Entering RollForwardAfter applySetDataFileDone: applyDataFileToDone: applyDataFileToDone: RestoreSetPieceDone: RestoreBackupPiecePL/SQL procedure successfully completed.
源端最后一次增量备份(应用停止写入，正式迁移)
源端将表空间设置为只读
alter tablespace TICKET_DATA1 read only;alter tablespace TICKET_DATA2 read only;alter tablespace TICKET_INDEX1 read only;alter tablespace TICKET_INDEX2 read only;
源端备份增量数据
--备份增量数据export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --backup --debug 3##############################在只读表空间以下报错可以忽略Warning:------Warnings found in executing /tmp/xtts/backup_Nov12_Sat_12_49_09_957//xttpreparenextiter.sql####################################################################DECLARE*ERROR at line 1:ORA-20001: TABLESPACE(S) IS READONLY OR,OFFLINE JUST CONVERT, COPYORA-06512: at line 284TABLESPACE STRING :'TICKET_INDEX2'Prepare newscn for Tablespaces: 'TICKET_INDEX2'DECLARE*ERROR at line 1:ORA-20001: TABLESPACE(S) IS READONLY OR,OFFLINE JUST CONVERT, COPYORA-06512: at line 284
将xtts最后一次增量备份文件以及res.txt文件拷贝到目标端
--增量备份(incrbackups.txt存放增量备份的路径)scp 'cat incrbackups.txt' oracle@dest:/tmp/backup/--res文件scp -rp /tmp/xtts/res.txt oracle@dest:/tmp/xtts/
目标端执行xtts最后一次增量恢复
--执行增量恢复export TMPDIR=/tmp/xtts$ORACLE_HOME/perl/bin/perl /tmp/xtts/xttdriver.pl --restore --debug 3#################################################执行完成CONVERTED BACKUP PIECE/tmp/backup/xib_121cl0ku_1_1_9_14_10PL/SQL procedure successfully completed.Entering RollForwardAfter applySetDataFileDone: applyDataFileToDone: applyDataFileToDone: RestoreSetPieceDone: RestoreBackupPiecePL/SQL procedure successfully completed.--------------------------------------------------------------------End of rollforward phase
源端获取bss,ocs用户创建语句
set linesize 400set pagesize 400set long 9999set longc 9999select dbms_metadata.get_ddl('USER','OCS') from dual;--------------------------------------------------------------------------------CREATE USER "OCS" IDENTIFIED BY VALUES 'S:867CF33A6FD469A1EFCF4CECBF8432EBBF7864F7E10A34751A89E46CFD69;0EEF32CD09805F9A'DEFAULT TABLESPACE "TICKET_DATA1"TEMPORARY TABLESPACE "TEMP"select dbms_metadata.get_ddl('USER','BSS') from dual;--------------------------------------------------------------------------------CREATE USER "BSS" IDENTIFIED BY VALUES 'S:794E754CCFE417133AAD13503F5590062291F950862CDF6D4CC2346BA772;92CE18C581740B8C'DEFAULT TABLESPACE "TICKET_DATA1"TEMPORARY TABLESPACE "TEMP"
目标端pdbocs创建用户bss,ocs
--要进入pdb里面创建sqlplus / as sysdbaalter session set container=pdbocs;--创建ocs，由于表空间还未导入，所以先不设置用户默认表空间CREATE USER "OCS" IDENTIFIED BY VALUES 'S:867CF33A6FD469A1EFCF4CECBF8432EBBF7864F7E10A34751A89E46CFD69;0EEF32CD09805F9A'TEMPORARY TABLESPACE "TEMP" ;--创建bssCREATE USER "BSS" IDENTIFIED BY VALUES 'S:794E754CCFE417133AAD13503F5590062291F950862CDF6D4CC2346BA772;92CE18C581740B8C'TEMPORARY TABLESPACE "TEMP";
源端导出表空间元数据
--创建导出目录create directory xttsdir as '/tmp/backup';--执行导出cat exp.pardumpfile=xttdump.dmpdirectory=xttsdirexclude=statisticstransport_tablespaces=TICKET_DATA1,TICKET_DATA2,TICKET_INDEX1,TICKET_INDEX2transport_full_check=yeslogfile=xtts_export.log expdp system/oracle parfile=exp.par################################################Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=exp.par Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEX/INDEXProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:/tmp/backup/xttdump.dmp******************************************************************************Datafiles required for transportable tablespace TICKET_DATA1:+DATA/dbocs/datafile/ticket_data1.273.1120566217+DATA/dbocs/datafile/ticket_data1.294.1036076261+DATA/dbocs/datafile/ticket_data1.303.1036340585Datafiles required for transportable tablespace TICKET_DATA2:+DATA/dbocs/datafile/ticket_data2.258.1036349799+DATA/dbocs/datafile/ticket_data2.260.1036340629+DATA/dbocs/datafile/ticket_data2.295.1036076315Datafiles required for transportable tablespace TICKET_INDEX1:+DATA/dbocs/datafile/ticket_index1.297.1036076349Datafiles required for transportable tablespace TICKET_INDEX2:+DATA/dbocs/datafile/ticket_index2.270.1036350007+DATA/dbocs/datafile/ticket_index2.300.1036076367+DATA/dbocs/datafile/ticket_index2.302.1036337527Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Nov 12 13:28:40 elapsed 0 00:00:59
目标端pdbocs导入表空间元数据
--dmp拷贝到目标端scp /tmp/backup/xttdump.dmp oracle@dest:/tmp/backup/--创建导入目录sqlplus / as sysdbaalter session set container=pdbocs;create directory xttsdir as '/tmp/backup';--获取transport_datafiles路径，从dfcopy.txt[oracle@rac1 xtts]$ cat /tmp/xtts/dfcopy.txt|awk -F ':' '{a=a$4"," } END {print a}'+mgmt/cdbocs/pdbocs/TICKET_INDEX1_8.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_11.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_7.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_13.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_9.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_6.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_14.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_12.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_10.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_17.dbf,--执行导入cat imp.pardumpfile=xttdump.dmpdirectory=xttsdirtransport_datafiles=+mgmt/cdbocs/pdbocs/TICKET_INDEX1_8.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_11.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_7.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_13.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_9.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_6.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_14.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA2_12.dbf,+mgmt/cdbocs/pdbocs/TICKET_INDEX2_10.dbf,+mgmt/cdbocs/pdbocs/TICKET_DATA1_17.dbflogfile=tts_import.log--要连入pdb里面导入impdp system/oracle@dest:1521/pdbocs parfile=imp.par--导入完成Import: Release 12.2.0.1.0 - Production on Sat Dec 3 22:15:47 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@192.168.1.200:1521/pdbocs parfile=imp.par Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEX/INDEXProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Dec 3 22:16:07 elapsed 0 00:00:17
验证表空间是否正常
validate tablespace PDBOCS:TICKET_DATA1,PDBOCS:TICKET_DATA2,PDBOCS:TICKET_INDEX1,PDBOCS:TICKET_INDEX2 check logical;
表空间设置为读写
alter tablespace TICKET_DATA1 read write;alter tablespace TICKET_DATA2 read write;alter tablespace TICKET_INDEX1 read write;alter tablespace TICKET_INDEX2 read write;
验证抽查数据
SQL> select count(*) from bss.TEST;COUNT(*)----------86496SQL> select count(*) from ocs.test;COUNT(*)----------86496
其他对象导入
--第二次元数据导入,将第一次没有导入的过程，视图，包，触发器,权限导入。expdp system/oracle directory=xttsdir dumpfile=metadump.dmp schemas=OCS,BSS content=metadata_only exclude=index,table,constraint,statisticsimpdp system/oracle@dest:1521/pdbocs directory=xttsdir dumpfile=metadump schemas=OCS,BSS
目标端用户统计信息收集
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'OCS',ESTIMATE_PERCENT=>20,method_opt=>'forall columns size skewonly',cascade=>true,force=>true,degree=>8);exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BSS',ESTIMATE_PERCENT=>20,method_opt=>'forall columns size skewonly',cascade=>true,force=>true,degree=>8);
总结:
整个迁移的过程步骤相对是比较复杂的，但通过xtts增量同步的方式，可以大大降低停机的窗口,特别是在大数据量，跨平台的情况下，xtts方式的升级还是非常有价值的。
