XTTS数据迁移

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

1. OVERVIEW

1.1 XTTS功能和目标

传统的传输表空间方式要求数据第一次由源端到目标端传输时，表空间必须置于read only模式，从而生产不可用。而XTTS方式则只需要在最后一次增量备份时将表空间置于read only模式，显著的减少了停机的时间。

1.2 oracle建议使用场景

1. 大字节平台到小字节平台的迁移
2. 低版本数据库到高版本数据库的升级+迁移

1.3 平台、数据库版本要求

【DATABASE】

source端：Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2
dest端：如果使用dbmsfiletransfer（DFT），必须是11.2.0.4以上如果是Recovery Manager （RMAN），版本低于11.2.0.4时需要安装11.2.0.4的RDBMS运行11.2.0.4的实例

【OS】

source端: any platform provided the prerequisites：cannot be Windows
dest端: only 64-bit Oracle Linux or RedHat Linux certified

2. XTTS 环境检查

2.1 查看环境overview

【source端】

1.查看AIX操作系统及版本：

 oslevel -s    
    6100-06-05-1115

2.查看数据库版本:

 SQL> select * from v$version; 
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

【dest端】

1.查看linux操作系统及版本：

 #uname -a
    Linux rac1 2.6.32-504.el6.x86_64 #1 SMP Tue Sep 16 01:56:35 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux

2.查看数据库版本:

 SQL> select * from v$version; 
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.2 Prerequisites

1.source端和dest端的要使用兼容性数据库字符集及国家语言字符集:

 select * from nls_database_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_LANGUAGE' or parameter='NLS_NCHAR_CHARACTERSET';

2.dest端不能存在与source端migration的表空间名相同的tablespace，否则source端或者dest端的表空间需要rename:

 SQL> select tablespace_name from dba_tablespaces; 
    SQL> alter tablespace users rename to oriusers;

3.如果数据库有timestamp with local time zone数据类型，source端和dest端的时区必须相同，否则会在impdp时报错
4.传输的表空间必须自包含，诸如物化视图，分区表，索引要特别注意检查

对于SOE1，SOE 进行检查

 SQL> exec dbms_tts.transport_set_check('SOE,SOE1',TRUE,True);
    PL/SQL procedure successfully completed.
    SQL> select * from transport_set_violations;
    no rows selected

5.如果migration的表空间是加密表空间，source端和dest端endianness不同不可以使用此方法
6.查看源端是否有nested iot with key compression 表，如果有，需要修复Bug 14835322

 SQL> SELECT owner,table_name from dba_tables where nested='YES' and iot_type is not null and compression='ENABLED';

7.不可以传输system表空间或者sys用户对象
8.source端不可以是windows P7,source端的compatible.rdbms必须大于10.2.0，且不大于目标端compatible.rdbms

 SQL> show parameter compatible

9.source端必须处于归档模式

 此处开启归档并不是为了使用归档文件，而是因为oracle的rman备份必须开启归档才能使用。
    SQL> archive log list
    Database log mode              Archive Mode

10.dest端OS必须是64-bit Oracle Linux或者认证的redhat linux
11.source端RMAN默认是设备类型必须是DISK，且不能配置压缩，否则会报如下错误：

 Entering RollForward After applySetDataFile Done: applyDataFileTo Done: 
    RestoreSetPiece DECLARE * ERROR at line 1: ORA-19624: operation failed, 
    retry possible ORA-19870: error while restoring backup piece /
    dbfs_direct/FS1/xtts/incrementals/xtts_incr_backup ORA-19608: /
    dbfs_direct/FS1/xtts/incrementals/xtts_incr_backup is not a backup piece 
    ORA-19837: invalid blocksize 0 in backup piece header ORA-06512: at 
    "SYS.X$DBMS_BACKUP_RESTORE", line 2338 ORA-06512: at line 40

12.数据库操作用户必须属于DBA组
13.目标端目录不能有跟源端同名的数据文件
14.如果用户数据量量比较大或数据变化量比较大，需要在全备之前开启block change tracking功能，开启此项功能需要修复Bug 16850197，影响数据库版本：11.2.0.3。

 SQL> alter database enable block change tracking using file '/opt/oracle/oradata/blktrace/blk_trace.dbf';
    SQL> SELECT status, filename,bytes/1024/1024 FROM V$BLOCK_CHANGE_TRACKING;

BCT文件大小估计： = <# of redo threads> * (# of old backups + 2) * (size of db/250000)

of old backups是指控制文件记录的备份数，最大支持到8.

2.3 XTTS操作步骤

2.3.1 Initial Setup

step 1：source创建directory：sourcedir，路径使用当前数据文件使用的路径。如果数据文件有多个路径，需要创建多个directory。

 SQL> select file_name from dba_data_files; 
    FILE_NAME 
    -------------------------------------------------------------------- 
    /oracle/app/oracle/oradata/orcl/users01.dbf 
    /oracle/app/oracle/oradata/orcl/sysaux01.dbf 
    /oracle/app/oracle/oradata/orcl/undotbs01.dbf 
    /oracle/app/oracle/oradata/orcl/system01.dbf 
    /oracle/app/oracle/oradata/orcl/example01.dbf 
    /oracle/app/oracle/oradata/orcl/soe1.dbf 
    /oracle/app/oracle/oradata/orcl/soe.dbf  
    SQL> create directory sourcedir as '/oracle/app/oracle/oradata/orcl'; 
    Directory created.

