Oracle 使用xtts升级11g-to-19c

本文主要描述使用xtts升级Oracle11g 到19c的操作步骤，基于dbms_file_transfer全备+rman增量备份的xtts数据迁移，通常用于短时间(

牛牛的笔记

4250人浏览 · 2022-07-25 23:06:48

牛牛的笔记 · 2022-07-25 23:06:48 发布

前言:

本文主要描述使用xtts升级Oracle11g 到19c的操作步骤，基于dbms_file_transfer全备+rman增量备份的xtts数据迁移，通常用于短时间(<4小时)的停机窗口进行大数据量，跨数据库版本，跨平台不同endian format格式的数据库迁移。

xtts前置条件:

1 目前的官方提供的xtts工具不支持window平台

2 使用xtts，源端数据库版本都需要10.2.0.3以上版本

3 数据库版本必须为企业版不能为标准版本

4 源端数据库不能大于目标端数据库

5 源端数据库需要开启归档模式

6 目标端数据库版本都需要11.2.0.4以上

7 rman管道不能配置压缩

8 rman备份类型只能使用backupset方式，不能有copy

9 传输表空间状态必须是在线的

10 xtts虽然首要的操作系统为linux，但也可以用于unix操作系统平台，只是需要确保源端和目标端都是11.2.0.4以上

11 源端的版本低于或等于目标端，因此，xtts方法也可以作为一种upgrade的方式(各版本之前的升级路线，可以参考官方提供的19c升级路线)

12 全部的操作都是通过oracle用户身份osdba进行身份验证

13 可以使用dbms_file_transfer进行数据初始化，但要去目标端数据库11.2.0.4以上

也可以通过使用rman进行初始化

14 过程不支持在备库或者快照库执行

15 只同步非sys用户表空间,不同步sysaux,system

16 XTTS最后的迁移时间其实取决于元数据的大小，xtts只是同步那些段对象，对于pl/sql objects, sequences等存放于system，sysaux表空间的需要额外使用expdp/impdp进行导出导入)

17 对于目标库版本为11.2.0.3或往前的版本，需要使用11.2.0.4的临时实例进行增量恢复，或是存在生产库往生产库迁移的方式，由于增量恢复需要重启实例，所以可以通过建立临时实例进行增量恢复解决

rman_xttconvert_v3工具介绍:

rman_xttconvert_v3主要包含两个部分xttdriver.pl(用户执行xtts的主程序)以及xtt.properties(配置xtts的参数文件)

xttdriver.pl

---脚本登录数据库的方式为身份认证，通过ORACLE_SID，ORACLE_HOME确认要连接的实例
Perl script xttdriver.pl script that is run to perform the main steps of the XTTS with Cross Platform Incremental Backup procedure.
-S:prepare source for transfer, option is used only when Prepare phase method is dbms_file_transfer(源端使用)
-G:get datafiles from source,option is used only when Prepare phase method is dbms_file_transfer(目标端使用)
-p:prepare source for backup  only when Prepare phase method is RMAN backup.
   Prepare step is run once on the source system during Phase 2B with the environment (ORACLE_HOME and ORACLE_SID) set to the source database.
   This step connects to the source database and runs the xttpreparesrc.sql script once for each tablespace to be transported, as configured in xtt.properties. 
   xttpreparesrc.sql does the following:
   1 Verifies the tablespace is online, in READ WRITE mode, and contains no offline datafiles.
   2 Identifies the SCN that will be used for the first iteration of the incremental backup step and writes it into file $TMPDIR/xttplan.txt.
   3 Creates the initial datafile copies on the destination system in the location specified by the parameter dfcopydir set in xtt.properties.  These datafile copies must be transferred manually to the destination system.
   4 Creates RMAN script $TMPDIR/rmanconvert.cmd that will be used to convert the datafile copies to the required endian format on the destination system.
-c convert datafiles option is used only when Prepare phase method is RMAN backup.
Convert datafiles step is run once on the destination system during Phase 2B with the environment (ORACLE_HOME and ORACLE_SID) set to the destination database.
This step uses the rmanconvert.cmd file created in the Prepare step to convert the datafile copies to the proper endian format.  
Converted datafile copies are written on the destination system to the location specified by the parameter storageondest set in xtt.properties.

xtt.properties，配置xtts的参数文件

