目录:
AIX平台到LINUX平台传输Oracle表空间实现数据迁移(手动转换数据文件)
前言:
在oracle10g之前,数据文件是不能跨平台传输使用的,从oracle 10g开始,oracle支持跨平台表空间传输(transport tablespace)
数据文件不能跨平台的原因是:不同平台操作系统的字、字节存储顺序不同
如果将4567这个数据存储到不同的系统中,那么就会出现如下的顺序:
little-endian:存储的顺序是7654,小数在前(windows平台,LINUX平台)
big-endian:存储的顺序是4567,大数在前(solaris及aix平台)
本文主要描述采用oracle的transport tablespace技术,实现不同字节序跨平台迁移
注意:传输表空间技术不能应用于system表空间或sys用户拥有的对象
参考文章:https://blog.51cto.com/u_7642644/1600284,本文在此基础上重新进行测试和撰写
一、OS系统及DB版本
1、源端(Aix6.1+ Oracle 10.2.0.5.0)
select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
set lines 200
col platform_name for a30;
select d.platform_name,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
2、目表端(Linux7.9+ Oracle 11.2.0.4.0)
select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
set lines 200
col platform_name for a30;
select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ ------------------------------------------
Linux x86 64-bit Little
从上面的输出可以看到字节顺序是不同的, Linux平台是 低位 (Little),aix 平台是高位(Big)
二、创建测试条件
创建目录存放数据文件和转换后的数据文件
mkdir -p /oracle/test/datafile
mkdir -p /oracle/test/trans_datafile
创建测试表空间trans_test,测试用户test_user,测试表test_user.t1并插入数据
--Create tablespace
SQL> create tablespace trans_test datafile '/oracle/test/datafile/trans_test_aix.dbf' size 10m;
--Create user
SQL> create user test_user identified by test_user default tablespace trans_test quota unlimited on trans_test;
--Create test tables t1
SQL> create table test_user.t1(id number);
--Insert data to t1
SQL> insert into test_user.t1 values(10);
--Insert data to t1
SQL> insert into test_user.t1 values(20);
SQL> commit;
三、导出测试表空间
1、将表空间设置为只读状态
SQL> sqlplus / as sysdba
SQL> alter tablespace trans_test read only;
必须将要导出表空间置为read only,否则不能使用数据泵导出
2、登录数据库创建导出目录
SQL> sqlplus / as sysdba
SQL> create directory EXPDP_DIR as '/oracle/test/expdp_dir';
3、在操作系统创建对应目录
mkdir -p /oracle/test/expdp_dir
4、导出测试表空间
$ expdp \'/ as sysdba\' directory=EXPDP_DIR dumpfile=trans_test.dmp transport_tablespaces=trans_test logfile=trans_test.log
导出日志如下:
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 27 May, 2022 15:42:02
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=EXPDP_DIR dumpfile=trans_test.dmp transport_tablespaces=trans_test logfile=trans_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/oracle/test/expdp_dir/trans_test.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:42:12
四、转换数据文件格式
使用RMAN
的CONVERT
命令进行转换
方法一:在源端进行datafile转换
对源端trans_test表空间数据文件进行转换
$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Fri May 27 09:11:02 2022
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=xxxxxxxxxx)
RMAN> convert tablespace trans_test
to platform 'Linux x86 64-bit'
format='/oracle/test/trans_datafile/trans_test_linux.dbf';
Starting backup at 27-MAY-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2038 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00030 name=/oracle/test/datafile/trans_test_aix.dbf
converted datafile=/oracle/test/trans_datafile/trans_test_linux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-MAY-22
参数说明:
convert tablespace trans_test #要转换的表空间名 to platform 'Linux x86 64-bit' #目标端平台名称 format='/oracle/test/trans_datafile/trans_test_linux.dbf'; #新生成的数据文件名格式
多个数据文件也可以在结尾加%U会生成唯一的转换数据文件名,例如:trans_test_linux_data_D-ORCL_I-_TS-TRANS_TEST_FNO-30_020uiek3.dbf
方法二:目标端进行datafile转换
将源端库trans_test表空间的数据文件trans_test_aix.dbf
直接传输到目标端服务器进行转换
RMAN> convert datafile '/u01/impdp_dir/trans_test_aix.dbf'
from platform 'AIX-Based Systems (64-bit)'
db_file_name_convert '/u01/impdp_dir/trans_test_aix.dbf','/u01/impdp_dir/trans_test_linux.dbf';
Starting conversion at target at 2022-05-27 15:54:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/impdp_dir/trans_test_aix.dbf
converted datafile=/u01/impdp_dir/trans_test_linux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2022-05-27 15:54:36
或:
RMAN> convert datafile '/u01/impdp_dir/trans_test_aix.dbf'
from platform 'AIX-Based Systems (64-bit)'
format '/u01/impdp_dir/trans_test_linux.dbf';
Starting conversion at target at 2022-05-27 15:56:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1565 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/impdp_dir/trans_test_aix.dbf
converted datafile=/u01/impdp_dir/trans_test_linux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2022-05-27 15:56:39
参数说明:
convert datafile '/u01/impdp_dir/trans_test_aix.dbf' #源平台数据文件名 from platform 'AIX-Based Systems (64-bit)' #源平台名称 db_file_name_convert '/u01/impdp_dir/trans_test_aix.dbf','/u01/impdp_dir/trans_test_linux.dbf'; #手工指定转换前和转换后的数据文件名称格式 或: format '/u01/impdp_dir/trans_test_linux.dbf'; #或使用format直接指定转换后的数据文件名称格式
五、目标端导入表空间
1、登录数据库创建导出目录
SQL> sqlplus / as sysdba
SQL> create directory IMPDP_DIR as '/u01/impdp_dir';
2、在操作系统创建对应目录
mkdir /u01/impdp_dir
上传表空间dmp文件trans_test.dmp
和转换后的数据文件trans_test_linux.dbf
到目标端服务器导入目录下:
/u01/impdp_dir
3、目标端创建用户(与源端一致)
SQL> create user test_user identified by test_user;
若不创建用户在目标端导入时会报错
4、导入表空间
在目标端Linux 平台导入表空间(datafile:trans_test_linux.dbf
)与(dumpfile:trans_test.dmp
)
$ impdp \' / as sysdba\' directory=IMPDP_DIR dumpfile=trans_test.dmp transport_datafiles=/u01/impdp_dir/trans_test_linux.dbf logfile=trans_test.log
导如日志如下:
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
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is +08:00.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=IMPDP_DIR dumpfile=trans_test.dmp transport_datafiles=/u01/impdp_dir/trans_test_linux.dbf logfile=trans_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri May 27 15:59:52 2022 elapsed 0 00:00:01
特别注意:
数据文件在导入时要使用绝对路径,我这边因为测试,数据文件就存放在了导入目录,一般还是要与数据库其他datafile存放一起
验证:
SQL> conn / as sysdba
Connected.
SQL> select * from test_user.t1;
ID
----------
10
20
col TABLESPACE_NAME for a30
col file_name for a60
select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
---------------------- ------------------------------------------------------------
SYSTEM /oradata/orcl/system01.dbf
SYSAUX /oradata/orcl/sysaux01.dbf
UNDOTBS2 /oradata/orcl/undotbs201.dbf
UNDOTBS1 /oradata/orcl/undotbs101.dbf
USERS /oradata/orcl/users01.dbf
TRANS_TEST /u01/impdp_dir/trans_test_linux.dbf
六、收尾工作
1、将表空间置为读写状态
SQL> alter tablespace trans_test read write;
SQL> select username,default_tablespace from dba_users where username = 'TEST_USER';
USERNAME DEFAULT_TABLESPACE
-------------------------------------------------- ----------------------------------
TEST_USER USERS
SQL> select owner ,table_name from dba_tables where table_name = 'T1';
OWNER TABLE_NAME
------------------------------------------------- -----------------------------------
TEST_USER T1
2、更改用户默认表空间
SQL> alter user test_user default tablespace trans_test;
七、清理测试环境
--删除用户(及所拥有的对象)
SQL> drop user test_user cascade;
--删除表空间(包括数据文件)
SQL> drop tablespace trans_test including contents and datafiles;