CNDBA社区
本片我们演示Oracle 11.2.0.4 的单实例，从Linux 平台使用传输表空间(TTS)迁移到 Windows 平台的步骤。 迁移进行之前，目标端的Windows 实例要存在，字符集也要保持一致，可以理解成一个空库，通过TTS 直接把表空间附加上去。
TTS在使用时是有一些限制条件的，比如IOT对象，系统表空间和SYS对象等，具体参考我之前的博客：
Oracle 传输表空间(TTS) 的限制与局限性
https://www.cndba.cn/dave/article/4390
1 准备源库测试环境
[dave@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 5 12:04:20 2021
Copyright (c) 1982, 2013, Oracle. 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
SQL> select name,dbid from v$database;
NAME DBID
------------------ ----------
cndba 1591449381
SQL> col file_name for a50
SQL> set lines 120
SQL> col tablespace_name for a20
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
4 /u01/app/oracle/oradata/cndba/users01.dbf USERS
3 /u01/app/oracle/oradata/cndba/undotbs01.dbf UNDOTBS1
2 /u01/app/oracle/oradata/cndba/sysaux01.dbf SYSAUX
1 /u01/app/oracle/oradata/cndba/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/cndba/example01.dbf EXAMPLE
创建测试表空间：
SQL> create tablespace cndba datafile '/u01/app/oracle/oradata/cndba/dave01.dbf' size 20M,'/u01/app/oracle/oradata/cndba/dave02.dbf' size 20M;
Tablespace created.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
4 /u01/app/oracle/oradata/cndba/users01.dbf USERS
3 /u01/app/oracle/oradata/cndba/undotbs01.dbf UNDOTBS1
2 /u01/app/oracle/oradata/cndba/sysaux01.dbf SYSAUX
1 /u01/app/oracle/oradata/cndba/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/cndba/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/cndba/dave01.dbf CNDBA
7 /u01/app/oracle/oradata/cndba/dave02.dbf CNDBA
7 rows selected.
SQL> create user cndba identified by cndba default tablespace cndba;
User created.
SQL> grant connect,resource,dba to cndba;
Grant succeeded.
SQL> conn cndba/cndba
Connected.
SQL> create table dave tablespace cndba as select * from dba_objects;
Table created.
SQL> insert into dave select * from dba_objects;
86961 rows created.
SQL> commit;
Commit complete.
2 查看平台 endian format (byte ordering)和字符集
SQL> set pages 200
SQL> COLUMN PLATFORM_NAME FORMAT A40
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
--也可以直接查询当前平台：
SQL> 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
SQL> select userenv('LANGUAGE') FROM DUAL;
USERENV('LANGUAGE')
--------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
Linux 和Windows 都是Little，Unix 都是Big。
3 检查表空间自包含
在执行传输之前必须确保这一步通过，否则无法正常进行传输表空间。
SQL> execute sys.dbms_tts.transport_set_check('CNDBA,USERS', true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
我们这里只对CNDBA 这个表空间进行迁移，但是我们看到对USERS 也是可以进行的。
4 导出元数据
在执行导出之前，必须先将表空间设置为只读模式：
SQL> alter tablespace cndba read only;
Tablespace altered.
可以使用exp/imp 或者 expdp/impdp 进行导出元数据，我们这里选择expdp/impdp 进行：
[dave@www.cndba.cn ~]$ mkdir /tmp/cndba
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/cndba' ;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
Grant succeeded.
[dave@www.cndba.cn ~]$ expdp system/oracle DUMPFILE=cndba.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= CNDBA TRANSPORT_FULL_CHECK=Y
Export: Release 11.2.0.4.0 - Production on Fri Feb 5 13:01:27 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_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=cndba.dmp directory=dpump_dir transport_tablespaces= TRANSPORT_FULL_CHECK=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/tmp/cndba/cndba.dmp
******************************************************************************
Datafiles required for transportable tablespace CNDBA:
/u01/app/oracle/oradata/cndba/dave01.dbf
/u01/app/oracle/oradata/cndba/dave02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Feb 5 13:02:06 2021 elapsed 0 00:00:38
[dave@www.cndba.cn ~]$
5 在源端转换数据文件（RMAN)
这一步是从源端转换成目标端的ENDIAN_FORMAT，如果2端相同，那么可以省略这一步。 我们这里是从Linux 迁移到Windows，ENDIAN_FORMAT都是Little，转换操作如下。
PS C:/WINDOWS/system32> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期五 2月 5 13:37:40 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
----------------------------
Microsoft Windows x86 64-bit
Little
SQL>
[dave@www.cndba.cn ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 5 13:41:26 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: cndba (DBID=1591449381)
[dave@www.cndba.cn cndba]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 5 14:03:31 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: cndba (DBID=1591449381)
RMAN> convert tablespace CNDBA to platform="Microsoft Windows x86 64-bit" FORMAT '/tmp/cndba/dave%s.dbf';
Starting conversion at source at 05-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/u01/app/oracle/oradata/cndba/dave01.dbf
converted datafile=/tmp/cndba/dave5.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u01/app/oracle/oradata/cndba/dave02.dbf
converted datafile=/tmp/cndba/dave6.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 05-FEB-21
6 传输文件
将/tmp/cndba下的dump 文件和转换后的datafile 复制到目标端，我们这里复制到Windows 系统中。 这里要注意，因为我们这里都是文件系统的复制，不涉及ASM 磁盘组，所以数据库文件我们是直接复制到windows 实例对应的DATA 目录下，在impdp 之后，会直接使用这个路径。
7 导入元数据
同样适用data pump 进行操作。
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS 'G:/tmp';
目录已创建。
SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
授权成功。
SQL>
导入之前要确保用户是存在的，否则就需要在impdp时使用REMAP_SCHEMA=(source:target)。
SQL> create user cndba identified by cndba;
用户已创建。
SQL> grant connect,resource,dba to cndba;
授权成功。
PS G:/tmp> impdp system/oracle DUMPFILE=cndba.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='D:/app/zhixin/oradata/cndba/dave5.dbf','D:/app/zhixin/oradata/cndba/dave6.dbf'
Import: Release 11.2.0.4.0 - Production on 星期五 2月 5 14:41:03 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** DUMPFILE=cndba.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=D:/app/zhixin/oradata/cndba/dave5.dbf,D:/app/zhixin/oradata/cndba/dave6.dbf
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 星期五 2月 5 14:41:04 2021 elapsed 0 00:00:01 成功完成
8 将目标端表空间设置为读写并验证数据
SQL> alter tablespace cndba read write;
表空间已更改。
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------------------------------------- ------------------------------------------------------------
4 D:/APP/ZHIXIN/ORADATA/CNDBA/USERS01.DBF USERS
3 D:/APP/ZHIXIN/ORADATA/CNDBA/UNDOTBS01.DBF UNDOTBS1
2 D:/APP/ZHIXIN/ORADATA/CNDBA/SYSAUX01.DBF SYSAUX
1 D:/APP/ZHIXIN/ORADATA/CNDBA/SYSTEM01.DBF SYSTEM
5 D:/APP/ZHIXIN/ORADATA/CNDBA/EXAMPLE01.DBF EXAMPLE
6 D:/APP/ZHIXIN/ORADATA/CNDBA/DAVE6.DBF CNDBA
7 D:/APP/ZHIXIN/ORADATA/CNDBA/DAVE5.DBF CNDBA
已选择7行。
SQL> select count(1) from cndba.dave;
COUNT(1)
----------
173925
SQL>
9 注意事项（DBMS_FILE_TRANSFER 说明）
从Oracle 11.2.0.4 开始默认会进行DBMS_FILE_TRANSFER的操作。 即直接使用数据文件存放的位置，这也是我们为什么将数据文件直接放到windows 实例数据目录的原因。
如果是低于11.2.0.4的版本，或者使用了ASM 的情况，那么在目标端进行impd 之前还是必须先使用DBMS_FILE_TRANSFER进行一个路径的转换。
如果是文件系统，语法如下：
RMAN> CONVERT DATAFILE
'/path/tbs_31.f',
'/path/tbs_32.f',
'/path/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT= "/path_source/", "/path_dest/"
PARALLELISM=5;
如果是+ASM 磁盘组，语法如下：
RMAN> CONVERT DATAFILE
'/path/tbs_31.f',
'/path/tbs_32.f',
'/path/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT="/path_source/", "+diskgroup"
PARALLELISM=5;
关于磁盘组的操作，我们在后面的操作再单独演示。
参考：
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
Information Center: Transportable Tablespaces (TTS) for Oracle Database (Doc ID 1461278.2)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
版权声明：本文为博主原创文章，未经博主允许不得转载。
