oracle传输表空间技术测试和测试中遇到的问题

oracle的传输表空间技术支持，跨平台的表空间传输，线上生产环境使用传输表空间可以节省时间提高效率，因为传输表空间是先传输数据文件到目标库，然后使用exp/imp或者为expdp/impdp导入表空间的数据结构。

参考官方文档：http://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm#CHDEEEAG

跨平台迁移的限制
1.数据库必须是10g，10g以上的版本
2.数据库只能重低板本迁移到高版本，不能从高版本迁移到低板本
3.平台转换时，小平台跟小平台转，大平台只能跟大平台转
v$db_tracsportable_platform
v$transportable_platform-----platform_name,endian_format
这两个视图可以查看大平台小平台
4.数据库迁移时，目标平台必须不存在要迁移的对象（10g迁移user表空间给11g，11g中就不能存在user）

实验环境：

window转换到linux

准备工作：

w端：创建表空间，用户和表

linux端：查询数据系统平台

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

实施步骤：

w端：

第一步

检测到没有错误

第二步：表空间只读

alter tablespace test read only

第三步：

导出表空间数据结构

第四步：导出数据文件

第五步：恢复表空间读写

alter tablespace test read write;

第六步：传输出数据到linux平台

ftp ，mount均可

mount.cifs -o username="Administrator",password="123456" //192.168.6.150/test /mnt/

linux端：

第一步：

创建test用户，否则会报

[oracle@oracle2 ~]$ imp \'/ as sysdba\' transport_tablespace=y tablespaces=test datafiles='/u01/TEST01.DBF' file=/opt/test2016-06-06.dmp




Import: Release 11.2.0.3.0 - Production on Mon Jun 6 15:19:25 2016


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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option


Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
 "BEGIN   sys.dbms_plugts.checkUser('TEST'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user TEST does not exist in the database
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PLUGTS", line 2021
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

第二步：

[oracle@oracle2 ~]$ imp \'/ as sysdba\' transport_tablespace=y tablespaces='tes't datafiles='/u01/TEST01.DBF' file=/opt/test2016-06-06.dmp


Import: Release 11.2.0.3.0 - Production on Mon Jun 6 15:33:03 2016


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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option


Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
 "BEGIN   sys.dbms_plugts.checkUser('TEST'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user TEST does not exist in the database
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PLUGTS", line 2021
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[oracle@oracle2 ~]$ imp \'/ as sysdba\' transport_tablespace=y tablespaces='test' datafiles='/u01/TEST01.DBF' file=/opt/test2016-06-06.dmp


Import: Release 11.2.0.3.0 - Production on Mon Jun 6 15:33:15 2016


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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option


Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
 "BEGIN   sys.dbms_plugts.checkUser('TEST'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user TEST does not exist in the database
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PLUGTS", line 2021
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[oracle@oracle2 ~]$ imp \'/ as sysdba\' transport_tablespace=y tablespaces='test' datafiles='/u01/TEST01.DBF' file=/opt/test2016-06-06.dmp


Import: Release 11.2.0.3.0 - Production on Mon Jun 6 15:37:59 2016


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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option


Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TEST's objects into TEST
. . importing table                           "T1"
. . importing table                           "T2"
. . importing table                           "T3"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

第三步：

表空间恢复读写（注意文件的权限）

SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
CLSP                           ONLINE
SALARY                         ONLINE
TEST                           READ ONLY


8 rows selected.


SQL> alter tablespace TEST read write;
alter tablespace TEST read write
*
ERROR at line 1:
ORA-01114: IO error writing block to file 7 (block # 1)
ORA-01110: data file 7: '/u01/TEST01.DBF'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3




SQL> ho ll /u01/TEST01.DBF
/bin/bash: ll: command not found


SQL> alter tablespace TEST read write;


Tablespace altered.


SQL> 

查询test.t1和test.t2、test.t3表

SQL> alter tablespace TEST read write;


Tablespace altered.


SQL> select count(*) from test.t3;


  COUNT(*)
----------
     71899


SQL> select count(*) from test.t2;


  COUNT(*)
----------
         2


SQL> select count(*) from test.t1;


  COUNT(*)
----------
         1


SQL>