oracle 19c xtts迁移

一、环境检查

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;

数据迁移成功

本文参考Oracle XTTS数据迁移_oracle xtts迁移-CSDN博客