測試結論
1,Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (?文件 ID 1454872.1) 摘文如下：
It is not possible to transport the SYSTEM, SYSAUX, TEMP tablespaces or objects owned by the user SYS
Restriction : SYSTEM, SYSAUX, TEMP Tablespace Objects. It is not possible to transport the SYSTEM, SYSAUX, and TEMP tablespaces or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, triggers, dblinks, roles, procedures, synonyms, users, privileges, dimensions, directories, sequences, and temporary tables. These must be created separately from the TTS operation, with scripts or export/import.
Note: The following Database Schemas/Users are also treated as SYS: 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP', 'ORDDATA' are treated as objects owned by 'SYS' and not exported with TTS.
Reference : Oracle Database Administrator's Guide - Transporting Tablespaces Between Databases.
Affected Version : All versions

2,基於TTS傳輸表空間，不能傳輸SYS的內建資料庫使用者的物件，見上；
不能傳輸 java class,callout,views,trigger,dblink,roles,procedure,synonyms,users,privilege,dimension,directories,sequence,temporary table

3,所以如果要使用TTS遷移資料庫，上述的物件必須要在源資料庫遷移前進行獲取其定義，爾後在目標資料庫重新建立即可

4，所以TTS不適用如果要遷移的資料庫中各種物件型別比較多，且依賴關係複雜

5，遷移表空間的具體流程如下：
5.1,在源資料庫變更遷移表空間為只讀

5.2，在源資料庫檢查要遷移的表空間是否完整性且一致

5.3,在源資料庫採用EXPDP匯出表空間

5.4，在源資料庫或目標資料庫轉換要遷移的源資料庫表空間的資料檔案為另一個目錄的檔案集，用於目標資料庫的最終的資料檔案

5.5,把在源資料庫匯出的表空間DMP以及轉換的源資料庫資料檔案SCP或傳輸到目標資料庫的對應目錄，為了簡化，可以放到同一個DIRECTORY下

5.6，在目標資料庫基於源資料庫建立遷移表空間的測試使用者

5.7，在目標資料庫變更遷移後的表空間由只讀為讀寫以及變更遷移資料庫使用者預設表空間為對應表空間

5.8，驗證遷移後表空間的資料一致性

5.9，其它補充工作

上述的前提工作為源和目標資料庫的字符集相同，COMPATIBLE相同，還有TIME ZONE相同

測試明細

1,資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2，建立測試使用者及測試表空間
SQL> create tablespace tbs_zxy datafile '/oracle/mygirl/tbs_zxy01.dbf' size 10m;

Tablespace created.

SQL> create user user_zxy identified by system default tablespace tbs_zxy account unlock;

User created.

SQL> grant resource,connect,dba to user_zxy;

Grant succeeded.

3，在測試使用者構建測試表及儲存過程
SQL> conn user_zxy/system
Connected.
SQL> create table t_zxy(a int);

Table created.

SQL> create procedure proc_t_zxy
2 as
3 v_cnt integer;
4 begin
5 select count(*) into v_cnt from t_zxy;
6 end;
7 /

Procedure created.

4，查詢測試表及儲存過程的物件資訊
SQL> r
1* select object_type,object_name from user_objects

OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
PROCEDURE PROC_T_ZXY
TABLE T_ZXY

5，查詢資料庫目錄資訊
[oracle@mygirl ~]$ pwd
/home/oracle

SQL> r
1* select owner,directory_name,directory_path from dba_directories

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS XMLDIR /oracle/product/11.2.0/db_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
SYS DATA_PUMP_DIR /oracle/admin/mygirl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /oracle/product/11.2.0/db_1/ccr/state

SQL> host ls -lh /oracle/admin/mygirl/dpdump/
total 61M
-rw-r-----. 1 oracle oinstall 100K May 5 14:36 dmpfile.dmp
-rw-r-----. 1 oracle oinstall 116 May 5 00:06 dp.log
-rw-r--r--. 1 oracle oinstall 2.4K May 5 14:36 impscrpt.sql
-rw-r--r--. 1 oracle oinstall 1.1K May 5 14:45 imp_test_tablespace.log
-rw-r-----. 1 oracle oinstall 21M May 26 11:52 tbs_father01.dbf
-rw-r-----. 1 oracle oinstall 21M May 26 11:52 tbs_mother01.dbf
-rw-r-----. 1 oracle oinstall 21M May 26 11:52 tbs_sun01.dbf

6，在傳輸表空間前變更表空間為只讀

SQL> alter tablespace tbs_zxy read only;

Tablespace altered.

7，基於傳輸表空間進行EXPDP

[oracle@mygirl ~]$ expdp \'sys/system as sysdba\' dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transfort_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log
LRM-00101: unknown parameter name 'transfort_tablespaces'

[oracle@mygirl ~]$ expdp \'sys/system as sysdba\' dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transport_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log

Export: Release 11.2.0.4.0 - Production on Fri May 26 13:17:27 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 "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transport_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
/oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS_ZXY:
/oracle/mygirl/tbs_zxy01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri May 26 13:18:13 2017 elapsed 0 00:00:45

You have new mail in /var/spool/mail/oracle