Parameter file xtt.properties: the file which stores site-specific configuration.
tablespaces：需要迁移的表空间名，如果有多个表空间，使用逗号分开（注意大小写敏感，所以要填大写的表空间名）
platformid：数据库平台号（v$database视图platform_id）
dstdir:目标端数据库文件存放路径，只用于dbms_file_transfer进行传输(填directory name)
srcdir:源端数据库文件存放路径，只用于dbms_file_transfer进行传输，可以N:1,N:N(填directory name)
srclink:目标端端指向源端的dblink，dbms_file_transfer通过此dblink进行传输，用于dbms_file_transfer进行传输
dfcopydir：源机上rman复制数据文件进行存放的目录,使用rman进行初始化用到(全量数据)，该目录要有足够的空间存放备份
backupformat：源机上增量备份的存放目录(增量数据)，该目录要有足够的空间存放备份
stageondest：目标机上存放源机传输的备份的目录(全量+增量)，该目录要有足够的空间存放备份
storageondest：目标机上存放转换后的数据文件(即目标机数据库的数据文件所在目录),该目录要有足够的空间存放数据文件
backupondest：目标机上存放转换后的应用增量数据的目录,(即目标机数据库的数据文件所在目录),该目录要有足够的空间存放数据文件
cnvinst_home：增量转换实例的家目录
cnvinst_sid：执行增量转换的实例
asm_home:ORACLE_HOME for the ASM instance that runs on the destination system.NOTE: If backupondest is set to a file system location, then comment out both asm_home and asm_sid.
asm_sid:ORACLE_SID for the ASM instance that runs on the destination system.
parallel:Defines the degree of parallelism set in the RMAN CONVERT command file rmanconvert.cmd. This file is created during the prepare step and used by RMAN in the convert datafiles step to convert the datafile copies on the destination system.  
         If this parameter is unset, xttdriver.pl uses parallel=8.
     NOTE: RMAN parallelism used for the datafile copies created in the RMAN Backup prepare phase and the incremental backup created in the rollforward phase is controlled by the RMAN configuration on the source system. It is not controlled by this parameter. 
rollparallel：应用增量数据的并行度，对应-r 操作
getfileparallel：数据文件转换时的并行度对应-g 操作

测试环境:

xtts:使用v3版本(rman_xttconvert_v3)，注意v4版本(rman_xttconvert_VER4.3)方式不一样，更便捷简单
增量同步方式采用了中间实例，避免目标库在进行增量数据恢复的时候，重启数据库
环境都是linux，自己的测试环境有限，没有unix
源端主机linux:rac1-rac2 ,实例:xtttest,版本11.2.0.4
目标主机linux:ol7db, 实例:orcltest,版本19.3.0.0
中间实例linux:ol7db, 实例:xttscvn,版本11.2.0.4

测试场景:

1 对xttsuser用户进行迁移，主要涉及表xttstab,xttstab2,涉及表空间xttstab(存放表数据),xttsind(存放表索引),xttslob(存放lob字段)，初始化为100W数据，第一次增量为100W数据，第二次增量会对表空间xttslob添加数据文件并新增表空间xttstemp，并再新表空间创建新表xttstab3,最后一次增量操作增量100W数据

2 模拟增量数据在中间实例进行，避免目标主机增量操作期间重启

3 采用dbms_file_transfer 进行初始化

4 使用rman备份增量的方式，进行增量数据的追加

添加测试数据：

create tablespace xttstab datafile size 500M autoextend off;
alter tablespace  xttstab add datafile size 500m autoextend off;
create tablespace xttsind datafile size 500M autoextend off;
alter tablespace xttsind add datafile size 500m autoextend off;
create tablespace xttslob datafile size 1g autoextend off;

create user xttsuser identified by "xttsuser" default tablespace xttstab;
grant resource,connect,unlimited tablespace  to xttsuser;
create table xttsuser.xttstab(id number(10),name varchar2(200),record1 clob) tablespace xttstab lob(record1) store as securefile(tablespace xttslob) ;
create index xttsuser.xttsind1 on xttsuser.xttstab(id) tablespace xttsind;
create table xttsuser.xttstab2(id  number ) tablespace xttstab;
create index xttsuser.xttsind3 on xttsuser.xttstab2(id) tablespace xttsind;

declare
v_count number(10):=0;
begin
   for i in 1..1000000 loop
     insert into xttsuser.xttstab values(i,'aaaaaaaaaaaa','cccccccccccccc');
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/


