數(shù)據(jù)庫中如何實(shí)現(xiàn)表空間傳輸

這篇文章主要為大家展示了“數(shù)據(jù)庫中如何實(shí)現(xiàn)表空間傳輸”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“數(shù)據(jù)庫中如何實(shí)現(xiàn)表空間傳輸”這篇文章吧。

1、確保源端、目標(biāo)端的字符集一致
select userenv('language') from dual;
The source and the destination databases must use compatible database character sets
源和目標(biāo)數(shù)據(jù)庫必須使用兼容的數(shù)據(jù)庫字符集
The database character sets of the source and the target databases are the same.
源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫的數(shù)據(jù)庫字符集相同。
The source database character set is a strict (binary) subset of the target database character set
源數(shù)據(jù)庫字符集是目標(biāo)數(shù)據(jù)庫字符集的嚴(yán)格（二進(jìn)制）子集
The source and the target databases must use compatible national character sets
源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫必須使用兼容的國家字符集

2、確保源端表空間不包含SYS對(duì)象，在目標(biāo)端也建立這些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
不能將表空間傳輸?shù)桨嗤Q的表空間的目標(biāo)數(shù)據(jù)庫
You cannot transport the SYSTEM tablespace or objects owned by the user SYS
您不能傳輸SYSTEM表空間或用戶SYS擁有的對(duì)象

3、查詢?cè)炊?、目?biāo)端的字節(jié)序
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
如果源平臺(tái)和目標(biāo)平臺(tái)具有不同的字節(jié)順序，則必須在源平臺(tái)或目標(biāo)平臺(tái)上執(zhí)行額外的步驟，將正在傳輸?shù)谋砜臻g轉(zhuǎn)換為目標(biāo)格式

4、查詢?cè)炊吮砜臻g是否self-contained
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('XX');
SELECT * FROM TRANSPORT_SET_VIOLATIONS;--查詢結(jié)果為空，表示是self-contained

5、查詢?cè)炊吮砜臻g對(duì)應(yīng)的數(shù)據(jù)文件
select FILE_NAME from dba_data_files where TABLESPACE_NAME='XX';

6、源端設(shè)置表空間只讀并導(dǎo)出格式文件
SQL> ALTER TABLESPACE XX READ ONLY;
expdp system/123456 directory=data_pump_dir transport_tablespaces=XX dumpfile=expdpXX.dmp

7、如果源端和目標(biāo)的字節(jié)序一致，則拷貝第6步的expdpXX.dmp到目標(biāo)端data_pump_dir對(duì)應(yīng)的目錄,拷貝源端表空間對(duì)應(yīng)的數(shù)據(jù)文件至目標(biāo)端比如c:\app\orauser\oradata\orawin\XX.dbf

8、如果源端和目標(biāo)的字節(jié)序不一致，則源端執(zhí)行rman convert tablespace，再拷貝第6步的expdpXX.dmp和/tmp/%U的數(shù)據(jù)文件到目標(biāo)端，expdpXX.dmp拷貝到目標(biāo)端的data_pump_dir對(duì)應(yīng)的目錄，/tmp/%U的數(shù)據(jù)文件拷貝到目標(biāo)端dba_data_files.file_name對(duì)應(yīng)的目錄
RMAN> CONVERT TABLESPACE XX TO PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '/tmp/%U';
如上假如目標(biāo)端的字節(jié)序?yàn)镸icrosoft Windows IA (32-bit)，/tmp/%U就是存放XX表空間轉(zhuǎn)換后的數(shù)據(jù)文件

9、源端表空間設(shè)置回去read write
ALTER TABLESPACE XX READ WRITE;

10、目標(biāo)端導(dǎo)入表空間
impdp system/123456 directory=data_pump_dir dumpfile=expdpXX.dmp transport_datafiles='c:\app\orauser\oradata\orawin\XX.dbf'

11、檢查（EM做的話，源端默認(rèn)使用副本導(dǎo)出，目標(biāo)端默認(rèn)選擇read wirte，所以源端默認(rèn)都是ONLINE操作，使用命令的話，源端目標(biāo)端都要手工設(shè)置為read write）
源端：select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';--結(jié)果必須為ONLINE，為READ ONLY的話，就要設(shè)置為read write
目標(biāo)端：
select STATUS from dba_tablespaces where TABLESPACE_NAME='XX';--結(jié)果必須為ONLINE，為READ ONLY的話，就要設(shè)置為read write
select OWNER from dba_segments where TABLESPACE_NAME='XX';

備注：當(dāng)然，如果字節(jié)序不一樣，源端拷貝到目標(biāo)端的數(shù)據(jù)文件沒有經(jīng)過第8步，也可以在目標(biāo)端執(zhí)行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)"



實(shí)驗(yàn)步驟_使用命令模式（源端目標(biāo)端的OS一樣的，所以字節(jié)序一樣）
1、源端prod2、目標(biāo)端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、源端要傳輸?shù)谋砜臻g是PRO2017，表空間對(duì)象的用戶沒有SYS，在目標(biāo)端不存在這個(gè)表空間
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';--目標(biāo)端沒有結(jié)果
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、查詢?cè)炊吮砜臻g對(duì)應(yīng)的數(shù)據(jù)文件
SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='PRO2017';
FILE_NAME
--------------------------------------------------------------------------------
/mnt/sdb1/u01/app/oracle/oradata/prod2/pro2017.dbf

5、源端設(shè)置表空間只讀并導(dǎo)出格式文件
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、拷貝格式文件到目標(biāo)端的dump目錄，datafile到目標(biāo)端的dba_data_file.file_name對(duì)應(yīng)目錄
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、源端執(zhí)行
SQL> ALTER TABLESPACE PRO2017 READ WRITE;
Tablespace altered.

8、目標(biāo)端執(zhí)行，有報(bào)錯(cuò)，所以目標(biāo)端必須建立表空間對(duì)應(yīng)的用戶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.

以上是“數(shù)據(jù)庫中如何實(shí)現(xiàn)表空間傳輸”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！