oracle可傳輸表空間TTS小結

oracle載入資料最快的方式

 

限制

Source/target資料庫的字符集必須相容，target字符集要麼和source相同，要麼為source的超集；database/national character set

加密表空間不能跨endian平臺傳輸；

包含加密列的表不可支援傳輸；

不可傳輸system表空間/sys使用者下的object；

Target版本不能低於source；

 

步驟

1 檢查target/source endian是否一致

Windows/linux均為little，其餘為big

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

--source

PLATFORM_NAME                      ENDIAN_FORMAT

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

Solaris[tm] OE (32-bit)            Big

--target

PLATFORM_NAME                      ENDIAN_FORMAT

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

Microsoft Windows IA (32-bit)      Little

 

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

 

3 複製資料檔案並匯出其metadata—先將表空間設為只讀

SQL> ALTER TABLESPACE sales_1 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE sales_2 READ ONLY;

Tablespace altered.

 

expdp system dumpfile=expdat.dmp directory=data_pump_dir  transport_tablespaces=sales_1,sales_2 transport_full_check=y  logfile=tts_export.log

--引數transport_full_check用於確認表空間為自包含，如果驗證失敗則expdp會終止

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

  /u01/app/oracle/admin/salesdb/dpdump/expdat.dmp

******************************************************************************

Datafiles required for transportable tablespace SALES_1:

  /u01/app/oracle/oradata/salesdb/sales_101.dbf

Datafiles required for transportable tablespace SALES_2:

  /u01/app/oracle/oradata/salesdb/sales_201.dbf

 

匯出完畢後即可恢復表空間為讀寫模式

 

如果target為exadata且與source endian不同，oracle推出cross platform. incremental backup用於減少對source db的影響 ，詳情見1389592.1

線上複製資料檔案，然後不斷對其增量備份並應用到target，直至與source接近同步為止；

最後一步才將source tb設為read only，對其做最後一次增量備份，這種方式可將source tb的不可用時間減為最小；

 

4 將資料集傳輸到target

如果source/target使用了ASM，可使用dbms_file_transfer/rman進行傳輸

 

第4/5步根據實際情況可互換

 

5  轉換endianness

在source/target端均可執行

--source

RMAN> CONVERT TABLESPACE sales_1,sales_2

2> TO PLATFORM. 'Microsoft Windows IA (32-bit)'

3> 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

 

--target

Dumpfile位於DATA_PUMP_DIR目錄，而資料檔案位於C:\TEMP

RMAN> CONVERT DATAFILE  'C:\Temp\sales_101.dbf', 'C:\Temp\sales_201.dbf'

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

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

3> DB_FILE_NAME_CONVERT='C:\Temp\', 'C:\app\orauser\oradata\orawin\'

4> PARALLELISM=4;

注：如果source/target都沒有使用ASM，可以不指定source/target platform，RMAN透過檢查資料檔案可獲知source platform，而target platform預設為當前主機；

 

6 匯入target

如果表空間資料塊不是target db的標準塊，則需設定target db的db_nk_cache_size；

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

 

 

 

在standby上執行TTS   788176.1

物理備庫可以執行TTS，但首先需要將其activate，大致步驟

1 確保其與主庫同步，停止log傳輸和MRP

2 建立restore point，執行TTS後回滾

3 啟用備庫alter database activate standby database，並將保護模式設為maximize performance—alter database set standby database to maximize performance

4 執行TTS

5 閃回至restore point並重新轉換為物理備庫 flashback database to restore point b/alter database convert to physical standby

 

 

TTS單個表分割槽 731559.1

利用交換分割槽將單個分割槽置換到新表，然後刪除該分割槽，將該表空間傳輸即可；

 

 

基於ASM儲存的TTS  ID 394798.1

使用dbms_file_transfer傳輸dump/data file，需要dblink協助；

1 建立指向target的dblink：create database link db2 connect to system identified by manager1 using 'db2';

2在source/target建立基於ASM的directory，create directory tts_dump as '+DATA';

3匯出metadata

ora10g@host1]$ expdp system/manager1 directory=tts_dump dumpfile=tts1_db1.dmp logfile=tts_dump_log:tts.log transport_tablespaces=tts_1,tts_2 transport_full_check=y

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02": system/******** directory=tts_datafile dumpfile=tts1.dmp logfile=tts_dump_log:tts.log transport

_tablespaces=tts_1,tts_2 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/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded

****************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is:+DATA/tts1.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 14:00:34

4將dump/data檔案傳送至target

SQL> begin

2 dbms_file_transfer.put_file

3 (source_directory_object => 'tts_dump',

4 source_file_name => 'tts1_db1.dmp',

5 destination_directory_object => 'tts_dump',

6 destination_file_name => 'tts1_db1.dmp',

7 destination_database => 'db2');

8 end;

9 /

SQL> begin

2 dbms_file_transfer.put_file

3 (source_directory_object => 'tts_datafile',

4 source_file_name => 'tts_1.294.570721319',

5 destination_directory_object => ' tts_datafile',

6 destination_file_name => 'tts1_db1.dbf',

7 destination_database => 'db2');

8 end;

9 /

 

5匯入target

impdp directory=tts_dump dumpfile=tts1_db1.dmp logfile=tts_dump_log:tts1.log TRANSPORT_DATAFILES='+DATA1/tts1_db1.dbf','+DATA1/tts2_db1.dbf' keep_master=y

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03": system/******** parfile=impdp.par

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/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03" successfully completed at 15:05:00