简介
这两天一直在研究xtts(跨平台表空间迁移的方法),因为一是网上的资源不是很多,二是个人感觉这是个很实用的迁移技巧。下面就和大家来分享一下我个人做的测试过程吧。
1、搭建测试环境
跨平台表空间迁移过程 (参考文档官方1389592.1)
--过程主要分为四个部分
1、初始化阶段
2、准备阶段
3、增量备份前滚阶段
4、运输或叫迁移阶段
Phase 1 - 初始化安装
Step 1.1 - 在目标端安装数据库软件,并且创建target数据库。强烈建议使用11.2.0.4或之后的版本
Step 1.2 - 如果需要可以配置一个增量转换home和实例
a、如果软件是11.2.0.4可以忽略这步
b、如果数据库软件时11.2.0.3或是更低,你必须安装一个新的11.2.0.4的数据库软件home,作为增量转换home路径,并且仅启动一个11.2.0.4的实例到nomount状态(数据库可以不用创建)
--为了简化难度,我的测试环境都用的是64位linux,并且数据库版本也是11.2.0.4.0
(大家只注意操作步奏就好),就只要在目标端建好实例就行了。
Step 1.3 -选取要传送的表空间,测试源端用的TEST表空间
--测试里我分别搭建源和目标数据库起名为source、target
source:数据库实例tx9ab,创建表空间test;
创建用户tx9ab,dba权限默认tablespace为test;
创建测试用表 xttstest,并插入一条数据。
target:创建数据库实例tx9ab,并且迁移过程中实例保持open;
创建用户tx9ab,dba权限。
SQL> create tablespace test datafile '/space/oradata/tx9ab/test01.dbf' size 10m aotuextend on;
SQL> create user tx9ab identified by tx9ab default tablespace test;
User created.
SQL> grant connect,resource,select any table to tx9ab;
Grant succeeded.
SQL> conn tx9ab/tx9ab
Connected.
SQL> create table xttstest(a number);
Table created.
SQL> select * from xttstest;
A
----------
100
SQL> select username,default_tablespace from dba_users where username='TX9AB'; --查看用户默认的表空间;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TX9AB TEST
--查看各个系统平台信息
SQL> col platform_name for a32
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
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
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
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64 Little
)
21 Apple Mac OS (x86-64) Little
20 rows selected.
Step 1.4 -使用RMAN backup(略)
Step 1.5-创建一个转换过程的路径
-在源和目标系统中创建数据转换路径,相关参数定义在xtt.properties文件中:backupformat, backupondest。如果使用RMAN backups则还要设置
dfcopydir, stageondest。
Step 1.6 -在源端安装xttconvert脚本
-下载并解压rman-xttconvert_2.0.zip
测试解压/space/sys_software/oracle/scripts/xtts_scripts下
Step 1.7 -在源端配置xtt.properties
[oracle@tx9ab xtts_scripts]$ more xtt.properties
tablespaces=TEST
platformid=13
dfcopydir=/space/sys_software/oracle/xtts_dir/dfcopydir
backupformat=/space/sys_software/oracle/xtts_dir/backup
stageondest=/space/oradata/tx9ab
storageondest=/space/oradata/tx9ab/test
backupondest=/space/sys_software/oracle/xtts_dir/backup
Step 1.8 -将xttconvert脚本拷贝到目标端的相同位置下
--这步省了
Step 1.9 - Set TMPDIR
-告诉系统xttconvert脚本所在的位置
[oracle@single01 ~]$ export TMPDIR=/space/sys_software/oracle/scripts/xtts_scripts
Phase 2 - Prepare Phase
Phase 2A - Prepare Phase for dbms_file_transfer Method
Phase 2B - Prepare Phase for RMAN Backup Method
Step 2B.1 - 源端调用xttdriver.pl做迁移准备
[oracle@single01 xtts_scripts]$ $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:
'TEST' /space/oradata/tx9ab
xttpreparesrc.sql for 'TEST' started at Thu Sep 17 13:54:21 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:22 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:30 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:31 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:31 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:31 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:32 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:32 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:32 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:32 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:33 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:33 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:33 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:33 2015
Prepare source for Tablespaces:
'' /space/oradata/tx9ab
xttpreparesrc.sql for '' started at Thu Sep 17 13:54:34 2015
xttpreparesrc.sql for ended at Thu Sep 17 13:54:34 2015
--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------
在源端该准备脚本做这样几件事:
1、创建要钱仪表空间中的数据文件的拷贝并将它们搬到 xtt.properties文件中定义的dfcopydir路径下。
2、验证表空间是否在线, 是否为读写模式, 并且不包含下线的数据文件。
3、在TMPDIR下还会生成以下文件:
a、xttplan.txt
b、rmanconvert.cmd
Step 2B.2 -将源端的数据文件,传到目标端
拷贝source:/space/sys_software/oracle/xtts_dir/dfcopydir/TEST_6.tf ---> target: /space/oradata/tx9ab
Step 2B.3 -在目标端对来自源端的数据文件拷贝进行转换
拷贝source:/space/sys_software/oracle/scripts/xtts_scripts/rmanconvert.cmd---> target:/space/sys_software/oracle/scripts/xtts_scripts/rmanconvert.cmd
[oracle@tx9ab xtts_scripts]$ export TMPDIR=/space/sys_software/oracle/scripts/xtts_scripts
[oracle@tx9ab xtts_scripts]$ /space/sys_software/oracle/app/product/11.2.0/db_1/perl/bin/perl xttdriver.pl -c
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Performing convert
--------------------------------------------------------------------
--------------------------------------------------------------------
Converted datafiles listed in: /space/sys_software/oracle/scripts/xtts_scripts/xttnewdatafiles.txt
--------------------------------------------------------------------
-转换后的数据文件拷贝会出现在 xtt.properties文件中定义的参数storageondest下。这时在目标的/space/oradata/tx9ab/test/下会生成一个TEST_6.xtf经转换后的数据文件拷贝。
Phase 3 - Roll Forward Phase
在源端创造增量数据、做增量备份,然后传到目标端的, 在目标端对传过来的增量备份进行格式转换后,将增量数据应用到数据文件备份上。这个过程是可以多次重复的,这样备库上的数据文件拷贝,通过一次次应用增量数据就可以逐渐追上源库的生产数据。
创建增量数据
SQL> update xttstest set a=200;
SQL> commit;
SQL> select * from xttstest;
A
----------
200
Step 3.1 -源端对表空间进行增量备份
这里必须注意:在最后一次对源库进行增量备份以前,要把源库要迁移的表空间设为只读,不然以后目标端导入表空间元组时一定会报错的!!!
SQL> show user
USER is "SYS"
SQL> alter tablespace test read only;
Tablespace altered.
[oracle@single01 xtts_scripts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Backup incremental
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'TEST'
Prepare newscn for Tablespaces: ''
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 /space/sys_software/oracle/scripts/xtts_scripts/rmanincr.cmd
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 17 15:47:40 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TX9AB (DBID=390378578)
RMAN> set nocfau;
2> host 'echo ts::TEST';
3> backup incremental from scn 1120971
4> tag tts_incr_update tablespace 'TEST' format
5> '/space/sys_software/oracle/xtts_dir/backup/%U';
6>
executing command: SET NOCFAU
using target database control file instead of recovery catalog
ts::TEST
host command complete
Starting backup at 17-SEP-2015 15:47:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=43 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=44 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=/space/oradata/tx9ab/test01.dbf
channel ORA_DISK_1: starting piece 1 at 17-SEP-2015 15:47:42
channel ORA_DISK_1: finished piece 1 at 17-SEP-2015 15:47:43
piece handle=/space/sys_software/oracle/xtts_dir/backup/0fqhd10u_1_1 tag=TTS_INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-SEP-2015 15:47:43
Recovery Manager complete.
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
上面的操作还会在TMPDIR目录下产生以下的文件
1、tsbkupmap.txt
2、incrbackups.txt
[oracle@single01 xtts_scripts]$ ls -trl
total 172
-rwxrwxr-x 1 oracle oinstall 52 May 22 08:30 xttstartupnomount.sql
-rwxrwxr-x 1 oracle oinstall 11549 May 22 08:30 xttprep.tmpl
-rwxrwxr-x 1 oracle oinstall 91722 May 22 08:30 xttdriver.pl
-rwxrwxr-x 1 oracle oinstall 71 May 22 08:30 xttdbopen.sql
-rwxrwxr-x 1 oracle oinstall 1390 May 22 08:30 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 354 Sep 17 11:01 xtt.properties
-rw-r--r-- 1 oracle oinstall 18 Sep 17 13:54 xttplan.txt
-rw-r--r-- 1 oracle oinstall 181 Sep 17 13:54 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 11657 Sep 17 13:54 xttpreparesrc.sql
-rw-r--r-- 1 oracle oinstall 20 Sep 17 15:47 xttplan.txt.new
-rw-r--r-- 1 oracle oinstall 0 Sep 17 15:47 xttprepare.cmd
-rw-r--r-- 1 oracle oinstall 11592 Sep 17 15:47 xttdetnewfromscnsrc.sql
-rw-r--r-- 1 oracle oinstall 169 Sep 17 15:47 rmanincr.cmd
-rw-r--r-- 1 oracle oinstall 25 Sep 17 15:47 tsbkupmap.txt
-rw-r--r-- 1 oracle oinstall 56 Sep 17 15:47 incrbackups.txt
Step 3.2 -将增量备份和新生文件上传至目标端(略)
Step 3.3 -在源端对刚传过来的增量备份进行转换和应用
[oracle@tx9ab xtts_scripts]$ perl xttdriver.pl -r
################################## 前面报的错 ##################################
ERROR IN CONVERSION ORA-19624: operation failed, retry possible
ORA-19505:
failed to identify file "/space/oradata/tx9ab/0gqhs56u_1_1"
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional
information: 3
ORA-19600: input file is backup piece
(/space/oradata/tx9ab/0gqhs56u_1_1)
ORA-19601: output file is backup piece
(/space/sys_software/oracle/xtts_dir/backup/xib_0gqhs56u_1_1_6)
CONVERTED BACKUP
PIECE/space/sys_software/oracle/xtts_dir/backup/xib_0gqhs56u_1_1_6
PL/SQL procedure successfully completed.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Error:
------
/space/sys_software/oracle/scripts/xtts_scripts/xxttconv_0gqhs56u_1_1_6.sql execution failed
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--发现是路径的问题!!!!!!!!!
[oracle@tx9ab xtts_scripts]$ cp /space/sys_software/oracle/xtts_dir/backup/0gqhs56u_1_1 /space/oradata/tx9ab/0gqhs56u_1_1
--修改备份的路径再次尝试
[oracle@tx9ab xtts_scripts]$ perl xttdriver.pl -r
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: backupondest
Values: /space/sys_software/oracle/xtts_dir/backup
Key: platformid
Values: 13
Key: backupformat
Values: /space/sys_software/oracle/xtts_dir/backup
Key: storageondest
Values: /space/oradata/tx9ab/test
Key: dfcopydir
Values: /space/sys_software/oracle/xtts_dir/dfcopydir
Key: cnvinst_home
Values: /space/sys_software/oracle/app/product/11.2.0/db_1
Key: cnvinst_sid
Values: tx9ab
Key: stageondest
Values: /space/oradata/tx9ab
Key: tablespaces
Values: TEST
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
ARGUMENT stageondest
ARGUMENT backupondest
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : tx9ab
ORACLE_HOME : /space/sys_software/oracle/app/product/11.2.0/db_1
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
convert instance: /space/sys_software/oracle/app/product/11.2.0/db_1
convert instance: tx9ab
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
rdfno 6
BEFORE ROLLPLAN
datafile number : 6
datafile name : /space/oradata/tx9ab/test/TEST_6.xtf
AFTER ROLLPLAN
CONVERTED BACKUP
PIECE/space/sys_software/oracle/xtts_dir/backup/xib_0gqhs56u_1_1_6
PL/SQL procedure successfully completed.
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
Phase 4 - Transport Phase
-源端导出要迁移的表空间的Metadata
exp \'/ as sysdba\' tablespaces=test transport_tablespace=y file=/space/sys_software/oracle/xtts_dir/backup/test_xtts.dmp
由于,我前面有且只做了一次增量数据的备份和前滚应用,这里在目标库直接导入表空间的Metadata就可以完成xtts(虽然我这里的环境是一样的
)
--在目标导入迁移的表空间的Metadata
[oracle@tx9ab xtts_scripts]$ imp \'/ as sysdba\' tablespaces=test transport_tablespace=y file=/space/sys_software/oracle/scripts/xtts_scripts/test_xtts.dmp datafiles=/space/oradata/tx9ab/test/TEST_6.xtf
Import: Release 11.2.0.4.0 - Production on Wed Sep 23 23:07:00 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TX9AB's objects into TX9AB
. . importing table "XTTSTEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
--查看源端、目标端的数据一致性
[oracle@tx9ab dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 23 23:07:38 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, OLAP, Data Mining and Real Application Testing options
SQL> select * from tx9ab.XTTSTEST;
A
----------
200
--End