使用XTTS+增量迁移数据库

最新推荐文章于 2021-06-21 15:50:31 发布

文档搬运工

最新推荐文章于 2021-06-21 15:50:31 发布

阅读量1k

点赞数

分类专栏： EXPDPIMPDP

本文链接：https://blog.csdn.net/xxzhaobb/article/details/106491591

版权

EXPDPIMPDP 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

-- 参考文档，xtts使用到的脚本可以从以下MOS下载。
11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 1389592.1)
12c – 使用跨平台增量备份来减少传输表空间的停机时间 (文档 ID 2102859.1)

12C - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 2005729.1)

-- db info
源端： rdbms 11.2.0.4 192.168.2.52 , 实例名testogg
目标端：rdbms 12.2.0.1 192.168.2.74 , 实例名test

-- 在源端开启块跟踪

alter database enable block change tracking using file '/home/oracle/block_change_tracking.f';

-- 在源端创建测试用表空间，创建用户和测试数据

create tablespace xtts datafile '/u01/app/oracle/oradata/testogg/xtts01.dbf' size 100M autoextend on;
create user u_xtts identified by oracle;
grant connect,resource to u_xtts;
grant dba to u_xtts;
alter user u_xtts default tablespace xtts;
conn u_xtts/oracle
create table t_xtts as select * from dba_objects;

--在源端安装rman_xttconvert_v3.zip ,编辑xtt.properties文件

tablespaces=xtts 
platformid=13
dfcopydir=/backup/xtts/full     -- 源端datafile convert 保存路径，产生的是datafile copy 。
backupformat=/backup/xtts/inc    -- 源端备份保存路径（含增量备份） .源库上备份放置的地方，这个目录必须有足够的空闲磁盘空间来放置0级备份及所有之后产生的增量备份
stageondest=/backup/xtts/full    --目标端备份文件存放路径 .目标系统上用来放置从源库上传输过来的备份的位置
storageondest=/u01/app/oracle/oradata/testogg   -- 目标端的数据文件保存位置
backupondest=/backup/xtts/inc     --目标端增量备份集存放路径

-- export下环境变量TMPDIR，指向脚本所在的目录。否则会报错，报错看提示或者FAILED文件。然后Make sure srcdir dstdir srclink is not defined with prepare

export TMPDIR=/home/oracle

-- 对库进行全备。在/backup/xtts/full下生成了备份文件

/u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -p

-- 将备份和相关文件copy到目标库对应的位置。源端的backupformat目录中的备份要传送到目标端stageondest目录。

cd /backup/xtts/full
scp * 192.168.2.74:/backup/xtts/full/

cd /home/oracle/
scp rmanconvert.cmd 192.168.2.74:/home/oracle/

-- 在目标端恢复全备（恢复出数据文件），数据文件会被恢复到storageondest定义的目录中。

export TMPDIR=/home/oracle
cd /home/oracle
/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -c

-- 源端制造增量数据

conn u_xtts/oracle
insert into  t_xtts select * from dba_objects;
commit;

-- 源端执行增量备份，生成增量备份文件和txt文件

/u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -i

[oracle@oggtest ~]$ ll *.txt
-rw-r--r--. 1 oracle oinstall 150181 Apr 28 09:06 awrrpt_1_28_29.txt
-rw-r--r--  1 oracle oinstall     30 Jun  1 13:53 incrbackups.txt
-rw-r--r--  1 oracle oinstall     25 Jun  1 13:53 tsbkupmap.txt
-rw-r--r--  1 oracle oinstall     52 Jun  1 13:28 xttnewdatafiles.txt
-rw-r--r--  1 oracle oinstall     18 Jun  1 13:28 xttplan.txt
[oracle@oggtest ~]$

--将生成的增量备份文件（在incrbackups.txt中有记录）和相关TXT文件copy到目标库对应的文件夹下.（xttplan.txt, tsbkupmap.txt, incrbackups.txt）在每次增量备份中仍然需要拷贝。因为这些文件在每次执行增量备份后会发生改变。

$ scp `cat incrbackups.txt` oracle@dest:/stageondest  -- 和下面语句一样

[oracle@oggtest inc]$ pwd
/backup/xtts/full
[oracle@oggtest inc]$ scp 02v1lmv6_1_1 192.168.2.74:/backup/xtts/full/    

cd /home/oracle
scp xttplan.txt 192.168.2.74:/home/oracle/
scp tsbkupmap.txt 192.168.2.74:/home/oracle/
scp incrbackups.txt 192.168.2.74:/home/oracle/

-- 目标端进行增量恢复。（每次这个步骤在执行中，都需要拷贝 xttplan.txt 和 tsbkupmap.txt，因为它们的内容在每次执行时都会发生改变。）

/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -r

