暂无图片
暂无图片
9
暂无图片
暂无图片
暂无图片
首页 / Oracle跨平台，跨版本，大数据量迁移之XTTS的传输表空间

Oracle跨平台，跨版本，大数据量迁移之XTTS的传输表空间

原创 狗剩儿 2022-03-22
2602

文章目录

一、背景介绍

1.1源端开启归档模式
1.2备份文件的传输方案


二、实际操作步骤
2.1导出创建源库用户脚本
2.2 源端进行0级rman备份
2.3 目标端提取0级rman备份
2.4 源端进行1级rman备份
2.5 目标端提取1级rman备份
2.6 源端提取用户对象信息
2.7 源端表空间设置只读（停机开始）
2.8源端进行最后一次1级rman备份
2.9 源端导出表空间元数据
2.10 源端还原表空间可读写（停机结束）
2.11 目标端应用1级rman增量备份
2.12 目标端导入用户权限和对象
2.13 目标端导入表空间元数据
2.14 目标端修改用户默认表空间
2.15 目标端修改表空间可读写(迁移完成)


三、完善调整以及迁移验证
3.1重建序列
3.2 编译失效对象
3.3 收集统计信息
3.4 源端与目标端的数据对比


总结

前言

想必很多小伙伴工作中都遇到过迁移数据库的任务，但是我们做的最多的方式就是通过数据泵导出导入来实现，此方面很简单也很靠谱，但如果没有足够的停机时间窗口的时候，显然这个数据泵的方式就不能满足我们的工作要求，下面给大家详细介绍下一个真实案例，如何通过传输表空间xtts的技术来实现小停机窗口实现数据库迁移。（跨平台、跨版本）

提示：以下是本篇文章正文内容，下面案例可供参考

一、背景介绍

源端： windows2008R2+oracle11.2.0.1
目标端： linux7.9+oracle19.9.0+rac+asm
当然其他的源端平台也有些需要注意的事项：例如AIX的Oracle数据到Linux下需要一个转换步骤。

1.1源端开启归档模式

sqlplus / as sysdba
alter system set log_archive_dest_1='location=D:\archivelog' scope=spfile sid='*';
shutdown immedate
startup mount
alter database archivelog;
archive log list;
alter database open;
shutdown immediate
startup

创建D:\rman目录
自动删除归档脚本参考：
delarch.bat


rmancmdfile=D:\rman\rmandelete.txt

rmandelete.txt内容：

connect target /
run{
delete nopromptarchivelog all completed before 'sysdate-0';
crosscheck archivelog all;
delete noprompt expired archivelog all;
}

1.2备份文件的传输方案

因需要备份文件的传输，实施时应考虑如何高效的将备份文件从源端传输到目标端。
例如：nfs、acfs或者网络拷贝等

二、实际操作步骤

此计划表是某次实施过程的实际计划表，在实际操作之前要完成第一部分所有准备工作。如下：（只供参考，对本文无指导意义）
在这里插入图片描述

2.1导出创建源库用户脚本

后续会在目标端完成用户创建使用

set line 160
set pagesize 0
spool create_user.sql
select 'create user '||a.name||' identified by values '||''''||a.password||''''||';' from user$ a left join dba_users b on a.name=b.username where b.default_tablespace in('HTBASE');
spool off

2.2 源端进行0级rman备份

启用块跟踪，加快增量备份速度
SQL> alter database enable block change tracking using file 'D:\rman\change.rman';
SQL> select status, filename from v$block_change_tracking;

STATUS     FILENAME
---------- -------------------------------------------------
ENABLED    E:\RMAN\CHANGE.RMAN
SQL> exit

rman target /
RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup as compressed backupset incrementallevel=0 tablespace HTBASE format 'D:\rman\EMRLSZY0_%U' tag=emrlszy0;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
实际操作时备份操作拆分成10个备份集：
RMAN> list backup tag=emrlszy0;
备份集列表
===================
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
12      Incr 0  67.30G     DISK        00:48:08     02-2月 -21
        BP 关键字: 12   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0LVM74VU_1_1
  备份集 12 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  14   0  Incr 69888378   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE10.DBF
  24   0  Incr 69888378   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE20.DBF
  34   0  Incr 69888378   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE30.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
