跨平台XTTS增量备份迁移Step-By-Step

1. 测试环境

什么是Oracle跨平台XTTS,就不再多说了,相关解释说明内容,网上到处都有,此篇文章是Step by Step步骤,帮助降低小伙伴第一次实际操作的难度。
source: 192.168.187.43 TEST1 11.2.0.4.0 Linux 32
dest : 192.168.187.44 TEST2 11.2.0.4.0 Linux 32

  • source建库

$ dbca -silent -createDatabase -templateName General_Purpose.dbc \
-gdbName TEST1 -sid TEST1 -sysPassword oracle123 -systemPassword oracle123 \
-emConfiguration NONE -datafileDestination /u01/app/oracle/oradata \
-redoLogFileSize 100 -characterSet zhs16gbk -sampleSchema false \
-memoryPercentage 20 -continueOnNonFatalErrors true -databaseType OLTP

  • dest建库

$ dbca -silent -createDatabase -templateName General_Purpose.dbc \
-gdbName TEST2 -sid TEST2 -sysPassword oracle123 -systemPassword oracle123 \
-emConfiguration NONE -datafileDestination /u01/app/oracle/oradata \
-redoLogFileSize 100 -characterSet zhs16gbk -sampleSchema false \
-memoryPercentage 20 -continueOnNonFatalErrors true -databaseType OLTP

  • 监听端口:1521

1.1 源库配置

  • 开启归档

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter database force logging; -> 建议使用force logging

  • 在源库上创建要迁移的表空间

SQL> create tablespace ts1 datafile ‘/u01/app/oracle/oradata/TEST1/ts1_01.dbf’ size 100m autoextend on;
SQL> create tablespace ts2 datafile ‘/u01/app/oracle/oradata/TEST1/ts2_01.dbf’ size 100m autoextend on;

  • 在源库上创建要迁移的用户

SQL> create user test identified by test default tablespace ts1;
SQL> grant create session, resource, unlimited tablespace to test;
SQL> conn test/test
SQL> create table t1 tablespace ts1 as select * from all_objects;
SQL> create table t2 tablespace ts2 as select * from all_users;

  • 打开块跟踪

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/u01/app/oracle/oradata/TEST1/rman_change_track.f’ REUSE;

1.2 目标库配置

目标库为非归档,在目标库上创建要迁移的用户
SQL> create user test identified by test default tablespace users;
SQL> grant create session, resource, unlimited tablespace to test;

1.3 配置SSH互信

由于需要在源和目标库上传输文件,建议配置SSH互信

mkdir ~/.ssh
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub source
ssh-copy-id -i ~/.ssh/id_rsa.pub dest

2. 源库配置

[oracle@source]$ cd /u01/app/oracle
[oracle@source]$ unzip -q rman-xttconvert_2.0.zip -d ./xtt
[oracle@source]$ cd xtt

默认xtt.properties文件

[oracle@source]$ cat xtt.properties|grep -v ‘^#’| grep -v ‘^$’
tablespaces=TS1,TS2
platformid=2
srcdir=SOURCEDIR1,SOURCEDIR2
dstdir=DESTDIR1,DESTDIR2
srclink=TTSLINK
dfcopydir=/stage_source
backupformat=/stage_source
stageondest=/stage_dest
storageondest=+DATA
backupondest=+RECO
parallel=3
rollparallel=2
getfileparallel=4

关于xtt.properties参数说明:参考
11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 1389592.1)
Description of Parameters in Configuration File xtt.properties

2.1 创建Direcotry

创建数据文件所在目录,如果数据文件分布在多个目录中,可以创建多个directory

SQL> create or replace directory SOURCEDIR1 as ‘/u01/app/oracle/oradata/TEST1’;

2.2 创建目录

创建存放增量备份文件的目录
[oracle@source]$ mkdir -p /u01/app/oracle/xtts_rman_inc_bak
无论初始表空间迁移阶段,采用的是dbms_file_transfer方式还是RMAN备份方式,该目录都要准备。

2.3 配置RMAN

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

3. 目标库配置

[oracle@dest]$ cd /u01/app/oracle
[oracle@dest]$ unzip -q rman-xttconvert_2.0.zip -d ./xtt
[oracle@dest]$ cd xtt

3.1 创建directory

SQL> create or replace directory DESTDIR1 as ‘/u01/app/oracle/oradata/TEST2’;

3.2 创建dblink

在目标库创建指向源库的dblink
SQL> create public database link TTSLINK connect to system identified by ora_1234 using ‘TEST1’;

3.3 创建目录

# 创建存放增量备份文件的目录
[oracle@dest]$ mkdir -p /u01/app/oracle/xtts_rman_inc_bak
无论初始表空间迁移阶段,采用的是dbms_file_transfer方式还是RMAN备份方式,该目录都要准备。

3.4 配置RMAN

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

4. xtt.properties文件

  • 采用dbms_file_transfer方式的配置文件