declare
v_count number(10):=0;
begin
   for i in 1..1000000 loop
     insert into xttsuser.xttstab2 values(i);  
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/

全量数据初始化前检查:

1 源端和目标端版本大于11.2.0.4，并且源端<=目标端
select * from v$version;
2 源端和目标端补丁版本
$ORACLE_HOME/OPatch/opatch lspatches
3 数据库开启归档模式
archive log list 
4 rman管道不能配置压缩，备份类型为backupset
rman target /
show all
5 源端目标端字符集必须一致
sqlplus / as sysdba
select (select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_LANGUAGE')||'_'||
        (select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_TERRITORY')||'.'||
        (select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_CHARACTERSET')
   from dual
;

6 国家字符集一致
select property_name,property_value
from database_properties 
where property_name='NLS_NCHAR_CHARACTERSET';

7 时区一致
SELECT DBTIMEZONE FROM DUAL;

8 数据库软件组件一致
select * from v$option;

9 获取平台platform_id以及endian_format
select platform_id from v$database;

select a.platform_id,a.platform_name,a.endian_format
from v$transportable_platform a,v$database b
where  a.platform_id=b.platform_id;


10 开启快追踪，提示增量数据备份的速度
alter database enable block change tracking using file '+DATA';

11 db_file参数 源端<=目标端or 目标端>传输的表空间数量
show parameter db_file

12 传输表空间的数据文件为online
select name,status 
from v$datafile where ts# in (select ts# from v$tablespace where name in ('USERS'));

13 临时表查询(xtts最后导入元数据的时候，不支持导入临时表，需要手动获取)
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) from dba_tables where temporary='Y' and owner=upper('XTTS1');

14 查询集群索引，在xtts之后，需要analyze分析是否存在损坏，需要重建
select owner,cluster_name,tablespace_name
from dba_clusters
where tablespace_name not in ('SYSTEM','SYSAUX')
;

15 表空间自包含检查，将需要传输的表空间进行检查
PROCEDURE TRANSPORT_SET_CHECK
 Argument Name      Type      In/Out           Default?
 ------------------------------ ----------------------- ------ --------
 TS_LIST        CLOB      IN
 INCL_CONSTRAINTS    BOOLEAN     IN               DEFAULT
 FULL_CHECK        BOOLEAN     IN               DEFAULT

执行严格检查:full_check=true 
---要去表关联的约束，索引，lob字段等对象都需要包含在整个传输表空间集里面
---执行自包含检查
exec dbms_tts.transport_set_check('XTTSTAB,XTTSLOB,XTTSIND',TRUE,TRUE);
---查询是否有自包含对象
select * from transport_set_violations;
---对于出现自包含的对象，需要进行迁移，或者扩到传输表空间的数据以达到完全自包含
16 检查传输表空间集是否存在sys用户
select owner,table_name
from dba_tables
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('SYS','SYSTEM')
;

select owner,table_name
from dba_indexes
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('SYS','SYSTEM')
;

select owner,segment_name
from dba_segments
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('SYS','SYSTEM')
;

select owner,table_name
from dba_lobs
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('SYS','SYSTEM')
;


select owner,cluster_name,tablespace_name
from dba_clusters
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('SYS','SYSTEM')
;

17 检查传输表空间包含的用户是否存在system,sysaux表空间中
select owner,table_name
from dba_tables
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('XTTSUSER')
;

select owner,table_name
from dba_indexes
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('XTTSUSER')
;

select owner,segment_name
from dba_segments
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('XTTSUSER')
;

select owner,table_name
from dba_lobs
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('XTTSUSER')
;


select owner,cluster_name,tablespace_name
from dba_clusters
where tablespace_name in ('XTTSLOB','XTTSTAB','XTTSIND') and owner in ('XTTSUSER')
;

17 检查是否存在外部表
select owner,table_name
from dba_external_tables
----对于存在外部表，需要在xtts迁移后，手动把外部文件迁移

18 清空禁用回收站,重启生效
purge dba_recyclebins

19 获取用户权限
SET LONG 9999
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) DDL_SQL FROM DBA_USERS 
WHERE USERNAME IN (select owner  from dba_segments 
where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE'
) 
group by owner,tablespace_name) AND USERNAME NOT IN ('SYS')
;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) DDL_SQL FROM DBA_USERS 
WHERE USERNAME IN (select owner  from dba_segments 
where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE'
) 
group by owner,tablespace_name) AND USERNAME NOT IN ('SYS')
;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) DDL_SQL FROM DBA_USERS 
WHERE USERNAME IN (select owner  from dba_segments 
where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE'
) 
group by owner,tablespace_name) AND USERNAME NOT IN ('SYS')
;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) DDL_SQL FROM DBA_USERS 
WHERE USERNAME IN (select owner  from dba_segments 
where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE'
) 
group by owner,tablespace_name) AND USERNAME NOT IN ('SYS')

