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

160次閱讀

共計 7877 個字符，預計需要花費 20 分鐘才能閱讀完成。

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

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.

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

正文完