11g升级19c使用XTTS 手工迁移的关键步骤

1. 查询开始的scn

SELECT TO_CHAR(MIN(start_scn) ) AS SCN FROM gv$transaction UNION ALL SELECT TO_CHAR(current_scn) FROM gv$database;

2. backup copy

select 'backup as copy datafile ' ||file_id||' format ''/u01/media/datafile/' || tablespace_name||'_'||file_id||''';' from  dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') ;

3．rman convert

sql源端执行

set heanding off
set linesize 2000
select 'convert from platform ''AIX-Based Systems (64-bit)'' datafile ''/u01/media/datafile/'   
|| tablespace_name||'_'||file_id||''''|| ' format ''+DATA/RACDB/DATAFILE/' 
 ||  tablespace_name||'_'||file_id||'.dbf;' 
from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2'); 

步骤2的文件和步骤3生成的脚本传输到目标端执行

4．记录scn

SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM gv$transaction
UNION ALL
SELECT TO_CHAR(current_scn) FROM gv$database;

5．备份增量备份

run{
allocate channel c1 device type disk;  
set until scn=TTTTT;
backup incremental from scn xxxx datafile 5,6,7,8  
format '/u01/media/tablespace_incr_1';
release channel c1;
}

xxxx为rman copy时的 scn，TTTTT为当前的SCN

6．增量convert

目标端执行

set timing on 
spool incr_conv1.log
DECLARE 
   handle    varchar2(512); 
   comment   varchar2(80); 
   media     varchar2(80); 
   concur    boolean; 
   recid     number; 
   stamp     number; 
   pltfrmfr number; 
   devtype   VARCHAR2(512); 
BEGIN

   sys.dbms_backup_restore.restoreCancel(TRUE);
   devtype := sys.dbms_backup_restore.deviceAllocate;
   sys.dbms_backup_restore.backupBackupPiece(bpname => '/mnt/xtts/datafile_inc/tablespace_incr_1,fname => '/mnt/xtts/datafile_inc/tablespace_incr_1_con',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=>xxx);
END;
/

xxxx为传输到目标端的增量备份文件名称
pltfrmfr=>源端平台ID

7．应用convert后的增量备份

目标数据库需要在nomount状态下

set timing on 
set serveroutput on; 
spool apply_incr1.log 
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'); 
--USR_ARC_IDX,USR_ACC_TBS,EPMGIS_TB,WIPTBS
------------------------------------------------
--USR_ARC_IDX(106)
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>253,toname=>'+DATA/RACDB/DATAFILE/Udbis_253.dbf',fuzziness_hint=>0,max_corrupt=>0,islevel0=>0,recid=>0,stamp=>0);
   DBMS_OUTPUT.put_line('Done: applyDataFileTo'); 
   DBMS_OUTPUT.put_line('Done: applyDataFileTo'); 
   -- Restore Set Piece 
   sys.dbms_backup_restore.restoreSetPiece(handle => '/mnt/xtts/datafile_inc/tablespace_incr_1_con',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; 
/

8．可多次重复4,5,6,7步