数据库中如何实现表空间传输

这篇文章主要为大家展示了“数据库中如何实现表空间传输”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“数据库中如何实现表空间传输”这篇文章吧。

1、确保源端、目标端的字符集一致
select userenv('language') from dual;
The source and the destination databases must use compatible database character sets
源和目标数据库必须使用兼容的数据库字符集
The database character sets of the source and the target databases are the same.
源数据库和目标数据库的数据库字符集相同。
The source database character set is a strict (binary) subset of the target database character set
源数据库字符集是目标数据库字符集的严格（二进制）子集
The source and the target databases must use compatible national character sets
源数据库和目标数据库必须使用兼容的国家字符集

2、确保源端表空间不包含SYS对象，在目标端也建立这些OWNER
select OWNER from dba_segments where TABLESPACE_NAME='XX';
You cannot transport a tablespace to a destination database that contains a tablespace of the same name
不能将表空间传输到包含相同名称的表空间的目标数据库
You cannot transport the SYSTEM tablespace or objects owned by the user SYS
您不能传输SYSTEM表空间或用户SYS拥有的对象

3、查询源端、目标端的字节序
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
If the source platfORM and the destination platform are of different endianness, then an additional step must be done on either the source or destination platform to convert the tablespace being transported to the destination format
如果源平台和目标平台具有不同的字节顺序，则必须在源平台或目标平台上执行额外的步骤，将正在传输的表空间转换为目标格式

4、查询源端表空间是否self-contained
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('XX');
SELECT * FROM TRANSPORT_SET_VIOLATIONS;--查询结果为空，表示是self-contained

5、查询源端表空间对应的数据文件
select FILE_NAME from dba_data_files where TABLESPACE_NAME='XX';

6、源端设置表空间只读并导出格式文件
sql> ALTER TABLESPACE XX READ ONLY;
expdp system/123456 directory=data_pump_dir transport_tablespaces=XX dumpfile=expdpXX.dmp

7、如果源端和目标的字节序一致，则拷贝第6步的expdpXX.dmp到目标端data_pump_dir对应的目录,拷贝源端表空间对应的数据文件至目标端比如c:\app\orauser\oradata\orawin\XX.dbf

8、如果源端和目标的字节序不一致，则源端执行rman convert tablespace，再拷贝第6步的expdpXX.dmp和/tmp/%U的数据文件到目标端，expdpXX.dmp拷贝到目标端的data_pump_dir对应的目录，/tmp/%U的数据文件拷贝到目标端dba_data_files.file_name对应的目录
RMAN> CONVERT TABLESPACE XX TO PLATFORM 'Microsoft windows IA (32-bit)' FORMAT '/tmp/%U';
如上假如目标端的字节序为Microsoft Windows IA (32-bit)，/tmp/%U就是存放XX表空间转换后的数据文件

9、源端表空间设置回去read write
ALTER TABLESPACE XX READ WRITE;

10、目标端导入表空间
impdp system/123456 directory=data_pump_dir dumpfile=expdpXX.dmp transport_datafiles='c:\app\orauser\oradata\orawin\XX.dbf'

11、检查（EM做的话，源端默认使用副本导出，目标端默认选择read wirte，所以源端默认都是ONLINE操作，使用命令的话，源端目标端都要手工设置为read write）
源端：select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';--结果必须为ONLINE，为READ ONLY的话，就要设置为read write
目标端：
select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';--结果必须为ONLINE，为READ ONLY的话，就要设置为read write
select OWNER from dba_segments where TABLESPACE_NAME='XX';

备注：当然，如果字节序不一样，源端拷贝到目标端的数据文件没有经过第8步，也可以在目标端执行rman convert tablespace
RMAN> CONVERT DATAFILE 'c:\app\orauser\oradata\orawin\XX.dbf' TO PLATFORM="Microsoft Windows IA (32-bit)" FROM PLATFORM="Solaris[tm] OE (32-bit)"
或直接如下，不用管源端是什么
RMAN> CONVERT DATAFILE 'c:\app\orauser\oradata\orawin\XX.dbf' TO PLATFORM="Microsoft Windows IA (32-bit)"



实验步骤_使用命令模式（源端目标端的OS一样的，所以字节序一样）
1、源端prod2、目标端TDB的字符集一样
SQL> show parameter db_name
NAME      TYPE     VALUE 
------    -----    -----
db_name   string   prod2
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> show parameter db_name
NAME     TYPE      VALUE
-------- --------- --------
db_name  string    TDB
SQL>  select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------
AMERICAN_AMERICA.AL32UTF8

2、源端要传输的表空间是PRO2017，表空间对象的用户没有SYS，在目标端不存在这个表空间
SQL> select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='PRO2017';
TABLESPACE_NAME
-----------------
PRO2017
SQL> select OWNER from dba_segments where TABLESPACE_NAME='PRO2017';
OWNER
-------------
PRO2017
SQL> select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='PRO2017';--目标端没有结果
no rows selected

3、源端确保是self-contained
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('PRO2017');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected

4、查询源端表空间对应的数据文件
SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='PRO2017';
FILE_NAME
--------------------------------------------------------------------------------
/mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf

5、源端设置表空间只读并导出格式文件
SQL> ALTER TABLESPACE PRO2017 READ ONLY;
[oracle@mestest 2]$ expdp system/123456 directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp
Export: Release 11.2.0.4.0 - Production on Thu Oct 19 05:01:04 2017
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=data_pump_dir transport_tablespaces=PRO2017 dumpfile=expdpPRO2017.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp
******************************************************************************
Datafiles required for transportable tablespace PRO2017:
  /mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 19 05:01:33 2017 elapsed 0 00:00:28

6、拷贝格式文件到目标端的dump目录，datafile到目标端的dba_data_file.file_name对应目录
scp /mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf oracle@192.168.30.173:/db/oracle/oradata/TDB/
scp /mnt/sdb1/u01/app/oracle/admin/prod2/dpdump/expdpPRO2017.dmp  oracle@192.168.30.173:/db/oracle/admin/TDB/dpdump/

7、源端执行
SQL> ALTER TABLESPACE PRO2017 READ WRITE;
Tablespace altered.

8、目标端执行，有报错，所以目标端必须建立表空间对应的用户PRO2017
[oracle@localhost TDB]$ impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles='/db/oracle/oradata/TDB/pro2017.dbf'
Import: Release 11.2.0.4.0 - Production on Thu Oct 19 20:33:59 2017
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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user PRO2017 does not exist in the database
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Thu Oct 19 20:34:02 2017 elapsed 0 00:00:02

SQL> create user PRO2017 identified by 123456;
User created.

SQL> grant connect,resource to PRO2017;
Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost TDB]$ impdp system/123456 directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles='/db/oracle/oradata/TDB/pro2017.dbf'
Import: Release 11.2.0.4.0 - Production on Thu Oct 19 20:35:19 2017
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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=data_pump_dir dumpfile=expdpPRO2017.dmp transport_datafiles=/db/oracle/oradata/TDB/pro2017.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 19 20:35:23 2017 elapsed 0 00:00:02

SQL> select tablespace_name,status from dba_tablespaces where TABLESPACE_NAME='PRO2017';
TABLESPACE_NAME                                              STATUS
------------------------------------------------------------ ------------------
PRO2017                                                      READ ONLY

SQL> ALTER TABLESPACE PRO2017 READ WRITE;
Tablespace altered.

以上是“数据库中如何实现表空间传输”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网数据库频道！