Oracle 数据迁移升级（XTTS + DBUA）

迁移升级背景介绍

测试环境：VMware® Workstation 15 Pro 虚拟机
操作系统：CentOS Linux release 7.6.1810 (Core)
系统内核：3.10.0-957.el7.x86_64
硬件架构：x86_64
软件版本：Oracle 11.2.0.4，Oracle 19.3.0.0

节点名称	处理器	内存	硬盘	IP地址（公网）	IP地址（私网）
orclrac1	2core	3GB	30GB	192.168.32.139	192.168.49.192
orclrac2	2core	3GB	30GB	192.168.32.140	192.168.49.193
orclrac3	4core	2GB	30GB	192.168.32.146	192.168.49.198
orclrac4	4core	2GB	30GB	192.168.32.147	192.168.49.199

源端：orclrac1, orclrac2
目标端：orclrac3, orclrac4

迁移阶段准备

在USERS表空间下创建了测试用户和测试数据

EODA@orclrac1> select object_type,status,count(*) from user_objects group by object_type,status;

检验自包含

SYS@orclrac1> select distinct tablespace_name from dba_segments where owner='EODA' order by 1;
SYS@orclrac1> execute dbms_tts.transport_set_check('USERS');
SYS@orclrac1> select * from TRANSPORT_SET_VIOLATIONS;
-- 上述查询若没有结果行返回，说明自包含校验通过

创建XTTS工作目录

-- 源端创建相关目录
[oracle@orclrac1 ~]$ mkdir -p /home/oracle/exp/newxx
[oracle@orclrac1 ~]$ mkdir -p /home/oracle/exp/src_backup
[oracle@orclrac1 ~]$ mkdir -p /home/oracle/exp/tmp
[oracle@orclrac1 ~]$ mkdir -p /home/oracle/exp/dump
[oracle@orclrac1 ~]$ mkdir -p /home/oracle/exp/backup_incre
[oracle@orclrac1 ~]$ chown -R oracle:dba /home/oracle/exp/newxx
[oracle@orclrac1 ~]$ cd /home/oracle/exp/newxx/
[oracle@orclrac1 newxx]$ rz

[oracle@orclrac1 newxx]$ unzip rman_xttconvert_v3.zip
Archive: rman_xttconvert_v3.zip
inflating: xtt.properties
inflating: xttcnvrtbkupdest.sql
inflating: xttdbopen.sql
inflating: xttdriver.pl
inflating: xttprep.tmpl
extracting: xttstartupnomount.sql

-- 目标端创建相关目录
[oracle@orclrac3 ~]$ mkdir -p /home/oracle/exp/newxx
[oracle@orclrac3 ~]$ mkdir -p /home/oracle/exp/src_backup
[oracle@orclrac3 ~]$ mkdir -p /home/oracle/exp/tmp
[oracle@orclrac3 ~]$ mkdir -p /home/oracle/exp/dump
[oracle@orclrac3 ~]$ mkdir -p /home/oracle/exp/backup_incre
[oracle@orclrac3 ~]$ chown -R oracle:dba /home/oracle/exp/newxx
[oracle@orclrac3 ~]$ cd /home/oracle/exp/newxx/
[oracle@orclrac3 newxx]$ rz

[oracle@orclrac3 newxx]$ unzip rman_xttconvert_v3.zip
Archive: rman_xttconvert_v3.zip
inflating: xtt.properties
inflating: xttcnvrtbkupdest.sql
inflating: xttdbopen.sql
inflating: xttdriver.pl
inflating: xttprep.tmpl
extracting: xttstartupnomount.sql

源端开启bct（block change tracking）

SYS@orclrac1> alter database enable block change tracking using file '+DATA/bct/bct2';

SYS@orclrac1> col FILENAME for a45
SYS@orclrac1> select * from v$block_change_tracking;

配置 xtt.properties

-- 源端配置
[oracle@orclrac1 newxx]$ pwd
/home/oracle/exp/newxx
[oracle@orclrac1 newxx]$ vim xtt.properties
-- 增加如下配置信息：

