本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
自从2015年初进行了xtts增量的U2L迁移测试之后，国内很多人都开始利用这种方案进行数据库跨平台迁移了，基本上都是利用Oracle 封装的perl脚本。其中Oracle MOS文档 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 1389592.1) 明确提到目标端环境必须是Linux,这里该文档中的一段原话：
The source system may be any platform provided the prerequisites referenced and listed below for both platform and database are met. The destination system must be Linux, either 64-bit Oracle Linux or RedHat Linux, as long as it is a certified version. The typical use case is expected to be migrating data from a big endian platform, such as IBM AIX, HP-UX, or Solaris SPARC, to 64-bit Oracle Linux, such as Oracle Exadata Database Machine running Oracle Linux.
其实这里很容易让人产生误解，这里Oracle并非说不支持其他平台，而是说Oracle 提供的封装perl脚本不支持而已。但是手工进行xtts操作，完全是ok的；经过我的测试也是可行，这里是测试从Hp IA到Solaris Sparc的xtts增量迁移方式，供参考。
1、首先在原端创建测试表空间和测试表.
-创建测试表空间
create tablespace xtts datafile ‘+data’ size 100m;
create table test0504 as select * from dba_objects where 1=2;
alter table test504 move tablespace xtts;
2、备份xtts表空间文件，并传输到目标端(Solaris)
略.
3、目标端进行文件格式转换
convert from platform ‘HP-UX IA (64-bit)’ datafile ’/tmp/xtts.dbf’ format ‘+DATA/test/datafile/xtts_new.dbf’;
4、原端进行基于SCN的增量备份(这里由于我是测试表空间，所以未启用Block track changing)
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 4 16:20:45 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: CQDB (DBID=1910815733)
RMAN> run {
set until scn=14528565277766;
allocate channel t1 type disk ;
backup incremental from scn 14528539218186 tablespace 'XTTS' format '/tmp/xtts_incr1.bak';
release channel t1;
}2> 3> 4> 5> 6>
executing command: SET until clause
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=3692 instance=cqdb3 device type=DISK
Starting backup at 04-MAY-2017 16:21:06
backup will be obsolete on date 11-MAY-2017 16:21:09
archived logs will not be kept or backed up
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00941 name=+DATA/cqdb/datafile/xtts.1277.943107855
channel t1: starting piece 1 at 04-MAY-2017 16:21:10
channel t1: finished piece 1 at 04-MAY-2017 16:21:17
piece handle=/tmp/xtts_incr1.bak tag=TAG20170504T162108 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:07
backup will be obsolete on date 11-MAY-2017 16:21:18
archived logs will not be kept or backed up
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
channel t1: starting piece 1 at 04-MAY-2017 16:21:26
released channel: t1
5、创建备份集传到目标端并进行备份集格式手工转换(Solaris)
将脚本保存为xtts_conv1.sql并执行，如下是脚本内容：
DECLARE
handle varchar2(512);
comment varchar2(80);
media varchar2(80);
concur boolean;
recid number;
stamp number;
pltfrmfr number;
devtype VARCHAR2(512);
BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(bpname => '/tmp/xtts_incr1.bak',fname => '/tmp/xtts_conv_incr1.bak',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=> 4);
END;
END;
/
执行结果如下：
SQL> start xtts_conv1.sql; PL/SQL procedure successfully completed.
6、进行第一次增量应用(Solaris)
说明：为了验证增量数据是否能够同步到目标端，在进行增量备份之前，
我这里先进行了：
SQL > insert into test0504 select * fro dba_objects where rownm < 101;
SQL> commit;
将如下脚本保存为apply_incr1.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 => 941 ,toname => '+DATA/test/datafile/xtts_new.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 => '/tmp/xtts_conv_incr1.bak',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;
/
执行结果如下：
SQL> @apply_incr1.sql Entering RollForward After applySetDataFile Done: applyDataFileTo Done: applyDataFileTo Done: RestoreSetPiece Done: RestoreBackupPiece PL/SQL procedure successfully completed.
7、将原端表空间设置为只读模式
SQL> alter tablespace xtts read only ;
8、进行最后一次增量备份。
run {
allocate channel t1 type disk ;
backup incremental from scn 14528565277766 tablespace 'XTTS' format '/tmp/xtts_incr2.bak';
release channel t1;
}
9、将备份集传输到目标端并进行转换
步骤略(同上)
10、最后一次应用增量备份
步骤略(同上)
11、源端导出元数据
将下列内容保存为exp_xtts.par：
transport_tablespace=y
tablespaces=('XTTS')
file=xtts_tab.dmp
log=xtts_tab.log
执行如下命令导出xtts表空间上的元数据信息：
-bash-4.4$ imp \'/ as sysdba\' parfile=imp_xtts.par Import: Release 11.2.0.4.0 - Production on Thu May 4 17:47:27 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export file created by EXPORT:V11.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) export client uses ZHS16GBK character set (possible charset conversion) export server uses UTF8 NCHAR character set (possible ncharset conversion) . importing SYS's objects into SYS . importing SYS's objects into SYS . . importing table "TEST0504" Import terminated successfully without warnings.
12 目标端导入元数据
将下列内容保存为imp_xtts.par:
transport_tablespace=y
TABLESPACES=('XTTS')
file=xtts_tab.dmp
log=xtts_tab.log
datafiles=(
'+DATA/test/datafile/xtts.dbf')
执行如下命令导入元数据。
imp \’/ as sysdba\’ parfile=imp_xtts.par
13、检查数据
-bash-4.4$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:47:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/test/datafile/system.657.943109907
+DATA/test/datafile/sysaux.656.943109911
+DATA/test/datafile/undotbs1.654.943109911
+DATA/test/datafile/users.653.943109927
+DATA/test/datafile/xtts_new.dbf
SQL> select count(1) from test0504;
COUNT(1)
----------
100
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
--------------------------------------------------------------------------------
Solaris[tm] OE (64-bit)
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
XTTS READ ONLY
我们可以看出，xtts表空间已经被迁移过来了，并且增量的数据也已经同步了。
所以我想表达的是，所有的跨平台迁移，其实都可以利用XTTS Incremental Backup 功能进行迁移，无论目标端是什么平台。当然，如果字节序相同的情况下，可以直接使用convert database 功能。
补充：
在进行增量应用时，可能会出现如下错误：
ERROR at line 1: ORA-19583: conversation terminated due to error ORA-00600: internal error code, arguments: [2130], [941], [100], [4], [], [], [], [], [], [], [], [] ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 2335 ORA-06512: at line 13
如果遇到这个错误，那么只需要将实例停掉，启动到nomount状态下执行脚本即可。
Related posts: