數據庫中如何實現表空間傳輸

這篇文章主要為大家展示了“數據庫中如何實現表空間傳輸”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“數據庫中如何實現表空間傳輸”這篇文章吧。

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.

以上是“數據庫中如何實現表空間傳輸”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！