20 获取用户对象
select owner, object_type, count(*)
from dba_objects
where object_name not like 'BIN%'
and owner in ('xttsuser')
group by owner, object_type
order by 1,2 desc
;

第一次初始化：

---查看表数据
select count(*) from xttsuser.xttstab;
select count(*) from xttsuser.xttstab2;
SQL> 
  COUNT(*)
----------
   1000000

SQL> 
  COUNT(*)
----------
   1000000

创建中间实例xtt(目标端操作):

export ORACLE_SID=xtt
cat << EOF > $ORACLE_HOME/dbs/initxtt.ora
db_name=xtt
compatible=11.2.0.4.0
EOF
sqlplus / as sysdba
startup nomount

配置dbms_file_transfer进行数据初始化：

---源库创建,目录为数据库传输表空间存放目录
create directory sourcedir as '+DATA/xttsdb/datafile';
---目标库创建，目录为数据库传输表空间存放目录
create directory destdir as '/u01/app/oracle/oradata/ORCL/datafile';
---目标库创建dblink,指向源端
create public database link ttslink connect to system identified by oracle using 
'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.85)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xttsdb)
    )
  )';
---测试dblink
select * from dual@ttslink;
---安装以及配置xtts(源端目标端都操作)
---创建安装目录并解压配置(源端目标端都操作)
mkdir -p /home/oracle/xtts
cd /home/oracle/xtts
unzip /tmp/rman_xttconvert_v3.zip
---创建xtts文件放置目录(源端目标端都操作)
mkdir -p /home/oracle/xttsdir
---配置环境变量(源端目标端都操作)
export TMPDIR=/home/oracle/xttsdir
export PERL5LIB=$ORACLE_HOME/perl/lib
#export XTTDEBUG=3
#TMPDIR
指向XTTS脚本存放位置，后续在使用perl执行xtts脚本时，会生成一些文件，这些文件默认存放到TMPDIR指向的位置
#PERL5LIB
指向perl的lib库，如果该环境变量不设置，在使用perl执行xtts脚本时，会报错
#XTTDEBUG
执行xtt脚本时，调试信息会显示出来
---设置xtts参数信息(源端操作)
cd /home/oracle/xtts
vi xtt.properties
tablespaces=XTTSTAB,XTTSIND,XTTSLOB
platformid=13
srcdir=SOURCEDIR
dstdir=DESTDIR
srclink=TTSLINK
backupformat=/incbackup
stageondest=/backup
storageondest=/u01/app/oracle/oradata/ORCL/datafile
backupondest=/u01/app/oracle/oradata/ORCL/datafile
cnvinst_home=/u01/app/oracle/product/11.2.0.4/dbhome_1
cnvinst_sid=xtt
getfileparallel=4
rollparallel=4


---将xtts配置文件拷贝到源端(目标端操作)
scp xtt.properties ol7db:/home/oracle/xtts/
---第一次初始化(源端操作)
---使用dbms_file_transfer进行传输
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -S
---会在$TMPDIR下面生成以下文件
xttnewdatafiles.txt
getfile.sql
----把以下文件拷贝到目标端对应的$TMPDIR
scp getfile.sql xttnewdatafiles.txt oracle@ol7db:/home/oracle/xttsdir

----目标端进行恢复(目标端操作)
---注意ORACLE_HOME以及ORACLE_SID，将会直接关联到操作的数据库
---若第一次恢复失败，执行第二次时，需要根据提示删除/home/oracle/xttsdir/FAILED，再执行
#cd /home/oracle/xtts
#$ORACLE_HOME/perl/bin/perl xttdriver.pl -G
#对应的文件会恢复到dstdir

添加第一次增量数据

