【TTS】传输表空间Linux asm -> AIX asm
一.1 BLOG文档结构图
一.2 前言部分
一.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 异构平台下传输表空间的实施
② 传输表空间基于表空间的read only和rman2种方式
③ 平台字节序、自包含概念
④ expdp/impdp的应用
Tips：
① 若文章代码格式有错乱，推荐使用QQ或360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
② 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZFXDESKDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZFXDESKDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
一.2.2 相关参考文章链接
其他异构平台迁移的一些文章参考：
【推荐】 oracle 异构平台迁移之传输表空间一例 http://blog.itpub.net/26736162/viewspace-1391913/
【推荐】 oracle 传输表空间一例 http://blog.itpub.net/26736162/viewspace-1375260/
【推荐】 利用rman来实现linux平台数据库复制到windows平台数据库 http://blog.itpub.net/26736162/viewspace-1352436/
【推荐】 直接复制数据文件实现linux平台数据库复制到windows平台数据库 http://blog.itpub.net/26736162/viewspace-1352243/
一.3 相关知识点扫盲
可传输表空间的特性主要用于进行库对库的表空间复制，要进行传输的表空间必须置于read-only模式。如果生产库不允许表空间置为只读模式，没关系，方法还是有的，通过RMAN备份也可以创建可传输表空间集。要使用可传输表空间的特性，oracle至少是8i企业版或更高版本。如果是相同操作系统平台相互导入，则8i及以上版本均可支持，但如果是不同操作系统平台，数据库版本至少10g。被传输的表空间即可以是字典管理，也可以是本地管理。并且自oracle9i开始，被传输表空间的block size可以与目标数据库的block size不同。
可传输表空间(还有个集)最大的优势是其速度比export/import或unload/load要快的多。因为可传输表空间主要是复制数据文件到目标路径，然后再使用export/import或Data Pump export/import等应用仅导出/导入表空间对象的元数据到新数据库。
关于可传输表空间，还有个集(Transportable Tablespace Sets)的创建，其中都提到了很重要一点，就是被传输的表空间在传输过程中必须置为 read-only。而在实际操作过程中，对于某些生产数据库，将表空间置为 read-only 是件非常复杂的事情甚至完全不允许，有了 RMAN 的 Transportable Tablespace，这一切都得以避免。RMAN 通过备份创建可传输表空间集，它并不需要存取活动的数据文件，相应也就不需要将表空间置为 read-only。因此，数据库可用性得到提升，尤其对于超大的表空间，因为被传输的表空间在此期间仍可进行读写操作，而且把表空间置为 read-only 模式可能会花费较长时间，
使用 RMAN 创建可传输表空间集，允许你在传输过程中指定目标恢复时间点或 SCN，这样传输的数据可以更灵活，不必完全复制现有表空间，只要备份中存在，你就可以选择性的恢复数据。例如，你的备份策略为保留一周，你希望创建的可传输表空间中数据是截止本月底最后一天的数据，那么你在下个月第一周内任何时候都可以进行传输操作而不需要考虑这期间生产库是否会有写入操作。
一.3.1 注意事项
☞ 注意：
① source和target database的数据库版本最好一致，否则会因为db time zone 不一致导致报如下错误，但是如果source大于等于target的话是可以的，向下兼容的
ORA-39002: invalid operation
ORA-39322: Cannot use transportabletablespace with timestamp with timezone columns and different timezone version.
② source和target端的字符集必须一致，例如如下情况报错：
source为 ZHS16GBK，target为AL32UTF8
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Tartget db char set AL32UTF8 is not a superset of ZHS16GBK.
Failed to plug in a tablespace due to incompatible
database character set"AL32UTF8" and
transportable set database character set "ZHS16GBK"
③ source和target database的compatible 参数最好一致，但source如果小于等于target端的话是可以的，例如source为11.2.0.4.0，target为11.2.0.0.0就不行，impdp的时候报错：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 11.2.0.4.0 cannot be used by release 11.2.0.0.0
一.4 实验部分
一.4.1 实验环境介绍
项目 |
source db |
target db |
db 类型 |
单实例 |
单实例 |
db version |
11.2.0.3 |
11.2.0.4 |
db 存储 |
ASM |
ASM |
ORACLE_SID |
orclasm |
ora2lhr |
db_name |
orclasm |
ora2lhr |
主机IP地址： |
192.168.59.30 |
22.188.194.66 |
OS版本及kernel版本 |
RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
AIX 64位 7.1.0.0 |
OS hostname |
rhel6_lhr |
ZFXDESKDB2 |
platform_name |
Linux x86 64-bit |
AIX-Based Systems (64-bit) |
db time zone |
14 |
14 |
字符集 |
ZHS16GBK |
ZHS16GBK |
compatible |
11.2.0.0.0 |
11.2.0.4.0 |
归档模式 |
Archive Mode |
Archive Mode |
一.4.2 实验目标
要实现将自定义的应用程序表空间app1tbs,app2tbs,idxtbs从源平台传递到目标平台，而在实际的工作过程中，需要将AIX上的数据库迁移到Linux，或者将Linux上的数据库迁移到AIX上，除了exp/imp和expdp/impdp外，最常用的就是传输表空间了，若是整个库迁移的话，我们需要做的就是把业务用户和业务表空间的数据迁移过来就行，Undo、temp、system等等的就不用迁移了，整个处理过程和本文档的处理过程大同小异，需要关注的是业务对象的个数、大小、状态等。
一.4.3 实验过程
-------------------------------------------------------------------------------------------------------------
一.5 source端环境准备
一.5.1 在源库上创建3个用户应用的表空间
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期日 1月 31 23:34:27 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
23:34:27 SQL> create tablespace app1tbs datafile '+DATA' size 10m;
表空间已创建。
已用时间: 00: 00: 07.60
23:34:42 SQL> create tablespace app2tbs datafile '+DATA' size 10m;
表空间已创建。
已用时间: 00: 00: 27.25
23:35:53 SQL> create tablespace idxtbs datafile '+DATA' size 10m;
表空间已创建。
已用时间: 00: 00: 09.45
23:36:09 SQL> set line 9999 pagesize 9999
23:36:12 SQL> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;
NAME NAME
----------------------- -------------------------------------------------------------------------------
SYSTEM +DATA/orclasm/datafile/system.256.850260145
SYSAUX +DATA/orclasm/datafile/sysaux.257.850260145
UNDOTBS1 +DATA/orclasm/datafile/undotbs1.258.851526539
USERS +DATA/orclasm/datafile/users.259.850260147
EXAMPLE +DATA/orclasm/datafile/example.265.850260295
APP1TBS +DATA/orclasm/datafile/app1tbs.268.902619275
APP2TBS +DATA/orclasm/datafile/app2tbs.280.902619327
TS_LHR +DATA/orclasm/datafile/ts_lhr.269.852632495
ENCRYPTED_TS +DATA/orclasm/datafile/encrypted_ts.272.854650889
GOLDENGATE +DATA/orclasm/datafile/goldengate.273.862829891
IDXTBS +DATA/orclasm/datafile/idxtbs.281.902619361
TS_LHR +DATA/orclasm/datafile/ts_lhr.284.869738273
USERS +FRA/orclasm/datafile/users.449.880121199
SYSTEM +FRA/orclasm/datafile/system.349.880121287
已选择14行。
已用时间: 00: 00: 00.80
23:36:21 SQL>
一.5.2 在相应的表空间创建表和索引
23:36:21 SQL> create user user_app1 identified by user_app1 default tablespace app1tbs;
用户已创建。
已用时间: 00: 00: 00.14
23:40:13 SQL> create user user_app2 identified by user_app2 default tablespace app1tbs;
用户已创建。
已用时间: 00: 00: 00.35
23:43:51 SQL> create user user_app2 identified by user_app2 default tablespace app2tbs;
用户已创建。
已用时间: 00: 00: 02.72
23:43:56 SQL> grant connect,resource to user_app1;
授权成功。
已用时间: 00: 00: 00.06
23:44:50 SQL> grant connect,resource to user_app2;
授权成功。
已用时间: 00: 00: 00.00
23:44:52 SQL> create table user_app1.app1_tab tablespace app1tbs as select * from scott.emp;
表已创建。
已用时间: 00: 00: 01.02
23:45:09 SQL> create table user_app2.app2_tab tablespace app2tbs as select * from scott.dept;
表已创建。
已用时间: 00: 00: 00.23
23:45:27 SQL> create index user_app1.idx_emp_ename on user_app1.app1_tab(ename) tablespace idxtbs;
索引已创建。
已用时间: 00: 00: 00.25
23:45:51 SQL> create index user_app2.idx_dept_dname on user_app2.app2_tab(dname) tablespace idxtbs;
索引已创建。
已用时间: 00: 00: 00.01
23:46:13 SQL>
一.6 判断平台支持并确定字节序
如果传输表空间集到不同的平台，则要确定对于源和目标平台这种跨平台表空间被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要进行转化，否则必须做一个表空间集转化，在源端或目标端。
一.6.1 在源平台查询
23:46:13 SQL> col platform_name for a40
23:48:55 SQL> 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
---------------------------------------- --------------
Linux x86 64-bit Little
已用时间: 00: 00: 00.19
23:49:13 SQL>
结论：当前的系统平台支持跨平台表空间传输（因为上面的查询有记录返回）
一.6.2 在目标平台查询
[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr
[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 1 13:47:14 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ora2lhr> col platform_name for a40
SYS@ora2lhr> 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
结论： 当前的AIX平台支持跨平台的表空间传输
源平台和目标平台的Endian_format 不同，source端为Little，target端为Big，所以需要进行表空间集转换，前边说过在源端或目标端都可以进行转换，这里我们选择在目标端来进行转换。
一.7 选择自包含的表空间集
一.7.1 进行检查
Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.
先试试要传输app1tbs和idxtbs这2个表空间：
SQL> execute dbms_tts.transport_set_check('app1tbs,idxtbs',true);
PL/SQL procedure successfully completed.
一.7.2 查看检查结果
SQL> col violations for a70
23:50:53 SQL> select * from transport_set_violations;
VIOLATIONS
-------------------------------------------------------------------------------------------------------------------------------------------
ORA-39907: 索引 USER_APP2.IDX_DEPT_DNAME (在表空间 IDXTBS 中) 指向表 USER_APP2.APP2_TAB (在表空间 APP2TBS 中)。
已用时间: 00: 00: 00.18
23:51:14 SQL>
结论： 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的USER_APP2.APP2_TAB表，所以这里选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查
23:51:14 SQL> execute dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 07.24
23:52:14 SQL> select * from transport_set_violations;
未选定行
已用时间: 00: 00: 00.00
23:52:54 SQL>
结论： 此时这个表空间集已经不再违背自包含的条件，可以确定为一个可传输表空间集。在实际生产环境中也是如此检查的，若是全库迁移，得把需要迁移的表空间修改为自包含的。
一.8 产生可传输表空间集
一.8.1 使自包含的表空间集中的所有表空间变为只读状态
23:52:54 SQL> alter tablespace app1tbs read only;
表空间已更改。
已用时间: 00: 00: 00.36
23:54:31 SQL> alter tablespace app2tbs read only;
表空间已更改。
已用时间: 00: 00: 00.15
23:54:42 SQL> alter tablespace idxtbs read only;
表空间已更改。
已用时间: 00: 00: 00.14
23:54:48 SQL>
一.8.2 使用数据泵导出工具，导出要传输的各个表空间的元数据
一.8.2.1 确定导出目录
23:55:51 SQL> set line 9999
23:56:07 SQL> col directory_name for a28
23:56:07 SQL> col directory_path for a100
23:56:07 SQL> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
---------------------------- ----------------------------------------------------------------------------------------------------
OSDESC /home/oracle/
ASMSRC +DATA/orclasm/datafile/
DIR_ALERT /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
SCHEDULER$_WALLET_DIR /u01/app/oracle/product/11.2.0/dbhome_1/scheduler/wallet
TMP_HF_DIR +DATA/orclasm/datafile/
FY_DATA_DIR /tmp
REPDIR /oradata06/repdir
DIR_ALERT_CHECKHELTH_LHR_1 /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
已选择16行。
已用时间: 00: 00: 00.01
23:56:08 SQL>
一.8.2.2 开始导出
[oracle@rhel6_lhr ~]$ env | grep ORACLE
ORACLE_SID=orclasm
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rhel6_lhr ~]$ expdp \'/ as sysdba\' dumpfile=expdat_20160131.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs transport_full_check=y logfile=tts_export_20160131.log
Export: Release 11.2.0.3.0 - Production on 星期一 2月 1 00:03:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
启动 "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=expdat_20160131.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs transport_full_check=y logfile=tts_export_20160131.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
/u01/app/oracle/admin/orclasm/dpdump/expdat_20160131.dmp
******************************************************************************
可传输表空间 APP1TBS 所需的数据文件:
+DATA/orclasm/datafile/app1tbs.268.902619275
可传输表空间 APP2TBS 所需的数据文件:
+DATA/orclasm/datafile/app2tbs.280.902619327
可传输表空间 IDXTBS 所需的数据文件:
+DATA/orclasm/datafile/idxtbs.281.902619361
作业 "SYS"."SYS_EXPORT_TRANSPORTABLE_01" 已于 00:07:22 成功完成
[oracle@rhel6_lhr ~]$
查看文件：
[oracle@rhel6_lhr ~]$ cd /u01/app/oracle/admin/orclasm/dpdump/
[oracle@rhel6_lhr dpdump]$ ll
total 13536
-rw-r----- 1 oracle asmadmin 110592 Feb 1 00:07 expdat_20160131.dmp
-rw-r--r-- 1 oracle asmadmin 1450 Feb 1 00:07 tts_export_20160131.log
[oracle@rhel6_lhr dpdump]$
告警日志可以看到：
Sun Dec 21 17:48:50 2014
DM00 started with pid=45, OS id=13188, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01
Sun Dec 21 17:48:56 2014
DW00 started with pid=46, OS id=13190, wid=1, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01
Sun Dec 21 17:49:15 2014
XDB installed.
XDB initialized.
一.8.3 生成数据文件
[root@rhel6_lhr ~]# su - grid
[grid@rhel6_lhr ~]$ asmcmd
ASMCMD> cd +DATA/orclasm/datafile/
ASMCMD> ls
APP1TBS.274.866911939
APP2TBS.275.866912075
ENCRYPTED_TS.272.854650889
EXAMPLE.265.850260295
GOLDENGATE.273.862829891
IDXTBS.276.866912133
SYSAUX.257.850260145
SYSTEM.256.850260145
TBS_RC.268.852116523
TS_LHR.269.852632495
UNDOTBS1.258.851526539
UNDOTBS2.267.851204361
USERS.259.850260147
example.265.850260295_bk
ASMCMD> cp APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdump
copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939
ASMCMD-8016: copy source->'+DATA/orclasm/datafile/APP1TBS.274.866911939' and target->'/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' failed
ORA-19505: failed to identify file "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-15120: ASM file name '/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
无权限，暂时拷贝到grid目录下：
ASMCMD> cp APP1TBS.274.866911939 /home/grid
copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /home/grid/APP1TBS.274.866911939
ASMCMD> cp APP2TBS.275.866912075 /home/grid
copying +DATA/orclasm/datafile/APP2TBS.275.866912075 -> /home/grid/APP2TBS.275.866912075
ASMCMD> cp IDXTBS.276.866912133 /home/grid
copying +DATA/orclasm/datafile/IDXTBS.276.866912133 -> /home/grid/IDXTBS.276.866912133
ASMCMD>
[grid@rhel6_lhr ~]$ asmcmd
ASMCMD> cd +DATA/orclasm/datafile/
ASMCMD> ls
APP1TBS.268.902619275
APP2TBS.280.902619327
ENCRYPTED_TS.272.854650889
EXAMPLE.265.850260295
GOLDENGATE.273.862829891
IDXTBS.281.902619361
SYSAUX.257.850260145
SYSTEM.256.850260145
TS_LHR.269.852632495
TS_LHR.284.869738273
UNDOTBS1.258.851526539
USERS.259.850260147
example.265.850260295_bk
ASMCMD> rm -rf example.265.850260295_bk
ASMCMD> ls
APP1TBS.268.902619275
APP2TBS.280.902619327
ENCRYPTED_TS.272.854650889
EXAMPLE.265.850260295
GOLDENGATE.273.862829891
IDXTBS.281.902619361
SYSAUX.257.850260145
SYSTEM.256.850260145
TS_LHR.269.852632495
TS_LHR.284.869738273
UNDOTBS1.258.851526539
USERS.259.850260147
ASMCMD> cp APP1TBS.268.902619275 /u01/app/oracle/admin/orclasm/dpdump
copying +DATA/orclasm/datafile/APP1TBS.268.902619275 -> /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.268.902619275
ASMCMD-8016: copy source->'+DATA/orclasm/datafile/APP1TBS.268.902619275' and target->'/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.268.902619275' failed
ORA-19505: failed to identify file "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.268.902619275"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-15120: ASM file name '/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.268.902619275' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
ASMCMD> cp APP1TBS.268.902619275 /home/grid
copying +DATA/orclasm/datafile/APP1TBS.268.902619275 -> /home/grid/APP1TBS.268.902619275
ASMCMD> cp APP2TBS.280.902619327 /home/grid
copying +DATA/orclasm/datafile/APP2TBS.280.902619327 -> /home/grid/APP2TBS.280.902619327
ASMCMD> cp IDXTBS.281.902619361 /home/grid
copying +DATA/orclasm/datafile/IDXTBS.281.902619361 -> /home/grid/IDXTBS.281.902619361
ASMCMD>
然后利用root用户将数据文件和expdp出来的源数据再拷贝到同一个目录下：
[root@rhel6_lhr ~]# ll /home/grid
total 209832
-rw-r--r-- 1 grid oinstall 316 Jan 5 2015 aa.txt
-rw-r--r--. 1 grid oinstall 244 Jun 23 2014 a.ora
-rw-r----- 1 grid oinstall 10493952 Feb 1 00:22 APP1TBS.268.902619275
-rw-r----- 1 grid oinstall 52436992 Dec 21 2014 APP1TBS.274.866911939
-rw-r----- 1 grid oinstall 52436992 Dec 21 2014 APP2TBS.275.866912075
-rw-r----- 1 grid oinstall 10493952 Feb 1 00:22 APP2TBS.280.902619327
-rw-r----- 1 grid oinstall 52436992 Dec 21 2014 IDXTBS.276.866912133
-rw-r----- 1 grid oinstall 10493952 Feb 1 00:22 IDXTBS.281.902619361
drwxr-xr-x. 3 grid oinstall 4096 Jun 14 2014 oradiag_grid
-rw-r-----. 1 grid oinstall 3584 Jul 1 2014 spfileorclasm.ora
-rw-r----- 1 grid oinstall 2105344 Jan 13 2015 testdg.dbf
-rw-r----- 1 grid oinstall 23950848 Dec 24 2014 thread_1_seq_754.333.865158557
[root@rhel6_lhr ~]# cp /home/grid/APP1TBS.268.902619275 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr ~]# cp /home/grid/APP2TBS.280.902619327 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr ~]# cp /home/grid/IDXTBS.281.902619361 /u01/app/oracle/admin/orclasm/dpdump/
[root@rhel6_lhr ~]#
[root@rhel6_lhr ~]# ll /u01/app/oracle/admin/orclasm/dpdump/
total 40540
-rw-r----- 1 root root 10493952 Feb 1 00:28 APP1TBS.268.902619275
-rw-r----- 1 root root 10493952 Feb 1 00:28 APP2TBS.280.902619327
-rw-r----- 1 oracle asmadmin 110592 Feb 1 00:07 expdat_20160131.dmp
-rw-r----- 1 root root 10493952 Feb 1 00:28 IDXTBS.281.902619361
-rw-r--r-- 1 oracle asmadmin 1450 Feb 1 00:07 tts_export_20160131.log
[root@rhel6_lhr ~]#
一.9 还原源库中的表空间为读/写模式
[oracle@rhel6_lhr dpdump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 2月 1 00:27:13 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
00:27:13 SQL> alter tablespace app1tbs read write;
表空间已更改。
已用时间: 00: 00: 01.19
00:27:16 SQL> alter tablespace app2tbs read write;
表空间已更改。
已用时间: 00: 00: 00.11
00:27:22 SQL> alter tablespace idxtbs read write;
表空间已更改。
已用时间: 00: 00: 00.17
00:27:31 SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options 断开
[oracle@rhel6_lhr dpdump]$
一.10 传输数据文件和元数据到target端
这里需要传输转储元文件和数据文件到目标库
一.10.1 传输转储元文件到目标库
一.10.2 查看目标库数据文件位置和目录
[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr
[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 1 14:53:49 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ora2lhr> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------
+DATA/ora2lhr/datafile/system.335.902674033
+DATA/ora2lhr/datafile/sysaux.336.902674033
+DATA/ora2lhr/datafile/undotbs1.337.902674033
+DATA/ora2lhr/datafile/users.338.902674033
+DATA/ora2lhr/datafile/example.348.902674109
SYS@ora2lhr> set line 9999
SYS@ora2lhr> col directory_name for a28
SYS@ora2lhr> col directory_path for a100
SYS@ora2lhr> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
---------------------------- ----------------------------------------------------------------------------------------------------
SUBDIR /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep
SS_OE_XMLDIR /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry/
LOG_FILE_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/log/
MEDIA_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/product_media/
DATA_FILE_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/sales_history/
XMLDIR /oracle/app/oracle/product/11.2.0/db/rdbms/xml
ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/db/ccr/state
DATA_PUMP_DIR /oracle/app/oracle/admin/ora2lhr/dpdump/
ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/db/ccr/state
9 rows selected.
SYS@ora2lhr>
SYS@ora2lhr>
一.10.3 拷贝文件到目标库相应位置并修改权限
将表空间文件和元数据文件拷贝到/oracle/app/oracle/admin/ora2lhr/dpdump/ 下，如下：
[ZFXDESKDB2:root]:/>cd /oracle/app/oracle/admin/ora2lhr/dpdump
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>l
total 123424
-rw-r----- 1 root system 10493952 Feb 01 00:28 APP1TBS.268.902619275
-rw-r----- 1 root system 10493952 Feb 01 00:28 APP2TBS.280.902619327
-rw-r----- 1 root system 10493952 Feb 01 00:28 IDXTBS.281.902619361
-rw-r----- 1 root system 10493952 Feb 01 01:02 IDXTBS.dbf
-rw-r----- 1 root system 10493952 Feb 01 01:01 app1tbs.dbf
-rw-r----- 1 root system 10493952 Feb 01 01:01 app2tbs.dbf
-rw-r----- 1 root system 110592 Feb 01 00:07 expdat_20160131.dmp
-rw-r----- 1 root system 110592 Feb 01 00:58 expdat_20160131_2.dmp
-rw-r----- 1 root system 1452 Feb 01 00:58 expdat_20160131_2.log
-rw-r----- 1 root system 1450 Feb 01 00:07 tts_export_20160131.log
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>chown oracle:dba ./*
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>l
total 123424
-rw-r----- 1 oracle dba 10493952 Feb 01 00:28 APP1TBS.268.902619275
-rw-r----- 1 oracle dba 10493952 Feb 01 00:28 APP2TBS.280.902619327
-rw-r----- 1 oracle dba 10493952 Feb 01 00:28 IDXTBS.281.902619361
-rw-r----- 1 oracle dba 10493952 Feb 01 01:02 IDXTBS.dbf
-rw-r----- 1 oracle dba 10493952 Feb 01 01:01 app1tbs.dbf
-rw-r----- 1 oracle dba 10493952 Feb 01 01:01 app2tbs.dbf
-rw-r----- 1 oracle dba 110592 Feb 01 00:07 expdat_20160131.dmp
-rw-r----- 1 oracle dba 110592 Feb 01 00:58 expdat_20160131_2.dmp
-rw-r----- 1 oracle dba 1452 Feb 01 00:58 expdat_20160131_2.log
-rw-r----- 1 oracle dba 1450 Feb 01 00:07 tts_export_20160131.log
一.11 target端转换字节序
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>su - oracle
[ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=ora2lhr
[ZFXDESKDB2:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 1 14:58:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA2LHR (DBID=4055514164)
RMAN>
RMAN> CONVERT DATAFILE
2> "/oracle/app/oracle/admin/ora2lhr/dpdump/APP1TBS.268.902619275",
3> "/oracle/app/oracle/admin/ora2lhr/dpdump/APP2TBS.280.902619327",
4> "/oracle/app/oracle/admin/ora2lhr/dpdump/IDXTBS.281.902619361"
5> TO PLATFORM="AIX-Based Systems (64-bit)"
6> FROM PLATFORM="Linux x86 64-bit"
7> FORMAT '+DATA';
Starting conversion at target at 2016-02-01 15:00:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=222 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/APP1TBS.268.902619275
converted datafile=+DATA/ora2lhr/datafile/app1tbs.350.902674809
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/APP2TBS.280.902619327
converted datafile=+DATA/ora2lhr/datafile/app2tbs.351.902674809
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/oracle/app/oracle/admin/ora2lhr/dpdump/IDXTBS.281.902619361
converted datafile=+DATA/ora2lhr/datafile/idxtbs.352.902674811
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2016-02-01 15:00:11
RMAN>
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>
[ZFXDESKDB2:root]:/oracle/app/oracle/admin/ora2lhr/dpdump>su - grid
[ZFXDESKDB2:grid]:/home/grid>asmcmd
ASMCMD> cd +DATA/ora2lhr/datafile/
ASMCMD> ls
APP1TBS.350.902674809
APP2TBS.351.902674809
EXAMPLE.348.902674109
IDXTBS.352.902674811
SYSAUX.336.902674033
SYSTEM.335.902674033
UNDOTBS1.337.902674033
USERS.338.902674033
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE FEB 01 15:00:00 Y APP1TBS.350.902674809
DATAFILE UNPROT COARSE FEB 01 15:00:00 Y APP2TBS.351.902674809
DATAFILE UNPROT COARSE FEB 01 14:00:00 Y EXAMPLE.348.902674109
DATAFILE UNPROT COARSE FEB 01 15:00:00 Y IDXTBS.352.902674811
DATAFILE UNPROT COARSE FEB 01 14:00:00 Y SYSAUX.336.902674033
DATAFILE UNPROT COARSE FEB 01 14:00:00 Y SYSTEM.335.902674033
DATAFILE UNPROT COARSE FEB 01 14:00:00 Y UNDOTBS1.337.902674033
DATAFILE UNPROT COARSE FEB 01 14:00:00 Y USERS.338.902674033
ASMCMD>
一.12 开始导入
一.12.1 创建source库的2个用户并赋权限
如果不创建用户会报如下的错误：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_APP1 does not exist in the database
[ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 1 15:07:32 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ora2lhr> create user user_app1 identified by user_app1;
User created.
SYS@ora2lhr> create user user_app2 identified by user_app2;
User created.
SYS@ora2lhr> grant connect , resource to user_app1;
Grant succeeded.
SYS@ora2lhr> grant connect , resource to user_app2;
Grant succeeded.
SYS@ora2lhr> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[ZFXDESKDB2:oracle]:/oracle>
一.12.2 开始导入
[ZFXDESKDB2:oracle]:/oracle>impdp \'/ as sysdba \' DUMPFILE=expdat_20160131.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/ora2lhr/datafile/APP1TBS.350.902674809','+DATA/ora2lhr/datafile/app2tbs.351.902674809','+DATA/ora2lhr/datafile/idxtbs.352.902674811' LOGFILE=impdp_tts_20160131.log
Import: Release 11.2.0.4.0 - Production on Mon Feb 1 15:08:24 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=expdat_20160131.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/ora2lhr/datafile/APP1TBS.350.902674809,+DATA/ora2lhr/datafile/app2tbs.351.902674809,+DATA/ora2lhr/datafile/idxtbs.352.902674811 LOGFILE=impdp_tts_20160131.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Feb 1 15:08:32 2016 elapsed 0 00:00:06
[ZFXDESKDB2:oracle]:/oracle>
日志：
Plug in tablespace APP1TBS with datafile
'+DATA/ora2lhr/datafile/APP1TBS.350.902674809'
Plug in tablespace APP2TBS with datafile
'+DATA/ora2lhr/datafile/app2tbs.351.902674809'
Plug in tablespace IDXTBS with datafile
'+DATA/ora2lhr/datafile/idxtbs.352.902674811'
一.12.3 查看目标平台信息
SYS@ora2lhr> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
APP1TBS READ ONLY
APP2TBS READ ONLY
IDXTBS READ ONLY
9 rows selected.
SYS@ora2lhr> alter tablespace APP1TBS read write;
Tablespace altered.
SYS@ora2lhr> alter tablespace APP2TBS read write;
Tablespace altered.
SYS@ora2lhr> alter tablespace IDXTBS read write;
Tablespace altered.
SYS@ora2lhr> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
APP1TBS ONLINE
APP2TBS ONLINE
IDXTBS ONLINE
9 rows selected.
SYS@ora2lhr>
SYS@ora2lhr> SELECT d.username,d.default_tablespace FROM dba_users d where d.username like 'USER_%' ;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
USER_APP2 USERS
USER_APP1 USERS
SYS@ora2lhr> alter user user_app1 default tablespace app1tbs;
User altered.
SYS@ora2lhr> alter user user_app2 default tablespace app2tbs;
User altered.
SYS@ora2lhr> SELECT d.username,d.default_tablespace FROM dba_users d where d.username like 'USER_%' ;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
USER_APP2 APP2TBS
USER_APP1 APP1TBS
SYS@ora2lhr>
一.13 查看导入后结果
SYS@ora2lhr> set line 9999 pagesize 9999
SYS@ora2lhr> select * from user_app1.app1_tab;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
12 rows selected.
SYS@ora2lhr> select * from user_app2.app2_tab;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SYS@ora2lhr> select D.owner,D.index_name,D.table_name,D.tablespace_name from dba_indexes d WHERE d.table_name in ('APP1_TAB','APP2_TAB');
OWNER INDEX_NAME TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
USER_APP2 IDX_DEPT_DNAME APP2_TAB IDXTBS
USER_APP1 IDX_EMP_ENAME APP1_TAB IDXTBS
SYS@ora2lhr> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;
NAME NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM +DATA/ora2lhr/datafile/system.335.902674033
SYSAUX +DATA/ora2lhr/datafile/sysaux.336.902674033
UNDOTBS1 +DATA/ora2lhr/datafile/undotbs1.337.902674033
USERS +DATA/ora2lhr/datafile/users.338.902674033
EXAMPLE +DATA/ora2lhr/datafile/example.348.902674109
APP1TBS +DATA/ora2lhr/datafile/app1tbs.350.902674809
APP2TBS +DATA/ora2lhr/datafile/app2tbs.351.902674809
IDXTBS +DATA/ora2lhr/datafile/idxtbs.352.902674811
8 rows selected.
SYS@ora2lhr>
至此说明3个表空间已经完全由Linux平台迁移到AIX平台上。