Oracle跨平台，跨版本，大数据量迁移之XTTS

文章目录

- - - - 一、背景介绍
      - 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 源端与目标端的数据对比

一、背景介绍

源端：

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

内容：

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

1.2备份文件的传输方案

因需要备份文件的传输，实施时应考虑如何高效的将备份文件从源端传输到目标端。

例如：

nfs、acfs

或者网络拷贝等

二、实际操作步骤

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

Oracle跨平台，跨版本，大数据量迁移之XTTS

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备份

启用块跟踪，加快增量备份速度
alter database enable block change tracking using file 'D:\rman\change.rman';
set line 200 pagesize 1000
col filename for a100
select * 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.2

中有

个备份集：

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 HTBASE format '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

的

级增量备份操作，但最多操作

次，否则块跟踪记录会被覆盖，再次执行导致进行全库扫描，导致长时间等待就失去了此方案意义

所以从开启块跟踪到迁移完成要控制在

次备份之内。

2.6 源端提取用户对象信息

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

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 HTBASE format 'D:\rman\emrlszy1_%U' tag=emrlszy1;
release channel t1;
}

2.9 源端导出表空间元数据

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

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;

目标端导入

本步骤建议在

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 编译失效对象

$ sqlplus sys/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;

Oracle跨平台，跨版本，大数据量迁移之XTTS

文章目录

一、背景介绍

二、实际操作步骤

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

继续阅读

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List