Oracle傳輸表空間學習

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT

  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

--源端

PLATFORM_NAME                      ENDIAN_FORMAT

---------------------    --------------

Linux IA (32-bit)            Little

--目標端

PLATFORM_NAME                      ENDIAN_FORMAT

---------------------------------- --------------

Solaris[tm] OE (32-bit)            Big

:說明源端、目標端都支援表空間傳輸，且endian不同，需要conversion。

注：如索引、分割槽、引用完整性約束、LOB欄位等不在同一表空間下的情況。

--檢視錶空間sales_1、sales_2的自包含情況

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS

---------------------------------------------------------------------------

Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table

JIM.DEPT in tablespace OTHER

Partitioned table JIM.SALES is partially contained in the transportable set

：即不滿足自包含關係，完整性約束DEPT_FK也存在在OTHER表空間中。(可以考慮繞過這個約束，不匯出)

1）修改表空間read only

SQL> ALTER TABLESPACE sales_1 READ ONLY;

SQL> ALTER TABLESPACE sales_2 READ ONLY;

2）expdp匯出

$ expdp system/oracle dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=sales_1,sales_2 logfile=tts_export.log

注：transport_full_check=y 嚴格的包含檢查

$ RMAN TARGET /

RMAN> CONVERT TABLESPACE sales_1,sales_2 TO PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '/tmp/%U';

Starting conversion at source at 30-SEP-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00007 name=/u01/app/oracle/oradata/salesdb/sales_101.dbf

converted datafile=/tmp/data_D-SALESDB_I-1192614013_TS-SALES_1_FNO-7_03jru08s

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00008 name=/u01/app/oracle/oradata/salesdb/sales_201.dbf

converted datafile=/tmp/data_D-SALESDB_I-1192614013_TS-SALES_2_FNO-8_04jru0aa

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25

Finished conversion at source at 30-SEP-08

可以使用copy、ftp、rman、DBMS_FILE_TRANSFER 包等方法傳輸。（裸裝置可使用dd命令）

--convert資料檔案目錄及名稱(如果前面沒有轉換endianness，可以在此步轉換)

$ RMAN TARGET /

RMAN> CONVERT DATAFILE

'C:\Temp\sales_101.dbf',

'C:\Temp\sales_201.dbf'

TO PLATFORM="Microsoft Windows IA (32-bit)"

FROM PLATFORM="Solaris[tm] OE (32-bit)"

DB_FILE_NAME_CONVERT=

'C:\Temp\', 'C:\app\orauser\oradata\orawin\'

PARALLELISM=4;

ALTER TABLESPACE sales_1 READ WRITE;

ALTER TABLESPACE sales_2 READ WRITE;

impdp system dumpfile=expdat.dmp directory=data_pump_dir

   transport_datafiles=

   c:\app\orauser\oradata\orawin\sales_101.dbf,

   c:\app\orauser\oradata\orawin\sales_201.dbf

   remap_schema=sales1:crm1  remap_schema=sales2:crm2

   logfile=tts_import.log

Password: password

此時可使表空間read write模式。