[oracle@source]$ cat xtt.properties|grep -v ‘^#’| grep -v ‘^$’
tablespaces=TS1,TS2
platformid=10
srcdir=SOURCEDIR1
dstdir=DESTDIR1
srclink=TTSLINK
backupformat=/u01/app/oracle/xtts_rman_inc_bak
backupondest=/u01/app/oracle/xtts_rman_inc_bak
stageondest=/u01/app/oracle/oradata/TEST2
parallel=3
rollparallel=2
getfileparallel=4

  • xtt.properties文件在源和目标端一致

[oracle@source]$ scp xtt.properties dest:/u01/app/oracle/xxt
注意:
在准备阶段使用dbms_file_transfer方法,需要使用的参数:
backupformat
backupondest
stageondest

5. 表空间初始迁移阶段(dbms_file_transfer)

这个阶段主要是directory指定的目录起作用
在这个阶段,会把源端的数据文件复制到目标端,建议使用screen工具进行后台执行

5.1 源库检查表空间的自包含性

SQL> execute dbms_tts.transport_set_check('TS1', TRUE);
PL/SQL procedure successfully completed.

SQL> execute dbms_tts.transport_set_check('TS2', TRUE);
PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;
no rows selected

5.2 设置TMPDIR

[oracle@source]$ export TMPDIR=/u01/app/oracle/xtt
[oracle@dest]$ export TMPDIR=/u01/app/oracle/xtt

5.3 源库检查和生成文件

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -S

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
Prepare source for Tablespaces:
                  'TS1'  /u01/app/oracle/xtts_rmanbak_datafile
xttpreparesrc.sql for 'TS1' started at Tue Nov  8 12:38:32 2016
xttpreparesrc.sql for  ended at Tue Nov  8 12:38:32 2016
Prepare source for Tablespaces:
                  'TS2'  /u01/app/oracle/xtts_rmanbak_datafile
xttpreparesrc.sql for 'TS2' started at Tue Nov  8 12:38:32 2016
xttpreparesrc.sql for  ended at Tue Nov  8 12:38:32 2016
Prepare source for Tablespaces:
                  ''  /u01/app/oracle/xtts_rmanbak_datafile
xttpreparesrc.sql for '' started at Tue Nov  8 12:38:32 2016
xttpreparesrc.sql for  ended at Tue Nov  8 12:38:32 2016

--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------

以上命令在原系统上执行如下动作:
(1)校验表空间是否是online、READ WRITE模式,并且不能包含offline的数据文件。
(2)在这个过程中创建以下文件:
xtnewdatafiles.txt
getfile.sql

5.4 把生成的脚本文件复制到目标端

[oracle@source]$ scp xttnewdatafiles.txt dest:/u01/app/oracle/xtt
[oracle@source]$ scp getfile.sql dest:/u01/app/oracle/xtt

5.5 目标端执行

[oracle@dest xtt]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -G

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Getting datafiles from source
--------------------------------------------------------------------

--------------------------------------------------------------------
Executing getfile for getfile_sourcedir1_ts1_01.dbf_0.sql
--------------------------------------------------------------------

--------------------------------------------------------------------
Executing getfile for getfile_sourcedir1_ts2_01.dbf_1.sql
--------------------------------------------------------------------

--------------------------------------------------------------------
Completed getting datafiles from source
--------------------------------------------------------------------

目标端检查传输过来的文件

[oracle@dest oracle]$ ls -l $ORACLE_BASE/oradata/TEST2
total 1881452
-rw-r----- 1 oracle oinstall   9748480 Nov  8 12:45 control01.ctl
-rw-r----- 1 oracle oinstall 104858112 Nov  8 09:16 redo01.log
-rw-r----- 1 oracle oinstall 104858112 Nov  8 09:16 redo02.log
-rw-r----- 1 oracle oinstall 104858112 Nov  8 12:44 redo03.log
-rw-r----- 1 oracle oinstall 534781952 Nov  8 12:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Nov  8 12:40 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Nov  8 10:16 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov  8 12:44 ts1_01.dbf   ->已经传输过来
-rw-r----- 1 oracle oinstall 104865792 Nov  8 12:44 ts2_01.dbf
-rw-r----- 1 oracle oinstall  73408512 Nov  8 12:40 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov  8 09:16 users01.dbf

源端和目标端定义的backupformat、backupondest都还没有使用

[oracle@source oracle]$ ls -lrt xtts_rman_inc_bak
total 0

[oracle@dest oracle]$ ls -lrt xtts_rman_inc_bak
total 0

6. 前滚阶段(这个过程可以执行多次)

在这个阶段,从原库上创建增量备份,传输到目标系统,转换为目标系统的字节格式,然后对目标库进行前滚。
这个阶段必须要运行多次,使目标库不断接近原始库。

6.1 源库上执行

