一、环境检查
1.检查数据库时区
select dbtimezone from dual;
检查一致
2.检查数据库时间
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
检查一致
3.检查两边字符集
set linesize 120
select * from nls_database_parameters where parameter like '%CHARACTERSET%';
检查一致
4.组件安装情况
select comp_name from dba_registry
检查一致
5.检查是否使用了 Key Compression 的索引组织表
select index_name,table_name from dba_indexes where compression='ENABLED';
6.检查sys和system用户在业务表空间上是否创建对象
select table_name, owner, tablespace_name from dba_tables where tablespace_name not in('SYSTEM','SYSAUX') and owner in ('SYS','SYSTEM');
如果数据库用户 SYS、SYSTEM 在业务表空间上创建有对象,则这些对象不能通过 XTTS 迁移,需要在目标库手工创建。
7.检查源端 Compatible 参数
show parameter compatible;
目标端的版本也要是这个
8.确认recycle in 功能是关闭,并手工清空回收站
show parameter recyclebin;
alter system set recyclebin='off' scope=spfile;
9.数据文件状态检查
select distinct status from v$datafile;
正常返回应为:ONLINE、SYSTEM
10.查看v$transportable_platform检查平台信息
col PLATFORM_NAME format a50
select * from v$transportable_platform;
二、源端创建测试用数据
1.创建表空间和用户
create tablespace test02 datafile '/u01/app/oradata/ORACLE/test02.dbf' size 10m autoextend on next 1m;
create user test02 identified by 123 default tablespace test02;
grant resource,connect,dba to test02;
2.添加测试用数据
create table test02.tab1(marchantid number(10) primary key,marchantname varchar2(20) not null,email varchar2(30));
insert into test02.tab1 values(0000000001,'Java','zs@163.com');
insert into test02.tab1 values(0000000002,'Oracle','ls@163.com');
insert into test02.tab1 values(0000000003,'C++','ww@163.com');
insert into test02.tab1 values(0000000004,'Python','zl@163.com');
insert into test02.tab1 values(0000000005,'PHP','zy@163.com');
insert into test02.tab1 values(0000000006,'IOS','ht@163.com');
insert into test02.tab1 values(0000000007,'Sun','yg@163.com');
三、实施迁移
1.创建源端和目标端用于存放备份数据文件夹
mkdir xtts
cd xtts/
mkdir source_back
mkdir source_incr_back
mkdir target_data
mkdir target_back
mkdir target_incr_back
将rman_xttconvert_v3上传至该目录
2.解压rman_xttconvert_v3工具
unzip rman_xttconvert_v3.zip
ls
3.给该路径下的文件授权
chmod -R 775 /home/oracle/xtts
4.编辑和配置xtt.properties文件
tablespaces= {需要传输的表空间}
platformid= {源端平台号,select * from v$transportable_platform order by platform_id 语句查询得到)}
srcdir= {当使用dbms_file_transfer时使用,表示源库存放数据文件的路径(数据库里的dir,不是os上的 dir)}
dstdir= {当使用dbms_file_transfer时使用,表示目标库存放数据文件的路径(数据库里的dir,不是os上的dir)}
srclink= {从目标端指向源端的dblink,当使用dbms_file_transfer时使用}
dfcopydir= {源端datafile convert保存路径}
backupformat= {源端增量备份保存路径}
stageondest= {目标端备份文件,建议和backupondest设置为同一路径}
backupondest= {目标端备份文件保存路径,建议和stageondest设置为同一路径}
storageondest= {目标端恢复到数据库的数据文件保存位置,如果是diskgroup则需要注意还需要对应的数据库名}
cnvinst_home= {新环境$ORACLE_HOME}
cnvinst_sid= {新环境ORACLE_SID}
asm_home= {如果是直接恢复到diskgroup,则需要指定对应的grid_home和ASM实例}
asm_sid= {如果是直接恢复到diskgroup,则需要指定对应的grid_home和ASM实例}
parallel= {设置并行,建议设置为6或者8,根据CPU情况设置}
rollparallel= {roll forward 并行,如果数据库大的话建议设置为8}
getfileparallel= {getfile 并行度,建议设置为6或者8,根据CPU情况设置}
desttmpdir= {设置目标端tempdir目录,建议指向xtts目录)}
## Specify tablespace names in CAPITAL letters.
tablespaces=TEST02 # 表示需要迁移的表空间
## Source database platform ID
## ===========================
##
## platformid
## ----------
## Source database platform id, obtained from V$DATABASE.PLATFORM_ID
platformid=13 # 表示源OS平台ID
......
## dbms_file_transfer.
#srclink=TTSLINK #表示从目标库指向源库的db_link,由于这里采用的是rman方式,所以不需要db_link
......
## as the stageondest property for the destination system.
dfcopydir=/home/oracle/xtts/source_back #表示源库备份文件存放目录
## backupformat
## ------------
## Location where incremental backups are created.
##
## This location may be an NFS-mounted filesystem that is shared with the
## destination system, in which case it should reference the same NFS location
## as the stageondest property for the destination system.
backupformat=/home/oracle/xtts/source_incr_back #表示源库增备文件存放目录
......
## as the dfcopydir and backupformat properties for the source system.
stageondest=/home/oracle/xtts/target_back #表示目标库备份文件存放目录
## storageondest
## -------------
## This parameter is used only when Prepare phase method is RMAN backup.
##
## Location where the converted datafile copies will be written during the
## "-c conversion of datafiles" step. This is the final location of the
## datafiles where they will be used by the destination database.
storageondest=/home/oracle/xtts/target_data #表示目标库正式文件存放目录
## backupondest
## ------------
## Location where converted incremental backups on the destination system
## will be written during the "-r roll forward datafiles" step.
##
## NOTE: If this is set to an ASM location then define properties
## asm_home and asm_sid below. If this is set to a file system
## location, then comment out asm_home and asm_sid below
backupondest=/home/oracle/xtts/target_incr_back #表示目标库增备文件存放目录
......
## If undefined, default value is 8.
parallel=3 #表示备份转化并行度
## rollparallel
## ------------
## Defines the level of parallelism for the -r roll forward operation.
##
## If undefined, default value is 0 (serial roll forward).
rollparallel=2 #表示增备的并行度
## getfileparallel
## ---------------
## Defines the level of parallelism for the -G operation
##
## If undefined, default value is 1. Max value supported is 8.
## This will be enhanced in the future to support more than 8
## depending on the destination system resources.
getfileparallel=4 #表示使用 dbms_file_transfer 方式的并行度
5.、将源端xtts目录拷贝到目标端
scp -rp /home/oracle/xtts/ 192.168.232.114:/home/oracle/
6.设置源端和目标端的TMPDIR环境变量
vi ~/.bash_profile
export TMPDIR=/home/oracle/xtts
7.源端执行如下命令开始初始备份
$ORACLE_HOME/perl/bin/perl /home/oracle/xtts/xttdriver.pl -p
8.源端查看备份文件,并将备份文件传到目标端
scp -p /home/oracle/xtts/source_back/TEST02_5.tf oracle@192.168.232.114:/home/oracle/xtts/target_back
9.将源端生成的rmanconvert.cmd文件传到目标端,并执行文件转换命令
scp rmanconvert.cmd 192.168.232.114:/home/oracle/xtts
目标端执行:
$ORACLE_HOME/perl/bin/perl /home/oracle/xtts/xttdriver.pl -c
将生成的xttnewdatafiles.txt传给源端
scp xttnewdatafiles.txt 192.168.232.113:/home/oracle/xtts
10.开始增量同步(由于这里只是测试,所以直接将源库表空间离线然后做最后一次增量同步)
alter tablespace test02 read only;
源端做最后一次增量备份
$ORACLE_HOME/perl/bin/perl /home/oracle/xtts/xttdriver.pl -i
11.将最后的增备文件传到目标端
scp -p /home/oracle/xtts/source_incr_back/02tu4ui5_1_1 oracle@192.168.127.71:/home/oracle/xtts/target_back
scp -p xttplan.txt tsbkupmap.txt 192.168.232.114:/home/oracle/xtts
12.目标端应用增备
$ORACLE_HOME/perl/bin/perl /home/oracle/xtts/xttdriver.pl -r
13.源端导出数据
mkdir -p /home/oracle/data
create directory source_dbdata as '/home/oracle/data';
grant read,write on directory source_dbdata to system;
# 源端导出元数据
expdp system/oracle directory=source_dbdata dumpfile=test02.dmp transport_tablespaces=test02 transport_full_check=y
14.将导出的数据传给目标端
scp -p /home/oracle/data/test02.dmp oracle@192.168.232.114:/home/oracle/data
15.目标端导入元数据
创建test02用户,但是不要创建表空间
create user test02 identified by 123;
grant dba to test02;
impdp test02/123 DUMPFILE=test02.dmp DIRECTORY=source_dbdata TRANSPORT_DATAFILES=/home/oracle/xtts/target_back/TEST02_5.dbf
16.目标端更改test02默认表空间
alter user test02 default tablespace TEST02;
17.检查数据
select * from test02.tab1;
数据迁移成功