使用rman convert和TTS跨平台数据库迁移

来源：互联网发布：linux 安全扫描工具编辑：程序博客网时间：2024/06/05 15:01

一.环境描述

我们一般跨平台进行数据库迁移使用expdp/impdp,或者使用dsg/ogg之类的收费产品.其实oracle还有xtts也可以进行跨平台迁移数据库.其中xtts调用的就是rman的convert命令来实现.xtts可以进行增量复制,停机时间更短.我这里直接使用rman convert+TTS来实现.

为什么我不使用expdp/impdp呢,因为数据库里有大量的lob数据,而expdp/impdp对这些对象操作是很慢的.停机时间不允许.经过测试发现500G的数据,停机时间在4小时左右即可完成迁移.时间大致分布为:convert datafile(1.5小时)+文件传输(1.5小时)+元数据导入和后续操作(1小时)

当然停机的时间和存储的速度,网络速度有关.具体环境需要具体测试.

这里的测试环境为源端aix oracle 11204,目标端linux oracle 11204,版本都已经升级到11204了.

注意:binary xml不支持TTS,只能使用expdp/impdp来实现,而且binary xml的bug很多.

二.环境检查

对于使用convert和TTS是有环境要求的.

1.首先检查源端和目标端的操作系统平台和字节顺序:

源端:

col platform_name for a40
select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d
where tp.platform_name=d.platform_name;
PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
AIX-Based Systems (64-bit)               Big

目标端:


PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit Little

2.检查表空间是否是可传输

目标端数据库的字符集,blocksize,国家字符集都必须设置成一致,具体要求参考这位朋友的博客:

http://blog.itpub.net/22521389/viewspace-764641/

临时表空间不能传输,执行检查:

select wm_concat(tablespace_name) from dba_tablespaces;
execute sys.dbms_tts.transport_set_check('USERS,BHDATA,BHINDEX,BHMAIL,ZHBASIS,ZHARCHIVES,ZHSERVICES,ZHADVICES,ZHEXPENSES,ZHMEDICINE,ZHLAB,ZHCHECK,ZHLOB,ZHINDEX,SLREPORT,HJI,BHDATA1,ZHINSURE,ZHMEDREC,ZLPERFBASE,ZLPERFSAMP,ZL_INQUIRIES,SHENJIJU,ETL,YZJKXW,ZHMATERIAL',true);

查看检查结果:

col violations for a70
select * from sys.transport_set_violations;
SQL>

没有输出表示所选择的表空间是可以进行传输的.

检查表空间是否有坏块:

