AIX平台到LINUX平台传输Oracle表空间实现数据迁移（手动转换数据文件）

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;