##为下次增量备份确定 from_scn （本次测试没有使用该命令。但是xttplan.txt中每次增量备份，该文件中记录的值都会变化）

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

-- 再次制造增量数据，完毕后，将表空间做read only 。进行最后的传输，将备份的增量文件和txt文件传送到目标端。

conn u_xtts/oracle
insert into  t_xtts select * from dba_objects;
commit;

alter tablespace xtts read only;

/u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -i
 
##scp `cat incrbackups.txt` oracle@dest:/stageondest
scp `cat incrbackups.txt` 192.168.2.74:/home/oracle/
scp xttplan.txt.new 192.168.2.74:/home/oracle/
scp tsbkupmap.txt 192.168.2.74:/home/oracle/   -- 里面包含第二次增量备份的文件 
scp incrbackups.txt 192.168.2.74:/home/oracle/

-- 最后一次应用增量，这样，源端和目标端的数据就一致了。

/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -r

-- 源端导出元数据

expdp "'/ as sysdba'" dumpfile = xtts.dmp directory = DUMP transport_tablespaces=xtts logfile=xtts.log

##或者使用perl命令生成导入元数据的命令（通过dblink导入） 

/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -e     -- 生成 /home/oracle/xttplugin.txt

[oracle@wls10306-01 ~]$ more /home/oracle/xttplugin.txt
impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \
network_link=<ttslink> transport_full_check=no \
transport_tablespaces=XTTS \
transport_datafiles='/u01/app/oracle/oradata/testogg/XTTS_9.dbf'
[oracle@wls10306-01 ~]$ 


##-- 执行导入 ，在目标端创建连接到源端的dblink 。（没有用这种方法。）
## 
##create database link to_test connect to system identified by oracle using 'TEST';
## 
##impdp system/oracle directory=DUMP logfile=tts_imp.log network_link=to_test transport_full_check=no transport_tablespaces=XTTS transport_datafiles='/u01/app/oracle/oradata/testogg/XTTS_9.dbf'
##

-- 导入元数据，先创建用户

U_XTTS
create user u_xtts identified by oracle;
grant connect,resource to u_xtts;
grant dba to u_xtts;
alter user u_xtts default tablespace xtts;
 

impdp system/oracle directory=DUMP logfile=tts_imp.log  dumpfile=xtts.dmp  transport_datafiles='/u01/app/oracle/oradata/testogg/XTTS_9.dbf'

-- 验证是否存在逻辑或物理损坏

RMAN>validate tablespce xtts;

-- 将目标端的表空间xtts 更改为读写模式

alter tablespace xtts read write;

--对比数据

