Oracle-使用XTTS方式迁移11G到PDB数据库

前言:

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.zip
  inflating: xtt.newproperties       
  inflating: xtt.properties          
  inflating: xttcnvrtbkupdest.sql    
  inflating: xttdbopen.sql           
  inflating: xttdriver.pl            
  inflating: xttprep.tmpl            
 extracting: xttstartupnomount.sql

源端确认需要迁移用户涉及的表空间

select DEFAULT_TABLESPACE
from dba_users
where username in ('OCS','BSS')
union
select distinct tablespace_name
from dba_segments
where owner in ('OCS','BSS')
union 
select distinct tablespace_name
from dba_tables
where owner in ('OCS','BSS')
union  
select distinct tablespace_name
from dba_indexes
where owner in ('OCS','BSS')
union  
select distinct tablespace_name
from dba_lobs
where owner in ('OCS','BSS');
---涉及的表空间
DEFAULT_TABLESPACE
------------------------------
TICKET_DATA1
TICKET_DATA2
TICKET_INDEX1
TICKET_INDEX2

查询表空间里面是否涉及其他用户对象

select owner,table_name,tablespace_name
from dba_tables
where tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in ('OCS','BSS');
select owner,table_name,tablespace_name
from dba_indexes
where tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in  ('OCS','BSS');
select owner,segment_name,tablespace_name
from dba_segments
where tablespace_name in ('TICKET_DATA1','TICKET_DATA2','TICKET_INDEX1','TICKET_INDEX2') and owner not in  ('OCS','BSS');
select owner,table_name,tablespace_name
from dba_lobs
where 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 set
cat /tmp/xtts/xtt.properties
tablespaces=TICKET_DATA1,TICKET_DATA2,TICKET_INDEX1,TICKET_INDEX2
platformid=13
src_scratch_location=/tmp/backup
dest_scratch_location=/tmp/backup
dest_datafile_location=+mgmt/cdbocs/pdbocs
parallel=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.test2022 as select * from dba_objects;
create table bss.test2022 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 sysdba
size of tablespace 8
No. of tablespaces per batch 1
TABLESPACE 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 mode
ORACLE instance started.
Total System Global Area 1845493760 bytes
Fixed Size                  8793976 bytes
Variable Size             620757128 bytes
Database Buffers         1207959552 bytes
Redo Buffers                7983104 bytes
rdfno 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_10
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
​
PL/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, COPY
ORA-06512: at line 284
TABLESPACE STRING :'TICKET_INDEX2'
Prepare newscn for Tablespaces: 'TICKET_INDEX2'
DECLARE*
ERROR at line 1:
ORA-20001: TABLESPACE(S) IS READONLY OR,
OFFLINE JUST CONVERT, COPY
ORA-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_10
PL/SQL procedure successfully completed.
Entering RollForwardAfter applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
​
PL/SQL procedure successfully completed.
​
--------------------------------------------------------------------
End of rollforward phase

源端获取bss,ocs用户创建语句

set linesize 400
set pagesize 400
set long 9999
set longc 9999
select dbms_metadata.get_ddl('USER','OCS') from dual;
--------------------------------------------------------------------------------
   CREATE USER "OCS" IDENTIFIED BY VALUES 'S:867CF33A6FD469A1EFCF4CECBF8432EBBF7
864F7E10A34751A89E46CFD69;0EEF32CD09805F9A'
      DEFAULT TABLESPACE "TICKET_DATA1"
      TEMPORARY TABLESPACE "TEMP"      
    
select dbms_metadata.get_ddl('USER','BSS') from dual;
--------------------------------------------------------------------------------
CREATE USER "BSS" IDENTIFIED BY VALUES 'S:794E754CCFE417133AAD13503F559006229
1F950862CDF6D4CC2346BA772;92CE18C581740B8C'
      DEFAULT TABLESPACE "TICKET_DATA1"
      TEMPORARY TABLESPACE "TEMP"

目标端pdbocs创建用户bss,ocs

--要进入pdb里面创建
sqlplus / as sysdba
alter session set container=pdbocs;
--创建ocs，由于表空间还未导入，所以先不设置用户默认表空间
 CREATE USER "OCS" IDENTIFIED BY VALUES 'S:867CF33A6FD469A1EFCF4CECBF8432EBBF7
864F7E10A34751A89E46CFD69;0EEF32CD09805F9A'
      TEMPORARY TABLESPACE "TEMP"   ;
--创建bss
  CREATE USER "BSS" IDENTIFIED BY VALUES 'S:794E754CCFE417133AAD13503F559006229
1F950862CDF6D4CC2346BA772;92CE18C581740B8C'
      TEMPORARY TABLESPACE "TEMP";

源端导出表空间元数据

--创建导出目录
create directory xttsdir as '/tmp/backup';
--执行导出
cat exp.par
​
dumpfile=xttdump.dmp
directory=xttsdir
exclude=statistics
transport_tablespaces=TICKET_DATA1,TICKET_DATA2,TICKET_INDEX1,TICKET_INDEX2
transport_full_check=yes
logfile=xtts_export.log 
​
expdp system/oracle parfile=exp.par
################################################
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=exp.par 
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/INDEX_STATISTICS
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:
  /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.1036340585
Datafiles 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.1036076315
Datafiles required for transportable tablespace TICKET_INDEX1:
  +DATA/dbocs/datafile/ticket_index1.297.1036076349
Datafiles 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.1036337527
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Nov 12 13:28:40 2022 elapsed 0 00:00:59

目标端pdbocs导入表空间元数据

--​dmp拷贝到目标端
scp /tmp/backup/xttdump.dmp oracle@dest:/tmp/backup/
--创建导入目录
sqlplus / as sysdba
alter 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.par
​
dumpfile=xttdump.dmp
directory=xttsdir
transport_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.dbf
logfile=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 2022
​
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
​
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@192.168.1.200:1521/pdbocs parfile=imp.par 
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/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Dec 3 22:16:07 2022 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.TEST2022;
  COUNT(*)
----------
     86496
​
SQL> select count(*) from ocs.test2022;
​
  COUNT(*)
----------
     86496

其他对象导入

--第二次元数据导入,将第一次没有导入的过程，视图，包，触发器,权限导入。
expdp system/oracle directory=xttsdir dumpfile=metadump.dmp   schemas=OCS,BSS content=metadata_only  exclude=index,table,constraint,statistics
impdp 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方式的升级还是非常有价值的。