---第一次增量
---源端插入xttstab,xttstab2表100W数据
declare
v_count number(10):=0;
begin
   for i in 1000001..2000000 loop
     insert into xttsuser.xttstab values(i,'aaaaaaaaaaaa','cccccccccccccc');
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/


declare
v_count number(10):=0;
begin
   for i in 1000001..2000000 loop
     insert into xttsuser.xttstab2 values(i);
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/

XTTS进行第一次增量备份同步(源端操作)

---注意ORACLE_HOME以及ORACLE_SID，将会直接关联到操作的数据库
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
增量备份存在backupformat
---以下文件会被此次增量备份更新，需要跟备份一起传输到目标端(源端)
This step will create an incremental backup for all tablespaces listed in xtt.properties.  It creates the following files which must always be moved to the destination along with the backup files: 
tsbkupmap.txt
incrbackups.txt

cd /home/oracle/xttsdir
---拷贝以下增量文件到目标端stageondest(源端操作)
scp `cat incrbackups.txt` oracle@ol7db:/backup
---拷贝以下文件到目标端$TMPDIR(源端操作)
scp tsbkupmap.txt incrbackups.txt oracle@ol7db:/home/oracle/xttsdir
---进行增量恢复(目标端操作)
---因为增量恢复用的是11g的中间实例(增量数据会重启库，不影响19c的正常使用)，所以变量要换成11g的,最终目标库是19c
source /home/oracle/.oracle11g
---追增量数据
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
---更新下一次增量备份的from_scn(源端操作)
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
---会生成xttplan文件记录scn
New /home/oracle/xttsdir/xttplan.txt with FROM SCN s generated

添加第二次增量数据

---为xttslob表空间添加新的数据文件
alter tablespace xttslob add datafile size 500M;
---新建表空间xttstemp并在表空间里面新增表
create tablespace xttstemp datafile size 100M;
create table xttsuser.xttstab3 (id number) tablespace xttstemp;
insert into xttsuser.xttstab3 values(1);
commit;
---源端插入xttstab,xttstab2表100W数据
declare
v_count number(10):=0;
begin
   for i in 1000001..2000000 loop
     insert into xttsuser.xttstab values(i,'aaaaaaaaaaaa','cccccccccccccc');
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/


declare
v_count number(10):=0;
begin
   for i in 1000001..2000000 loop
     insert into xttsuser.xttstab2 values(i);
     v_count:=v_count+1;
     if v_count>=10000 THEN
       commit;
       v_count:=0;
     end if;
   end loop;
   commit;
end;
/

XTTS进行第二次增量备份同步(源端操作)

---进行第二次增量备份(源端操作)
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
----处理新增的表空间
----配置文件更新tablespace(源端操作)
vi xtt.properties
tablespaces=XTTSTAB,XTTSIND,XTTSLOB,XTTSTEMP
----把xtt.properties文件拷贝到目标端(源端操作)
scp xtt.properties ol7db:/home/oracle/xtts/
----对于新增数据文件，增量会失败，并提示进行一下步骤处理新增数据文件
 ============================================================
11,12 2 new datafiles added
=============================================================
============================================================
Running prepare cmd for new files xttslob.291.106209473.(.*),xttstemp.309.106209474.(.*)
=============================================================

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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
 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
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----处理新增数据文件
1  复制源端$TMPDIR文件fixnewdf.txt 到目标端$TMPDIR(源端操作)
scp /home/oracle/xttsdir/fixnewdf.txt oracle@ol7db:/home/oracle/xttsdir/
2  执行修复新文件操作(目标端操作),这一步骤依然使用dbms_file_transfer进行传输,通过dblink拉数据文件，所以用19c的库进行拉取
source /home/oracle/.bash_profile （19c的环境变量）
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf
3 删除FAILIED
rm /home/oracle/xttsdir/FAILED 
4 重新再执行增量备份(源端)
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
----拷贝以下增量文件到目标端stageondest(源端操作)
cd /home/oracle/xttsdir
scp `cat incrbackups.txt` oracle@ol7db:/backup
----拷贝以下文件到目标端$TMPDIR(源端操作)
scp tsbkupmap.txt incrbackups.txt oracle@ol7db:/home/oracle/xttsdir
---进行增量恢复(目标端操作)
---因为增量恢复用的是11g的中间实例(增量数据会重启库，不影响19c的正常使用)，所以变量要换成11g的,最终目标库是19c
source /home/oracle/.oracle11g
---追增量数据
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
---更新下一次增量备份的from_scn(源端操作)
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s

