= TTS传输表空间
= 本次测试目标环境和源环境相同版本，相同平台，相同配置
= 参考《Oracle Database Administrator’s Guide》
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm=i1007169
=== 1 数据目录DIRECTORY
= 使用DATA_PUMP_DIR
= 源库目录/u02通过NFS共享到目标库/u03
set line 150 pages 90
SELECT * FROM DBA_DIRECTORIES;
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS ‘/u02/pump’; – 源库
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS ‘/u03/pump’; – 目标库
=== 2 导出表空间元数据
select * from v$tablespace; – 确认要导出的表空间
– 测试数据
create table cog.t1(id int) tablespace gz1;
create table cog.t2(id int) tablespace gz2;
insert into cog.t1 values(1);
insert into cog.t2 values(2);
commit;
alter system checkpoint;
– 使表空间只读
ALTER TABLESPACE gz1 READ ONLY;
ALTER TABLESPACE gz2 READ ONLY;
– 导出元数据（数据泵导出的只是元数据）并行只能为1
expdp system/oracle parfile=‘pra1.f’
= vi pra1.f
dumpfile=expdat.dmp
directory=DATA_PUMP_DIR
transport_tablespaces=GZ1,GZ2
transport_full_check=y
logfile=tts_export.log
=== 3 需要转换平台的时候使用RMAN（传输之前转储）
= （转储字符序平台）检查日志中的错误信息 endianness(字节顺序)
COLUMN PLATFORM_NAME FORMAT A36
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
select PLATFORM_ID,PLATFORM_NAME from v$database;
= rman target / = 在源库执行转储
= CONVERT TABLESPACE gz1,gz2 TO PLATFORM ‘Microsoft Windows IA (32-bit)’ FORMAT ‘/u02/rman/%U’;
RMAN> CONVERT TABLESPACE gz1,gz2 TO PLATFORM ‘Linux x86 64-bit’ FORMAT ‘/u02/rman/%U’;
= 源库导出元数据和数据文件之后，可以再次可读写
ALTER TABLESPACE gz1 READ WRITE;
ALTER TABLESPACE gz2 READ WRITE;
=== 4 传输数据文件
= 已经配置了NFS文件共享，不需要传输转储之后的数据文件
=== 5 目标库(转储or拷贝)文件到数据目录
= 在传输到目标主机之后再进行转储
= 已经在源库执行转储可以不用在目标库转储
= rman target /
= 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;
RMAN> CONVERT DATAFILE ‘/u03/rman/data_D-QNZW_I-2678639563_TS-GZ1_FNO-6_03v2b3jf’,’/u03/rman/data_D-QNZW_I-2678639563_TS-GZ2_FNO-4_04v2b3jg’
TO PLATFORM=“Linux x86 64-bit” FROM PLATFORM=“Linux x86 64-bit”
DB_FILE_NAME_CONVERT=’/u03/rman’, ‘/u02/gz851/gz851/’
PARALLELISM=2;
=== 6 在目标库创建用户(没有的需要先创建)
= 如果用户不存在，导入元数据会报错，
= 用户不存在可以用用户映射 把A用户导入之后映射在B用户
create user cog identified by cog;
=== 7 目标库导入元数据
impdp system/oracle parfile=‘pra2.f’
= vi pra2.f
dumpfile=expdat.dmp
directory=DATA_PUMP_DIR
TRANSPORT_DATAFILES=’/u02/gz851/gz851/data_D-QNZW_I-2678639563_TS-GZ2_FNO-4_04v2b3jg’,’/u02/gz851/gz851/data_D-QNZW_I-2678639563_TS-GZ1_FNO-6_03v2b3jf’
logfile=tts_import.log
= 导入会遇到如下错误提示，因为SYS的对象创建在表空间，GZ2的表空间是源库USERS rename
= 忽略跳过
= 在生产环境操作时需要执行一系列检查
CREATE TABLE “SYS”.“T1” (“ID” NUMBER(*,
ORA-39151: Table “SCOTT”.“DEPT” exists.
=== 8 验证，使导入的表空间读写
select * from gz1.t1;
select * from gz2.t2;
select tablespace_name,STATUS from dba_tablespaces;
alter tablespace gz1 read write;
alter tablespace gz2 read write;
===============================================
==== 分区表附加传输
= 分区表附加传输（数据仓库）
CREATE TABLE sales (invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL)
PARTITION BY RANGE (sale_year, sale_month, sale_day)
(partition jan98 VALUES LESS THAN (1998, 2, 1),
partition feb98 VALUES LESS THAN (1998, 3, 1),
partition mar98 VALUES LESS THAN (1998, 4, 1),
partition apr98 VALUES LESS THAN (1998, 5, 1),
partition may98 VALUES LESS THAN (1998, 6, 1),
partition jun98 VALUES LESS THAN (1998, 7, 1));
CREATE INDEX sales_index ON sales(invoice_no) LOCAL;
= 可传输表空间生成月度表(生产)
create table jul_sales (
invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL
);
CREATE INDEX sales_index_988 ON jul_sales(invoice_no);
insert into jul_sales values(1,1998,8,1);
ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1);
= 使用只读表空间传输
= 附加表空间中的表到分区表
= 交换EXCHANGE之后,子分区jul98数据会被覆盖，jul_sales中的数据会被清空
ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
INCLUDING INDEXES
WITHOUT VALIDATION;
= 验证
select * from jul_sales;
select * from sales partition(jul98);
insert into jul_sales values(1,1992,8,1);
insert into jul_sales values(1,1998,8,1);
= 如果分区中的数据有冲突 以下方式可以解决 然后重试exchange操作
ORA-19728: data object number conflict between table JUL_SALES and partition JAN98 in table SALES
ALTER TABLE sales MOVE PARTITION jan98;
