【TTS】传输表空间AIX asm -> linux asm
一.1 BLOG文档结构图
一.2 前言部分
一.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 异构平台下传输表空间的实施
② 传输表空间基于表空间的read only和rman2种方式
③ 平台字节序、自包含概念
④ expdp/impdp的应用
Tips：
① 若文章代码格式有错乱，推荐使用搜狗或360浏览器，也可以下载pdf格式的文档来查看
② 本篇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
一.2.2 相关参考文章链接
其他异构平台迁移的一些文章参考：
【推荐】 oracle 异构平台迁移之传输表空间一例
【推荐】 oracle 传输表空间一例
【推荐】 利用rman来实现linux平台数据库复制到windows平台数据库
【推荐】 直接复制数据文件实现linux平台数据库复制到windows平台数据库
【TTS】传输表空间Linux asm -> AIX asm
【TTS】传输表空间Linux asm -> AIX asm 基于rman
一.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.3 |
|
db 存储 |
ASM |
ASM |
|
ORACLE_SID |
ora11g |
orclasm |
|
db_name |
ora11g |
orclasm |
|
主机IP地址 |
22.188.139.33 |
192.168.59.30 |
|
OS版本及kernel版本 |
AIX 64位 5.3.0.0 |
RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
|
OS hostname |
ZFXDESKDB2 |
rhel6_lhr |
|
platform_name |
AIX-Based Systems (64-bit) |
Linux x86 64-bit |
|
db time zone |
14 |
14 |
|
字符集 |
ZHS16GBK |
ZHS16GBK |
|
compatible |
11.2.0.0.0 |
11.2.0.0.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@ZDMTRAIN2:/oracle$ echo $ORACLE_SID
ora11g
oracle@ZDMTRAIN2:/oracle$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 18 10:51:00 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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@ora11g> select name from v$datafile;
NAME
--------------------------------------------------
+DATA1/ora11g/datafile/system.379.936264859
+DATA1/ora11g/datafile/sysaux.378.936264861
+DATA1/ora11g/datafile/undotbs1.380.936264861
+DATA1/ora11g/datafile/users.382.936264861
+DATA1/ora11g/datafile/example.391.936264979
SYS@ora11g> create tablespace app1tbs DATAFILE '+DATA1' size 10m;
Tablespace created.
SYS@ora11g> create tablespace app2tbs DATAFILE '+DATA1' size 10m;
Tablespace created.
SYS@ora11g> CREATE TABLESPACE IDXTBS DATAFILE '+DATA1' SIZE 10M;
Tablespace created.
SYS@ora11g> create user user_app1 identified by user_app1 default tablespace app1tbs;
User created.
SYS@ora11g> create user user_app2 identified by user_app2 default tablespace app2tbs;
User created.
SYS@ora11g> grant connect , resource to user_app1;
Grant succeeded.
SYS@ora11g> grant connect , resource to user_app2;
Grant succeeded.
SYS@ora11g> create table user_app1.app1_tab tablespace app1tbs as select * from scott.emp;
Table created.
SYS@ora11g> create table user_app2.app2_tab tablespace app2tbs as select * from scott.dept;
Table created.
SYS@ora11g> create index user_app1.idx_emp_ename on user_app1.app1_tab(ename) tablespace idxtbs;
Index created.
SYS@ora11g> create index user_app2.idx_dept_dname on user_app2.app2_tab(dname) tablespace idxtbs;
Index created.
SYS@ora11g> set line 9999 pagesize 9999
SYS@ora11g> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;
NAME NAME
-------------- -----------------------------------------------
SYSTEM +DATA1/ora11g/datafile/system.379.936264859
SYSAUX +DATA1/ora11g/datafile/sysaux.378.936264861
UNDOTBS1 +DATA1/ora11g/datafile/undotbs1.380.936264861
USERS +DATA1/ora11g/datafile/users.382.936264861
EXAMPLE +DATA1/ora11g/datafile/example.391.936264979
APP1TBS +DATA1/ora11g/datafile/app1tbs.393.936269553
APP2TBS +DATA1/ora11g/datafile/app2tbs.394.936269559
IDXTBS +DATA1/ora11g/datafile/idxtbs.395.936269565
8 rows selected.
SYS@ora11g>
一.6 判断平台支持并确定字节序
如果传输表空间集到不同的平台，则要确定对于源和目标平台这种跨平台表空间被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要进行转化，否则必须做一个表空间集转化，在源端或目标端都可以，在源端用convert tablespace，在目标端用convert datafile。
SYS@ora11g> col platform_name for a40
SYS@ora11g> select tp.platform_name, tp.endian_format
2 from v$transportable_platform tp
3 where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- ----------------------------
AIX-Based Systems (64-bit) Big
Linux x86 64-bit Little
SQL>
源平台和目标平台的Endian_format 不同，source端为Big，target端为Little，所以需要进行表空间集转换，前边说过在源端或目标端都可以进行转换，这里我们选择在目标端来进行转换。
一.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.
execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
select * from sys.transport_set_violations;
先试试要传输app1tbs和idxtbs这2个表空间：
SYS@ora11g> execute sys.dbms_tts.transport_set_check('app1tbs,idxtbs',true);
PL/SQL procedure successfully completed.
SQL> col violations for a70
SYS@ora11g> select * from sys.transport_set_violations;
VIOLATIONS
----------------------------------------------------------------------
ORA-39907: Index USER_APP2.IDX_DEPT_DNAME in tablespace IDXTBS points
to table USER_APP2.APP2_TAB in tablespace APP2TBS.
SQL>
结论： 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的user_app2.APP2_TAB表，所以这里选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查
SYS@ora11g> execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
PL/SQL procedure successfully completed.
SYS@ora11g> select * from sys.transport_set_violations;
no rows selected
SYS@ora11g>
结论： 此时这个表空间集已经不在违背自包含的条件，可以确定为一个可传输表空间集
一.8 产生可传输表空间集
一.8.1 使自包含的表空间集中的所有表空间变为只读状态
SYS@ora11g> alter tablespace app1tbs read only;
Tablespace altered.
SYS@ora11g> alter tablespace app2tbs read only;
Tablespace altered.
SYS@ora11g> alter tablespace idxtbs read only;
Tablespace altered.
SYS@ora11g> 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@ora11g>
一.8.2 使用数据泵导出工具，导出要传输的各个表空间的元数据
一.8.2.1 确定导出目录
SYS@ora11g> set line 9999
SYS@ora11g> col directory_name for a28
SYS@ora11g> col directory_path for a100
SYS@ora11g> 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/
XMLDIR /oracle/app/oracle/product/11.2.0/db/rdbms/xml
DATA_FILE_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/sales_history/
DATA_PUMP_DIR /oracle/app/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/db/ccr/state
8 rows selected.
SYS@ora11g>
一.8.2.2 开始导出
oracle@ZDMTRAIN2:/oracle$ expdp \'/ as sysdba \' dumpfile=expdat_tts_read_20160203.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=expdat_20160201.log
Export: Release 11.2.0.3.0 - Production on Sat Feb 18 11:03:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: 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
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=expdat_tts_read_20160203.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=expdat_20160201.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
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/oracle/app/oracle/admin/ora11g/dpdump/expdat_tts_read_20160203.dmp
******************************************************************************
Datafiles required for transportable tablespace APP1TBS:
+DATA1/ora11g/datafile/app1tbs.393.936269553
Datafiles required for transportable tablespace APP2TBS:
+DATA1/ora11g/datafile/app2tbs.394.936269559
Datafiles required for transportable tablespace IDXTBS:
+DATA1/ora11g/datafile/idxtbs.395.936269565
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:04:49
oracle@ZDMTRAIN2:/oracle$
查看文件，使用asmcmd中copy命令将数据文件copy到文件系统，需要给目录赋予权限：
root@ZDMTRAIN2:/# chmod 777 /oracle/app/oracle/admin/ora11g/dpdump
root@ZDMTRAIN2:/oracle/app/oracle/admin/ora11g/dpdump# l
total 224
-rw-r--r-- 1 oracle asmadmin 1597 Feb 18 11:04 expdat_20160201.log
-rw-r----- 1 oracle asmadmin 110592 Feb 18 11:04 expdat_tts_read_20160203.dmp
root@ZDMTRAIN2:/oracle/app/oracle/admin/ora11g/dpdump# su - grid
grid@ZDMTRAIN2:/home/grid$ asmcmd
ASMCMD> cd +DATA1/ora11g/datafile/
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE FEB 18 10:00:00 Y UNDOTBS1.380.936264861
DATAFILE UNPROT COARSE FEB 18 10:00:00 Y SYSAUX.378.936264861
DATAFILE UNPROT COARSE FEB 18 10:00:00 Y IDXTBS.395.936269565
DATAFILE UNPROT COARSE FEB 18 10:00:00 Y APP2TBS.394.936269559
DATAFILE UNPROT COARSE FEB 18 10:00:00 Y APP1TBS.393.936269553
DATAFILE UNPROT COARSE FEB 18 09:00:00 Y USERS.382.936264861
DATAFILE UNPROT COARSE FEB 18 09:00:00 Y SYSTEM.379.936264859
DATAFILE UNPROT COARSE FEB 18 09:00:00 Y EXAMPLE.391.936264979
ASMCMD> cp IDXTBS.395.936269565 /oracle/app/oracle/admin/ora11g/dpdump
copying +DATA1/ora11g/datafile/IDXTBS.395.936269565 -> /oracle/app/oracle/admin/ora11g/dpdump/IDXTBS.395.936269565
ASMCMD> cp APP2TBS.394.936269559 /oracle/app/oracle/admin/ora11g/dpdump
copying +DATA1/ora11g/datafile/APP2TBS.394.936269559 -> /oracle/app/oracle/admin/ora11g/dpdump/APP2TBS.394.936269559
ASMCMD> cp APP1TBS.393.936269553 /oracle/app/oracle/admin/ora11g/dpdump
copying +DATA1/ora11g/datafile/APP1TBS.393.936269553 -> /oracle/app/oracle/admin/ora11g/dpdump/APP1TBS.393.936269553
ASMCMD>
一.9 还原源库中的表空间为读/写模式
SYS@ora11g> alter tablespace app1tbs read write;
Tablespace altered.
SYS@ora11g> alter tablespace app2tbs read write;
Tablespace altered.
SYS@ora11g> alter tablespace idxtbs read write;
Tablespace altered.
SYS@ora11g> col tablespace_name format a20
SYS@ora11g> 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@ora11g>
至此，已和源库没有任何关系。
一.10 传输文件
这里需要传输转储元文件和数据文件到目标库
一.10.1 查看目标库数据文件位置和导入目录
[oracle@rhel6 ~]$ export ORACLE_SID=orclasm
[oracle@rhel6_lhr dpdump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 20:17:58 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
SQL> select name from v$datafile;
NAM
--------------------------------------------------------------------------------
+DATA/orclasm/datafile/system.256.868235071
+DATA/orclasm/datafile/sysaux.257.868235073
+DATA/orclasm/datafile/undotbs1.258.868235073
+DATA/orclasm/datafile/users.259.868235073
13 rows selected.
SQL> set line 9999
SQL> col directory_name for a28
SQL> col directory_path for a100
SQL> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------- ----------------------------------------------------------------------------------------------------
XMLDIR /ade/b/2125410156/oracle/rdbms/xml
DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
3 rows selected.
SQL>
一.10.2 利用ftp工具传输转储元文件和数据文件到目标库DATA_PUMP_DIR目录并修改权限
[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/orclasm/dpdump
[root@rhel6_lhr dpdump]# ll
total 30856
-rw-r--r-- 1 root root 10493952 Feb 18 2017 APP1TBS.393.936269553
-rw-r--r-- 1 root root 10493952 Feb 18 2017 APP2TBS.394.936269559
-rw-r--r-- 1 root root 1597 Feb 18 2017 expdat_20160201.log
-rw-r--r-- 1 root root 110592 Feb 18 2017 expdat_tts_read_20160203.dmp
-rw-r--r-- 1 root root 10493952 Feb 18 2017 IDXTBS.395.936269565
[root@rhel6_lhr dpdump]# chown oracle:dba *
[root@rhel6_lhr dpdump]# ll
total 30856
-rw-r--r-- 1 oracle dba 10493952 Feb 18 2017 APP1TBS.393.936269553
-rw-r--r-- 1 oracle dba 10493952 Feb 18 2017 APP2TBS.394.936269559
-rw-r--r-- 1 oracle dba 1597 Feb 18 2017 expdat_20160201.log
-rw-r--r-- 1 oracle dba 110592 Feb 18 2017 expdat_tts_read_20160203.dmp
-rw-r--r-- 1 oracle dba 10493952 Feb 18 2017 IDXTBS.395.936269565
[root@rhel6_lhr dpdump]#
一.11 转换字节序
转换字节序可以在sorce端进行也可以在target端进行，我们选择在target端执行：
[oracle@rhel6_lhr dpdump]$ rman target /
ase 11.2.0.3.0 - Production on 星期三 2月 3 20:22:52 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: ORCLASM (DBID=3424884828)
RMAN> CONVERT DATAFILE
2> "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.393.936269553",
3> "/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.394.936269559",
4> "/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.395.936269565"
5> TO PLATFORM="Linux x86 64-bit"
6> FROM PLATFORM="AIX-Based Systems (64-bit)"
7> FORMAT '+DATA';
启动 conversion at target 于 2016-02-03 20:22:54
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=10 设备类型=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: SID=11 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.393.936269553
通道 ORA_DISK_2: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.394.936269559
已转换的数据文件 = +DATA/orclasm/datafile/app1tbs.299.902866977
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:02
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.395.936269565
已转换的数据文件 = +DATA/orclasm/datafile/app2tbs.300.902866977
通道 ORA_DISK_2: 数据文件转换完毕, 经过时间: 00:00:01
已转换的数据文件 = +DATA/orclasm/datafile/idxtbs.301.902866979
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
完成 conversion at target 于 2016-02-03 20:23:01
RMAN>
一.12 target端开始导入数据
一.12.1 创建source库的2个用户并赋权限
如果不创建用户会报如下的错误：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_APP1 does not exist in the database
create user user_app1 identified by user_app1;
create user user_app2 identified by user_app2;
grant connect , resource to user_app1;
grant connect , resource to user_app2;
SYS@orclasm> create user user_app1 identified by user_app1;
User created.
SYS@orclasm> create user user_app2 identified by user_app2;
User created.
SYS@orclasm> grant connect , resource to user_app1;
Grant succeeded.
SYS@orclasm> grant connect , resource to user_app2;
Grant succeeded.
SYS@orclasm> exit
一.12.2 开始导入
导入元数据，注意这里的TRANSPORT_DATAFILES为转换后的新文件名称：
[oracle@rhel6_lhr dpdump]$ impdp \'/ as sysdba \' DUMPFILE=expdat_tts_read_20160203.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/orclasm/datafile/app1tbs.299.902866977','+DATA/orclasm/datafile/app2tbs.300.902866977','+DATA/orclasm/datafile/idxtbs.301.902866979' LOGFILE=impdp_tts_read_20160203.log
Import: Release 11.2.0.3.0 - Production on 星期三 2月 3 20:24:52 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_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=expdat_tts_read_20160203.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/orclasm/datafile/app1tbs.299.902866977,+DATA/orclasm/datafile/app2tbs.300.902866977,+DATA/orclasm/datafile/idxtbs.301.902866979 LOGFILE=impdp_tts_read_20160203.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于 20:25:20 成功完成
[oracle@rhel6_lhr dpdump]$
[oracle@rhel6 ~]$
一.12.3 查看target端表空间信息
[oracle@rhel6_lhr dpdump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 20:27:41 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
SYS@orclasm > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_LHR ONLINE
ENCRYPTED_TS ONLINE
GOLDENGATE ONLINE
APP1TBS READ ONLY
APP2TBS READ ONLY
IDXTBS READ ONLY
已选择12行。
SYS@orclasm >
SYS@orclasm > alter tablespace APP1TBS read write;
表空间已更改。
SYS@orclasm > alter tablespace APP2TBS read write;
表空间已更改。
SYS@orclasm > alter tablespace IDXTBS read write;
表空间已更改。
SYS@orclasm > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_LHR ONLINE
ENCRYPTED_TS ONLINE
GOLDENGATE ONLINE
APP1TBS ONLINE
APP2TBS ONLINE
IDXTBS ONLINE
已选择12行。
SYS@orclasm >
SYS@orclasm> set line 9999 pagesize 9999
SYS@orclasm> select * from scott.app1_tab;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
9000 lastwiner
9001 lastwiner
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择16行。
SYS@orclasm> select * from scott.app2_tab;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SYS@orclasm > 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_APP1 IDX_EMP_ENAME APP1_TAB IDXTBS
USER_APP2 IDX_DEPT_DNAME APP2_TAB IDXTBS
SYS@orclasm >
SYS@orclasm > 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.299.902866977
APP2TBS +DATA/orclasm/datafile/app2tbs.300.902866977
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.301.902866979
TS_LHR +DATA/orclasm/datafile/ts_lhr.284.869738273
USERS +FRA/orclasm/datafile/users.449.880121199
SYSTEM +FRA/orclasm/datafile/system.349.880121287
已选择14行。
SYS@orclasm >
至此说明3个表空间已经完全由AIX平台迁移到Linux平台上。
