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>