进行最后一次增量数据同步(即应用停机，正式迁移开始)

---源端传输表空间设置只读(源端操作)
select 'alter tablespace '||tablespace_name||' read only;'
from dba_tablespaces
where tablespace_name in ('XTTSTAB','XTTSIND','XTTSLOB','XTTSTEMP');
---------------------------------------
alter tablespace XTTSIND read only;
alter tablespace XTTSLOB read only;
alter tablespace XTTSTAB read only;
alter tablespace XTTSTEMP read only;
---进行最后一次增量(final increment)(源端操作)
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
---拷贝以下增量文件到目标端stageondest(源端操作)
cd /home/oracle/xttsdir
scp `cat incrbackups.txt` oracle@ol7db:/backup
---拷贝以下文件到目标端$TMPDIR(源端操作)
scp tsbkupmap.txt incrbackups.txt oracle@ol7db:/home/oracle/xttsdir
---进行增量恢复(目标端操作)
---还是用中间实例进行
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r

进行传输表空间元数据的导出操作(源端操作)

---以下目标端均在19c环境实例进行(正式迁移可以停机啦！)
---进行传输表空间元数据的导出操作(源端操作)
---创建dump directory(源端目标端一起操作)
mkdir -p /home/oracle/destination/convert
create directory dpump_tts as '/home/oracle/destination/convert';
GRANT READ, WRITE ON DIRECTORY dpump_tts TO system;
---拷贝文件xttplan.txt到目标端$TMPDIR(源端操作)
cd /home/oracle/xttsdir
scp xttplan.txt ol7db:/home/oracle/xttsdir
---操作会生成一个impdp的操作命令，保留在$TMPDIR目录下面xttplugin.txt (目标端操作)
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e
---cat xttplugin.txt 
---替换<*>为实际参数
impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \
network_link=<ttslink> transport_full_check=no \
transport_tablespaces=XTTSTAB,XTTSIND,XTTSLOB,XTTSTEMP \
transport_datafiles='/u01/app/oracle/oradata/ORCL/datafile/xttstab_292_1061116105','/u01/app/oracle/oradata/ORCL/datafile/xttstab_264_1061116107','/u01/app/oracle/oradata/ORCL/datafile/xttsind_259_1061116107','/u01/app/oracle/oradata/ORCL/datafile/xttsind_257_1061116109','/u01/app/oracle/oradata/ORCL/datafile/xttslob_275_1061116109','/u01/app/oracle/oradata/ORCL/datafile/xttslob_291_1062094739','/u01/app/oracle/oradata/ORCL/datafile/xttstemp_309_1062094747'
---目标导入前,需把用户手动创建，否则会提示用户不存在(目标端操作)
---可以将之前获取到的用户ddl语句执行
create user xttsuser identified by "oracle";
grant resource,connect,unlimited tablespace  to xttsuser;
---在目标端执行impdp(目标端操作)
impdp system/oracle directory=dpump_tts logfile=tts_imp.log \
network_link=ttslink transport_full_check=no \
transport_tablespaces=XTTSTAB,XTTSIND,XTTSLOB,XTTSTEMP \
transport_datafiles='/u01/app/oracle/oradata/ORCL/datafile/xttstab_292_1061116105','/u01/app/oracle/oradata/ORCL/datafile/xttstab_264_1061116107','/u01/app/oracle/oradata/ORCL/datafile/xttsind_259_1061116107','/u01/app/oracle/oradata/ORCL/datafile/xttsind_257_1061116109','/u01/app/oracle/oradata/ORCL/datafile/xttslob_275_1061116109','/u01/app/oracle/oradata/ORCL/datafile/xttslob_291_1062094739','/u01/app/oracle/oradata/ORCL/datafile/xttstemp_309_1062094747'
---导入日志(目标端操作)
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=dpump_tts logfile=tts_imp.log network_link=ttslink transport_full_check=no transport_tablespaces=XTTSTAB,XTTSIND,XTTSLOB,XTTSTEMP transport_datafiles=/u01/app/oracle/oradata/ORCL/datafile/xttstab_292_1061116105,/u01/app/oracle/oradata/ORCL/datafile/xttstab_264_1061116107,/u01/app/oracle/oradata/ORCL/datafile/xttsind_259_1061116107,/u01/app/oracle/oradata/ORCL/datafile/xttsind_257_1061116109,/u01/app/oracle/oradata/ORCL/datafile/xttslob_275_1061116109,/u01/app/oracle/oradata/ORCL/datafile/xttslob_291_1062094739,/u01/app/oracle/oradata/ORCL/datafile/xttstemp_309_1062094747 
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/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 17 18:51:40 2021 elapsed 0 00:00:35

