oracle小知识点14--xtts传输表空间
来源：it-home
xtts(Cross Platform Transportable Tablespaces)可以说是tts的加强版,通过增量的概念用来减少停机时间.对于跨平台不同字节顺序endian_format还是比较有用,当然相同平台或者相同字节顺序也是可以使用.不足的地方是目标端只能是linux x86-64,而且目标端数据库必须是11.2.0.4或者有装11.2.0.4的软件.
官方参考文档:11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (文档 ID ),文档的最下方是用于执行rman-xttconvert的脚本下载,目前脚本版本是2.0.
整个操作过程自动化程度很高,通过准备prepare,转换convert,增量备份incremental,应用增量roll forward,导入到目标库transport.以下是测试:
os:源端:centos 6.6 目标端:centos 6.6
db:源端:11.2.0.4 文件系统 目标端:11.2.0.4 文件系统
host:源端:ct.56.121 目标端:ct.56.120
源端实例:ct66 目标端实例:ctdb 目标端辅助实例:xtt1.##ct6604##在目标端新建辅助实例,并启动到nomount.
#此处是为了通用使用了辅助实例,其实目标端数据库本来就是11.2.0.4,可以不需要辅助实例,直接在目标端面实例上就可以.如果目标端数据库不是11.2.0.4,新装了一个11.2.0.4的软件用以启动,记得要指定11.2.0.4的环境变量.
[oracle@ct6604 dbs]$ cat & $ORACLE_HOME/dbs/initxtt.ora
db_name=xtt
compatible=11.2.0.4.0
[oracle@ct6604 dbs]$ ORACLE_SID=xtt
[oracle@ct6604 dbs]$ sqlplus / as sysdba
SQL& exit2.##ct6604##在目标端实例上建连接源端的dblink和用于存放数据文件的目录directory.
#此步骤是为了最近通过impdp dblink的方式导入数据文件到目标端,如果准备采用本地导入则不需要建dblink.
[oracle@ct6604 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@ct6604 admin]$ vi tnsnames.ora
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.108.56.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ct66)
[oracle@ct6604 dbs]$ ORACLE_SID=ctdb
[oracle@ct6604 ~]$ sqlplus / as sysdba
SQL& create directory dump_oradata as '/u02/oradata/ctdb';
SQL& create public database link lnk_ct66 connect to system identified by system using 'ct66';
SQL& select * from dual@lnk_ct66;
SQL& exit2.##ct6604##在目标端配置nfs服务.
#整个xtts的过程源端产生的数据文件,增量备份,执行脚本都是要传到目标端.通过测试发现,使用nfs的方式将传输直接在生成文件的时候就完成了,方便操作,又减少错误.如果不使用nfs,手动去传也是可以.
[oracle@ct6604 ~]$ mkdir /home/oracle/xtts
[oracle@ct6604 ~]$ su -
[root@ct6604 oracle]# service nfs status
[root@ct6604 ~]# cat /etc/exports
/home/oracle/xtts *(rw,sync,no_root_squash,insecure,anonuid=500,anongid=500)
[root@ct6604 oracle]# service nfs start4.##ct6605##在源端建立测试用的用户,表空间,表,权限.
#此处的权限和表用于迁移之后的验证
[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL& create tablespace tbs01 datafile '/u02/oradata/ct66/tbs01.dbf' size 10m autoextend on next 2m maxsize 4g;
SQL& create tablespace tbs02 datafile '/u02/oradata/ct66/tbs02.dbf' size 10m autoextend on next 2m maxsize 4g;
SQL& create user test01 identified by test01 default tablespace tbs01;
SQL& create user test02 identified by test02 default tablespace tbs02;
SQL& grant connect,resource to test01;
SQL& grant connect,resource to test02;
SQL& grant execute on dbms_crypto to test02;
SQL& create table test01.tb01 as select * from dba_
SQL& create table test02.tb01 as select * from dba_
SQL& grant select on test01.tb01 to test02;
SQL& exit5.##ct6605##在源端连接目标端的nfs,mount到/home/oracle/xtts下.
[oracle@ct6605 ~]$ mkdir /home/oracle/xtts
[oracle@ct6605 ~]$ su -
[root@ct6605 ~]# showmount -e 192.108.56.120
Export list for 192.108.56.120:
/home/oracle/xtts *
[root@ct6605 ~]# mount -t nfs 192.108.56.120:/home/oracle/xtts /home/oracle/xtts6.##ct6605##在源端解压rman-xttconvert脚本,配置xtts的参数文件.
#此处的操作都是在/home/oracle/xtts下,它也目标端nfs是的一个目录,所以目标端就不需要再配置这些.
#配置文件参数说明:tablespaces要传输的表空间
platformid源端平台ID,通过V$DATABASE.PLATFORM_ID查看
srcdir,dstdir,srclink是用于通过dbms_file_transfer传输的参数,本测试通过rman,不使用
dfcopydir源端生成数据文件的目录
backupformat源端生成增量备份的目录
stageondest目标端存放源数据文件和增量备份的目录
storageondest目录端存放目标数据文件的目录
backupondest目标端使用ASM时转换增量备份的目录,目标端使用数据文件建议和stageondest设的一样
parallel,rollparallel,getfileparallel并行度,此处用的默认
此测试没使用的参数:cnvinst_home,cnvinst_sid目标端辅助实例的oracle_home,sid,如果目标端是单独又装的11.2.04的软件,需要指定
asm_home,asm_sid目标端使用ASM时,用于指定asm实例的oracle_home,sid.
#将dfcopydir,backupformat设的一样,然后对应到目标端stageondest的路径,这样使用nfs方式就不用去管文件的位置
#/home/oracle/xtts/script是用于存放执行过程中生成的各种脚本
[root@ct6605 xtts]# su - oracle
[oracle@ct6605 ~]# cd /home/oracle/xtts
[oracle@ct6605 xtts]$ mkdir
backup script
[oracle@ct6605 xtts]$ cp /home/oracle/rman-xttconvert_2.0.zip /home/oracle/xtts/
[oracle@ct6605 xtts]$ unzip rman-xttconvert_2.0.zip
[oracle@ct6605 xtts]$ mv xtt.properties xtt.properties.bak
[oracle@ct6605 xtts]$ cat xtt.properties.bak|grep -v ^#|grep -v ^$ &xtt.properties
[oracle@ct6605 xtts]$ vi xtt.properties
[oracle@ct6605 xtts]$ cat xtt.properties
tablespaces=TBS01,TBS02
platformid=13
#srcdir=SOURCEDIR1,SOURCEDIR2
#dstdir=DESTDIR1,DESTDIR2
#srclink=TTSLINK
dfcopydir=/home/oracle/xtts/backup
backupformat=/home/oracle/xtts/backup
stageondest=/home/oracle/xtts/backup
storageondest=/u02/oradata/ctdb
backupondest=/home/oracle/xtts/backup
parallel=3
rollparallel=2
getfileparallel=47.##ct6605##在源端执行准备prepare操作
#此处生成数据文件和转换脚本
[oracle@ct6605 xtts]$ ORACLE_SID=ct66
[oracle@ct6605 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6605 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -p
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
Prepare source for Tablespaces:
/home/oracle/xtts/backup
xttpreparesrc.sql for 'TBS01' started at Fri Jan 15 12:31:45 2016
xttpreparesrc.sql for
ended at Fri Jan 15 12:31:45 2016
Prepare source for Tablespaces:
/home/oracle/xtts/backup
xttpreparesrc.sql for 'TBS02' started at Fri Jan 15 12:31:50 2016
xttpreparesrc.sql for
ended at Fri Jan 15 12:31:50 2016
Prepare source for Tablespaces:
/home/oracle/xtts/backup
xttpreparesrc.sql for '' started at Fri Jan 15 12:31:53 2016
xttpreparesrc.sql for
ended at Fri Jan 15 12:31:53 2016
--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------8.##ct6604##在目标端执行行转换convert操作
#因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
[root@ct6604 ~]# su - oracle
[oracle@ct6604 ~]$ cd /home/oracle/xtts
[oracle@ct6604 xtts]$ ORACLE_SID=xtt
[oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -c
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Performing convert
--------------------------------------------------------------------
--------------------------------------------------------------------
Converted datafiles listed in: /home/oracle/xtts/script/xttnewdatafiles.txt
--------------------------------------------------------------------9.##ct6605##在源端模拟生成新增数据
[oracle@ct6605 xtts]$ ORACLE_SID=ct66
[oracle@ct6605 xtts]$ sqlplus / as sysdba
SQL& insert into test01.tb01 select * from test01.tb01;
SQL& insert into test02.tb01 select * from test02.tb01;
SQL& exit10.##ct6605##在源端执行增量备份incremental
[oracle@ct6605 xtts]$ ORACLE_SID=ct66
[oracle@ct6605 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6605 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'TBS01'
Prepare newscn for Tablespaces: 'TBS02'
Prepare newscn for Tablespaces: ''
rman target /
cmdfile /home/oracle/xtts/script/rmanincr.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 12:41:14 2016
Copyright (c) , Oracle and/or its affiliates.
All rights reserved.
connected to target database: CT66 (DBID=)
2& host 'echo ts::TBS01';
3& backup incremental from scn 1407706
tag tts_incr_update tablespace 'TBS01'
'/home/oracle/xtts/backup/%U';
7& host 'echo ts::TBS02';
8& backup incremental from scn 1407981
tag tts_incr_update tablespace 'TBS02'
'/home/oracle/xtts/backup/%U';
executing command: SET NOCFAU
using target database control file instead of recovery catalog
host command complete
Starting backup at 15-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 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=00006 name=/u02/oradata/ct66/tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/home/oracle/xtts/backup/2oqrebra_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-JAN-16
executing command: SET NOCFAU
host command complete
Starting backup at 15-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u02/oradata/ct66/tbs02.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/home/oracle/xtts/backup/2pqrebre_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JAN-16
Recovery Manager complete.
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------11.##ct6604##在目标端应用增量roll forward
#因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
#应用增量roll forward是应用到转换后的数据文件上
[oracle@ct6604 xtts]$ ORACLE_SID=xtt
[oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------12.##ct6605##在源端模拟生成新增数据,并将要传输的表空间设置只读
#此时才算开始计算停机时间
[oracle@ct6605 xtts]$ ORACLE_SID=ct66
[oracle@ct6605 xtts]$ sqlplus / as sysdba
SQL& insert into test01.tb01 select * from test01.tb01;
SQL& insert into test02.tb01 select * from test02.tb01;
SQL& alter tablespace tbs01
SQL& alter tablespace tbs02
SQL& exit13.##ct6605##在源端执行最后一次增量备份incremental
[oracle@ct6605 xtts]$ ORACLE_SID=ct66
[oracle@ct6605 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6605 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'TBS01'
Prepare newscn for Tablespaces: 'TBS02'
Prepare newscn for Tablespaces: ''
rman target /
cmdfile /home/oracle/xtts/script/rmanincr.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 12:50:58 2016
Copyright (c) , Oracle and/or its affiliates.
All rights reserved.
connected to target database: CT66 (DBID=)
2& host 'echo ts::TBS01';
3& backup incremental from scn 1407706
tag tts_incr_update tablespace 'TBS01'
'/home/oracle/xtts/backup/%U';
7& host 'echo ts::TBS02';
8& backup incremental from scn 1407981
tag tts_incr_update tablespace 'TBS02'
'/home/oracle/xtts/backup/%U';
executing command: SET NOCFAU
using target database control file instead of recovery catalog
host command complete
Starting backup at 15-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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=00006 name=/u02/oradata/ct66/tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/home/oracle/xtts/backup/2qqrecdj_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JAN-16
executing command: SET NOCFAU
host command complete
Starting backup at 15-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u02/oradata/ct66/tbs02.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/home/oracle/xtts/backup/2rqrecdl_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JAN-16
Recovery Manager complete.
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------14.##ct6604##在目标端应用最后一次增量roll forward
#因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
[oracle@ct6604 ~]$ cd /home/oracle/xtts
[oracle@ct6604 xtts]$ ORACLE_SID=xtt
[oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
[oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------15.##ct6604##在目标端产生执行导入的脚本
#因为之前没有设置dstdir,srclink参数,所以此处产生的导入脚本需要手动加上dblink和directory的名称
[oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------
--------------------------------------------------------------------
Done generating plugin file /home/oracle/xtts/script/xttplugin.txt
--------------------------------------------------------------------16.##ct6604##在目标端新建用户,导入传输表空间
[oracle@ct6604 ~]$ ORACLE_SID=ctdb
[oracle@ct6604 xtts]$ sqlplus / as sysdba
SQL& create user test01 identified by test01 ;
SQL& create user test02 identified by test02 ;
SQL& grant connect,resource to test01;
SQL& grant connect,resource to test02;
[oracle@ct6604 ~]$ ORACLE_SID=ctdb
/home/oracle/xtts/script/xttplugin.txt
[oracle@ct6604 ~]$ impdp directory=dump_oradata logfile=tts_imp_tbs01_tbs02.log network_link=lnk_ct66 transport_full_check=no transport_tablespaces=TBS01,TBS02 transport_datafiles='/u02/oradata/ctdb/TBS01_6.xtf','/u02/oradata/ctdb/TBS02_7.xtf'
Import: Release 11.2.0.4.0 - Production on Fri Jan 15 13:12:58 2016
Copyright (c) , Oracle and/or its affiliates.
All rights reserved.
Username: system
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":
system/******** directory=dump_oradata logfile=tts_imp_tbs01_tbs02.log network_link=lnk_ct66 transport_full_check=no transport_tablespaces=TBS01,TBS02 transport_datafiles=/u02/oradata/ctdb/TBS01_6.xtf,/u02/oradata/ctdb/TBS02_7.xtf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jan 15 13:13:30 2016 elapsed 0 00:00:2717.##ct6604##在目标端验证导入的数据和权限和源端是否一致
#此处发现源端给test02用户的execute on dbms_crypto权限没有导入,这是impdp原本的问题.所以在做xtts之前就要确定好这些权限的问题,以减少停机时间.
[oracle@ct6604 xtts]$ sqlplus / as sysdba
SQL& alter user test01 default tablespace tbs01;
SQL& alter user test02 default tablespace tbs02;
SQL& select count(1) from test01.tb01;
SQL& select * from dba_tab_privs where grantee='TEST02';
TABLE_NAME
#select * from dba_tab_privs where owner ='SYS' and grantee='TEST02';
SQL& grant execute on dbms_crypto to test02;
SQL& exit18.##ct6604##xtts完成,在目标端关闭辅助实例,以及关闭nfs略.
[oracle@ct6604 xtts]$ ORACLE_SID=xtt
[oracle@ct6604 xtts]$ sqlplus / as sysdba
SQL& exit测试中的一些小问题:
1.报Cant find xttplan.txt, TMPDIR undefined at xttdriver.pl line 1185.
要注意设定环境变量TMPDIR=/home/oracle/xtts/script
2.Unable to fetch platform name
执行xttdriver.pl之前没有指定ORACLE_SID
3.Some failure occurred. Check /home/oracle/xtts/script/FAILED for more details
If you have fixed the issue, please delete /home/oracle/xtts/script/FAILED and run it
again OR run xttdriver.pl with -L option
执行xttdriver.pl报错后,下次执行要删除FAILED文件.
4.Can't locate strict.pm in @INC
使用$ORACLE_HOME/perl/bin/perl而不是使用perl备注:
测试完成,比较简单吧.做好准备工作,通过在源端和目标端执行几次$ORACLE_HOME/perl/bin/perl xttdriver.pl,再执行impdp就完成.此测试中使用nfs可以省去文件的传输,使用整个操作方便清晰许多.
减少迁移停机时间的goldengate也是不错.另外整库迁移如果平台不同或相同,但字节顺序相同,可先考虑dataguard,Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (文档 ID ).
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动