以下是XTTS迁移的全过程,
需要根据实际情况更改,主要包括以下几个流程:
检查数据库用户自包含
准备
xtts
前期环境
进行全量备份并传输备份集到目标端
目标端进行全量恢复
源端目标端多次增量恢复
表空间
readonly
进行最后一次增量
元数据导入导出
检查对象
源端
目标端
Oracle
版本
10.2.0.3
11.2.0.4
操作系统
AIX 5.3
RHEL 6.7
是否
RAC
否
是
数据库名称
oraold
oranew
本次只迁移
TESTUSER
用户,只检查
TESTUSER
用户所在表空间的自包含验证即可,无需额外操作。
SQL>
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.
PL/SQL procedure successfully completed.
SQL>
no rows selected
所需目录
源端和目标端创建
相关
目录
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
SQL>
Database altered.
SQL>
;
STATUS
----------
FILENAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BYTES
----------
ENABLED
/exp/xtts/bct 11599872
关闭tracking可以使用如下命令完成:
配置源端AIX xtt.properties属性文件
cd /exp/xtts
vi
#增加如下配置信息:
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
以下命令为创建用户命令参考,也可以使用
plsql
等工具生成类似相关信息。用户和角色必须手工创建。最好使用
plsql
拿出所需用户的
ddl
以免漏掉权限。
创建
EBPF
用户,完成元数据导入后才可修改默认表空间
SQL> select name ,PASSWORD from user$ where name='TESTUSER';
NAME PASSWORD
------------------------------ ------------------------------
TESTUSER
create user TESTUSER identified by values 'C987AC3B738BCF43';
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;
创建角色
赋予角色相应操作权限
源端AIX执行被传输业务表空间全量备份创建xtts表空间全量备份脚本执行过程中产生的配置文件,用于数据文件转换及每次增量备份及恢复,同时每次执行增量备份过程中,配置文件内容会发生变化,用于新的增量恢复,主要是SCN的变化。增加rman备份并行度
show all;
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
目标端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
执行恢复
> full_restore.log &
源端进行增量备份
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
> incre_backup.log &
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
执行增量恢复
> incre_recover.log &
源端AIX将被传输业务表空间修改为READ ONLY状态
--恢复
alter system set job_queue_processes=10;
select tablespace_name,status from dba_tablespaces order by 2;
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;
按照
2.4.1
、2.4.2完成最后一次增量备份与恢复。
目标端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;
元数据
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
LINUX
目标端表空间
readwrite
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;
目标端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;
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