通过xtts迁移单实例文件系统表空间到RAC ASM存储表空间

最新推荐文章于 2022-05-07 16:11:00 发布

congse3359

最新推荐文章于 2022-05-07 16:11:00 发布

阅读量516

点赞数

文章标签：数据库

     xtts迁移文件系统表空间到文件系统表空间可参考,oracle小知识点14--xtts传输表空间 http://blog.itpub.net/28539951/viewspace-1978401/

测试:
os:源端:centos 6.6 目标端:centos 6.6
db:源端:11.2.0.4 文件系统单实例目标端:11.2.0.4 ASM RAC
host:源端:ct6604 192.108.56.120 目标端:ct66rac01 192.108.56.101
源端实例:ctdb 目标端实例:rac11g1

1.##ct66rac01
##在目标端实例上建连接源端的dblink和用于存放数据文件的目录directory.
   #此步骤是为了最近通过impdp dblink的方式导入数据文件到目标端,如果准备采用本地导入则不需要建dblink.
   [oracle@ct66rac01 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
   [oracle@ct66rac01 admin]$ vi tnsnames.ora
   CTDB =
      (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.108.56.120)(PORT = 1521))
       (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ctdb)
       )
      )
   [oracle@ct66rac01 dbs]$ ORACLE_SID=ctdb
   [oracle@ct66rac01 ~]$ sqlplus / as sysdba
   SQL> create directory dump_oradata as '+DATA';
   SQL> create public database link lnk_ctdb connect to system identified by system using 'ctdb';
   SQL> select * from dual@lnk_ctdb;
   /*
   DUMMY
   X
   */
   SQL> exit

2.##ct66rac01
##在目标端配置nfs服务.
   #整个xtts的过程源端产生的数据文件,增量备份,执行脚本都是要传到目标端.通过测试发现,使用nfs的方式将传输直接在生成文件的时候就完成了,方便操作,又减少错误.如果不使用nfs,手动去传也是可以.
   [oracle@ct66rac01 ~]$ mkdir /home/oracle/xtts

   [oracle@ct66rac01 ~]$ su -
   [root@ct66rac01 oracle]# service nfs status
   [root@ct66rac01 ~]# cat /etc/exports
   /home/oracle/xtts *(rw,sync,no_root_squash,insecure,anonuid=500,anongid=500)
   [root@ct66rac01 oracle]# service nfs start

3.##ct6604
##在源端建立测试用的用户,表空间,表,权限.
   #此处的权限和表用于迁移之后的验证
   [oracle@ct6604 ~]$ ORACLE_SID=ctdb
   [oracle@ct6604 ~]$ sqlplus / as sysdba
   SQL> create tablespace tbs01 datafile '/u02/oradata/ctdb/tbs01.dbf' size 10m autoextend on next 2m maxsize 4g;
   SQL> create tablespace tbs02 datafile '/u02/oradata/ctdb/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_objects;
   SQL> create table test02.tb01 as select * from dba_objects;
   SQL> grant select on test01.tb01 to test02;
   SQL> exit

4.##ct6604
##在源端连接目标端的nfs,mount到/home/oracle/xtts下.
   [oracle@ct6604 ~]$ mkdir /home/oracle/xtts
   [oracle@ct6604 ~]$ su -
   [root@ct6604 ~]# showmount -e 192.108.56.101
   Export list for 192.108.56.101:
   /home/oracle/xtts *
   [root@ct6604 ~]# mount -t nfs 192.108.56.101:/home/oracle/xtts /home/oracle/xtts

