Oracle异构平台迁移利器之XTTS(使用dbms_file_transfer方式)
1.5、迁移过程导图
二、XTTS迁移示例
环境情况如下:
源库 | 目标库 | |
---|---|---|
版本 | 11.2.0.3 | 11.2.0.4 |
ORACLE_SID | LHR11G | LHR11G |
用户表空间 | TS_LHR、TS_XXT、USERS | TS_LHR、TS_XXT、USERS |
业务用户 | lhr、xxt | lhr、xxt |
平台 | Linux x86 64-bit | Linux x86 64-bit |
字节序 | Little | Little |
IP地址 | 172.17.0.3 | 172.17.0.4 |
字符集 | AMERICAN_CHINA.AL32UTF8 | AMERICAN_CHINA.AL32UTF8 |
归档模式 | 归档模式 | 归档模式 |
注:虽然我这里使用的同构平台,但是异构平台下的步骤是一样的,例如从Aix到Linux,该过程完全适用。
2.3.4、目标端进行增量转换和数据写入同步
export XTTDEBUG=1
cd /home/oracle/scripts
export TMPDIR=/home/oracle/scripts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
如果一套库上有多个实例的话,在执行该步骤之前,需要对环境变量进行确认,如检查当前ORACLE_SID是否是需要执行的SID,否则可能会恢复到其他实例上。(并非是真实的恢复,因为其他实例跟这个备份集没有任何关系,但恢复的过程会在其他实例上进行一遍,如关闭/启动数据库,包括增量恢复的日志都会在另一个数据库上显示。)如果发生了这种事情,不用紧张,调整好环境变量,再执行一次perl xttdriver.pl –r即可。误操作的实例不受影响。
注:
1.每次增量时都必须复制xttplan.txt、tsbkupmap.txt和incrbackups.txt,因为它们的内容在每次增量时都是不同的。
2.不修改、不复制文件incrbackups.txt.new。
3.该过程每次执行都会重启目标数据库。
4.如果重新开始,那么需要删除/home/oracle/scripts/FAILED
5.XTTDEBUG=1为打开debug模式,进行调试。Debug 模式可以打印更多的屏幕输出,并且开启 RMAN 的 debug 模式。要启用 debug 模式,或者以 -d 参数运行 xttdriver.pl 或者在运行 xttdriver.pl 前设置环境变量 XTTDEBUG=1。这个参数接受3种级别,-d[1/2/3]级别3会显示最多的信息。
执行过程:
[oracle@lhrora11204 scripts]$ cd /home/oracle/scripts
[oracle@lhrora11204 scripts]$ export TMPDIR=/home/oracle/scripts
[oracle@lhrora11204 scripts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
============================================================
trace file is /home/oracle/scripts/rollforward_Apr13_Tue_15_52_56_869//Apr13_Tue_15_52_56_869_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
2.3.5、源端确定下一个增量备份的FROM_SCN
cd /home/oracle/scripts
export TMPDIR=/home/oracle/scripts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -s
该步骤会计算下一个FROM_SCN,将其记录在xttplan.txt文件中,然后在创建下一个增量备份时使用该SCN。
该步骤会将-i时生成的xttplan.txt.new改名为xttplan.txt,并将原来的xttplan.txt备份。
建议在目标端每次做完recover动作后,源端就执行一次该命令,以免遗忘。
执行过程:
[oracle@lhrora11203 scripts]$ cd /home/oracle/scripts
[oracle@lhrora11203 scripts]$ export TMPDIR=/home/oracle/scripts
[oracle@lhrora11203 scripts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -s
============================================================
trace file is /home/oracle/scripts/determinescn_Apr13_Tue_15_53_08_940//Apr13_Tue_15_53_08_940_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
Prepare newscn for Tablespaces: 'TS_LHR'
Prepare newscn for Tablespaces: 'TS_XXT'
Prepare newscn for Tablespaces: 'USERS'
Prepare newscn for Tablespaces: ''''
Prepare newscn for Tablespaces: ''''
New /home/oracle/scripts/xttplan.txt with FROM SCN's generated
2.4、XTTS 最后一次增量前滚
2.4.1、将表空间置为RO状态
假设我们进行了多次增量操作之后,在停机时间的时候,再将源端数据库中需要传输的表空间设置为只读模式 :
-- 产生增量数据
create table lhr.testxtts66 tablespace users as select * from dual;
select 'ALTER TABLESPACE '||name||' READ ONLY;' exec_sql
from v$tablespace
where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
SYS@LHR11G> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
TS_LHR READ ONLY
TS_XXT READ ONLY
2.4.2、源端做增量备份
首先对上一次的增量备份目录做迁移:
mv /u01/app/xtts/inc_bk /u01/app/xtts/inc_bk1
mkdir -p /u01/app/xtts/inc_bk
开始做增量备份:
cd /home/oracle/scripts
export TMPDIR=/home/oracle/scripts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -i
2.4.3、将源端的增量数据传到目标端
这里传递增量数据的时候,还需要将源端/home/oracle/scripts/目录下的 xttplan.txt、 tsbkupmap.txt和incrbackups.txt文件都传输到目标端。每当你进行一次增量的备份操作,这 3个文件的内容都会发现变化 。
每一次增量操作之后,都需要将这 3个文件传到目标端数据库的 /home/oracle/scripts/目录中。
-- 注意:增量内容应该拷贝到目标端的全量备份路径下
scp /u01/app/xtts/inc_bk/* 172.17.0.4:/u01/app/xtts/df_bk/
scp /home/oracle/scripts/xttplan.txt 172.17.0.4:/home/oracle/scripts/
scp /home/oracle/scripts/tsbkupmap.txt 172.17.0.4:/home/oracle/scripts/
scp /home/oracle/scripts/incrbackups.txt 172.17.0.4:/home/oracle/scripts/
2.4.4、目标端进行增量转换和数据写入同步
cd /home/oracle/scripts
export TMPDIR=/home/oracle/scripts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
2.5、迁移元数据
注意在导出时,TRANSPORT_TABLESPACES参数需要的表空间要写全,别遗漏。
在目标端进行IMPDP时,transport_datafiles需要将所有的数据文件添加进去。
如果数据文件很多,人为添加容易出错,可以使用perl xttdriver.pl –e获取全部数据文件列表。
2.5.1、在目标库创建业务用户
相关脚本在前边的准备阶段已经准备好了:
create user xxt identified by lhr;
create user lhr identified by lhr;
grant dba to xxt,lhr;
可以使用导出和导入分开来执行,也可以直接通过network_link来做远程导入,而不需要做导出操作:
2.5.2、导入xtts的元数据
生成导入脚本:
-- 在目标端可以执行如下命令生成impdp,命令在xttplugin.txt文件
[oracle@lhrora11204 scripts]$ cd /home/oracle/scripts
[oracle@lhrora11204 scripts]$ export TMPDIR=/home/oracle/scripts
[oracle@lhrora11204 scripts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
============================================================
trace file is /home/oracle/scripts/generate_Apr13_Tue_15_58_53_193//Apr13_Tue_15_58_53_193_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Generating plugin
--------------------------------------------------------------------
--------------------------------------------------------------------
Done generating plugin file /home/oracle/scripts/xttplugin.txt
--------------------------------------------------------------------
[oracle@lhrora11204 scripts]$ more /home/oracle/scripts/xttplugin.txt
impdp directory=<DATA_PUMP_DIR> logfile=<tts_imp.log> \
network_link=<ttslink> transport_full_check=no \
transport_tablespaces=TS_LHR,TS_XXT,USERS \
transport_datafiles='/u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf','/u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf','/u01/app/oracle/oradata/LHR11G/users01.dbf'
在目标端开始导入:
-- 目标端创建dblink
create public database link ttslink connect to system identified by lhr using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.3 )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = LHR11G) ) )';
-- 利用dblink直接迁移元数据,这里只会迁移表、索引、约束等,因为其它对象存储在系统表空间中
impdp system/lhr directory=DATA_PUMP_DIR logfile=tts_imp_lhr.log \
network_link=ttslink transport_full_check=no \
EXCLUDE=STATISTICS \
transport_tablespaces=TS_LHR,TS_XXT,USERS \
transport_datafiles='/u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf','/u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf','/u01/app/oracle/oradata/LHR11G/users01.dbf'
执行过程:
[oracle@lhrora11204 scripts]$ impdp system/lhr directory=DATA_PUMP_DIR \
> network_link=ttslink transport_full_check=no \
> EXCLUDE=STATISTICS \
> transport_tablespaces=TS_LHR,TS_XXT,USERS \
> transport_datafiles='/u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf','/u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf','/u01/app/oracle/oradata/LHR11G/users01.dbf'
Import: Release 11.2.0.4.0 - Production on Tue Apr 13 16:04:45 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR network_link=ttslink transport_full_check=no EXCLUDE=STATISTICS transport_tablespaces=TS_LHR,TS_XXT,USERS transport_datafiles=/u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf,/u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf,/u01/app/oracle/oradata/LHR11G/users01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 13 16:06:19 2021 elapsed 0 00:01:27
2.5.3、导入存储过程、触发器、函数、包、视图、序列
-- 导入存储过程、触发器、函数、包、视图、序列
impdp system/lhr directory=DATA_PUMP_DIR \
network_link=ttslink schemas=LHR,XXT content=metadata_only exclude=index,table,constraint parallel=8
执行过程:
[oracle@lhrora11204 scripts]$ impdp system/lhr directory=DATA_PUMP_DIR \
> network_link=ttslink schemas=LHR,XXT content=metadata_only exclude=index,table,constraint parallel=8
Import: Release 11.2.0.4.0 - Production on Tue Apr 13 16:07:08 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=DATA_PUMP_DIR network_link=ttslink schemas=LHR,XXT content=metadata_only exclude=index,table,constraint parallel=8
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Apr 13 16:07:31 2021 elapsed 0 00:00:20
2.5.4、导入公共同义词和dblink等其它对象
-- 导入其它对象
impdp system/lhr directory=DATA_PUMP_DIR \
network_link=ttslink full=y include=JOB,PROCOBJ,USER,DB_LINK,DIRECTORY,SYNONYM,PROFILE,ROLE parallel=8
impdp system/lhr directory=DATA_PUMP_DIR \
network_link=ttslink full=y content=metadata_only exclude=index,table,constraint parallel=8
-- 导入公共同义词
set long 9999
select dbms_metadata.get_ddl('SYNONYM',SYNONYM_NAME,OWNER)
FROM dba_synonyms
where owner='PUBLIC' and table_owner in ('LHR','XXT');
-- 导入dblink
set long 9999
select dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER)
FROM DBA_DB_LINKS;
2.6、迁移完成后的收尾工作
2.6.1、查看源端、目标端的数据一致性
select count(*) from lhr.testxtts7;
select count(*) from lhr.testxtts77;
select owner, object_type, count(*)
from dba_objects
where object_name not like 'BIN%'
and owner in ('LHR','XXT')
group by owner, object_type
order by 1,2 desc;
select owner, object_type, count(*)
from dba_objects
where status <> 'VALID'
and owner in ('LHR','XXT')
group by owner, object_type
order by 1, 2 desc;
执行过程:
SYS@LHR11G> select owner, object_type, count(*)
2 from dba_objects
where object_name not like 'BIN%'
group by owner, object_type
and owner in ('LHR','XXT')
5 group by owner, object_type
6 order by 1,2 desc;
OWNER OBJECT_TYPE COUNT(*)
------------------------------------------------------------ -------------------------------------- ----------
LHR VIEW 1
LHR TABLE 11
LHR SYNONYM 1
LHR SEQUENCE 1
LHR PROCEDURE 2
XXT TABLE 2
6 rows selected.
SYS@LHR11G>
SYS@LHR11G> select owner, object_type, count(*)
2 from dba_objects
where status <> 'VALID'
and owner in ('LHR','XXT')
5 group by owner, object_type
6 order by 1, 2 desc;
OWNER OBJECT_TYPE COUNT(*)
------------------------------------------------------------ -------------------------------------- ----------
LHR PROCEDURE 1
迁移后的对象个数和之前一致。
2.6.2、更改用户默认表空间
alter user xxt default tablespace USERS;
2.6.3、目标库的表空间修改为读写模式
select 'ALTER TABLESPACE '||name||' READ WRITE;' exec_sql
from v$tablespace
where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
select tablespace_name,status from dba_tablespaces;
执行过程:
SYS@LHR11G> select 'ALTER TABLESPACE '||name||' READ WRITE;' exec_sql
2 from v$tablespace
3 where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
EXEC_SQL
----------------------------------------------------------------------------------------------------------------------
ALTER TABLESPACE TS_LHR READ WRITE;
ALTER TABLESPACE TS_XXT READ WRITE;
ALTER TABLESPACE USERS READ WRITE;
SYS@LHR11G>
SYS@LHR11G> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
TS_LHR READ ONLY
TS_XXT READ ONLY
7 rows selected.
SYS@LHR11G> ALTER TABLESPACE TS_LHR READ WRITE;
Tablespace altered.
SYS@LHR11G> ALTER TABLESPACE TS_XXT READ WRITE;
Tablespace altered.
SYS@LHR11G> ALTER TABLESPACE USERS READ WRITE;
Tablespace altered.
SYS@LHR11G> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TS_LHR ONLINE
TS_XXT ONLINE
7 rows selected.
2.6.4、校验数据文件完整性
运行RMAN,通过运行VALIDATE TABLESPACE检查物理和逻辑块损坏情况,如下所示:
RMAN> validate tablespace TS_LHR,TS_XXT,USERS check logical;
Starting validate at 2021-04-13 16:09:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/LHR11G/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 53 1280 2315665
File Name: /u01/app/oracle/oradata/LHR11G/ts_lhr01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1065
Index 0 0
Other 0 162
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 3705 3840 2315459
File Name: /u01/app/oracle/oradata/LHR11G/ts_xxt01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2
Index 0 0
Other 0 133
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 17 672 3165644
File Name: /u01/app/oracle/oradata/LHR11G/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 100
Index 0 37
Other 0 486
Finished validate at 2021-04-13 16:09:25
2.6.5、收集统计信息
exec dbms_stats.gather_database_stats(degree => 8);
exec dbms_stats.gather_dictionary_stats(degree => 8);
exec dbms_stats.gather_fixed_objects_stats();
2.7、总结
1、若使用rman方式,则源端和目标端的文件系统需要留有足够的空间
2、本文所有操作,均在oracle用户下执行。
3、如果使用dbms_file_Transfer,那么dbms_file_Transfer准备阶段方法要求目标数据库为11.2.0.4。如果目标端是11.2.0.3或更早的版本,那么需要配置一个增量转换实例。
4、若执行xttdriver.pl脚本报错,要想重新执行,那么需要删除文件FAILED
5、每次执行增量恢复xttdriver.pl -r时,都会重启目标端数据库。如果目标端是一个在用的生产库,那么需要创建一个临时实例,这样就不会影响生产库。
[oracle@dest]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/xtt_home
[oracle@dest]$ export ORACLE_SID=xtt
[oracle@dest]$ cat > $ORACLE_HOME/dbs/init$ORACLE_SID.ora << "EOF"
db_name=xtt
compatible=11.2.0.4.0
EOF
[oracle@dest]$ sqlplus / as sysdba
SQL> startup nomount
6、XTTS停机的关键的时间点是增量的备份和恢复的时间,以及元数据的导入时间。所以,可以通过如下手段减少XTTS的停机时间:
-
在源库开启块改变跟踪功能BCT
-
开启并行备份,例如:RMAN> configure device type disk parallelism 8;
-
提升impdp导入元数据库的速度,例如排除统计信息,开启并行等
-
临时提高源库到目标库的带宽网络
7、迁移时尽量减少增量的批次,因为操作批次越多越容易出错
8、导入元数据时,需要分2次导入,第1次导入表、索引等,第2次导入存储过程、触发器、函数、包、同义词、dblink、序列等。
9、只有被迁移表空间里物理存储的数据库对象才会被拷贝至目标系统;如果要迁移存储在其它表空间的其它类型的对象(比如存储在 SYSTEM 表空间内的 pl/sql 对象,sequences 等),你可以使用数据泵来拷贝这些对象至目标系统。
注意:本文所使用的的测试环境皆为docker环境,可以参考:
所有版本的Oracle环境:https://mp.weixin.qq.com/s/XJxgyCPcVTdhlPq7VcTMDg
11.2.0.4的Oracle环境: https://mp.weixin.qq.com/s/1Z630TbilkQ3aAyOcosl4w
11.2.0.3的Oracle环境:https://mp.weixin.qq.com/s/jb0hnhsCwyGWjOmP2biGJA
- 点赞
- 收藏
- 关注作者
评论(0)