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

這篇文章給大家分享的是有關(guān)oracle可傳輸表空間TTS的示例分析的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

oracle加載數(shù)據(jù)最快的方式

限制

Source/target數(shù)據(jù)庫(kù)的字符集必須兼容，target字符集要么和source相同，要么為source的超集；database/national character set

加密表空間不能跨endian平臺(tái)傳輸；

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

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

Target版本不能低于source；

 時(shí)區(qū)文件要求一致

步驟

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檢查表空間是否自包含

基表所屬的索引/表分區(qū)/物化視圖必須位于同一表空間

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復(fù)制數(shù)據(jù)文件并導(dǎo)出其metadata—先將表空間設(shè)為只讀

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

--參數(shù)transport_full_check用于確認(rèn)表空間為自包含，如果驗(yàn)證失敗則expdp會(huì)終止

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

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

導(dǎo)出完畢后即可恢復(fù)表空間為讀寫模式

如果target為exadata且與source endian不同，oracle推出cross platform. incremental backup用于減少對(duì)source db的影響 ，詳情見(jiàn)1389592.1

在線復(fù)制數(shù)據(jù)文件，然后不斷對(duì)其增量備份并應(yīng)用到target，直至與source接近同步為止；

最后一步才將source tb設(shè)為read only，對(duì)其做最后一次增量備份，這種方式可將source tb的不可用時(shí)間減為最??；

4將數(shù)據(jù)集傳輸?shù)?/span>target

如果source/target使用了ASM，可使用dbms_file_transfer/rman進(jìn)行傳輸

第4/5步根據(jù)實(shí)際情況可互換

5 轉(zhuǎn)換endianness

在source/target端均可執(zhí)行

--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目錄，而數(shù)據(jù)文件位于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都沒(méi)有使用ASM，可以不指定source/target platform，RMAN通過(guò)檢查數(shù)據(jù)文件可獲知source platform，而target platform默認(rèn)為當(dāng)前主機(jī)；

6導(dǎo)入target

如果表空間數(shù)據(jù)塊不是target db的標(biāo)準(zhǔn)塊，則需設(shè)置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上執(zhí)行TTS  788176.1

物理備庫(kù)可以執(zhí)行TTS，但首先需要將其activate，大致步驟

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

2創(chuàng)建restore point，執(zhí)行TTS后回滾

3激活備庫(kù)alter database activate standby database，并將保護(hù)模式設(shè)為maximize performance—alter database set standby database to maximize performance

4執(zhí)行TTS

5閃回至restore point并重新轉(zhuǎn)換為物理備庫(kù)flashback database to restore point b/alter database convert to physical standby

TTS單個(gè)表分區(qū)731559.1

利用交換分區(qū)將單個(gè)分區(qū)置換到新表，然后刪除該分區(qū)，將該表空間傳輸即可；

基于ASM存儲(chǔ)的TTS ID 394798.1

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

1創(chuàng)建指向target的dblink：create database link db2 connect to system identified by manager1 using 'db2';

2在source/target創(chuàng)建基于ASM的directory，create directory tts_dump as '+DATA';

3導(dǎo)出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導(dǎo)入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

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