-- srclink需要注释否则会报错

-- 需要传输的表空间，多个表空间使用逗号分隔
tablespaces=USERS
-- 源库的platform_id, v$database中得到
platformid=13
-- 源端用于存放数据文件的copy，使用rman时使用
dfcopydir=/home/oracle/exp/src_backup
-- 源端用于存放增量备份的目录，无论哪种方式都需要设置
backupformat=/home/oracle/exp/backup_incre
-- 增量备份格式转换后的输出目录
backupondest=/home/oracle/exp/backup_incre
-- 目标端存放数据文件copy目录，和存放增量备份的目录
stageondest=/home/oracle/exp/src_backup
-- 数据文件的最终存放点
storageondest=+DATA/ORCLRAC
-- 默认为3
parallel=16
rollparallel=16
-- 默认8，使用rman时的并行设置
getfileparallel=6

-- 目标端配置
[oracle@orclrac3 newxx]$ pwd
/home/oracle/exp/newxx
[oracle@orclrac3 newxx]$ vim xtt.properties
-- 增加如下配置信息：

目标端提前建立用户角色

-- 目标端创建EODA用户，完成元数据导入后才可修改默认表空间。
以下是在源端执行获取创建用户和对应角色、权限的语句后，在目标端对应创建（如果你很清楚要迁移业务用户的用户密码和权限等信息，也可以选择直接创建）：
-- 源端执行
-- create user
[oracle@orclrac1 newxx]$ pwd
/home/oracle/exp/newxx
[oracle@orclrac1 newxx]$ sqlplus -S / as sysdba
set pages 0
set feedback off
spool /home/oracle/exp/newxx/create_user.sql
select 'create user '||name||' identified by values '''||password||''';' from user$ where name = 'EODA' and type#=1;
create user EODA identified by values 'D289D40C00A789DC';
spool off
exit
[oracle@orclrac1 newxx]$ cat create_user.sql
create user EODA identified by values 'D289D40C00A789DC';

-- create role
[oracle@orclrac1 newxx]$ sqlplus -S / as sysdba
set pages 0
set feedback off
spool /home/oracle/exp/newxx/create_role.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee = 'EODA';
grant DBA to EODA;
spool off
exit
[oracle@orclrac1 newxx]$ cat create_role.sql
grant DBA to EODA;

-- owner为sys的表的权限需要手动赋予
[oracle@orclrac1 newxx]$ sqlplus -S / as sysdba
set pages 0
set feedback off
spool /home/oracle/exp/newxx/grant_sys_privs.sql
select 'grant '||PRIVILEGE||' on '||owner||'.'||table_name||' to '||GRANTEE||';' from dba_tab_privs where owner='SYS' and GRANTEE = 'EODA';
grant SELECT on SYS.V_$TIMER to EODA;
grant SELECT on SYS.V_$STATNAME to EODA;
grant SELECT on SYS.V_$MYSTAT to EODA;
grant SELECT on SYS.V_$LATCH to EODA;
grant EXECUTE on SYS.DBMS_STATS to EODA;
spool off
exit
[oracle@orclrac1 newxx]$ cat grant_sys_privs.sql
grant SELECT on SYS.V_$TIMER to EODA;
grant SELECT on SYS.V_$STATNAME to EODA;
grant SELECT on SYS.V_$MYSTAT to EODA;
grant SELECT on SYS.V_$LATCH to EODA;
grant EXECUTE on SYS.DBMS_STATS to EODA;

[oracle@orclrac1 newxx]$ scp create_user.sql create_role.sql grant_sys_privs.sql 192.168.32.146:/home/oracle/exp/newxx/
-- 目标端执行

表空间全量备份

增加rman备份并行度

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 16 BACKUP TYPE TO BACKUPSET;

创建备份方式

-- 编辑备份文件，每次备份失败会在/exp/newxx/tmp产生fails文件需要删除后方可再次运行
[oracle@orclrac1 newxx]$ pwd
/home/oracle/exp/newxx
[oracle@orclrac1 newxx]$ vim full_backup.sh
[oracle@orclrac1 newxx]$ cat full_backup.sh
export ORACLE_SID=orclrac1
export TMPDIR=/home/oracle/exp/tmp
export PERL5LIB=/u01/app/oracle/product/11.2.0/db_1/perl/lib
/u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /home/oracle/exp/newxx/xttdriver.pl -p -d
-- -p使用rman备份，-d采用debug

全量备份

[oracle@orclrac1 newxx]$ nohup sh full_backup.sh > full_backup.log &
-- 备份完毕以后，生成如下文件

表空间全量恢复和转换

-- 将备份文件传输至目标端
[oracle@orclrac1 newxx]$ cd /home/oracle/exp/src_backup/
[oracle@orclrac1 src_backup]$ scp USERS_4.tf 192.168.32.146:/home/oracle/exp/src_backup/
[oracle@orclrac1 src_backup]$ cd /home/oracle/exp/tmp/
[oracle@orclrac1 tmp]$ scp * 192.168.32.146:/home/oracle/exp/tmp/

-- 目标端执行表空间恢复并将数据文件转换至ASM磁盘组中，每次恢复失败时会在/home/oracle/exp/tmp产生fails文件需要删除后方可再次运行
[oracle@orclrac3 newxx]$ cd /home/oracle/exp/newxx/
[oracle@orclrac3 newxx]$ vim full_restore.sh
[oracle@orclrac3 newxx]$ cat full_restore.sh
export TMPDIR=/home/oracle/exp/tmp
export ORACLE_SID=orclrac1
/u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /home/oracle/exp/newxx/xttdriver.pl -c -d

表空间增量备份

源端进行增量备份

[oracle@orclrac1 tmp]$ cd /home/oracle/exp/newxx/
[oracle@orclrac1 newxx]$ vim incre_backup.sh
[oracle@orclrac1 newxx]$ cat incre_backup.sh
export ORACLE_SID=orclrac1
export TMPDIR=/home/oracle/exp/tmp
export PERL5LIB=/u01/app/oracle/product/11.2.0/db_1/perl/lib
/u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /home/oracle/exp/newxx/xttdriver.pl -i -d

-- 增量备份
[oracle@orclrac1 newxx]$ nohup sh incre_backup.sh > incre_backup.log &

-- 做一个测试增量备份有效的测试表
EODA@orclrac1> create table xttstest tablespace USERS as select * from dba_objects;

将增量文件传输至目标端

[oracle@orclrac1 newxx]$ cd /home/oracle/exp/backup_incre/
[oracle@orclrac1 backup_incre]$ scp 0306r8t5_1_1 192.168.32.146:/home/oracle/exp/backup_incre/
[oracle@orclrac1 backup_incre]$ cd /home/oracle/exp/tmp/
[oracle@orclrac1 tmp]$ rsync -aP * 192.168.32.146:/home/oracle/exp/tmp/

表空间增量恢复

目标端进行增量恢复

[oracle@orclrac3 newxx]$ cd /home/oracle/exp/newxx/
[oracle@orclrac3 newxx]$ vim incre_recover.sh
[oracle@orclrac3 newxx]$ cat incre_recover.sh
export TMPDIR=/home/oracle/exp/tmp
export ORACLE_SID=orclrac1
/u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /home/oracle/exp/newxx/xttdriver.pl -r -d

-- xttplan.txt需要进行文件名的修改，用最新的scn号

[oracle@orclrac1 newxx]$ scp ../backup_incre/0506rahn_1_1 192.168.32.146:/home/oracle/exp/backup_incre/
[oracle@orclrac1 newxx]$ scp ../tmp/* 192.168.32.146:/home/oracle/exp/tmp/

增量恢复

[oracle@orclrac3 newxx]$ nohup sh incre_recover.sh > incre_recover.log &

特别说明

-- 以上增量前滚的步骤在正式迁移前可重复执行多次，用于对目标库进行多次表空间增量恢复，使目标端数据库在正式迁移前与生产数据库近乎一致，大幅减少迁移停机时间。

-- 每次备份(全量和增量)成功后，源端/home/oracle/exp/tmp目录中会生成文件，需要将此目录下的所有文件传输到目标端的/home/oracle/exp/tmp下（每次覆盖即可）

-- 每次备份(全量和增量)后，/home/oracle/exp/tmp目录中会生成最新的xttplan.txt.new文件，该文件中记录了各表空间最新的scn，需要将旧的xttplan.txt文件在每次增量恢复前linux端进行如下改名操作：
cd /exp/newxx/tmp
mv xttplan.txt xttplan.old1.txt
mv xttplan.txt.new xttplan.txt

正式迁移阶段

表空间read-only

-- 应用暂停业务以后，确认数据库层面没有用户会话连接，源端将被传输表空间设置为read-only状态
[oracle@orclrac1 newxx]$ sqlplus -S / as sysdba
set pages 0
set feedback off
spool /home/oracle/exp/newxx/read_only.sql
select 'alter tablespace '||name||' read only;' from v$tablespace where name in ('USERS') order by 1;
alter tablespace USERS read only;
spool off
exit
[oracle@orclrac1 newxx]$ cat read_only.sql
alter tablespace USERS read only;
SYS@orclrac1> @/home/oracle/exp/newxx/read_only.sql

最后一次增量操作

按照前面 增量前滚阶段的方法，完成最后一次增量备份与恢复。

目标端开启闪回

-- 目标端在导入元数据前开启闪回

导入XTTS元数据

源端导出XTTS元数据

SYS@orclrac1> create directory dump as '/home/oracle/exp/dump';
[oracle@orclrac1 newxx]$ pwd
/home/oracle/exp/newxx
-- 导出表空间元数据
[oracle@orclrac1 newxx]$ vim expdp_xtts.sh
[oracle@orclrac1 newxx]$ cat expdp_xtts.sh
expdp system/oracle parfile=expdp_xtts.par
[oracle@orclrac1 newxx]$ vim expdp_xtts.par
[oracle@orclrac1 newxx]$ cat expdp_xtts.par
directory=dump
dumpfile=tbs_xtts.dmp
logfile=expdp_xtts.log
transport_tablespaces=('USERS')
transport_full_check=y
metrics=yes
-- 导出用户元数据
[oracle@orclrac1 newxx]$ vim expdp_xtts_other.sh
[oracle@orclrac1 newxx]$ cat expdp_xtts_other.sh
expdp system/oracle parfile=expdp_xtts_other.par
[oracle@orclrac1 newxx]$ vim expdp_xtts_other.par
[oracle@orclrac1 newxx]$ cat expdp_xtts_other.par
directory=dump
dumpfile=tbs_xtts_other.dmp
logfile=expdp_xtts_other.log
content=metadata_only
schemas=EODA
metrics=yes

执行导出表空间、用户元数据的脚本

[oracle@orclrac1 newxx]$ sh expdp_xtts.sh
[oracle@orclrac1 newxx]$ sh expdp_xtts_other.sh
[oracle@orclrac1 newxx]$ scp ../dump/*.dmp 192.168.32.146:/home/oracle/exp/dump/

目标端导入XTTS元数据

SQL> create or replace directory dump as '/home/oracle/exp/dump';
-- 导入XTTS元数据
[oracle@orclrac3 newxx]$ vim impdp_xtts.sh
[oracle@orclrac3 newxx]$ cat impdp_xtts.sh
impdp system/oracle parfile=impdp_xtts.par
[oracle@orclrac3 newxx]$ vim impdp_xtts.par
[oracle@orclrac3 newxx]$ cat impdp_xtts.par
directory=dump
logfile=impdp_xtts.log
dumpfile=tbs_xtts.dmp
cluster=n
metrics=yes
transport_datafiles=+DATA/ORCLRAC/users_4.dbf

-- 执行导入XTTS元数据的脚本