文章目錄
一、背景介紹
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！！！
最后修改時間：2022-03-23 09:20:15
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