13      Incr 0  70.80G     DISK        00:49:28     02-2月 -21
        BP 关键字: 13   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0KVM74VU_1_1
  备份集 13 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  13   0  Incr 69888375   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE09.DBF
  23   0  Incr 69888375   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE19.DBF
  33   0  Incr 69888375   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE29.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
14      Incr 0  80.43G     DISK        00:58:24     02-2月 -21
        BP 关键字: 14   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0JVM74VU_1_1
  备份集 14 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  12   0  Incr 69888372   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE08.DBF
  22   0  Incr 69888372   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE18.DBF
  32   0  Incr 69888372   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE28.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
15      Incr 0  91.03G     DISK        01:04:59     02-2月 -21
        BP 关键字: 15   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0IVM74VT_1_1
  备份集 15 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  11   0  Incr 69888369   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE07.DBF
  21   0  Incr 69888369   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE17.DBF
  31   0  Incr 69888369   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE27.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
16      Incr 0  92.88G     DISK        01:06:06     02-2月 -21
        BP 关键字: 16   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0FVM74VT_1_1
  备份集 16 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  8    0  Incr 69888360   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE04.DBF
  18   0  Incr 69888360   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE14.DBF
  28   0  Incr 69888360   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE24.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
17      Incr 0  92.01G     DISK        01:06:35     02-2月 -21
        BP 关键字: 17   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0HVM74VT_1_1
  备份集 17 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  10   0  Incr 69888366   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE06.DBF
  20   0  Incr 69888366   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE16.DBF
  30   0  Incr 69888366   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE26.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
18      Incr 0  92.62G     DISK        01:06:47     02-2月 -21
        BP 关键字: 18   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0GVM74VT_1_1
  备份集 18 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  9    0  Incr 69888363   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE05.DBF
  19   0  Incr 69888363   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE15.DBF
  29   0  Incr 69888363   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE25.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
19      Incr 0  93.51G     DISK        01:07:02     02-2月 -21
        BP 关键字: 19   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0EVM74VT_1_1
  备份集 19 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  7    0  Incr 69888358   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE03.DBF
  17   0  Incr 69888358   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE13.DBF
  27   0  Incr 69888358   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE23.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
20      Incr 0  95.99G     DISK        01:07:21     02-2月 -21
        BP 关键字: 20   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0DVM74VT_1_1
  备份集 20 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  6    0  Incr 69888356   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE02.DBF
  16   0  Incr 69888356   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE12.DBF
  26   0  Incr 69888356   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE22.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
21      Incr 0  96.00G     DISK        01:07:26     02-2月 -21
        BP 关键字: 21   状态: AVAILABLE  已压缩: NO  标记: LS
段名:D:\RMAN\LS_0CVM74VT_1_1
  备份集 21 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  5    0  Incr 69888354   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE01.DBF
  15   0  Incr 69888354   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE11.DBF
  25   0  Incr 69888354   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE21.DBF

2.3 目标端提取0级rman备份

将源端的rman备份文件传输到目标端后，按照备份集进行备份提取操作，注意标号对应表空间：
本小节不全部列举，不同备份集都需要进行对应提取，本案例2.1中有10个备份集：

conn / as sysdba
set serveroutput on;
DECLARE
devtype varchar2(256);
done Boolean;
BEGIN
Devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>14,toname=>'+DATAC1/EMRLSZY/HTBASE10.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>24,toname=>'+DATAC1/EMRLSZY/HTBASE20.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>34,toname=>'+DATAC1/EMRLSZY/HTBASE30.DBF');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/xtts/LS_0LVM74VU_1_1', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

注意：
1、如果提取失败建议使用伪实例进行提取操作
2、不同备份集的提取注意要切换不同的会话提取
3、如表空间太多可以通过rman备份设置在一个备份集中，可添加 filesperset 300 参数

