本文共 10172 字，大约阅读时间需要 33 分钟。
今天来测试一下Oracle跨版本与平台执行传输表空间的具体实现方法，
实验的目的是把windows(11.2.0.4)平台上的源数据库中的表空间传到Linux(12.2.0.1)平台，并在源主机上使用目录E:\app\Administrator\oradata\transport 来存储被转换的数据文件。操作步骤如下： 一、首先创建测试环境： 1.在源库上创建测试的tablespaceSQL> create tablespace yuhuashi datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\YUHUASHI01.DBF' size 32M autoextend on next 32M maxsize unlimited;
2.创建测试用户并赋予权限
SQL> create user yuhuashi identified by test default tablespace yuhuashi;SQL> grant dba to yuhuashi;3.创建测试表
SQL> conn yuhuashi/test已连接。SQL> create table test1 as select * from dba_objects;表已创建。SQL> select count(*) from test1; COUNT(*)---------- 82162SQL> create table test2 as select * from all_objects;表已创建。SQL> select count(*) from test2; COUNT(*)---------- 80220SQL> create table test3 as select * from dba_users;表已创建。SQL> select count(*) from test3; COUNT(*)---------- 28表空间已创建。至此测试环境已经构建好，那么下面就正式进行测试 二、具体测试步骤 1.在源库上,将要被传输的表空间test01设置为只读
SQL> alter tablespace yuhuashi read only;表空间已更改。SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='YUHUASHI';TABLESPACE_NAME STATUS-------------------------------------------------------YUHUASHI READ ONLY2.检查源平台与目标平台信息看是支持传输操作，数据库所支持的平台信息如下:
SQL> COLUMN PLATFORM_NAME FORMAT A32 SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- -------------------------------- -------------- --------1 Solaris[tm] OE (32-bit) Big2 Solaris[tm] OE (64-bit) Big7 Microsoft Windows IA (32-bit) Little10 Linux IA (32-bit) Little6 AIX-Based Systems (64-bit) Big3 HP-UX (64-bit) Big5 HP Tru64 UNIX Little4 HP-UX IA (64-bit) Big11 Linux IA (64-bit) Little15 HP Open VMS Little8 Microsoft Windows IA (64-bit) Little9 IBM zSeries Based Linux Big13 Linux x86 64-bit Little16 Apple Mac OS Big12 Microsoft Windows x86 64-bit Little17 Solaris Operating System (x86) Little18 IBM Power Based Linux Big19 HP IA Open VMS Little20 Solaris Operating System (x86-64) Little21 Apple Mac OS (x86-64) Little20 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-------------------------------- ----------------------------Microsoft Windows x86 64-bit Little目标平台：
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 Little3.确认要被传输的表空间是否是自包含表空间(yuhuashi):
SQL> exec sys.dbms_tts.transport_set_check('YUHUASHI',true);PL/SQL 过程已成功完成。SQL> select * from sys.transport_set_violations;未选定行 没有记录，表示该表空间只包含表数据，可以传输。 4.使用RMAN将源数据库中的表空间yuhuashi转换为目标平台字节序格式，使用format参数来控制被转换后数据文件的文件名和存储目录.(其实这里该步骤可以省略) 解释： 使用跨平台传输表空间，平台之间可以是不同的字节存储顺序（endian format）。当源平台和目的地平台的字节存储顺序不同，源平台要传输表空间下的数据 文件的字节存储顺序必须转换成和目的地平台相同。转换操作使用RMAN CONVERT TABLESPACE命令（在源平台转换）或者CONVERT DATAFILE命令（在目的地平台转换）。 CONVERT TABLESPACE命令必须用在源平台，CONVERT DATAFILE命令用在目的地平台。 备注：由于这里Windows 64bit和Linux x86-64的字节存储次序相同，所以该步骤不用操作 这里是由于没有实际的操作环境，只能选择2个相同字节存储的环境来模拟一下，其实下面的操作是可以忽略的 C:\Users\Administrator.USER-20171009AH>rman target /恢复管理器: Release 11.2.0.4.0 - Production on 星期一 7月 9 13:42:17 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.已连接到目标数据库: ORCL (DBID=1508801192)RMAN> convert tablespace "YUHUASHI" to platform 'Linux x86 64-bit' format ='E:\app\Administrator\oradata\transport\yuhuashi01.dbf';启动 conversion at source 于 09-7月 -18使用目标数据库控制文件替代恢复目录分配的通道: ORA_DISK_1通道 ORA_DISK_1: SID=8 设备类型=DISK通道 ORA_DISK_1: 启动数据文件转换输入数据文件: 文件号=00005 名称=E:\APP\ADMINISTRATOR\ORADATA\ORCL\YUHUASHI01.DBF已转换的数据文件 = E:\APP\ADMINISTRATOR\ORADATA\TRANSPORT\YUHUASHI01.DBF通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03完成 conversion at source 于 09-7月 -185.使用导出工具创建传输表空间元数据dump文件 (源数据库上操作)
SQL> create or replace directory dump_dir as 'E:\app\Administrator\dump_dir';目录已创建。SQL> grant read,write on directory dump_dir to public;授权成功。SQL> grant read,write on directory dump_dir to yuhuashi;授权成功。C:\Users\Administrator.USER-20171009AH>expdp system/oracle directory=dump_dir dumpfile=yuhuashi.dmp logfile=yuhuashi.log transport_tablespaces=yuhuashiExport: Release 11.2.0.4.0 - Production on 星期一 7月 9 13:16:58 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dump_dir dumpfile=yuhuashi.dmp logfile=yuhuashi.log transport_tablespaces=yuhuashi处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK处理对象类型 TRANSPORTABLE_EXPORT/TABLE处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"******************************************************************************SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为: E:\APP\ADMINISTRATOR\DUMP_DIR\YUHUASHI.DMP******************************************************************************可传输表空间 YUHUASHI 所需的数据文件: E:\APP\ADMINISTRATOR\ORADATA\ORCL\YUHUASHI01.DBF作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 星期一 7月 9 13:17:26 2018 elapsed 0 00:00:28 成功完成6.导出除表之外的yuhuashi用户中的其它对象的元数据(比如存储过程，视图等)
SQL> alter tablespace yuhuashi read write;表空间已更改。C:\Users\Administrator.USER-20171009AH>expdp yuhuashi/test content=metadata_only directory=dump_dir dumpfile=yuhuashi20180709.dmp logfile=yuhuashi.log exclude=tableExport: Release 11.2.0.4.0 - Production on 星期一 7月 9 13:59:07 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options启动 "YUHUASHI"."SYS_EXPORT_SCHEMA_01": yuhuashi/******** content=metadata_only directory=dump_dir dumpfile=yuhuashi20180709.dmp logfile=yuhuashi.log exclude=table处理对象类型 SCHEMA_EXPORT/USER处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT处理对象类型 SCHEMA_EXPORT/ROLE_GRANT处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA已成功加载/卸载了主表 "YUHUASHI"."SYS_EXPORT_SCHEMA_01"******************************************************************************YUHUASHI.SYS_EXPORT_SCHEMA_01 的转储文件集为: E:\APP\ADMINISTRATOR\DUMP_DIR\YUHUASHI20180709.DMP作业 "YUHUASHI"."SYS_EXPORT_SCHEMA_01" 已于 星期一 7月 9 13:59:10 2018 elapsed 0 00:00:03 成功完成7.使用ftp工具分别拷贝表空间（集）和与表空间对应数据文件到相应的目录。 这里将转换后存储在E:\app\Administrator\oradata\transport目录中的数据文件,传输到目标主机的目录/data/oradata/shiyu中 将expdp导出的dmp文件传输到/data/dump_dir中
SQL> create or replace directory dump_dir as '/data/dump_dir';Directory created.SQL> grant read,write on directory dump_dir to public; Grant succeeded8.将要被传输的表空间附加到目标数据库中
SQL> create user yuhuashi identified by test;User created.SQL> grant dba to yuhuashi;SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='YUHUASHI';USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE------------------------------ ------------------------------YUHUASHI USERS TEMPshiyu:/data/transport@test1>impdp system/oracle directory=dump_dir dumpfile=YUHUASHI.DMP logfile=yuhuashi.log transport_datafiles=/data/oradata/shiyu/YUHUASHI01.DBF Import: Release 12.2.0.1.0 - Production on Mon Jul 9 13:44:39 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dump_dir dumpfile=YUHUASHI.DMP logfile=yuhuashi.log transport_datafiles=/data/oradata/shiyu/YUHUASHI01.DBF Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 9 13:45:17 2018 elapsed 0 00:00:359.导入用户yuhuashi下其它对象的元数据:
SQL> alter tablespace yuhuashi read write; Tablespace altered.shiyu:/data/dump_dir@test1>impdp yuhuashi/test directory=dump_dir dumpfile=YUHUASHI20180709.DMP logfile=yuhuashi.log Import: Release 12.2.0.1.0 - Production on Mon Jul 9 13:59:55 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionMaster table "YUHUASHI"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "YUHUASHI"."SYS_IMPORT_FULL_01": yuhuashi/******** directory=dump_dir dumpfile=YUHUASHI20180709.DMP logfile=yuhuashi.log Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"YUHUASHI" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAJob "YUHUASHI"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Jul 9 14:00:04 2018 elapsed 0 00:00:05
10.查询yuhuashi表中的记录，与源数据库中的记录数一致。
SQL> conn yuhuashi/testConnected.SQL> select count(*) from test1; COUNT(*)---------- 82162SQL> select count(*) from test2; COUNT(*)---------- 80220SQL> select count(*) from test3; COUNT(*)---------- 2811.将用户yuhuashi的缺省表空间修改为yuhuashi
SQL> alter user yuhuashi default tablespace yuhuashi;User altered.SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='YUHUASHI';USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE------------------------------ ------------------------------YUHUASHI YUHUASHI TEMP
转载地址：http://ychji.baihongyu.com/