[oracle@source xtt]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'TS1'
Prepare newscn for Tablespaces: 'TS2'
Prepare newscn for Tablespaces: ''
rman target /  cmdfile /u01/app/oracle/xtt/rmanincr.cmd

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 8 12:51:12 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST1 (DBID=1304406803)

RMAN> set nocfau;
2> host 'echo ts::TS1';
3> backup incremental from scn 675214
4>   tag tts_incr_update tablespace 'TS1'  format
5>  '/u01/app/oracle/xtts_rman_inc_bak_datafile/%U';
6> set nocfau;
7> host 'echo ts::TS2';
8> backup incremental from scn 675495
9>   tag tts_incr_update tablespace 'TS2'  format
10>  '/u01/app/oracle/xtts_rman_inc_bak_datafile/%U';
11>
executing command: SET NOCFAU
using target database control file instead of recovery catalog

ts::TS1
host command complete

Starting backup at 08-NOV-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=50 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=83 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/TEST1/ts1_01.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-16
channel ORA_DISK_1: finished piece 1 at 08-NOV-16
piece handle=/u01/app/oracle/xtts_rman_inc_bak_datafile/01rkda23_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-NOV-16

executing command: SET NOCFAU

ts::TS2
host command complete

Starting backup at 08-NOV-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/TEST1/ts2_01.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-16
channel ORA_DISK_1: finished piece 1 at 08-NOV-16
piece handle=/u01/app/oracle/xtts_rman_inc_bak_datafile/02rkda25_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-NOV-16

Recovery Manager complete.

--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------

检查产生的增量备份文件

[oracle@source oracle]$ ls -lrt xtts_rman_inc_bak
total 10212
-rw-r----- 1 oracle oinstall 9330688 Nov  8 12:51 01rkda23_1_1
-rw-r----- 1 oracle oinstall 1105920 Nov  8 12:51 02rkda25_1_1

创建增量的步骤:是执行RMAN命令生成xtt.properties文件中tablespaces参数定义的表空间的增量。
它创建了后续过程要使用的以下文件:
tsbkupmap.txt
incrbackups.txt

6.2 传输增量备份到目标系统

[oracle@source oracle]$ scp `cat incrbackups.txt` oracle@dest:/u01/app/oracle/xtts_rman_inc_bak

复制源端生成的xttplan.txt和tsbkupmap.txt到目标端
[oracle@source]$ scp xttplan.txt dest:/u01/app/oracle/xtt
[oracle@source]$ scp tsbkupmap.txt dest:/u01/app/oracle/xtt

6.3 把增量备份并应用到目标库

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------

--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------

注意:xttplan.txt和tsbkupmap.txt文件每次都必须复制过来,因为里面的内容不同。

第二次执行,会把第一次执行的文件改名,而不是删掉

7. 正式迁移阶段

在这个阶段,原库相关表空间被置为READ ONLY模式,目标库应用最后一次增量备份,应用完成后,目标和原库保持一致,
使用普通的传输表空间步骤导出原库上的对象原数据,然后在目标库上导入。这个阶段,原库保持READ ONLY。

7.1 源库表空间置为readonly

SQL> alter tablespace TS1 read only;
SQL> alter tablespace TS2 read only;

7.2 创建最后一次增量备份,传输,转换并应用到目标库

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
[oracle@source]$ scp `cat incrbackups.txt` oracle@dest:/u01/app/oracle/xtts_rman_inc_bak
[oracle@source]$ scp xttplan.txt oracle@dest:/u01/app/oracle/xtt
[oracle@source]$ scp tsbkupmap.txt oracle@dest:/u01/app/oracle/xtt

7.3 目标库应用增量备份

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

7.4 目标库导入对象原数据

在目标库上执行以下脚本生成DataPump TTS命令

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e

--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------

--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------

--------------------------------------------------------------------
Done generating plugin file /u01/app/oracle/xtt/xttplugin.txt
--------------------------------------------------------------------

[oracle@dest]$ cat /u01/app/oracle/xtt/xttplugin.txt
impdp directory= logfile= \
network_link= transport_full_check=no \
transport_tablespaces=TS1,TS2 \
transport_datafiles=’/u01/app/oracle/oradata/TEST2/ts1_01.dbf’,’/u01/app/oracle/oradata/TEST2/ts2_01.dbf’

修改脚本如下:
impdp “‘/ as sysdba'” directory=DATA_PUMP_DIR logfile=tts_imp.log \
network_link=ttslink transport_full_check=no \
transport_tablespaces=TS1,TS2 \
transport_datafiles=’/u01/app/oracle/oradata/TEST2/ts1_01.dbf’,’/u01/app/oracle/oradata/TEST2/ts2_01.dbf’

执行导入

7.4 修改表空间为read write

SQL> alter tablespace TS1 read write;
SQL> alter tablespace TS2 read write;

关于紫砂壶

感悟技术人生
此条目发表在数据迁移分类目录,贴了标签。将固定链接加入收藏夹。