2.4 源端进行1级rman备份

C:\Users\Administrator>set  ORACLE_SID=emrlszy
C:\Users\Administrator>rman target /

恢复管理器: Release 11.2.0.1.0 - Production on 星期日 1月 19 11:14:30 2020

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到目标数据库: EMRLSZY (DBID=278480429)

RMAN>run {
allocate channel t1 type disk;
backup incremental level=1 tablespace HTBASEformat 'D:\rman\emrlszy1_%U' tag=emrlszy1;
release channel t1;
}

RMAN>list backup;
BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
22      Incr 1  264.00K    DISK        00:00:04     02-2月 -21
        BP 关键字: 22   状态: AVAILABLE  已压缩: NO  标记: EMRLSZY1
段名:\\192.168.16.51\XTTS\EMRLSZY1_0MVM7BLR_1_1
  备份集 22 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  5    1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE01.DBF
  6    1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE02.DBF
  7    1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE03.DBF
  8    1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE04.DBF
  9    1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE05.DBF
  10   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE06.DBF
  11   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE07.DBF
  12   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE08.DBF
  13   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE09.DBF
  14   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE10.DBF
  15   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE11.DBF
  16   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE12.DBF
  17   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE13.DBF
  18   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE14.DBF
  19   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE15.DBF
  20   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE16.DBF
  21   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE17.DBF
  22   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE18.DBF
  23   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE19.DBF
  24   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE20.DBF
  25   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE21.DBF
  26   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE22.DBF
  27   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE23.DBF
  28   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE24.DBF
  29   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE25.DBF
  30   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE26.DBF
  31   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE27.DBF
  32   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE28.DBF
  33   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE29.DBF
  34   1  Incr 69900005   02-2月 -21 D:\APP\ADMINISTRATOR\ORADATA\EMRLSZY\HTBASE30.DBF
RMAN>

2.5 目标端提取1级rman备份

sqlplus sys/xxxxx@emrlszy as sysdba
SQL>
SQL> set serveroutput on;
DECLARE
    outhandle varchar2(512) ;
outtag varchar2(30) ;
    done boolean ;
    failover boolean ;
devtype VARCHAR2(512);
    BEGIN
DBMS_OUTPUT.put_line('Entering RollForward');
    -- Now the rolling forward.
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ; 
DBMS_OUTPUT.put_line('After applySetDataFile');
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>8,toname=>'+DATAC1/EMRLSZY/HTBASE04.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'+DATAC1/EMRLSZY/HTBASE08.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>16,toname=>'+DATAC1/EMRLSZY/HTBASE12.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>20,toname=>'+DATAC1/EMRLSZY/HTBASE16.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>24,toname=>'+DATAC1/EMRLSZY/HTBASE20.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>28,toname=>'+DATAC1/EMRLSZY/HTBASE24.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>32,toname=>'+DATAC1/EMRLSZY/HTBASE28.DBF'',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>7,toname=>'+DATAC1/EMRLSZY/HTBASE03.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'+DATAC1/EMRLSZY/HTBASE07.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>15,toname=>'+DATAC1/EMRLSZY/HTBASE11.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>19,toname=>'+DATAC1/EMRLSZY/HTBASE15.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>23,toname=>'+DATAC1/EMRLSZY/HTBASE19.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>27,toname=>'+DATAC1/EMRLSZY/HTBASE23.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>31,toname=>'+DATAC1/EMRLSZY/HTBASE27.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6,toname=>'+DATAC1/EMRLSZY/HTBASE02.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'+DATAC1/EMRLSZY/HTBASE06.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>14,toname=>'+DATAC1/EMRLSZY/HTBASE10.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>18,toname=>'+DATAC1/EMRLSZY/HTBASE14.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>22,toname=>'+DATAC1/EMRLSZY/HTBASE18.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>26,toname=>'+DATAC1/EMRLSZY/HTBASE22.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>30,toname=>'+DATAC1/EMRLSZY/HTBASE26.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>34,toname=>'+DATAC1/EMRLSZY/HTBASE30.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>5,toname=>'+DATAC1/EMRLSZY/HTBASE01.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>9,toname=>'+DATAC1/EMRLSZY/HTBASE05.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'+DATAC1/EMRLSZY/HTBASE09.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>17,toname=>'+DATAC1/EMRLSZY/HTBASE13.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>21,toname=>'+DATAC1/EMRLSZY/HTBASE17.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>25,toname=>'+DATAC1/EMRLSZY/HTBASE21.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>29,toname=>'+DATAC1/EMRLSZY/HTBASE25.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>33,toname=>'+DATAC1/EMRLSZY/HTBASE29.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
DBMS_OUTPUT.put_line('Done: applyDataFileTo');
  -- Restore Set Piece