[oracle@mygirl ~]$ ls -lh /oracle/admin/mygirl/dpdump/expdp*
-rw-r-----. 1 oracle oinstall 88K May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp
-rw-r--r--. 1 oracle oinstall 1.2K May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.log

8,傳輸匯出的EXPDP表空間資料集到目標資料庫伺服器
[oracle@mygirl ~]$ sz /oracle/admin/mygirl/dpdump/expdp*
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
100% 88 KB 88 KB/s 00:00:01 0 Errors
100% 1 KB 1 KB/s 00:00:01 0 Errors

dbca -silent -createDatabase -templateName $ORACLE_HOME/assistants/dbca/templates/New_Database.dbt -gdbName other -sysPassword system -systemPassword system -emConfiguration NONE \
-disableSecurityConfiguration ALL -datafileDestination /oracle/mygirl -nodelist mygirl \
-characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -memoryPercentage 25 -databaseType OLTP

9,源資料庫字符集
1* select parameter,value from v$nls_parameters where lower(parameter) like '%char%'

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE

SQL> show parameter compat

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
plsql_v2_compatibility boolean FALSE

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

10,檢查源資料庫遷移表空間的是否完整一致性
SQL> exec dbms_tts.transport_set_check('tbs_zxy',true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

11,在源資料庫轉換
[oracle@mygirl admin]$ mkdir -p tts_dir
[oracle@mygirl admin]$ cd tts_dir/
[oracle@mygirl tts_dir]$ pwd
/oracle/admin/tts_dir

12,在相同機器建立另一個other資料庫
略

13,在源資料庫轉換要遷移表空間資料檔案

[oracle@mygirl tts_dir]$ env|grep SID
ORACLE_SID=mygirl
[oracle@mygirl tts_dir]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 26 21:13:01 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: MYGIRL (DBID=2527996082)

RMAN> convert tablespace tbs_zxy db_file_name_convert='/oracle/mygirl/tbs_zxy01.dbf','/oracle/admin/tts_dir/tbs_zxy01.dbf';

Starting conversion at source at 26-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/oracle/mygirl/tbs_zxy01.dbf
converted datafile=/oracle/admin/tts_dir/tbs_zxy01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 26-MAY-17

[oracle@mygirl tts_dir]$ ls -l /oracle/admin/tts_dir/tbs_zxy01.dbf
-rw-r-----. 1 oracle oinstall 10493952 May 26 21:13 /oracle/admin/tts_dir/tbs_zxy01.dbf

14,在目標資料庫建立測試使用者
[oracle@mygirl tts_dir]$ export ORACLE_SID=other
[oracle@mygirl tts_dir]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 26 21:14:43 2017

Copyright (c) 1982, 2013, Oracle. 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

SQL>

SQL> create user user_zxy identified by system;

User created.

SQL> grant resource,connect,dba to user_zxy;

Grant succeeded.

15,移動傳輸表空間匯出的表空間DMP檔案到目標資料庫的對應目錄
/oracle/admin/other/dpdump/

[oracle@mygirl tts_dir]$ ls -l /oracle/admin/mygirl/dpdump/*dmp
-rw-r-----. 1 oracle oinstall 102400 May 5 14:36 /oracle/admin/mygirl/dpdump/dmpfile.dmp
-rw-r-----. 1 oracle oinstall 90112 May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp

[oracle@mygirl tts_dir]$ cp /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp /oracle/admin/other/dpdump
You have new mail in /var/spool/mail/oracle
[oracle@mygirl tts_dir]$ ls -l /oracle/admin/other/dpdump
total 92
-rw-r-----. 1 oracle oinstall 116 May 26 20:01 dp.log
-rw-r-----. 1 oracle oinstall 90112 May 26 21:44 expdp_tbs_zxy.dmp

16,在目標資料庫把傳輸表空間匯入到目標資料庫
[oracle@mygirl tts_dir]$ export ORACLE_SID=other

[oracle@mygirl tts_dir]$ impdp \'/as sysdba\' directory=DATA_PUMP_DIR transport_datafiles='/oracle/admin/tts_dir/tbs_zxy01.dbf' dumpfile=expdp_tbs_zxy.dmp logfile=imp_nb.log

Import: Release 11.2.0.4.0 - Production on Fri May 26 21:47:58 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 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR transport_datafiles=/oracle/admin/tts_dir/tbs_zxy01.dbf dumpfile=expdp_tbs_zxy.dmp logfile=imp_nb.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri May 26 21:48:08 2017 elapsed 0 00:00:06

17,在目標資料庫變更遷移後的表空間由只讀為讀寫，且變更測試使用者的預設使用者為遷移的表空間
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS_ZXY READ ONLY

6 rows selected.

SQL> alter tablespace tbs_zxy read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS_ZXY ONLINE

6 rows selected.

SQL> alter user user_zxy default tablespace tbs_zxy;

User altered.

SQL>

18，在目標資料庫驗證資料一致性
SQL> conn user_zxy/system
Connected.

SQL> r
1* select object_type,object_name from user_objects

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------
TABLE T_ZXY

基於redhat 6.5 oracle 11.2.0.4測試tts傳輸表空間之資料庫遷移之一

相關文章