step 2：dest创建directory：destdir，路径使用当前数据文件使用的路径。如果目标端有多个路径需要创建多个directory。

 sys@ORCL>select file_name from dba_data_files; 
    FILE_NAME 
    -----------------------------------------------------------------  
    +DATADG/orcl/datafile/users.932.890771737  
    sys@ORCL>create directory destdir as '+DATADG/orcl/datafile'; 
    Directory created.

如果源端有多个路径，目标端可以有1个路径或者跟源端等同数量的路径。比如：

 /opt/a.dbf
    /oracle/b.dbf
    /u01/c.dbf
    以上源端的3个数据文件需要迁移到目标端，那么目标端可以定义一个路径，或者定义三个路径，不能定义2个路径。
    如果定义一个路径，那么上面3个文件都会传输到这一个路径下，如果定义3个路径，会依次填充到对应的路径下，如果定义两个路径会报错。

step 3：创建dest端到source端的dblink
step 3.1:以sys用户创建dblink

 sys@ORCL>create public database link ttslink connect to system identified by oracle using 'orcl'; 
    Database link created.

step 3.2:验证创建的dblink

 sys@ORCL>select * from v$version@ttslink;  
    BANNER 
    ---------------------------------------------------------------- 
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

其中orcl是在tnsname.ora中配置的service name，参考配置文件：

 ORCL = 
    (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.20.61)(PORT = 1521))
      (CONNECT_DATA =   
       (SERVER = DEDICATED)   
       (SERVICE_NAME = orcl) 
      ) 
    )

step 4：source端和dest端创建目录用于存放xtts脚本。Oracle用户下操作，文件的属主/组需要为oracle.oinstall。

 source端： $ mkdir -p /home/oracle/rman-xtt

在source端解压XTTS使用的脚本

 $ pwd                                    
    /home/oracle 
    $ unzip rman-xttconvert_2.0.zip -d rman-xtt 
    Archive:  rman-xttconvert_2.0.zip 
    inflating: rman-xtt/xttcnvrtbkupdest.sql   
    inflating: rman-xtt/xttdbopen.sql   
    inflating: rman-xtt/xttdriver.pl    
    inflating: rman-xtt/xttprep.tmpl    
    inflating: rman-xtt/xtt.properties   
    inflating: rman-xtt/xttstartupnomount.sql

解压后共计6个脚本，其中最重要的两个脚本：

 1）xttdriver.pl：XTTS命令的调用文件  
    2）xtt.properties：XTTS工具的配置文件

step 5 添加source端和dest端temdir到环境变量里面，路径为step 4创建的存在XTTS工具的文件

 $ more .profile 
    PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:. export PATH 
    if [ -s "$MAIL" ]           # This is at Shell startup.  In normal 
    then echo "$MAILMSG"        # operation, the Shell checks 
    fi                          # periodically. 
    export ORACLE_BASE=/oracle/app/oracle 
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 
    export ORACLE_SID=orcl 
    export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH 
    export TMPDIR=/home/oracle/rman-xtt

【说明】：定义tmpdir是为了存放下面步骤中perl脚本产生的文件，如果不定义，产生的文件会生成到/tmp下或报错

step 6:编辑xtt.properties文件

 $vi xtt.properties   
    tablespaces=SOE,SOE1    
    platformid=6   
    srcdir=SOURCEDIR   
    dstdir=DESTDIR   
    srclink=TTSLINK   
    backupformat=/oracle/app/oracle/backup源端增量文件
    stageondest=/home/oracle/source 从源端传输过来的源备份集文件
    backupondest=+DATADG 增量备份集转换后的小端文件 
    dfcopydir=/oracle/app/oracle/backup 使用rman方式时指定的备份集存放位置
    storageondest=+ssddg/orcl/datafile 使用rman方法时数据文件转换后的存放位置

【参数说明】

 1)SOE,SOE1是source端migration的表空间名，需要大写,末尾不要有空格。
    2）platformid值得是source端平台ID，可以通过以下两种方式查询，推荐使用方式一   
    a.方式一   
       SQL> select PLATFORM_ID,PLATFORM_NAME from v$database;  
       PLATFORM_ID PLATFORM_NAME 
       ----------  --------------------------------------------       
           6     AIX-Based Systems (64-bit)     
    b.方式二   
       SQL> col PLATFORM_NAME for a40   
       SQL> set pages 200   
       SQL> select * from v$transportable_platform ;   
       PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT 
       ----------- ---------------------------------------- -------------- 
       1 Solaris[tm] OE (32-bit)                  Big       
       2 Solaris[tm] OE (64-bit)                  Big       
       7 Microsoft Windows IA (32-bit)            Little      
      10 Linux IA (32-bit)                        Little       
       6 AIX-Based Systems (64-bit)               Big       
       3 HP-UX (64-bit)                           Big       
       5 HP Tru64 UNIX                            Little       
       4 HP-UX IA (64-bit)                        Big      
      11 Linux IA (64-bit)                        Little      
      15 HP Open VMS                              Little       
       8 Microsoft Windows IA (64-bit)            Little       
       9 IBM zSeries Based Linux                  Big      
      13 Linux x86 64-bit                         Little      
      16 Apple Mac OS                             Big      
      12 Microsoft Windows x86 64-bit             Little      
      17 Solaris Operating System (x86)           Little      
      18 IBM Power Based Linux                    Big      
      20 Solaris Operating System (x86-64)        Little      
      19 HP IA Open VMS                           Little  

    3)srcdir:source端数据库数据库文件存放路径，也即在directory：sourcedir定义的路径，需要大写。
    4)dstdir：dest端数据库数据库文件存放路径，也即在directory：destdir定义的路径，需要大写。
    5)srclink:指定dest端到source端使用的dblink，需要大写。
    6）backupformat：定义source端rman增量备份产生的文件存在位置  
    7）stageondest：定义dest端存放source端发送的copies和incremental backups
    8）stageondest 从源端传输过来的源增量备份集文件
    9）backupondest 增量备份集转换后的小端文件
   10)如果目标端共享了nfs目录，dfcopydir和stageondest指向该目录