sys.dbms_backup_restore.restoreSetPiece(handle     => '/xtts/EMRLSZY1_05VH67T3_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ;
DBMS_OUTPUT.put_line('Done: RestoreSetPiece');
   -- Restore Backup Piece
sys.dbms_backup_restore.restoreBackupPiece(done =>  done, params =>  null, outhandle  =>outhandle,outtag =>outtag, failover => failover);
DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
   END;
   /

根据实际情况可多次进行2.4~2.5的1级增量备份操作，但最多操作8次，否则块跟踪记录会被覆盖，再次执行导致进行全库扫描，导致长时间等待就失去了此方案意义

所以从开启块跟踪到迁移完成要控制在8次备份之内。

2.6 源端提取用户对象信息

此时操作后，最好源端不会再新增对象，否则新增对象需要手动恢复。

expdp '/ as sysdba' directory=xtts_dmp dumpfile=meta_%U.dmp LOGFILE=meta.log SCHEMAS=用户名1,用户名2,用户名3 CONTENT=METADATA_ONLY PARALLEL=8 CLUSTER=N EXCLUDE=TABLE,INDEX,SEQUENCE,STATISTICS

2.7 源端表空间设置只读（停机开始）

SQL> conn /as sysdba
已连接。
SQL>
alter tablespace HTBASE read only;

2.8源端进行最后一次1级rman备份

RMAN>run {
allocate channel t1 type disk;
backup incremental level=1 tablespace HTBASEformat 'D:\rman\emrlszy1_%U' tag=emrlszy1;
release channel t1;
}

2.9 源端导出表空间元数据

测试期间，考虑到最小停机影响，先备份到本地：d:\dmpts

create directory dmp as 'd:\dmpts;

正式割接直接备份到目标端:

create directory xtts_dmp as '\\192.168.XX.XX\xtts\';
grant read,write on directory xtts_dmp to public;
expdp '/ as sysdba' directory=xtts_dmp dumpfile=tts.dmp transport_tablespaces=HTBASE exclude=STATISTICS

2.10 源端还原表空间可读写（停机结束）

sqlplus / as sysdba
alter tablespace HTBASE read write;
select TABLESPACE_NAME,STATUS from dba_tablespaces;

2.11 目标端应用1级rman增量备份

SQL>set serveroutput on;
DECLARE
    outhandle varchar2(512) ;
outtag varchar2(30) ;
    done boolean ;
    failover boolean ;
devtype VARCHAR2(512);
    BEGIN
DBMS_OUTPUT.put_line('Entering RollForward');
    -- Now the rolling forward.
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE); 
DBMS_OUTPUT.put_line('After applySetDataFile');
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>8,toname=>'+DATAC1/EMRLSZY/HTBASE04.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>12,toname=>'+DATAC1/EMRLSZY/HTBASE08.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>16,toname=>'+DATAC1/EMRLSZY/HTBASE12.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>20,toname=>'+DATAC1/EMRLSZY/HTBASE16.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>24,toname=>'+DATAC1/EMRLSZY/HTBASE20.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>28,toname=>'+DATAC1/EMRLSZY/HTBASE24.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>32,toname=>'+DATAC1/EMRLSZY/HTBASE28.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>7,toname=>'+DATAC1/EMRLSZY/HTBASE03.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>11,toname=>'+DATAC1/EMRLSZY/HTBASE07.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>15,toname=>'+DATAC1/EMRLSZY/HTBASE11.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>19,toname=>'+DATAC1/EMRLSZY/HTBASE15.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>23,toname=>'+DATAC1/EMRLSZY/HTBASE19.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>27,toname=>'+DATAC1/EMRLSZY/HTBASE23.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>31,toname=>'+DATAC1/EMRLSZY/HTBASE27.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>6,toname=>'+DATAC1/EMRLSZY/HTBASE02.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'+DATAC1/EMRLSZY/HTBASE06.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>14,toname=>'+DATAC1/EMRLSZY/HTBASE10.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>18,toname=>'+DATAC1/EMRLSZY/HTBASE14.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>22,toname=>'+DATAC1/EMRLSZY/HTBASE18.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>26,toname=>'+DATAC1/EMRLSZY/HTBASE22.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>30,toname=>'+DATAC1/EMRLSZY/HTBASE26.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>34,toname=>'+DATAC1/EMRLSZY/HTBASE30.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>5,toname=>'+DATAC1/EMRLSZY/HTBASE01.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>9,toname=>'+DATAC1/EMRLSZY/HTBASE05.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>13,toname=>'+DATAC1/EMRLSZY/HTBASE09.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>17,toname=>'+DATAC1/EMRLSZY/HTBASE13.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>21,toname=>'+DATAC1/EMRLSZY/HTBASE17.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>25,toname=>'+DATAC1/EMRLSZY/HTBASE21.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>29,toname=>'+DATAC1/EMRLSZY/HTBASE25.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>33,toname=>'+DATAC1/EMRLSZY/HTBASE29.DBF',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
DBMS_OUTPUT.put_line('Done: applyDataFileTo');
sys.dbms_backup_restore.restoreSetPiece(handle => '/xtts/EMRLSZY1_0MVM7BLR_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ;
DBMS_OUTPUT.put_line('Done: RestoreSetPiece');
sys.dbms_backup_restore.restoreBackupPiece(done =>  done, params =>  null, outhandle  =>outhandle,outtag =>outtag, failover => failover);
DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
   END;
   /

2.12 目标端导入用户权限和对象

如是容器数据库必须进入到容器pdb内内操作
SQL>alter session set container=emrlszy;   
或sqlplu ssys/xxxxxx@emrlszy as sysdba
show pdbs;

目标端导入
impdp 'sys/"xxxxxxx"@emrlszy as sysdba' DIRECTORY=xtts_dmp dumpfile=META_%U.DMP LOGFILE=meta-imp.log SCHEMAS=用户名1,用户名2,用户名3

本步骤建议在 2.15 小节之后操作

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

create directory xtts_dmp as '/xtts';
grant read,write on directory xtts_dmp to public;


$ cat impdp-par.txt 

transport_datafiles='+DATAC1\EMRLSZY\HTBASE01.DBF','+DATAC1\EMRLSZY\HTBASE02DBF','+DATAC1\EMRLSZY\HTBASE03.DBF','+DATAC1\EMRLSZY\HTBASE04.DBF','+DATAC1\EMRLSZY\HTBASE05.DBF','+DATAC1\EMRLSZY\HTBASE06.DBF','+DATAC1\EMRLSZY\HTBASE07.DBF','+DATAC1\EMRLSZY\HTBASE08.DBF','+DATAC1\EMRLSZY\HTBASE09.DBF','+DATAC1\EMRLSZY\HTBASE10.DBF','+DATAC1\EMRLSZY\HTBASE11.DBF','+DATAC1\EMRLSZY\HTBASE12.DBF','+DATAC1\EMRLSZY\HTBASE13.DBF','+DATAC1\EMRLSZY\HTBASE14.DBF','+DATAC1\EMRLSZY\HTBASE15.DBF','+DATAC1\EMRLSZY\HTBASE16.DBF','+DATAC1\EMRLSZY\HTBASE17.DBF','+DATAC1\EMRLSZY\HTBASE18.DBF','+DATAC1\EMRLSZY\HTBASE19.DBF','+DATAC1\EMRLSZY\HTBASE20.DBF','+DATAC1\EMRLSZY\HTBASE21.DBF','+DATAC1\EMRLSZY\HTBASE22.DBF','+DATAC1\EMRLSZY\HTBASE23.DBF','+DATAC1\EMRLSZY\HTBASE24.DBF','+DATAC1\EMRLSZY\HTBASE25.DBF','+DATAC1\EMRLSZY\HTBASE26.DBF','+DATAC1\EMRLSZY\HTBASE27.DBF','+DATAC1\EMRLSZY\HTBASE28.DBF','+DATAC1\EMRLSZY\HTBASE29.DBF', '+DATAC1\EMRLSZY\HTBASE30.DBF'

 
impdp 'sys/"xxxxxxx"@emrlszy as sysdba' dumpfile=TTS.DMP directory=xtts_dmp PARFILE=impdp-par.txt



$ cd /dmp/xtts

$ sqlplussys/xxxxxx@EMRLSZY as sysdba

SQL> show pdbs;      
测试普通用户连接：
SQL> conn XXX/xxx@EMRLSZY
SQL> select name from v$tablespace;
SQL> select name from v$datafile;
SQL> select count(*) from user_tables;

2.14 目标端修改用户默认表空间

$ sqlplussys/xxxxxxx@htemr as sysdba
show pdbs;

alter user 用户名1	default tablespace HTBASE; 
alter user 用户名2	default tablespace HTBASE;
alter user 用户名3	default tablespace HTBASE;
......

2.15 目标端修改表空间可读写(迁移完成)

$ sqlplussys/xxxxxxx@emrlszy as sysdba
show pdbs;
alter tablespace HTBASE read write;

select TABLESPACE_NAME,STATUS from dba_tablespaces;

至此，我们数据库迁移已然完成，之后需要配合应用验证数据库的完整性

三、完善调整以及迁移验证

3.1重建序列

视实际情况而定

单机到rac优化性能考虑：
目标端删除序列:
select 'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name ||'";' SEQDDL from dba_sequences where sequence_owner not in
 (select name from system.logstdby$skip_support
 where action=0);

在源端提取创建序列:
set heading off feedback off trimspool on escape off
set long 1000 linesize 1000 pagesize 0
col SEQDDL format A300
spool tts_create_seq.sql
select regexp_replace(dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner), '^.*(CREATE SEQUENCE.*CYCLE).*$', 'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name ||'";'||chr(10)||'\1;')||';' SEQDDL
 from dba_sequences where sequence_owner not in
 (select name from system.logstdby$skip_support where action=0);
spool off;
脚本需根据情况调整 cache大小和 order顺序。

注意：原cache为20的都改为1000
no order的可能需改成order，需要问业务是否能跳号？还是说序列得保持顺序号。不清楚就先不变。等业务测试反馈。

3.2 编译失效对象

$ sqlplussys/xxxxxxx@emrlszy as sysdba
show pdbs;
@$ORACLE_HOME/rdbms/admin/utlrp.sql
select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

3.3 收集统计信息

详细略

3.4 源端与目标端的数据对比

对象数量比对：
select owner,OBJECT_TYPE, count(*)
  from dba_objects
 where owner in ('用户名1','用户名2'，'用户名3'...)
 group by owner,OBJECT_TYPE
 order by count(*) desc;

总结

那么，到此我们就完整的完成了数据库的迁移，接下来就由应用接手测试业务就ok了，经过实际测试，利用此方法，停机窗口可控制在1~2小时内。针对库大小的不同，我们前期准备时间相对就会不同。多理解，多操作，拒绝Ctrl+C、Ctrl+V！！！

墨力计划数据库迁移升级迁移xtts迁移
最后修改时间：2022-03-23 09:20:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论