Oracle Transporting Tablespaces

實驗目的：
將源庫TTS表空間資料，快速遷移到目標資料庫；

實驗環境說明:
源  庫：11.2.0.4.0    PLATFORM_NAME:Linux x86 64-bit              compatible: 11.2.0.4.0
目標庫：11.2.0.4.0    PLATFORM_NAME:Microsoft Windows x86 64-bit  compatible: 11.2.0.4.0

源  庫
建立測試資料；
create tablespace tts datafile '/u01/app/oracle/oradata/orcl/tts01.dbf' size 1M;
create user tts identified by tts default tablespace tts;
grant connect,resource,dba to tts;
conn tts/tts
create table t1 as select level as id from dual connect by level<=10;

1：源庫和目標庫，檢視作業系統平臺
Task 1: Determine if Platforms are Supported and Determine Endianness

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

The following is the query result from the source platform:
PLATFORM_NAME                       ENDIAN_FORMAT
------------------------------------          --------------
Linux x86 64-bit                             Little

The following is the result from the destination platform:
PLATFORM_NAME                        ENDIAN_FORMAT
------------------------------------           --------------
Microsoft Windows x86 64-bit           Little

2：源庫，檢視準備傳輸表空間是否自包含
Task 2: Pick a Self-Contained Set of Tablespaces

SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('tts,', TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
no rows selected

3：源庫，準備後設資料和資料檔案
Task 3: Generate a Transportable Tablespace Set

SQL> ALTER TABLESPACE tts READ ONLY ;
SQL> create directory data1_pump_dir as '/home/oracle/test';
SQL> grant read,write on directory data1_pump_dir to tts;
---select status,tablespace_name from dba_tablespaces where tablespace_name like '%TTS%';
---alter tablespace tts read write;

[oracle@chen test]$ expdp system/oracle dumpfile=tts.dmp directory=data1_pump_dir transport_tablespaces=tts transport_full_check=y logfile=tts.log

Export: Release 11.2.0.4.0 - Production on Mon Jul 10 15:13:00 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/******** dumpfile=tts.dmp directory=data1_pump_dir transport_tablespaces=tts transport_full_check=y logfile=tts.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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/test/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
  /u01/app/oracle/oradata/orcl/tts01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 10 15:13:59 2017 elapsed 0 00:00:53

---TRANSPORT_FULL_CHECK
---該選項用於指定被搬移表空間和未搬移表空間關聯關係的檢查方式,預設為N.

4：將後設資料和資料檔案上傳到目標資料庫上；
D:\>cd BACKUP\test
D:\BACKUP\test 的目錄
2017/07/10  15:13            90,112 tts.dmp
2017/07/10  15:13             1,205 tts.log
2017/07/10  15:12         1,056,768 tts01.dbf

5：目標庫，檢視支援的作業系統平臺資訊
SQL> set long 1000
SQL> col platform_name for a36
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
          6 AIX-Based Systems (64-bit)           Big
         16 Apple Mac OS                         Big
         21 Apple Mac OS (x86-64)                Little
         19 HP IA Open VMS                       Little
         15 HP Open VMS                          Little
          5 HP Tru64 UNIX                        Little
          3 HP-UX (64-bit)                       Big
          4 HP-UX IA (64-bit)                    Big
         18 IBM Power Based Linux                Big
          9 IBM zSeries Based Linux              Big
         10 Linux IA (32-bit)                    Little
         11 Linux IA (64-bit)                    Little
         13 Linux x86 64-bit                     Little
          7 Microsoft Windows IA (32-bit)        Little
          8 Microsoft Windows IA (64-bit)        Little
         12 Microsoft Windows x86 64-bit         Little
         17 Solaris Operating System (x86)       Little
         20 Solaris Operating System (x86-64)    Little
          1 Solaris[tm] OE (32-bit)              Big
          2 Solaris[tm] OE (64-bit)              Big


20 rows selected.


6:目標庫，建立目錄和使用者
建立使用者及directory:
SQL> create directory trans as 'D:\BACKUP\test';
SQL> grant read,write on directory trans to public;
SQL> create user trans_user identified by trans;
SQL> grant connect,resource to trans_user;

7:目標庫rman 轉換資料檔案適應當前作業系統平臺：

D:\>rman target sys/oracle
恢復管理器: Release 11.2.0.4.0 - Production on 星期一 7月 10 15:29:45 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
已連線到目標資料庫: CHENJCH (DBID=579646690)

RMAN> convert datafile 'D:\BACKUP\test\tts01.dbf' to platform='Microsoft Windows
 x86 64-bit' from platform='Linux x86 64-bit' db_file_name_convert='D:\BACKUP\te
st\tts01.dbf','D:\app\Administrator\oradata\chenjch\tts01.dbf';

啟動 conversion at target 於 10-7月 -17
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=69 裝置型別=DISK
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=D:\BACKUP\TEST\TTS01.DBF
已轉換的資料檔案 = D:\APP\ADMINISTRATOR\ORADATA\CHENJCH\TTS01.DBF
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
完成 conversion at target 於 10-7月 -17

D:\>cd D:\app\Administrator\oradata\chenjch
D:\app\Administrator\oradata\chenjch>dir
D:\app\Administrator\oradata\chenjch 的目錄
......
2017/07/10  15:31         1,056,768 TTS01.DBF
......

8:目標庫：匯入資料
d:\> impdp dumpfile=tts.dmp logfile=tts.log directory=trans transport_datafiles=D:\app\Administrator\oradata\chenjch\tts01.dbf remap_schema=tts:trans_user

Import: Release 11.2.0.4.0 - Production on 星期一 7月 10 15:35:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
使用者名稱: sys/oracle as sysdba
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功載入/解除安裝了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  sys/******** AS SYSDBA dumpfile=tts.d
mp logfile=tts.log directory=trans transport_datafiles=D:\app\Administrator\orad
ata\chenjch\tts01.dbf remap_schema=tts:trans_user
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作業 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已於 星期一 7月 10 15:35:58 2017 elapsed 0 00:00:12 成功完成

9:目標庫：將匯入的表空間置為read write
SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like '%TTS%';
STATUS    TABLESPACE_NAME
--------- ------------------------------
READ ONLY TTS

SQL> alter tablespace tts read write;
表空間已更改。

SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like '%TTS%';
STATUS    TABLESPACE_NAME
--------- ------------------------------
ONLINE    TTS

10:驗證資料
SQL> conn trans_user/trans
已連線。
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                                TABLE

SQL> select * from t1;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
已選擇10行。

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！