聊聊Oracle可傳輸表空間（Transportable Tablespace）（中）
6、Oracle資料泵Data Pump匯出匯入
Exp/Imp是Oracle早期推出的資料邏輯備份還原工具，使用簡單、功能強大。但是Exp/Imp對一些Oracle新特性支援不是很好，而且對於海量資料備份還原速度還是不能滿足要求。於是從10g開始，Oracle推出了資料泵（Data Pump）作為Exp/Imp的升級替代版本。
使用Data Pump也是可以進行TTS後設資料的匯出。下面我們將實驗使用Data Pump重新做一次。注意：表空間Read Only配置和相關的檢查步驟略過。
Data Pump是一個服務端工具，使用的話需要定義directory物件。
SQL> create or replace directory TTSDMP
2 as '/transtts';
Directory created
SQL> grant read, write on directory ttsdmp to public;
Grant succeeded
匯出後設資料dmp檔案。
[oracle@bsplinux transtts]$ expdp userid=\'/ as sysdba\' transport_tablespaces=ttsind,ttstbl dumpfile=ttsdmp.dmp directory=ttsdmp transport_full_check=y
Export: Release 11.2.0.1.0 - Production on Mon Nov 19 20:06:33 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/******** AS SYSDBA" transport_tablespaces=ttsind,ttstbl dumpfile=ttsdmp.dmp directory=ttsdmp transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/transtts/ttsdmp.dmp
******************************************************************************
Datafiles required for transportable tablespace TTSIND:
/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf
Datafiles required for transportable tablespace TTSTBL:
/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:08:02
複製資料檔案。
[oracle@bsplinux datafile]$ cp /u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf /transtts
cp /u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf /transtts
[oracle@yjz69_.dbf /transttscp /u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bm
[oracle@bsplinux transtts]$ pwd
/transtts
[oracle@bsplinux transtts]$ ls -l
total 30888
-rw-r--r-- 1 oracle oinstall 1402 Nov 19 20:08 export.log
-rw-r----- 1 oracle oinstall 10493952 Nov 19 20:09 o1_mf_ttsind_8bmyjz69_.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 19 20:09 o1_mf_ttstbl_8bmyjf3w_.dbf
-rw-r----- 1 oracle oinstall 106496 Nov 19 20:08 ttsdmp.dmp
標紅部分的檔案為進行TTS的必要內容。
說明：由於我們在相同的環境下進行測試，所以將表空間刪除。
SQL> drop tablespace ttstbl including contents and datafiles;
Tablespace dropped
SQL> drop tablespace ttsind including contents and datafiles;
Tablespace dropped
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'TTS%';
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
表空間刪除成功。下面進行實際還原過程。
[oracle@bsplinux transtts]$ cp o1_mf_ttsind_8bmyjz69_.dbf /u01/app/oradata/ORA11G/datafile/
[oracle@bsplinux transtts]$ cp o1_mf_ttstbl_8bmyjf3w_.dbf /u01/app/oradata/ORA11G/datafile/
[oracle@bsplinux datafile]$ ls -l | grep tts
-rw-r----- 1 oracle oinstall 10493952 Nov 19 20:15 o1_mf_ttsind_8bmyjz69_.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 19 20:15 o1_mf_ttstbl_8bmyjf3w_.dbf
使用impdp工具匯入後設資料資訊。
[oracle@bsplinux transtts]$ impdp userid=\'/ as sysdba\' directory=ttsdmp dumpfile=ttsdmp.dmp transport_datafiles='/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf','/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf'
Import: Release 11.2.0.1.0 - Production on Mon Nov 19 20:18:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": userid="/******** AS SYSDBA" directory=ttsdmp dumpfile=ttsdmp.dmp transport_datafiles=/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf,/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:18:56
此時，表空間已經導回，對應的資料表也能夠還原回去。
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'TTS%';
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf TTSIND
/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf TTSTBL
--切換回test使用者
SQL> select count(*) from test.t;
COUNT(*)
----------
72348
注意：無論是使用exp/imp還是Data Pump，匯入物件對應的owner必須存在並且名稱一致。如果不存在owner物件，即使使用Data Pump也不會自動將使用者重新建立。
7、異平臺TTS移植
相同平臺型別移植，一直是TTS使用的一個前提條件，也是一個很難逾越的遮蔽。這給TTS的使用帶來一些限制。比如，我們不能實現從AIX平臺到開放Linux平臺的遷移。於是，在10g以上的版本中，我們可以突破這個限制。
下面我們嘗試將一個表空間從AIX平臺遷移到Linux平臺。
在AIX平臺上，我們將其作為source database。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select count(*),sum(bytes)/1024/1024 from dba_segments where tablespace_name='NBSPOC';
COUNT(*) SUM(BYTES)/1024/1024
---------- --------------------
174 286
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='NBSPOC';
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf NBSPOC
NBSPOC表空間中包括174個物件，約286M資料。下面設定表空間Read Only和平臺檢查。
SQL> alter tablespace nbspoc read only;
Tablespace altered
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
AIX-Based Systems (64-bit)
我們從v$transportable_platform檢視中，可以看出AIX-Based Systems(64-bit)屬於Big型別。而Target目標Linux為Little型別。所以，我們在進行跨平臺遷移。
注意：跨平臺遷移也需要滿足字符集相同的要求。
後設資料匯出我們計劃使用expdp，所以配置directory物件。
oracle:/ftptemp>cd trans
oracle:/ftptemp/trans>ls -l
total 0
oracle:/ftptemp/trans>pwd
/ftptemp/trans
oracle:/ftptemp/trans>
SQL> create directory dmpdir as '/ftptemp/trans';
Directory created
檢查表空間NBSPOC的依賴性。
SQL> exec dbms_tts.transport_set_check('NBSPOC',true);
PL/SQL procedure successfully completed
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Expdp匯出表空間物件後設資料資訊。
oracle:/ftptemp/trans>=ttspoc.dmp directory=dmpdir transport_full_check=y <
Export: Release 11.2.0.1.0 - Production on Mon Nov 19 14:26:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/******** AS SYSDBA" transport_tablespaces=nbspoc dumpfile=ttspoc.dmp directory=dmpdir transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/ftptemp/trans/ttspoc.dmp
******************************************************************************
Datafiles required for transportable tablespace NBSPOC:
/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:28:43
oracle:/ftptemp/trans>ls -l
total 19224
-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log
-rw-r----- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp –大約9M
注意，下面是遷移的關鍵步驟，我們需要將對應的遷移資料檔案Convert到目標平臺格式。Oracle推薦使用Rman進行這個工作。
oracle:/ftptemp/trans>rman nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 19 14:36:47 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: NBSTEST (DBID=2351142467)
using target database control file instead of recovery catalog
RMAN> convert tablespace 'NBSPOC'
2> to platform="Linux IA (32-bit)" –目標平臺格式（可以查詢目標資料庫的v$database檢視）
3> db_file_name_convert='/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf','/ftptemp/trans/NBSPOCTBL01.dbf';
Starting conversion at source at 19-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00014 name=/nbstdata01/oradata/NBSTEST/NBSTEST/NBSPOCTBL01.dbf
converted datafile=/ftptemp/trans/NBSPOCTBL01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished conversion at source at 19-NOV-12
從命令列情況看，Rman相當於將指定的資料檔案進行轉換，複製轉換過的版本到一個Stage目錄上。這樣，目錄/ftptemp/trans中就已經包括了所有的遷移內容了。
oracle:/ftptemp/trans>ls -l
total 2067240
-rw-r----- 1 oracle dba 1048584192 Nov 19 14:41 NBSPOCTBL01.dbf
-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log
-rw-r----- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp
轉換到Target環境，是一臺Linux伺服器。
SQL> conn sys/oracle@ora11gp as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
Linux IA (32-bit)
下面就是用FTP將傳輸檔案傳輸到目標平臺，我們使用Linux自帶的FTP命令列客戶端完成。
[oracle@bsplinux transtts]$ ls -l
total 0
[oracle@bsplinux transtts]$ pwd
/transtts
[oracle@bsplinux transtts]$
--呼叫FTP客戶端
[oracle@bsplinux transtts]$ ftp
ftp> open 10.1.15.66
Connected to 10.1.15.66.
220 P550_05_LC FTP server (Version 4.2 Wed Dec 23 11:06:15 CST 2009) ready.
502 authentication type cannot be set to GSSAPI
502 authentication type cannot be set to KERBEROS_V4
KERBEROS_V4 rejected as an authentication type
Name (10.1.15.66:oracle): oracle
331 Password required for oracle.
Password:
230-Last unsuccessful login: Wed Nov 14 15:35:34 GMT+08:00 2012 on /dev/pts/1 from 10.1.39.109
230-Last login: Mon Nov 19 14:24:58 GMT+08:00 2012 on /dev/pts/2 from 10.1.39.62
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp>
--切換目錄
ftp> cd /ftptemp/trans
250 CWD command successful.
ftp> dir
227 Entering Passive Mode (10,1,15,66,134,182)
150 Opening data connection for /bin/ls.
total 2067240
-rw-r----- 1 oracle dba 1048584192 Nov 19 14:41 NBSPOCTBL01.dbf
-rw-r--r-- 1 oracle dba 1529 Nov 19 14:28 export.log
-rw-r----- 1 oracle dba 9838592 Nov 19 14:28 ttspoc.dmp
226 Transfer complete.
ftp> get ttspoc.dmp
local: ttspoc.dmp remote: ttspoc.dmp
227 Entering Passive Mode (10,1,15,66,134,206)
150 Opening data connection for ttspoc.dmp (9838592 bytes).
226 Transfer complete.
9838592 bytes received in 0.85 seconds (1.1e+04 Kbytes/s)
ftp> get export.log
local: export.log remote: export.log
227 Entering Passive Mode (10,1,15,66,134,211)
150 Opening data connection for export.log (1529 bytes).
226 Transfer complete.
1529 bytes received in 0.17 seconds (9 Kbytes/s)
ftp> get NBSPOCTBL01.dbf
local: NBSPOCTBL01.dbf remote: NBSPOCTBL01.dbf
227 Entering Passive Mode (10,1,15,66,134,228)
150 Opening data connection for NBSPOCTBL01.dbf (1048584192 bytes).
226 Transfer complete.
1048584192 bytes received in 3.6e+02 seconds (2.9e+03 Kbytes/s)
ftp>
--傳輸完畢
[oracle@bsplinux transtts]$ ls -l
total 1034640
-rw-r--r-- 1 oracle oinstall 1529 Nov 19 21:51 export.log
-rw-r--r-- 1 oracle oinstall 1048584192 Nov 19 21:58 NBSPOCTBL01.dbf
-rw-r--r-- 1 oracle oinstall 9838592 Nov 19 21:51 ttspoc.dmp
[oracle@bsplinux transtts]$
將複製的資料複製到資料檔案目錄，進行匯入後設資料步驟。注意，在Target環境中，要首先建立好使用者和許可權資訊。
SQL> create user nbspoc identified by nbspoc;
User created
SQL> grant resource, connect to nbspoc;
Grant succeeded
匯入表空間資訊。
[oracle@bsplinux transtts]$ impdp userid=\'/ as sysdba\' directory=ttsdmp dumpfile=ttspoc.dmp transport_datafiles='/u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf'
Import: Release 11.2.0.1.0 - Production on Mon Nov 19 22:13:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": userid="/******** AS SYSDBA" directory=ttsdmp dumpfile=ttspoc.dmp transport_datafiles=/u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:16:29
設定收尾資訊和驗證。
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name like 'NBS%';
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oradata/ORA11G/datafile/NBSPOCTBL01.dbf NBSPOC
SQL> select count(*), sum(bytes)/1024/1024 from dba_segments where tablespace_name='NBSPOC';
COUNT(*) SUM(BYTES)/1024/1024
---------- --------------------
174 286
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name like 'NBS%';
TABLESPACE_NAME STATUS
------------------------------ ---------
NBSPOC READ ONLY
SQL> alter tablespace nbspoc read write;
Tablespace altered
遷移成功。那麼，還有很多更復雜的情況，比如ASM的引入。在ASM環境下，我們是不能夠直接訪問到資料檔案，複製資料檔案的（Oracle 11gR2之前）。如何遷移ASM下的資料檔案，我們繼續探討。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2145046/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（上）Oracle
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（下）Oracle
- MySQL Transportable Tablespace(傳輸表空間) 使用詳解MySql
- Oracle可傳輸表空間測試Oracle
- oracle可傳輸表空間TTS小結OracleTTS
- Oracle 表空間傳輸Oracle
- oracle表空間傳輸Oracle
- Oracle傳輸表空間Oracle
- 利用oracle10g_rman_convert_transportable tablespace遷移表空間Oracle
- 傳輸表空間操作-OracleOracle
- Oracle傳輸表空間（TTS）OracleTTS
- Oracle 傳輸表空間-RmanOracle
- 關於oracle可傳輸表空間的總結Oracle
- Oracle 10g的可傳輸表空間操作Oracle 10g
- 基於可傳輸表空間的表空間遷移
- 5.7 mysql的可傳輸表空間MySql
- Oracle傳輸表空間學習Oracle
- Oracle 傳輸表空間-EXPDP/IMPDPOracle
- Oracle 傳輸表空間-EXP/IMPOracle
- Oracle表空間傳輸詳解Oracle
- 使用Oracle可傳輸表空間的特性複製資料(7)實戰RMAN備份傳輸表空間Oracle
- 用可傳輸表空間實現Oracle跨平臺移植Oracle
- oracle 傳輸表空間一例Oracle
- 使用RMAN實現可傳輸的表空間
- 【TABLESPACE】Oracle表空間最佳實踐Oracle
- Oracle基礎 01 表空間 tablespaceOracle
- MySQL 傳輸表空間MySql
- MySQL表空間傳輸MySql
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- oracle表空間傳輸的限制條件Oracle
- 【TABLESPACE】Oracle 表空間結構說明Oracle
- oracle的臨時表空間temporary tablespaceOracle
- 【Oracle】oracle tablespace&datafile -- oracle表空間 分享[轉]Oracle
- Oracle 10g的可傳輸表空間操作（轉並驗證）Oracle 10g
- mysql之 表空間傳輸MySql
- 總結-表空間傳輸
- Oracle TTS ORA-39322: 表空間傳輸OracleTTS
- 表空間配額和UNLIMITED TABLESPACE許可權MIT