step 7：将source的XTTS脚本传到dest端：

 $ scp -r /home/oracle/rman-xtt 10.10.20.1: '/home/oracle'

2.3.2 Prepare Phase:only once

该阶段有两种方式，优先选择方案A。如果源端数据文件放置在裸设备或者不满足1.3所述条件，选择方案B。

方案A：dbms_file_transfer方式

step 1:source端开启debug模式，开启debug模式后，在调用脚本过程中，会产生详细的输出信息，如果有报错，方便做故障诊断。

 $ export XTTDEBUG=1

step 2:source端prepare

 $ perl xttdriver.pl -S 
    --------------------------------------------------------------------  
    Parsing properties  
    -------------------------------------------------------------------- 
    Done parsing properties 
    -------------------------------------------------------------------- 
    Checking properties 
    -------------------------------------------------------------------- 
    Done checking properties 
    -------------------------------------------------------------------- 
    Starting prepare phase
    -------------------------------------------------------------------- 
    Prepare source for Tablespaces: 
                  'SOE'  /home/oracle/source 
    xttpreparesrc.sql for 'SOE' started at Wed Sep 23 14:36:54 2015
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:54 2015 
    Prepare source for Tablespaces:
                  'SOE1'  /home/oracle/source 
    xttpreparesrc.sql for 'SOE1' started at Wed Sep 23 14:36:54 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:54 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:54 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:55 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:55 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:55 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:55 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Prepare source for Tablespaces:
                   ''  /home/oracle/source 
    xttpreparesrc.sql for '' started at Wed Sep 23 14:36:55 2015 
    xttpreparesrc.sql for  ended at Wed Sep 23 14:36:55 2015 
    Done with prepare phase

说明：这一步XTTS主要做以下两件事

1）验证migration的表空间SOE，SOE1是read write状态，且不存在offline状态的文件
2）本阶段生成八个文件：其中xttnewdatafiles.txt，getfile.sql需要传送到dest端

 $ more xttnewdatafiles.txt 
   ::SOE 
   14,DESTDIR:/soe.dbf 
   ::SOE1 
   6,DESTDIR:/soe1.dbf 

   $ more getfile.sql         
   0,SOURCEDIR,soe.dbf,DESTDIR,soe.dbf 
   1,SOURCEDIR,soe1.dbf,DESTDIR,soe1.dbf

同时XTTS会生成xttplan.txt，记录数据库SCN号，其内容如下：

 $ more xttplan.txt 
   SOE::::3871502 
   14 
   SOE1::::3871518 
   6

step 3:source端将rman-xtt内的xttnewdatafiles.txt getfile.sql传输到dest端

$ scp xttnewdatafiles.txt getfile.sql 10.10.20.1:pwd

