oracle可傳輸表空間TTS的示例分析

這篇文章給大家分享的是有關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_2transport_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=00007name=/u01/app/oracle/oradata/salesdb/sales_101.dbf

converteddatafile=/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

感謝各位的閱讀！關于“oracle可傳輸表空間TTS的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！