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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/196700/viewspace-764103/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/196700/viewspace-764103/