[oracle@oggtest admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 15:20:00 2020

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

@>conn / as sysdba
Connected.
SYS@testogg>select count(*) from u_xtts.t_xtts;

  COUNT(*)
----------
    261273

SYS@testogg>


[oracle@wls10306-01 admin]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 1 16:59:41 2020

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

@>conn / as sysdba
Connected.
SYS@test>select count(*) from u_xtts.t_xtts;

  COUNT(*)
----------
    261273

SYS@test>

附加：
###################################################备份和恢复的过程

-- 源端全库备份

[oracle@oggtest ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -p
============================================================
trace file is /home/oracle/prepare_Jun1_Mon_13_28_36_720//Jun1_Mon_13_28_36_720_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------

Prepare source for Tablespaces:
                  'XTTS'  /backup/xtts/full
xttpreparesrc.sql for 'XTTS' started at Mon Jun  1 13:28:36 2020
xttpreparesrc.sql for  ended at Mon Jun  1 13:28:36 2020

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


--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------


--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------

[oracle@oggtest ~]$

## 生成的文件

[oracle@oggtest ~]$ ls -l /backup/xtts/full
total 102408
-rw-r----- 1 oracle oinstall 104865792 Jun  1 13:28 XTTS_9.tf
[oracle@oggtest ~]$

-- 目标端执行全库恢复，恢复出数据文件

[oracle@wls10306-01 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -c
============================================================
trace file is /home/oracle/convert_Jun1_Mon_15_28_45_475//Jun1_Mon_15_28_45_475_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Performing convert
--------------------------------------------------------------------


--------------------------------------------------------------------
Converted datafiles listed in: /home/oracle/xttnewdatafiles.txt
--------------------------------------------------------------------

[oracle@wls10306-01 ~]$

## 转换后的文件

[oracle@wls10306-01 ~]$ more /home/oracle/xttnewdatafiles.txt
::XTTS
9,/u01/app/oracle/oradata/testogg/XTTS_9.dbf
[oracle@wls10306-01 ~]$

-- 执行增量备份

[oracle@oggtest ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -i
============================================================
trace file is /home/oracle/incremental_Jun1_Mon_13_53_39_822//Jun1_Mon_13_53_39_822_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'XTTS'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


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

[oracle@oggtest ~]$

-- 进行一次增量恢复

[oracle@wls10306-01 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -r
============================================================
trace file is /home/oracle/rollforward_Jun1_Mon_15_53_01_314//Jun1_Mon_15_53_01_314_.log
=============================================================

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


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


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


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


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


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

[oracle@wls10306-01 ~]$

-- 最后一次增量备份

[oracle@oggtest ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl xttdriver.pl -i
============================================================
trace file is /home/oracle/incremental_Jun1_Mon_14_19_19_347//Jun1_Mon_14_19_19_347_.log
=============================================================

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


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


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


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


--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------

============================================================
No new datafiles added
=============================================================
Prepare newscn for Tablespaces: 'XTTS'

--------------------------------------------------------------------
Starting incremental backup
--------------------------------------------------------------------


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

[oracle@oggtest ~]$

-- 最后一次增量恢复

[oracle@wls10306-01 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl xttdriver.pl -r
============================================================
trace file is /home/oracle/rollforward_Jun1_Mon_16_20_55_894//Jun1_Mon_16_20_55_894_.log
=============================================================

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


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


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


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


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


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

[oracle@wls10306-01 ~]$

-- 导入元数据

[oracle@wls10306-01 dump]$ impdp system/oracle directory=DUMP logfile=tts_imp.log  dumpfile=xtts.dmp  transport_datafiles='/u01/app/oracle/oradata/testogg/XTTS_9.dbf'

Import: Release 12.2.0.1.0 - Production on Mon Jun 1 16:56:07 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=DUMP logfile=tts_imp.log dumpfile=xtts.dmp transport_datafiles=/u01/app/oracle/oradata/testogg/XTTS_9.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jun 1 16:56:39 2020 elapsed 0 00:00:26

[oracle@wls10306-01 dump]$

-- 补充 xttdriver.pl 的用法

[oracle@oggtest ~]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl
============================================================
trace file is /home/oracle//Jun2_Tue_09_51_40_380//Jun2_Tue_09_51_40_380_.log
=============================================================

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


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


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


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


   This program prepares, backsup and rollsforward tablespaces
   for cross-platform transportable tablespaces.

    usage: xttdriver.pl
                  {[--backup|-b] || [--bkpincr|-B] || [--bkpexport/E]
                   [--resincrdmp|M]
                   [--fixnewdf|W]
                   [--convert/-c] || [--generate|-e] || [--incremental|-i] ||
                   [[--prepare|-p] || [--getfile|-G]] ||
                   [--restore|R] || [--recover|X]
                   [--rollforward|-r [--rolltbs|-T <TBS1[,TBS2]>] ||
                   [--determinescn|-s] ||
                   [--orasid/O] || [--orahome|-o]]
                   [--help|-h]}

       Additional options
       ------------------
               [--debug|d] [--clearerrorfile|-C] [--xttdir|Dir <tmpdir>]
               [-F/--propfile] [-I/--propdir]

     -b  : For 12c and above, generate transportable backups
     -B  : For 12c and above, generate level 1 transportable backups
     -c  : conversion of datafiles
     -M  : create the dump file from the generated backup
     -e  : generate impdp script: export over new link
     -i  : incremental backup
     -p  : prepare
     -G  : get datafiles from source database using get_file, should not
           be used together with -p
     -r  : roll forward datafiles
     -s  : new from_scn values into xttplan.txt
     -R  : For 12c restore the datafiles from the backups
     -X  : For 12c recover the datafiles from the backups
     -T  : roll forward specific tablespace(s)
     -h  : this (help) message (Default)
     -d  : provides more debug information, also rman is called with debug
           option so that tracing is better.
     -L  : delete the ERROR FILE and proceed with the execution
     -D  : Instead of defining environement variable, user can pass tmpdir
           through xttdir
     -O  : Use this option to pass ORACLE_SID to override the environment
           variable
     -o  : Use this option to pass ORACLE_HOME to override the environment
           variable
     -I  : Use this option to mention the location from where the script
           will pick the properties file etc
     -F  : Use this option to mention the location from where the script
           will pick the properties file.
     -W  : Will try to reconstruct files on the destination after new 
           datafiles have been added

    example: xttdriver.pl -p
             xttdriver.pl -i
             xttdriver.pl -r
             xttdriver.pl -s

[oracle@oggtest ~]$

END

文档搬运工

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
使用XTTS+增量迁移数据库

-- 参考文档，xtts使用到的脚本可以从以下MOS下载。11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID 1389592.1)12c – 使用跨平台增量备份来减少传输表空间的停机时间 (文档 ID 2102859.1)12C - Reduce Transportable Tablespace Downtime using Cross Platform Incre
复制链接

扫一扫