12c 資料泵傳輸表空間
1，檢視待傳輸表空間example是否違反了獨立性規則
[oracle@snow ~]$ export ORACLE_SID=ora12c
[oracle@snow ~]$ sqlplus / as sysdba
SYS@ora12c >exec dbms_tts.transport_set_check('EXAMPLE',TRUE);
PL/SQL procedure successfully completed.
SYS@ora12c >select * from transport_set_violations;
no rows selected
2，將表空間example置為只讀
SYS@ora12c >alter tablespace example read only;
Tablespace altered.
源端資料檔案路徑
SYS@ora12c >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora12c/system01.dbf
/u01/app/oracle/oradata/ora12c/example01.dbf
/u01/app/oracle/oradata/ora12c/sysaux01.dbf
/u01/app/oracle/oradata/ora12c/undotbs01.dbf
/u01/app/oracle/oradata/ora12c/users01.dbf
目標端資料檔案路徑
SYS@OCM12C >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_system_8xf29zsz_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_sysaux_8xf1zgd7_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_undotbs1_8xf2pgsg_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_users_8xf2p505_.dbf
3，將源端的表空間資料檔案scp到目標端資料檔案路徑
SYS@ora12c >!scp /u01/app/oracle/oradata/ora12c/example01.dbf 172.16.228.9:/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
oracle@172.16.228.9's password:
example01.dbf 100% 323MB 32.3MB/s 00:10
SYS@ora12c >exit
4，使用資料泵匯出表空間example的後設資料scp到目標端的資料泵目錄（和源端一樣也是設定為dp_dir=/home/oracle）
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example
Export: Release 12.1.0.1.0 - Production on Mon Feb 9 12:45:28 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DP"."SYS_EXPORT_TRANSPORTABLE_01": dp/******** directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "DP"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DP.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/trans.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
/u01/app/oracle/oradata/ora12c/example01.dbf
Job "DP"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Feb 9 12:46:34 2015 elapsed 0 00:01:04
[oracle@snow ~]$ scp trans.dmp 172.16.228.9:/home/oracle
The authenticity of host '172.16.228.9 (172.16.228.9)' can't be established.
RSA key fingerprint is 70:7d:ec:8f:42:44:21:c9:24:d3:fc:23:1e:20:4b:ec.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.228.9' (RSA) to the list of known hosts.
oracle@172.16.228.9's password:
trans.dmp 100% 3172KB 3.1MB/s 00:00
6，將後設資料匯入目標端資料庫
[oracle@test ~]$ impdp hr/hr directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
Import: Release 12.1.0.1.0 - Production on Wed Feb 25 18:01:16 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "HR"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TRANSPORTABLE_01": hr/******** directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user PM does not exist in the database
Job "HR"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fata
錯誤提示沒有PM使用者，建立該使用者後重新執行impdp陸續提示SH，oe，ix使用者不存在，逐個建立上述使用者。
SYS@OCM12C >create user pm identified by pm;
SYS@OCM12C >create user sh identified by sh;
SYS@OCM12C >create user oe identified by oe;
SYS@OCM12C >create user ix identified by ix;
新增使用者後再次執行impdp成功
7，分別將源端和目標端端將表空間修改為read write狀態
SYS@OCM12C >alter tablespace example read write;
Tablespace altered.
SYS@OCM12C >select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SYS@ora12c >alter tablespace example read write;
Tablespace altered.
SYS@ora12c >select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
全文完
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2150455/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 傳輸表空間MySql
- mysql之 表空間傳輸MySql
- 用傳輸表空間跨平臺遷移資料
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- Oracle資料泵建立使用者避坑（表空間配額、許可權）Oracle
- MySQL傳輸表空間的簡單使用方法MySql
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- Oraclc 12C使用不一致備份執行跨平臺傳輸表空間
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (文件 ID 2102859.1)
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (Doc ID 2102859.1)
- 【資料遷移】XTTS跨平臺傳輸表空間v3（3.DFT方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v3（2.RMAN增量）TTS
- 【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19CTTSOracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- Oracle 12C新特性-資料泵新引數(LOGTIME)Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 16、表空間 建立表空間
- 表空間和資料檔案的管理
- 達夢資料庫表空間等空間大小查詢方法總結資料庫
- Oracle 12C新特性-資料泵新引數(VIEWS_AS_TABLES)OracleView
- Oracle 12C RMAN備份佔用大量臨時表空間Oracle
- MYSQL造資料佔用臨時表空間MySql
- oracle 普通表空間資料檔案壞塊Oracle
- 表空間（資料檔案shrink）收縮示例
- DB2建立資料庫，建立表空間DB2資料庫
- 【MOS】如何利用RMAN可傳輸表空間遷移資料庫到不同位元組序的平臺(文件 ID 1983639.1)資料庫
- [轉帖]達夢資料庫-統計資料表資料量及空間表大小資料庫
- SciPy 空間資料
- 12C關於CDB、PDB 臨時temp表空間的總結
- 12C關於CDB、PDB 回滾undo表空間的總結
- onethink上傳到空間之後，runtime資料夾將空間佔滿如何解決
- MySQL InnoDB系統表空間資料檔案配置MySql
- 2.5.9 在資料庫建立期間支援大檔案表空間資料庫
- GBase8s 檢視資料庫表空間資訊資料庫
- Oracle表空間Oracle
- oracle 表空間Oracle