验证表空间是否有坏块(目标端操作)

rman target /
validate tablespace XTTSTAB,XTTSIND,XTTSLOB,XTTSTEMP check logical;

验证数据，表空间设置为可读写

-----将表空间设置为read write
select 'alter tablespace '||tablespace_name||' read write;'
from dba_tablespaces
where tablespace_name in ('XTTSTAB','XTTSIND','XTTSLOB','XTTSTEMP');
-------------设置表空间为读写
alter tablespace XTTSIND read write;
alter tablespace XTTSLOB read write;
alter tablespace XTTSTAB read write;
alter tablespace XTTSTEMP read write;
-----验证数据
select count(*) from xttsuser.xttstab;
select count(*) from xttsuser.xttstab2;
select count(*) from xttsuser.xttstab3;

最后针对用户，由于只进行段对象的表空间迁移，对于哪些plsql存储过程等非段对象，权限可能还需要再手动导过来进行最后的迁移

---第二次元数据导入,将第一次没有导入的过程，视图，包，触发器导入。
impdp directory=dpump_tts  network_link=ttslink schemas= 'XTTSUSER' content=metadata_only  exclude=index,table,constraint,statistics
---从源端获取role ddl并导入目标端
select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs 
where grantee in (select distinct  GRANTED_ROLE from dba_role_privs where GRANTEE in ('XTTSUSER'))
;
---从源端获取profile，并导入目标端
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On
SET LONGCHUNKSIZE 400
spool create_profile.sql
with profile as(
select distinct profile from dba_profiles
)
select dbms_metadata.get_ddl('PROFILE',PROFILE) from profile
;
spool off 
---从源端获取临时表，并导入目标端
spool create_temp_table.sql
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On
SET LONGCHUNKSIZE 400
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER)
from DBA_TABLES
where TEMPORARY='Y' and owner in ('XTTSUSER');
spool off 

---将原库中的其余用户,权限导入到目标端中


SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_role_priv.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';'from dba_role_privs where grantee in('XTTSUSER') and admin_option='NO'
union
select 'grant '||GRANTED_ROLE||' to '||grantee||' with admin option;' from dba_role_privs where grantee in ('XTTSUSER') and admin_option='YES';
spool off

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET TRIMSPOOL ON
SET PAGESIZE 1000
spool grant_sys_priv.sql
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in() and admin_option='NO'
union
select 'grant '||privilege||' to '||grantee||' with admin option;' from dba_sys_privs where grantee in('XTTSUSER') and admin_option='YES';
spool off

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_tab_privs.sql
select 'grant ' || privilege || ' on ' || owner || '.'|| table_name || ' to ' || grantee || ';' from dba_tab_privs where grantee in ('XTTSUSER') and grantable='NO'
union
select 'grant ' || privilege || ' on ' || owner || '.'|| table_name || ' to ' || grantee || ' with grant option;' from dba_tab_privs where grantee in() and grantable='YES';
spool off
---对象编译
@?/rdbms/admin/utlrp
---比较对象
select owner, object_type, count(*)
from dba_objects
where object_name not like 'BIN%'
and owner in ('xttsuser')
group by owner, object_type
order by 1,2 desc
;

目标端统计信息收集

---目标端搜集统计信息
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'XTTSUSER',ESTIMATE_PERCENT=>20,method_opt=>'forall columns size skewonly',cascade=>true,force=>true,degree=>8);

总结:

1 采用xtts将数据从11g迁移到了19c，整个操作过程，除了最后一次的增量以及元数据导出在停机窗口，其他操作都是在线进行，不影响生产

2 采用中间实例进行增量恢复，避免了追增量数据期间目标端数据库的重启

3 采用dbms_file_transfer通过dblink传输表空间，避免数据文件的中间存放，不需要额外的空间存储，但增量还是得基于rman进行