5.##ct6604
##在源端解压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设的一样,测试发现目标端为ASM也可以把目录设为和stageondest一样,因为无需转换增量备份即可应用增量roll forward
                      parallel,rollparallel,getfileparallel并行度,此处用的默认
                      asm_home,asm_sid目标端使用ASM时,用于指定asm实例的oracle_home,sid.    此测试没使用的参数:cnvinst_home,cnvinst_sid目标端辅助实例的oracle_home,sid,如果目标端是单独又装的11.2.04的软件,需要指定

   [root@ct6604 xtts]# su - oracle
   [oracle@ct6604 ~]# cd /home/oracle/xtts
   [oracle@ct6604 xtts]$ mkdir backup script
   [oracle@ct6604 xtts]$ cp /home/oracle/rman-xttconvert_2.0.zip /home/oracle/xtts/
   [oracle@ct6604 xtts]$ unzip rman-xttconvert_2.0.zip
   [oracle@ct6604 xtts]$ mv xtt.properties xtt.properties.bak
   [oracle@ct6604 xtts]$ cat xtt.properties.bak|grep -v ^#|grep -v ^$ >xtt.properties
   [oracle@ct6604 xtts]$ vi xtt.properties
   [oracle@ct6604 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=+DATA
   backupondest=/home/oracle/xtts/backup
   asm_home=/u01/app/11.2.0/grid
   asm_sid=+ASM1
   parallel=3
   rollparallel=2
   getfileparallel=4

6.##ct6604
##在源端执行准备prepare操作
   #此处生成数据文件和转换脚本
   [oracle@ct6604 xtts]$ ORACLE_SID=ctdb
   [oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -p

7.##ct66rac01
##在目标端执行行转换convert操作
   #因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
   [root@ct66rac01 ~]# su - oracle
   [oracle@ct66rac01 ~]$ cd /home/oracle/xtts
   [oracle@ct66rac01 xtts]$ ORACLE_SID=rac11g1
   [oracle@ct66rac01 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct66rac01 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -c

8.##ct6604
##在源端模拟生成新增数据
   [oracle@ct6604 xtts]$ ORACLE_SID=ctdb
   [oracle@ct6604 xtts]$ sqlplus / as sysdba
   SQL> insert into test01.tb01 select * from test01.tb01;
   SQL> insert into test02.tb01 select * from test02.tb01;
   SQL> commit;
   SQL> exit

9.##ct6604
##在源端执行增量备份incremental
   [oracle@ct6604 xtts]$ ORACLE_SID=ctdb
   [oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

10.##ct66rac01
##在目标端应用增量roll forward
   #因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
   #应用增量roll forward是应用到转换后的数据文件上
   [oracle@ct66rac01 xtts]$ ORACLE_SID=rac11g1
   [oracle@ct66rac01 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct66rac01 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

11.##ct6604
##在源端模拟生成新增数据,并将要传输的表空间设置只读
   #此时才算开始计算停机时间
   [oracle@ct6604 xtts]$ ORACLE_SID=ctdb
   [oracle@ct6604 xtts]$ sqlplus / as sysdba

   SQL> insert into test01.tb01 select * from test01.tb01;
   SQL> insert into test02.tb01 select * from test02.tb01;
   SQL> commit;

   SQL> alter tablespace tbs01 read only;
   SQL> alter tablespace tbs02 read only;

   SQL> exit

12.##ct6604
##在源端执行最后一次增量备份incremental
   [oracle@ct6604 xtts]$ ORACLE_SID=ctdb
   [oracle@ct6604 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct6604 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

13.##ct66rac01
##在目标端应用最后一次增量roll forward
   #因为使用nfs,所以转换之前源端产生的文件就无需传过来,直接执行就可以
   [oracle@ct66rac01 ~]$ cd /home/oracle/xtts
   [oracle@ct66rac01 xtts]$ ORACLE_SID=rac11g1
   [oracle@ct66rac01 xtts]$ TMPDIR=/home/oracle/xtts/script
   [oracle@ct66rac01 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

14.##ct66rac01
##在目标端产生执行导入的脚本
   #因为之前没有设置dstdir,srclink参数,所以此处产生的导入脚本需要手动加上dblink和directory的名称
   [oracle@ct66rac01 xtts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e

15.##ct66rac01
##在目标端新建用户,导入传输表空间
   [oracle@ct66rac01 ~]$ ORACLE_SID=rac11g1
   [oracle@ct66rac01 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;
   SQL> exit

   [oracle@ct66rac01 ~]$ ORACLE_SID=rac11g1
   /home/oracle/xtts/script/xttplugin.txt
   [oracle@ct66rac01 ~]$ impdp directory=dump_oradata nologfile=y network_link=lnk_ctdb transport_full_check=no transport_tablespaces=TBS01,TBS02 transport_datafiles='+DATA/tbs01_5.xtf','+DATA/tbs02_6.xtf'

   Import: Release 11.2.0.4.0 - Production on Fri Jan 15 17:18:14 2016

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

   Username: system
   Password:

   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=dump_oradata nologfile=y network_link=lnk_ctdb transport_full_check=no transport_tablespaces=TBS01,TBS02 transport_datafiles=+DATA/tbs01_5.xtf,+DATA/tbs02_6.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 17:19:07 2016 elapsed 0 00:00:48

16.##ct66rac01
##在目标端验证导入的数据和权限和源端是否一致
   #此处发现源端给test02用户的execute on dbms_crypto权限没有导入,这是impdp原本的问题.所以在做xtts之前就要确定好这些权限的问题,以减少停机时间.
   [oracle@ct66rac01 xtts]$ sqlplus / as sysdba
   SQL> alter tablespace tbs01 read write;
   SQL> alter tablespace tbs02 read write;
   SQL> alter user test01 default tablespace tbs01;
   SQL> alter user test02 default tablespace tbs02;

   SQL> select count(1) from test01.tb01;
   /*
   COUNT(1)
   345732
   */

   SQL> select * from dba_tab_privs where grantee='TEST02';
   /*
   GRANTEE   OWNER   TABLE_NAME   GRANTOR   PRIVILEGE   GRANTABLE   HIERARCHY
   TEST02   TEST01   TB01   TEST01   SELECT   NO   NO
   */
   #select * from dba_tab_privs where owner ='SYS' and grantee='TEST02';
   SQL> grant execute on dbms_crypto to test02;
   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 413484.1).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-1978432/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1978432/