step 4:dest端执行脚本从源端获取所有指定文件

 oracle@rac1:/home/oracle/rman-xtt>perl xttdriver.pl -G 
    --------------------------------------------------------------------
    Parsing properties 
    -------------------------------------------------------------------- 
    -------------------------------------------------------------------- 
    Done parsing properties 
    -------------------------------------------------------------------- 
    -------------------------------------------------------------------- 
    Checking properties 
    -------------------------------------------------------------------- 
    -------------------------------------------------------------------- 
    Done checking properties 
    --------------------------------------------------------------------  
    --------------------------------------------------------------------
    Getting datafiles from source 
    -------------------------------------------------------------------- 
    sh: line 5: warning: here-document at line 0 delimited by end-of-file (wanted `EOF') 
    sh: line 6: warning: here-document at line 0 delimited by end-of-file (wanted `EOF')  
    --------------------------------------------------------------------
    Executing getfile for getfile_sourcedir_soe.dbf_0.sql 
    -------------------------------------------------------------------  
    -------------------------------------------------------------------- 
    Executing getfile for getfile_sourcedir_soe1.dbf_0.sql 
    --------------------------------------------------------------------  
    --------------------------------------------------------------------
    Completed getting datafiles from source 
    --------------------------------------------------------------------

方案B：rman备份方式

step1. 在源端执行脚本

 $   perl xttdriver.pl -p

这一步创建数据文件备份，位置由xtt.properties的dfcopydir指定；创建了xttplan.txt和rmanconvert.cmd两个文件。

确保需要传输的表空间数据文件都是读写模式，非offline状态。

step2. 将备份集和rmanconvert.cmd文件拷贝到目标端，如果使用共享的nfs，备份集的拷贝可以忽略，源端做完备份后，目标端对产生的备份集赋予相应的权限。

 $  scp /oracle/app/oracle/backup/* 10.10.20.1:'/home/oracle/source'
    $  scp /home/oracle/rman-xtt/rmanconvert.cmd 10.10.20.1:'/home/oracle/rman-xtt'

step3 目标端转换数据文件备份*

 $  perl xttdriver.pl -c

2.3.3 roll forward Phase

step 1:source 端做第一次增量备份

 $ perl xttdriver.pl -i
   -------------------------------------------------------------------- 
   Parsing properties 
   -------------------------------------------------------------------- 
   Done parsing properties 
   -------------------------------------------------------------------- 
   Checking properties 
   -------------------------------------------------------------------- 
   Done checking properties 
   -------------------------------------------------------------------- 
   Backup incremental 
   -------------------------------------------------------------------- 
   Prepare newscn for Tablespaces: 'SOE'  
   Prepare newscn for Tablespaces: 'SOE1'  
   Prepare newscn for Tablespaces: ''  
   Prepare newscn for Tablespaces: ''  
   Prepare newscn for Tablespaces: ''  
   Prepare newscn for Tablespaces: ''  
   Prepare newscn for Tablespaces: ''  
   Prepare newscn for Tablespaces: ''  
   rman target /  cmdfile /home/oracle/rman-xtt/rmanincr.cmd  

   Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 23 14:57:13 2015  

   Copyright (c) 1982, 2007, Oracle.  All rights reserved.  

   connected to target database: ORCL (DBID=1418362679)  

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

  ts::SOE 
  host command complete  

  Starting backup at 23-SEP-15 
  allocated channel: ORA_DISK_1 
  channel ORA_DISK_1: sid=33 devtype=DISK 
  channel ORA_DISK_1: starting full datafile backupset 
  channel ORA_DISK_1: specifying datafile(s) in backupset 
  input datafile fno=00014 name=/oracle/app/oracle/oradata/orcl/soe.dbf 
  channel ORA_DISK_1: starting piece 1 at 23-SEP-15 
  channel ORA_DISK_1: finished piece 1 at 23-SEP-15 
  piece handle=/oracle/app/oracle/backup/2gqhsoac_1_1 tag=TTS_INCR_UPDATE comment=NONE 
  channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55 
  Finished backup at 23-SEP-15  

  executing command: SET NOCFAU  
  ts::SOE1 
  host command complete  

  Starting backup at 23-SEP-15 
  using channel ORA_DISK_1 
  channel ORA_DISK_1: starting full datafile backupset 
  channel ORA_DISK_1: specifying datafile(s) in backupset 
  input datafile fno=00006 name=/oracle/app/oracle/oradata/orcl/soe1.dbf 
  channel ORA_DISK_1: starting piece 1 at 23-SEP-15 
  channel ORA_DISK_1: finished piece 1 at 23-SEP-15 
  piece handle=/oracle/app/oracle/backup/2hqhsodv_1_1 tag=TTS_INCR_UPDATE comment=NONE 
  channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 
  Finished backup at 23-SEP-15 

  Recovery Manager complete.  
  --------------------------------------------------------------------  
  Done backing up incrementals  
  --------------------------------------------------------------------

该step产生两个文件tsbkupmap.txt、incrbackups.txt需要传到dest端，内容如下：

 $ more tsbkupmap.txt 
  SOE1::6:::1=2hqhsodv_1_1 
  SOE::14:::1=2gqhsoac_1_1

  $ more incrbackups.txt 
  /oracle/app/oracle/backup/2hqhsodv_1_1 
  /oracle/app/oracle/backup/2gqhsoac_1_1

同时其会产生一个新的记录scn的文件：xttplan.txt.new，

 $ more xttplan.txt.new 
  SOE::::3871502 
  14 
  SOE1::::3871518 
  6

step 2 将tsbkupmap.txt，incrbackups.txt，xttplan.txt传到dest端

 $ scp `cat incrbackups.txt` oracle@10.10.20.1:/home/oracle/source 
  2hqhsodv_1_1    100%  104KB 104.0KB/s   00:00                                                                                            2gqhsoac_1_1    100%   48KB  48.0KB/s   00:00     

  $ scp xttplan.txt tsbkupmap.txt oracle@10.10.20.1:/home/oracle/rman-xtt 
  oracle@10.10.20.1's password:      
  xttplan.txt      100%   63     0.1KB/s   00:00
  tsbkupmap.txt     100%   77     0.1KB/s   00:00

step 3:dest 端应用增量备份

 oracle@rac1:/home/oracle/rman-xtt>perl xttdriver.pl -r  
  --------------------------------------------------------------------
  Parsing properties 
  --------------------------------------------------------------------
  -------------------------------------------------------------------- 
  Done parsing properties
  --------------------------------------------------------------------
  -------------------------------------------------------------------- 
  Checking properties 
  --------------------------------------------------------------------  
  -------------------------------------------------------------------- 
  Done checking properties 
  --------------------------------------------------------------------
  -------------------------------------------------------------------- 
  Start rollforward 
  --------------------------------------------------------------------  
  -------------------------------------------------------------------- 
  End of rollforward phase 
  -------------------------------------------------------------------

step 4:源端确定下一次增量备份的from_scn

内部触发的其实是删除xttplan.txt.new，将其内容备份到xttplan.txt中

 $ perl xttdriver.pl -s 
  --------------------------------------------------------------------
  Parsing properties 
  -------------------------------------------------------------------- 
  -------------------------------------------------------------------- 
  Done parsing properties 
  --------------------------------------------------------------------
  -------------------------------------------------------------------- 
  Checking properties 
  --------------------------------------------------------------------  
  -------------------------------------------------------------------- 
  Done checking properties 
  -------------------------------------------------------------------- 
  Prepare newscn for Tablespaces: 'SOE'  
  Prepare newscn for Tablespaces: 'SOE1'  
  Prepare newscn for Tablespaces: ''  
  Prepare newscn for Tablespaces: ''  
  Prepare newscn for Tablespaces: ''  
  Prepare newscn for Tablespaces: ''  
  Prepare newscn for Tablespaces: ''  
  Prepare newscn for Tablespaces: ''  
  New /home/oracle/rman-xtt/xttplan.txt with FROM SCN's generated

查看from_scn

 $ more xttplan.txt 
  TEST1::::2818317 
  6 
  7 
  8 
  11 
  12 
  16 
  17 
  18 
  19 
  TEST2::::2818328 
  9 
  10 
  xttplan.txt: END

step 5:第二次增量备份

 $ perl xttdriver.pl -i

查看生成的文件：

 $ more xttplan.txt.new 

  $ more incrbackups.txt

step6：将生成的文件传到dest端

 $ scp `cat incrbackups.txt` oracle@10.10.20.1:/home/oracle/source   
  $ scp xttplan.txt tsbkupmap.txt oracle@10.10.20.1:/home/oracle/rman-xtt

step 7:在目标端应用增量备份

 oracle@rac1:/home/oracle/rman-xtt>perl xttdriver.pl -r

step 8:soure端更新from_scn

 perl xttdriver -s

2.3.4 Transport Phase

以上的步骤，source端migration的表空间都是read write状态，即不会影响应用。下面进行的最后一次增量备份，要求将表空间置于read only状态，生产不可用。因而下面的操作也是实际影响downtime的最主要部分。

step 1:最后一次增量备份

 **************************************************************************
  将source端migration的表空间置于read only状态  
  SQL> alter tablespace SOE1 read only;  
  Tablespace altered.  

  SQL> alter tablespace SOE read only;  
  Tablespace altered.    

  在source端进行最后一次增量备份 
  perl xttdriver.pl -i

step 2:将生成的文件传到dest端，如果使用共享nfs，增量备份集的传输可以忽略，注意备份集要赋予相应权限

 $ scp `cat incrbackups.txt` oracle@10.10.20.1:/home/oracle/source 
  $ scp xttplan.txt tsbkupmap.txt oracle@10.10.20.1:/home/oracle/rman-xtt

step 3 在dest端应用备份

 oracle@rac1:/home/oracle/rman-xtt>perl xttdriver.pl -r

step 4在source查询相应schema的权限及创建语句，以及directory,dblink等对象所需要的主机文件，tnsnames等。以下spool出来的脚本做相应修改后在新数据库执行。

 mkdir -p /user/oracle/xtts_metadata
  cd /user/oracle/xtts_metadata

  sqlplus / as sysdba
  set long 9999 linesize 300 pagesize 9999 long 9999

  spool /user/oracle/xtts_metadata/dir.sql
  select dbms_metadata.get_ddl('DIRECTORY',DIRECTORY_NAME)||’;’ from dba_directories;
  spool off

  spool /user/oracle/xtts_metadata/user.sql
  select dbms_metadata.get_ddl('USER',username) from dba_users where username in('IDAP2','BIAP','GD','ZQ_ETL','ZQ_ZHENGQI','IDAP_UI','IDAP_ETL','HEMS','HEMS_ETL','CRM','INFOGUARDIAN','IDAP','RPMS','U_WEIHU2','IRM','ZQ_LIXIANG','U_SIT','IDAP4HEMSS','IDAP_SP'); --默认表空间在导入表空间元信息后再指定     
  spool off

step 5根据上述语句在dest端创建相应schema及授权
step 6在dest端执行以下命令，产生datapump脚本

 perl xttdriver.pl -e

step 7 确认AQTMPROCESSES为非零值

源端AQTMPROCESSES 为非零值，否则impdp会出错

step 8 修改上一步产生的脚本，在dest端第一次抽取元数据：基于表空间抽取

 创建directory：
  mkdir -p /xxx/xxx
  chown oracle:dba /xxx/xxx
  CREATE OR REPLACE DIRECTORY "xxx" AS '/xxx/xxx';

  impdp directory=DATA_PUMP_DIR logfile=imp.log \ network_link=ttslink transport_full_check=no \ transport_tablespaces=SOE,SOE1 \ cluster=n \ transport_datafiles='+DATADG/orcl/datafile/soe.dbf','+DATADG/orcl/datafile/soetest1.dbf','+DATADG/orcl/datafile/soe1.dbf'，'+DATADG/orcl/datafile/soetest2.dbf'  

  检查确认用户的对象和表空间正常：   
  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 
  Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** 
  directory=DATA_PUMP_DIR logfile=imp.log network_link=ttslink 
  transport_full_check=no transport_tablespaces=SOE,SOE1 cluster=n 
  transport_datafiles=+DATADG/orcl/datafile/soe.dbf,+DATADG/orcl/datafile/soetest1.dbf,+DATADG/orcl/datafile/soe1.dbf,+DATADG/orcl/datafile/soetest2.dbf  
  Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 
  Processing object type TRANSPORTABLE_EXPORT/TABLE 
  Processing object type TRANSPORTABLE_EXPORT/INDEX 
  Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS 
  Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TRIGGER 
  Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX 
  Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS 
  Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 
  Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 
  Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Sep 23 16:57:50 2015 elapsed 0 00:00:54

step 9 修改用户默认表空间

 select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users@ttslink where username in ('SOE’,’SOE1’);

由上面的结果得知，此次抽取元数据只抽取了表、索引、触发器，对于序列、过程、视图、包都没有抽取，所以我们进行第二次抽取元数据。在此之前先检查以下dest端表空间、用户对象的状态：

1）表空间

 select tablespace_name,file_name,status from dba_data_files;
  TABLESPACE_NAME            FILE_NAME  
  ------------------------------ --------- 
  USERS                  +DATADG/orcl/datafile/users.932.890771737 
  UNDOTBS1               +DATADG/orcl/datafile/undotbs1.928.890771737 
  SYSAUX                 +DATADG/orcl/datafile/sysaux.875.890771737 
  SYSTEM                 +DATADG/orcl/datafile/system.263.890771735 
  UNDOTBS2               +DATADG/orcl/datafile/undotbs2.929.890771853 
  SOE1                   +DATADG/orcl/datafile/soetest2.dbf 
  SOE1                   +DATADG/orcl/datafile/soe1.dbf 
  USERS                  +DATADG/orcl/datafile/users.1205.891175465 
  USERS                  +DATADG/orcl/datafile/users.1206.891175501 
  USERS                  +DATADG/orcl/datafile/users.1207.891175533 
  USERS                  +DATADG/orcl/datafile/users.1208.891175611 
  USERS                  +DATADG/orcl/datafile/users.1209.891175647 
  USERS                  +DATADG/orcl/datafile/users.1210.891176427 
  SOE                    +DATADG/orcl/datafile/soe.dbf 
  SOE                    +DATADG/orcl/datafile/soetest1.dbf

2)用户对象

 soe： 
  soe@ORCL> select * from ((select 's-t',owner, object_type, count(*)
                from dba_objects
            where owner in ('SOE', 'SOE1')
            group by owner, object_type
                MINUS   
            select 's-t',owner, object_type, count(*)
                from dba_objects@TTSLINK
            where owner in ('SOE', 'SOE1')
            group by owner, object_type)
                union all
            (select 't-s',owner, object_type, count(*)
              from dba_objects@ttslink
            where owner in ('SOE', 'SOE1')
            group by owner, object_type
                MINUS
            select 't-s',owner, object_type, count(*)
                from dba_objects
            where owner in ('SOE', 'SOE1')
            group by owner, object_type)) order by owner,object_type;

step 10 第二次抽取除表、索引、约束、trigger以外的元数据：基于schema抽取

 impdp directory=DATA_PUMP_DIR logfile=imp.log \ network_link=ttslink \ schemas=SOE,SOE1  cluster=n \ content=metadata_only \ exclude=index,table,constraint\

程序输出：

 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 
  Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** 
  directory=DATA_PUMP_DIR logfile=imp.log network_link=ttslink schemas=SOE,SOE1 cluster=n content=metadata_onlyinclude=view,PROCEDURE,SEQUENCE,PACKAGE 
  transport_datafiles=+DATADG/orcl/datafile/soe.dbf,+DATADG/orcl/datafile/soetest1.dbf,+DATADG/orcl/datafile/soe1.dbf,+DATADG/orcl/datafile/soetest2.dbf  
  Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE 
  Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC 
  Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE 
  Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPECALTER_PACKAGE_SPEC 
  ORA-39082: Object type ALTER_PACKAGE_SPEC:"SOE"."ORDERENTRY" created with compilation warnings 
  ORA-39082: Object type ALTER_PACKAGE_SPEC:"SOE1"."ORDERENTRY" created with compilation warnings 
  Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 
  Processing object type SCHEMA_EXPORT/VIEW/VIEW 
  Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY 
  ORA-39082: Object type PACKAGE_BODY:"SOE1"."ORDERENTRY" created with compilation warnings 
  ORA-39082: Object type PACKAGE_BODY:"SOE"."ORDERENTRY" created with compilation warnings 
  Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at Wed Sep 23 17:17:52 2015 elapsed 0 00:00:05

step 11 检查schema对象状态

 select * from ((select 's-t',owner, object_type, count(*)
         from dba_objects
   where owner in ('SOE', 'SOE1')
   group by owner, object_type
   MINUS    
   select 's-t',owner, object_type, count(*)
         from dba_objects@TTSLINK
   where owner in ('SOE', 'SOE1')
   group by owner, object_type)
   union all
   (select 't-s',owner, object_type, count(*)
          from dba_objects@ttslink
   where owner in ('SOE', 'SOE1')
   group by owner, object_type
   MINUS
   select 't-s',owner, object_type, count(*)
          from dba_objects
   where owner in ('SOE', 'SOE1')
   group by owner, object_type)) 
   order by owner,object_type;

由上面的结果，PACKAGE BODY ORDERENTRY状态是invalid的，这也与第二次抽取元数据的errors对应，我们可以重编译以解决此问题：

 soe1@ORCL>alter package ORDERENTRY compile body;  Package body altered.
   soe@ORCL>alter package ORDERENTRY compile body;   Package body altered.

step 12 dest端验证transported的数据

 RMAN> validate tablespace SOE, SOE1 check logical;  

   Starting validate at 2015-09-23 17:34:29 
   using target database control file instead of recovery catalog 
   allocated channel: ORA_DISK_1 
   channel ORA_DISK_1: SID=1239 instance=orcl1 device type=DISK 
   channel ORA_DISK_1: starting validation of datafile 
   channel ORA_DISK_1: specifying datafile(s) for validation 
   input datafile file number=00014 name=+DATADG/orcl/datafile/soe.dbf 
   input datafile file number=00007 name=+DATADG/orcl/datafile/soe1.dbf 
   input datafile file number=00015 name=+DATADG/orcl/datafile/soetest1.dbf 
   input datafile file number=00006 name=+DATADG/orcl/datafile/soetest2.dbf 
   channel ORA_DISK_1: validation complete, elapsed time: 00:00:07 
   List of Datafiles 
   ================= 
   File Status Marked Corrupt Empty Blocks Blocks Examined High SCN 
   ---- ------ -------------- ------------ --------------- ---------- 
   6    OK     0              1254         1280            3972895    
   File Name: +DATADG/orcl/datafile/soetest2.dbf 
   Block Type Blocks Failing Blocks Processed 
   ---------- -------------- ---------------- 
   Data       0              0                
   Index      0              16               
   Other      0              10                
   File Status Marked Corrupt Empty Blocks Blocks Examined High SCN 
   ---- ------ -------------- ------------ --------------- ---------- 
   7    OK     0              137048       262144          3973354    
   File Name: +DATADG/orcl/datafile/soe1.dbf 
   Block Type Blocks Failing Blocks Processed 
   ---------- -------------- ---------------- 
   Data       0              78344            
   Index      0              44136            
   Other      0              2616              
   File Status Marked Corrupt Empty Blocks Blocks Examined High SCN 
   ---- ------ -------------- ------------ --------------- ---------- 
   14   OK     0              128771       262144          3972304     
   File Name: +DATADG/orcl/datafile/soe.dbf  
   Block Type Blocks Failing Blocks Processed 
   ---------- -------------- ---------------- 
   Data       0              83909            
   Other      0              2701              
   File Status Marked Corrupt Empty Blocks Blocks Examined High SCN 
   ---- ------ -------------- ------------ --------------- ---------- 
   15   OK     0              1144         1280            3972293    
   File Name: +DATADG/orcl/datafile/soetest1.dbf 
   Block Type Blocks Failing Blocks Processed 
   ---------- -------------- ---------------- 
   Data       0              0                
   Index      0              126              
   Other      0              10               
   Finished validate at 2015-09-23 17:34:37

由上可以看出，逻辑验证通过。

此时，需要将应用切换到新库！

step 13 将dest端表空间置于read write状态

 SQL> alter tablespace SOE read write;  
   Tablespace altered  

   SQL> alter tablespace SOE1 read write;  
   Tablespace altered

到此，整个XTTS的步骤完成

2.4 增加数据文件的异常处理

强烈推荐在数据迁移开始后，不要再增加数据文件，如果在数据迁移过程中产生新的数据文件，XTTS不能正确传输这些文件，可以通过如下两种方式来解决：

2.4.1 方式一

对于临时增加的两个数据文件，可以利用DBMSFILETRANSFER.GET_FILE包来实现文件由source端传到dest端，数据文件传输完成后，会自动完成大小端的转化，该包的参数如下：

 DBMS_FILE_TRANSFER.GET_FILE( 
      source_directory_object IN VARCHAR2,–源目录 
      source_file_name IN VARCHAR2,–源文件名 
      dblink_to_source -源端到目标端的dblink
      destination_directory_object IN VARCHAR2,–目标目录 
      destination_file_name IN VARCHAR2，–目标文件名 
    );
    执行该功能的时机是在最后一次增量迁移开始时，把表空间置为只读后。

   目标端执行：  

   SQL>  exec DBMS_FILE_TRANSFER.GET_FILE('SOURCEDIR','soetest1.dbf', 'DFTLINK'，'DESTDIR','soetest1.dbf'); PL/SQL procedure successfully completed. 

   dest端检查文件是否存在  
   ASMCMD> ls -ll +datadg/orcl/datafile/soetest1.dbf  
   Type      Redund  Striped  Time             Sys  Name                                         N    soetest1.dbf => +DATADG/ORCL/DATAFILE/FILE_TRANSFER.1223.891187881

2.4.2 方式二

例如对于新增的数据文件test_sylar,需要执行以下步骤，手动恢复到dest端

step 1:在xttplan.txt文件中对应表空间test2增加文件号13

 vi xttplan.txt 
    TEST1::::2818317 
    6
    7
    8
    11
    12
    16
    17
    18
    19
    TEST2::::2818328 
    9
    10
    13

step 2:在getfile.sql增加相应文件内容

 vi getfile.sql 
    0,SOURCEDIR,test1.dbf,DESTDIR,test1.dbf 
    0,SOURCEDIR,test2.dbf,DESTDIR,test2.dbf 
    0,SOURCEDIR,test3.dbf,DESTDIR,test3.dbf
    0,SOURCEDIR,test1_4.dbf,DESTDIR,test1_4.dbf
    0,SOURCEDIR,test1_88.dbf,DESTDIR,test1_88.dbf 
    0,SOURCEDIR,test1_1988.dbf,DESTDIR,test1_1988.dbf 
    0,SOURCEDIR,test1_1989.dbf,DESTDIR,test1_1989.dbf 
    0,SOURCEDIR,test1990.dbf,DESTDIR,test1990.dbf 
    0,SOURCEDIR,test1991.dbf,DESTDIR,test1991.dbf 
    1,SOURCEDIR,test21.dbf,DESTDIR,test21.dbf
    1,SOURCEDIR,test22.dbf,DESTDIR,test22.dbf 
    1,SOURCEDIR,test_sylar.dbf,DESTDIR,test_sylar.dbf

step 3:编辑xttnewdatafiles.txt增加相应内容

 ::TEST1 
   6,+DATADG/orcl/datafile/test1.dbf 
   7,+DATADG/orcl/datafile/test2.dbf 
   8,+DATADG/orcl/datafile/test3.dbf 
   11,+DATADG/orcl/datafile/test1_4.dbf 
   12,+DATADG/orcl/datafile/test1_88.dbf 
   16,+DATADG/orcl/datafile/test1_1988.dbf 
   17,+DATADG/orcl/datafile/test1_1989.dbf 
   18,+DATADG/orcl/datafile/test1990.dbf 
   19,+DATADG/orcl/datafile/test1991.dbf 
   ::TEST2 
   9,+DATADG/orcl/datafile/test21.dbf 
   10,+DATADG/orcl/datafile/test22.dbf 
   13,+DATADG/orcl/datafile/test_sylar.dbf

step 4:将source端的备份字节convert为目标端的字节

 oracle@rac1:/home/oracle/rman-xtt>pwd 
   /home/oracle/rman-xtt 
   oracle@rac1:/home/oracle/rman-xtt>dba  

   SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 19:47:30 2015  

   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  

   sys@ORCL>@xttcnvrtbkupdest.sql 
   Enter value for 1: /home/oracle/source/1rqhilqu_1_1 
   Enter value for 2: /home/oracle/source Enter value for 3: 6 
   ERROR IN CONVERSION ORA-19624: operation failed, retry possible 
   ORA-19504: failed to create file "/home/oracle/source/xtts_incr_backup" 
   ORA-27038: created file already exists Additional information: 1 
   ORA-19600: input file is backup piece  (/home/oracle/source/1rqhilqu_1_1) 
   ORA-19601: output file is backup piece  (/home/oracle/source/xtts_incr_backup) 
   CONVERTED BACKUP PIECE/home/oracle/source/xtts_incr_backup  
   PL/SQL procedure successfully completed.  

   Disconnected from 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

step 5：利用dbmsbackuprestore包恢复数据文件test_sylar

 DECLARE  
    devtype varchar2(256);   
    done boolean;  
  BEGIN  
   devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 't1');
   dbms_backup_restore.RestoreSetDatafile;  
   dbms_backup_restore.RestoreDatafileTo(dfnumber => 13,toname => '+datadg/orcl/datafile/TEST_SYLAR.DBF');  
   dbms_backup_restore.RestoreBackupPiece(done => done,handle => '/home/oracle/source/xtts_incr_backup', params => null);  
   dbms_backup_restore.DeviceDeallocate; 
   END;

step 6:检查数据库是否存在此文件

 ASMCMD> ls -l +DATADG/orcl/datafile/test_sylar.dbf 
   Type      Redund  Striped  Time             Sys  Name  
   N    test_sylar.dbf => +DATADG/ORCL/DATAFILE/UNKNOWN.1074.890857729

3. 问题及注意事项

3.1 迁移过程中使用nfs的注意事项

在server端开启异步io：/etc/exports文件中指定async选项。

3.2 IO优化

开启block change tracking可以大幅增加增量备份速度，具体开启方法和注意事项见2.2.

在prepare阶段使用rman方式，下列选项设置为大于1的数，可以在copy数据文件过程中开启多个channel：实际场景中需通过测试获取最佳值。

CONFIGURE DEVICE TYPE DISK PARALLELISM 1;

3.3 相关Bug

3.3.1.bug 17565514：

【BUG说明】导致convert过程失败，rman trace文件中显示rman进程不断去探测数据库实例是否是archive模式和mount状态；

【解决方法】创建测试库作为辅助实例，开启归档，数据库开启到Mount状态。每次做-c操作前注意指定 ORACLE_SID为辅助实例。

3.3.2.Bug 7700493：

【BUG说明】导入元数据时会触发。

【解决方法】触发该bug之后，导入元数据的作业会断掉。对比源库和目标库的对象数量，对于缺失的对象单独导入元数据。

ITPub博客

XTTS数据迁移

XTTS数据迁移

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

1. OVERVIEW

1.1 XTTS功能和目标

1.2 oracle建议使用场景

1.3 平台、数据库版本要求

2. XTTS 环境检查

2.1 查看环境overview

2.2 Prerequisites

2.3 XTTS操作步骤

2.3.1 Initial Setup

2.3.2 Prepare Phase:only once

2.3.3 roll forward Phase

2.3.4 Transport Phase

2.4 增加数据文件的异常处理

2.4.1 方式一

2.4.2 方式二

3. 问题及注意事项

3.1 迁移过程中使用nfs的注意事项

3.2 IO优化

3.3 相关Bug

最新文章