XTTS迁移的全过程
需要根据实际情况更改，主要包括以下几个流程：
检查数据库用户自包含
准备 xtts 前期环境
进行全量备份并传输备份集到目标端
目标端进行全量恢复
源端目标端多次增量恢复
表空间 readonly 进行最后一次增量
元数据导入导出
检查对象
| 源端 | 目标端 | |
|---|---|---|
| Oracle 版本 | 10.2.0.3 | 11.2.0.4 |
| 操作系统 | AIX 5.3 | RHEL 6.7 |
| 是否 RAC | 否 | 是 |
| 数据库名称 | oraold | oranew |
1 迁移实施方案
1.1 迁移主要步骤
1.2 备份前准备
1.2.1 校验自包含
本次只迁移 TESTUSER 用户，只检查 TESTUSER 用户所在表空间的自包含验证即可，无需额外操作。
SQL> select distinct tablespace_name from dba_segments where owner='TESTUSER';
TABLESPACE_NAME
TESTUSER_DAT_4
TESTUSER_DAT_1
TESTUSER_DAT_2
TESTUSER_IDX_2
TESTUSER_DAT_3
TESTUSER_IDX_3
TESTUSER_ALL
TESTUSER_IDX_1
TESTUSER_IDX_4
9 rows selected.
SQL> execute dbms_tts.transport_set_check ('TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4' ,true,true);
PL/SQL procedure successfully completed.
SQL> select * from TRANSPORT_SET_VIOLATIONS;
no rows selected
1.2.2 创建 xtts 所需目录
源端和目标端创建 相关 目录
mkdir -p /exp/xtts
mkdir -p /exp/xtts/src_backup
mkdir -p /exp/xtts/tmp
mkdir -p /exp/xtts/dump
mkdir -p /exp/xtts/backup_incre
chown -R ora103:dba /exp/xtts
源端AIX上传rman-xttconvert_2.0.zip至/exp/xtts
cd /exp/xtts
unzip rman-xttconvert_2.0.zip
目标端上传rman-xttconvert_2.0.zip至/exp/xtts
cd /exp/xtts
unzip rman-xttconvert_2.0.zip
目标端创建其他相关目录
mkdir -p /exp/xtts
mkdir -p /exp/xtts/src_backup
mkdir -p /exp/xtts/tmp
mkdir -p /exp/xtts/dump
mkdir -p /exp/xtts/backup_incre
chown -R ora11g:dba /exp/xtts
1.2.3 源端开启 tracking
SQL> alter database enable block change tracking using file '/exp/xtts/bct';
Database altered.
SQL> select * from v$ block_change_tracking ;
STATUS FILENAME BYTES
ENABLED /exp/xtts/bct 11599872
关闭tracking可以使用如下命令完成： alter database disable block change tracking;
1.2.4 配置 xtt.properties
配置源端AIX xtt.properties属性文件
cd /exp/xtts
vi xtt.properties
#增加如下配置信息：
tablespaces=TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4
platformid=6
dfcopydir=/exp/xtts/src_backup
backupformat=/exp/xtts/backup_incre
backupondest=/exp/xtts/backup_incre
stageondest=/exp/xtts/src_backup
storageondest=+DG_DATA/oranew/datafile
parallel=16
rollparallel=16
getfileparallel=6
配置目标端Linux xtt.properties属性文件
cd /exp/xtts
vi xtt.properties
#增加如下配置信息：
tablespaces=TESTUSER_DAT_4,TESTUSER_DAT_1,TESTUSER_DAT_2,TESTUSER_IDX_2,TESTUSER_DAT_3,TESTUSER_IDX_3,TESTUSER_ALL,TESTUSER_IDX_1,TESTUSER_IDX_4
platformid=6
dfcopydir=/exp/xtts/src_backup
backupformat=/exp/xtts/backup_incre
backupondest=/exp/xtts/backup_incre
stageondest=/exp/xtts/backup_incre
storageondest=+DG_DATA/oranew/datafile
parallel=16
rollparallel=16
getfileparallel=6
asm_home=/opt/app/11.2.0/grid
asm_sid=+ASM1
1.2.5 目标端提前建立用户角色
以下命令为创建用户命令参考，也可以使用 plsql 等工具生成类似相关信息。用户和角色必须手工创建。最好使用 plsql 拿出所需用户的 ddl 以免漏掉权限。
创建 EBPF 用户，完成元数据导入后才可修改默认表空间
SQL> select name ,PASSWORD from user$ where name='TESTUSER';
NAME PASSWORD
TESTUSER C987AC3B738BCF43
create user TESTUSER identified by values 'C987AC3B738BCF43';
grant connect to TESTUSER;
grant resource to TESTUSER;
grant alter session to TESTUSER;
grant create session to TESTUSER;
grant select any dictionary to TESTUSER;
grant unlimit tablespace to TESTUSER;
创建角色
create role R_SELTESTUSER;
create role R_UPDTESTUSER;
赋予角色相应操作权限
1.3 全量备份及全量恢复
1.3.1 表空间全量备份
源端AIX执行被传输业务表空间全量备份创建xtts表空间全量备份脚本执行过程中产生的配置文件，用于数据文件转换及每次增量备份及恢复,同时每次执行增量备份过程中，配置文件内容会发生变化，用于新的增量恢复，主要是SCN的变化。增加rman备份并行度
rman target /
show all;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
cd /exp/xtts
full_backup.sh脚本内容如下
export ORACLE_SID=TESTUSER
export TMPDIR=/exp/xtts/tmp
export PERL5LIB=/opt/app/ora103/10.2.0/product/perl/lib
/opt/app/ora103/10.2.0/product/perl/bin/perl /exp/xtts/xttdriver.pl -p -d
执行全量备份
cd /exp/xtts
nohup sh full_backup.sh > full_backup.log &
1.3.2 表空间全量恢复及转换
目标端Linux执行表空间恢复并将数据文件转换至ASM磁盘组中，每次恢复失败时会在 /exp/xtts/tmp 产生fails文件需要删除后方可再次运行（做之前查看下文中的特别说明步骤）
cd /exp/xtts
full_restore.sh脚本内容如下
export TMPDIR=/exp/xtts/tmp
export ORACLE_SID=oranew1
/opt/app/ora11g/product/11.2.0/perl/bin/perl /exp/xtts/xttdriver.pl -c -d
执行恢复
nohup sh full_restore.sh > full_restore.log &
1.4 增量备份及恢复
1.4.1 表空间增量备份
源端进行增量备份
cd /exp/xtts
增量备份脚本incre_backup.sh内容如下
export ORACLE_SID=TESTUSER
export TMPDIR=/exp/xtts/tmp
export PERL5LIB=/opt/app/ora103/10.2.0/product/perl/lib
/opt/app/ora103/10.2.0/product/perl/bin/perl /exp/xtts/xttdriver.pl -i -d
执行增量备份
cd /exp/xtts
nohup sh incre_backup.sh > incre_backup.log &
1.4.2 表空间增量恢复
cd /exp/xtts
incre_recover.sh脚本内容如下
export TMPDIR=/exp/xtts/tmp
export ORACLE_SID=oranew1
/opt/app/ora11g/product/11.2.0/perl/bin/perl /exp/xtts/xttdriver.pl -r -d
执行增量恢复
nohup sh incre_recover.sh > incre_recover.log &
1.5 迁移阶段
1.5.1 表空间 readonly
源端AIX将被传输业务表空间修改为READ ONLY状态
alter system set job_queue_processes=0;
--恢复
alter system set job_queue_processes=10;
select tablespace_name,status from dba_tablespaces order by 2;
alter tablespace TESTUSER_DAT_4 read only;
alter tablespace TESTUSER_DAT_1 read only;
alter tablespace TESTUSER_DAT_2 read only;
alter tablespace TESTUSER_IDX_2 read only;
alter tablespace TESTUSER_DAT_3 read only;
alter tablespace TESTUSER_IDX_3 read only;
alter tablespace TESTUSER_ALL read only;
alter tablespace TESTUSER_IDX_1 read only;
alter tablespace TESTUSER_IDX_4 read only;
1.5.2 最后一次增量操作
按照 2.4.1 、2.4.2完成最后一次增量备份与恢复。
1.5.3 目标端开启闪回
目标端Linux开启在导入元数据前开启闪回
SQL> alter system set db_recovery_file_dest_size=50g scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='+DG_DATA' scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
YES
SQL> create restore point before_imp_xtts guarantee flashback database;
Restore point created.
SQL> select name from v$restore_point;
1.5.4 导入 XTTS 元数据
AIX 源端导出XTTS元数据
导出表空间元数据
expdp system/passwd parfile=expdp_xtts.par
expdp_xtts.par内容如下
directory=dump
dumpfile=tbs_xtts.dmp
logfile=expdp_xtts.log
transport_tablespaces=('TESTUSER_DAT_4','TESTUSER_DAT_1','TESTUSER_DAT_2','TESTUSER_IDX_2','TESTUSER_DAT_3','TESTUSER_IDX_3','TESTUSER_ALL','TESTUSER_IDX_1','TESTUSER_IDX_4')
transport_full_check=y
cluster=n
metrics=yes
导出用户元数据
expdp system/passwd parfile=expdp_xtts_other.par
expdp_xtts_other.par内容如下
directory=dump
dumpfile=tbs_xtts_other.dmp
logfile=expdp_xtts_other.log
content=metadata_only
schemas=TESTUSER
metrics=yes
执行
cd /exp/xtts/dump
./expdp_xtts.sh
./expdp_xtts_other.sh
LINUX 目标端导入元数据
impdp system/passwd parfile=impdp_xtts.par
impdp_xtts.par内容如下
directory=dump
logfile=impdp_xtts.log
dumpfile=tbs_xtts.dmp
cluster=n
metrics=yes
transport_datafiles='+DG_DATA/ORANEW/DATAFILE/TESTUSER_ALL.456.995114385',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.532.995106883',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.528.995107837',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.524.995107839',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_DAT_1.543.995106883',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_2.496.995109429',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_4.432.995115329',
'+DG_DATA/ORANEW/DATAFILE/TESTUSER_IDX_4.430.995115329'
cd /exp/xtts/dump
./impdp_xtts.sh
1.5.5 表空间 READ WRITE
LINUX 目标端表空间 readwrite
alter tablespace TESTUSER_DAT_4 read write;
alter tablespace TESTUSER_DAT_1 read write;
alter tablespace TESTUSER_DAT_2 read write;
alter tablespace TESTUSER_IDX_2 read write;
alter tablespace TESTUSER_DAT_3 read write;
alter tablespace TESTUSER_IDX_3 read write;
alter tablespace TESTUSER_ALL read write;
alter tablespace TESTUSER_IDX_1 read write;
alter tablespace TESTUSER_IDX_4 read write;
select tablespace_name,status from dba_tablespaces order by 1;
1.5.6 第二次开启闪回
目标端Linux在其他元数据导入前再次开启闪回
sqlplus / as sysdba
select flashback_on from v$database;
create restore point before_imp_other guarantee flashback database;
select name from v$restore_point;
1.5.7 导入其它元数据
impdp system/passwd parfile=impdp_xtts_other.par
impdp_xtts_other.par 内容如下
directory=dump
dumpfile=tbs_xtts_other.dmp
logfile=impdp_xtts_other.log
content=metadata_only
schemas=TESTUSER
cluster=n
metrics=yes
执行
cd /exp/xtts/dump
./impdp_xtts_other.sh
参考网址: http://blog.itpub.net/26964624/viewspace-2557295/ https://www.enmotech.com/web/detail/1/476/2.html https://www.enmotech.com/web/detail/1/477/2.html