select 'execute dbms_space_admin.tablespace_verify('''||tablespace_name||''');' from dba_tablespaces where tablespace_name in ('USERS','BHDATA','BHINDEX','BHMAIL','ZHBASIS','ZHARCHIVES','ZHSERVICES','ZHADVICES','ZHEXPENSES','ZHMEDICINE','ZHLAB','ZHCHECK','ZHLOB','ZHINDEX','SLREPORT','HJI','BHDATA1','ZHINSURE','ZHMEDREC','ZLPERFBASE','ZLPERFSAMP','ZL_INQUIRIES','SHENJIJU','ETL','YZJKXW','ZHMATERIAL');

执行上面产生的sql查看是否有坏块,如果有坏块会产生ORA的错误,具体的错误可以搜索MOS来进行解决.

3.目标端数据库环境准备

目标端数据库的字符集,blocksize,国家字符集必须设置和源端一致.

由于我们传输表空间中有users表空间,所以目标端的user表空间必须删除掉,否则会报错

SQL> create tablespace users2 datafile '/u01/oradata/orcl/user02.dbf' size 500m autoextend on;
Tablespace created.
SQL> alter database default tablespace users2;
Database altered.
SQL> DROP TABLESPACE users INCLUDING CONTENTS and datafiles;
Tablespace dropped.

目标端需要先创建所有的源端用户,所以检查源端用户:

select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace not in('USERS','SYSTEM','SYSAUX') or  temporary_tablespace not in ('TEMP');

由于临时表空间不能传输,所以首先在目标端创建所有的临时表空间:

create temporary tablespace ZLPERFTEMP tempfile '/u01/oradata/orcl/ZLPERFTEMP.dbf' size 100m autoextend on;
create temporary tablespace INQUIRIESTEMP tempfile '/u01/oradata/orcl/INQUIRIESTEMP.dbf' size 100m autoextend on;
create temporary tablespace BHTEMP tempfile '/u01/oradata/orcl/bhtemp.dbf' size 100m autoextend on;

由于这些用户的表空间目标端还没有,所以在创建用户的时候需要把default_tablespace先设置为其它表空间

select dbms_metadata.get_ddl('USER',username)||';' from dba_users where default_tablespace not in('USERS','SYSTEM','SYSAUX') or  temporary_tablespace not in ('TEMP');

获取语句后将表空间替换成users2表空间.

创建一个directory用来进行impdp元数据:

create directory tts as '/home/oracle/dmp';

三.源端rman convert数据转换

在进行convert的时候,需要将表空间只读:

select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces where tablespace_name in ('USERS','BHDATA','BHINDEX','BHMAIL','ZHBASIS','ZHARCHIVES','ZHSERVICES','ZHADVICES','ZHEXPENSES','ZHMEDICINE','ZHLAB','ZHCHECK','ZHLOB','ZHINDEX','SLREPORT','HJI','BHDATA1','ZHINSURE','ZHMEDREC','ZLPERFBASE','ZLPERFSAMP','ZL_INQUIRIES','SHENJIJU','ETL','YZJKXW','ZHMATERIAL');
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
alter tablespace USERS read only;
alter tablespace BHDATA read only;
alter tablespace BHINDEX read only;
alter tablespace BHMAIL read only;
alter tablespace ZHBASIS read only;
alter tablespace ZHARCHIVES read only;
alter tablespace ZHSERVICES read only;
alter tablespace ZHADVICES read only;
alter tablespace ZHEXPENSES read only;
alter tablespace ZHMEDICINE read only;
alter tablespace ZHLAB read only;
alter tablespace ZHCHECK read only;
alter tablespace ZHLOB read only;
alter tablespace ZHINDEX read only;
alter tablespace SLREPORT read only;
alter tablespace HJI read only;
alter tablespace BHDATA1 read only;
alter tablespace ZHINSURE read only;
alter tablespace ZHMEDREC read only;
alter tablespace ZLPERFBASE read only;
alter tablespace ZLPERFSAMP read only;
alter tablespace ZL_INQUIRIES read only;
alter tablespace SHENJIJU read only;
alter tablespace ETL read only;
alter tablespace YZJKXW read only;
alter tablespace ZHMATERIAL read only;

在源端进行数据文件转换:

下面是我写的一个脚本,将源端的文件转换后放到/oradata/backup下:

> 1.log
for i in USERS BHDATA BHINDEX BHMAIL ZHBASIS ZHARCHIVES ZHSERVICES ZHADVICES ZHEXPENSES ZHMEDICINE ZHLAB ZHCHECK ZHLOB ZHINDEX SLREPORT HJI BHDATA1 ZHINSURE ZHMEDREC ZLPERFBASE ZLPERFSAMP ZL_INQUIRIES SHENJIJU ETL YZJKXW ZHMATERIAL
do
convername=`sqlplus -slient / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off line 2000
col name for a60
col tbs for a60
select ''''||file_name||''','||'''/oradata/backup'||substr(file_name,length(file_name)-INSTR(reverse(file_name),'/')+1,INSTR(reverse(file_name),'/'))||''',' file_name
from dba_data_files where tablespace_name='$i';
`
str="convert tablespace '$i' to platform=\"Linux x86 64-bit\" db_file_name_convert=${convername};"
echo $str >>1.log
done
sed 's/\,\;/\;/g' 1.log >1.sql

执行上面的脚本后,1.sql输出如下convert命令:

convert tablespace 'USERS' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/users.dbf','/oradata/backup/users.dbf';
convert tablespace 'BHDATA' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/bhdata.dbf','/oradata/backup/bhdata.dbf';
convert tablespace 'BHINDEX' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/bhindex.dbf','/oradata/backup/bhindex.dbf';
convert tablespace 'BHMAIL' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/bhmail.dbf','/oradata/backup/bhmail.dbf';
convert tablespace 'ZHBASIS' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhbasis.dbf','/oradata/backup/zhbasis.dbf', '/oradata/dsg/backup/datafile/zhbasis2.dbf','/oradata/backup/zhbasis2.dbf';
convert tablespace 'ZHARCHIVES' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zharchives.dbf','/oradata/backup/zharchives.dbf';
convert tablespace 'ZHSERVICES' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhservices.dbf','/oradata/backup/zhservices.dbf', '/oradata/dsg/backup/datafile/zhservices4.dbf','/oradata/backup/zhservices4.dbf', '/oradata/dsg/backup/datafile/zhservices5.dbf','/oradata/backup/zhservices5.dbf';
convert tablespace 'ZHADVICES' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhadvices.dbf','/oradata/backup/zhadvices.dbf';
convert tablespace 'ZHEXPENSES' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhexpenses.dbf','/oradata/backup/zhexpenses.dbf', '/oradata/dsg/backup/datafile/zhexpenses10.dbf','/oradata/backup/zhexpenses10.dbf', '/oradata/dsg/backup/datafile/zhexpenses11.dbf','/oradata/backup/zhexpenses11.dbf', '/oradata/dsg/backup/datafile/zhexpenses12.dbf','/oradata/backup/zhexpenses12.dbf';
convert tablespace 'ZHMEDICINE' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhmedicine.dbf','/oradata/backup/zhmedicine.dbf';
convert tablespace 'ZHLAB' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhlab.dbf','/oradata/backup/zhlab.dbf';
convert tablespace 'ZHCHECK' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhcheck.dbf','/oradata/backup/zhcheck.dbf';
convert tablespace 'ZHLOB' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhlob.dbf','/oradata/backup/zhlob.dbf', '/oradata/dsg/backup/datafile/zhlob2.dbf','/oradata/backup/zhlob2.dbf', '/oradata/dsg/backup/datafile/zhlob3.dbf','/oradata/backup/zhlob3.dbf';
convert tablespace 'ZHINDEX' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhindex.dbf','/oradata/backup/zhindex.dbf', '/oradata/dsg/backup/datafile/zhindex1.dbf','/oradata/backup/zhindex1.dbf', '/oradata/dsg/backup/datafile/zhindex2.dbf','/oradata/backup/zhindex2.dbf', '/oradata/dsg/backup/datafile/zhindex3.dbf','/oradata/backup/zhindex3.dbf', '/oradata/dsg/backup/datafile/zhindex11.dbf','/oradata/backup/zhindex11.dbf', '/oradata/dsg/backup/datafile/zhindex4.dbf','/oradata/backup/zhindex4.dbf', '/oradata/dsg/backup/datafile/zhindex5.dbf','/oradata/backup/zhindex5.dbf', '/oradata/dsg/backup/datafile/zhindex6.dbf','/oradata/backup/zhindex6.dbf', '/oradata/dsg/backup/datafile/zhindex12.dbf','/oradata/backup/zhindex12.dbf', '/oradata/dsg/backup/datafile/zhindex7.dbf','/oradata/backup/zhindex7.dbf';
convert tablespace 'SLREPORT' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/slreport.dbf','/oradata/backup/slreport.dbf';
convert tablespace 'HJI' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/hji.dbf','/oradata/backup/hji.dbf';
convert tablespace 'BHDATA1' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/bhdata1.dbf','/oradata/backup/bhdata1.dbf';
convert tablespace 'ZHINSURE' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhinsure.dbf','/oradata/backup/zhinsure.dbf';
convert tablespace 'ZHMEDREC' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zhmedrec.dbf','/oradata/backup/zhmedrec.dbf';
convert tablespace 'ZLPERFBASE' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zlperfbase.dbf','/oradata/backup/zlperfbase.dbf';
convert tablespace 'ZLPERFSAMP' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zlperfsamp.dbf','/oradata/backup/zlperfsamp.dbf';
convert tablespace 'ZL_INQUIRIES' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/zl_inquiries.dbf','/oradata/backup/zl_inquiries.dbf';
convert tablespace 'SHENJIJU' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/shenjiju.dbf','/oradata/backup/shenjiju.dbf';
convert tablespace 'ETL' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/etl.dbf','/oradata/backup/etl.dbf';
convert tablespace 'YZJKXW' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/yzjkxw.dbf','/oradata/backup/yzjkxw.dbf';
convert tablespace 'ZHMATERIAL' to platform="Linux x86 64-bit" db_file_name_convert='/oradata/dsg/backup/datafile/system.3zhmaterial.dbf','/oradata/backup/system.3zhmaterial.dbf';

使用rman进行convert,执行上面的sql语句.执行完成后在/oradata/backup下面就保存了转换后的数据文件.

导出源端表空间的元数据:

expdp system/manager dumpfile=tts.dmp directory=tts transport_tablespaces=USERS,BHDATA,BHINDEX,BHMAIL,ZHBASIS,ZHARCHIVES,ZHSERVICES,ZHADVICES,ZHEXPENSES,ZHMEDICINE,ZHLAB,ZHCHECK,ZHLOB,ZHINDEX,SLREPORT,HJI,BHDATA1,ZHINSURE,ZHMEDREC,ZLPERFBASE,ZLPERFSAMP,ZL_INQUIRIES,SHENJIJU,ETL,YZJKXW,ZHMATERIAL transport_full_check=y logfile=tts.log

四.目标端impdp元数据

将源端的/oradata/backup复制到目标端的/u01/oradata/backup下

下面是获取目标端文件的一个脚本:

> 2.log
for i in USERS BHDATA BHINDEX BHMAIL ZHBASIS ZHARCHIVES ZHSERVICES ZHADVICES ZHEXPENSES ZHMEDICINE ZHLAB ZHCHECK ZHLOB ZHINDEX SLREPORT HJI BHDATA1 ZHINSURE ZHMEDREC ZLPERFBASE ZLPERFSAMP ZL_INQUIRIES SHENJIJU ETL YZJKXW ZHMATERIAL
do
convername=`sqlplus -slient / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off line 2000
col name for a60
col tbs for a60
select '''/oradata/backup'||substr(file_name,length(file_name)-INSTR(reverse(file_name),'/')+1,INSTR(reverse(file_name),'/'))||''',' file_name
from dba_data_files where tablespace_name='$i';
`
str="${convername}\\"
echo $str >>2.log
done
sed 's/\,\;/\;/g' 2.log >2.sql

然后进行impdp导入元数据:

impdp system/manager dumpfile=tts.dmp directory=tts \
transport_datafiles='/u01/oradata/backup/users.dbf',\
'/u01/oradata/backup/bhdata.dbf',\
'/u01/oradata/backup/bhindex.dbf',\
'/u01/oradata/backup/bhmail.dbf',\
'/u01/oradata/backup/zhbasis.dbf', '/u01/oradata/backup/zhbasis2.dbf',\
'/u01/oradata/backup/zharchives.dbf',\
'/u01/oradata/backup/zhservices.dbf', '/u01/oradata/backup/zhservices4.dbf', '/u01/oradata/backup/zhservices5.dbf',\
'/u01/oradata/backup/zhadvices.dbf',\
'/u01/oradata/backup/zhexpenses.dbf', '/u01/oradata/backup/zhexpenses10.dbf', '/u01/oradata/backup/zhexpenses11.dbf', '/u01/oradata/backup/zhexpenses12.dbf',\
'/u01/oradata/backup/zhmedicine.dbf',\
'/u01/oradata/backup/zhlab.dbf',\
'/u01/oradata/backup/zhcheck.dbf',\
'/u01/oradata/backup/zhlob.dbf', '/u01/oradata/backup/zhlob2.dbf', '/u01/oradata/backup/zhlob3.dbf',\
'/u01/oradata/backup/zhindex.dbf', '/u01/oradata/backup/zhindex1.dbf', '/u01/oradata/backup/zhindex2.dbf', '/u01/oradata/backup/zhindex3.dbf', '/u01/oradata/backup/zhindex11.dbf', '/u01/oradata/backup/zhindex4.dbf', '/u01/oradata/backup/zhindex5.dbf', '/u01/oradata/backup/zhindex6.dbf', '/u01/oradata/backup/zhindex12.dbf', '/u01/oradata/backup/zhindex7.dbf',\
'/u01/oradata/backup/slreport.dbf',\
'/u01/oradata/backup/hji.dbf',\
'/u01/oradata/backup/bhdata1.dbf',\
'/u01/oradata/backup/zhinsure.dbf',\
'/u01/oradata/backup/zhmedrec.dbf',\
'/u01/oradata/backup/zlperfbase.dbf',\
'/u01/oradata/backup/zlperfsamp.dbf',\
'/u01/oradata/backup/zl_inquiries.dbf',\
'/u01/oradata/backup/shenjiju.dbf',\
'/u01/oradata/backup/etl.dbf',\
'/u01/oradata/backup/yzjkxw.dbf',\
'/u01/oradata/backup/system.3zhmaterial.dbf' logfile=tts.log

导入成功后数据的导入已经完成了.

但是其它的数据库对象并没有导入,最简单的办法就是在源端重新导出一份全库的结构,然后在目标端导入:

expdp system/manager directory=expdir dumpfile=str.dmp logfile=str.log full=y content=metadata_only exclude=index,statistics

然后进行全库导入:

impdp system/manager directory=tts dumpfile=str.dmp full=y

导完之后,查看一下对象是否都在,编译一下无效对象,查看用户的默认表空间是否已经和源端一致.查询数据看数据是否一致.

0 0