使用交换表空间和传输表空间来实现大表的迁移——exchange partition

 

一个案例，把一张分区表倒到另外一个数据库，db和os之类环境都是一样的，数据量比较大，不加索引就有120G左右
综合考虑对数据库性能的影响，加上分区表的哥哥分区都是单独的表空间，所以决定使用交换表空间和传输表空间的方式来实现表的迁移。
源库：
1：建立中转表空间
    create tablespace TS_USER_CLIENT_LOG_2  logging           
    datafile '/opt/oracle/oradata/xxx/ts_user_client_log_2.ora' size 5m
    reuse autoextend
    on next  512k maxsize  16000m extent management local
    segment space management  auto
    uniform size 1m ;
   
   
2：建立中转表，表和索引都建立到中转表空间上   
-- Create table
create table USER_CLIENT_LOG_TR2
(
  USER_ID     NUMBER(18) not null,
  LOGIN_IP    NUMBER(10) not null,
  LOGIN_TIME  DATE default sysdate not null,
  LAST_TIME   DATE,
  TOTAL_COUNT NUMBER(10)
)
tablespace TS_USER_CLIENT_LOG_2;
create index IDX_USER_CLIENT_LOG_TR2 on USER_CLIENT_LOG_TR2 (LOGIN_TIME)
  tablespace TS_USER_CLIENT_LOG_2;
create index IDX_USER_CLIENT_LOG_T2_UID on USER_CLIENT_LOG_TR2 (USER_ID)
  tablespace TS_USER_CLIENT_LOG_2;

3：交换表空间 
--alter table USER_CLIENT_LOG  exchange partition P_USER_CLIENT_LOG_1
--with table USER_CLIENT_LOG_TR1 INCLUDING INDEXES without validation update global indexes;

alter table USER_CLIENT_LOG  exchange partition P_USER_CLIENT_LOG_2
with table USER_CLIENT_LOG_TR2 INCLUDING INDEXES without validation;
这里的一些参数注意事项
a:Including indexes: 表明将Local indexes 一同exchange出来。此选项需要用来与之exchange的table 有建立一样的Index.

b:Without validation: with validation是验证是否rows被正确的Mapping. Without validation则是跳过此验证，在这种情况下，exchange 只做data dictionary的更新(这也是exchange快的原因，因为它根本上只是在data dictionary里将segments的归属作了变更)。这里有一个例外，如果在exchange中的table有PK或者unique constraint，是会强制作with validation，不管有没有指定without validation，这样做是为了保证constraint的完整性。如果要避免这个问题，可以在exchange之前先disable相关的constraint,在完成后再enable。

c:Update global indexes:使用此选项可避免Partitioned table的global index在exchange后被mark unusable。用这种方式在功能上不会影响到正常业务，但其过程中产生的额外loading会对业务性能的表现产生影响。如果不使用此选项，则必须在exchange后对global index进行rebuild。不过这个选项只会update被exchange出Partition的table的global index。如上的例子，表A的global index是不会被update，所以如果接下来exchange出的Partition的资料还要用的话，依然需要rebuild，但这已经不影响正常业务。

d:Drop 被exchange出来的Partition所在的Tablespace之前，注意把segments从物理上移动到归档Table A所在的Tablespace。上文提到了exchange只是在data dictionary里对segments的归属作了改变。

e:Exchange后，空的Partition可以直接DROP，无须带update global index。

4：设置传输的表空间为只读
ALTER TABLESPACE TS_USER_CLIENT_LOG2 read ONLY;

5：校验表空间是否可以传输
begin
 
  dbms_tts.transport_set_check('TS_USER_CLIENT_LOG1', TRUE , TRUE);
end;
 
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
如果表空间集满足自包含检查，则视图返回空记录。
不满足自包含验证，SELECT语句返回违反的信息，你可以根据其提示进行修正。

6：使用expdp传输表空间
expdp system/system DUMPFILE=TS_USER_CLIENT_LOG2.dmp LOGFILE=TS_USER_CLIENT_LOG2.log DIRECTORY=DATA_PUMP_DIR TRANSPORT_TABLESPACES=TS_USER_CLIENT_LOG2

7：拷贝expdp文件和表空间数据文件到目标库
scp /opt/oracle/pumpdata/TS_USER_CLIENT_LOG2.dmp oracle@10.55.4.45:/opt/oracle/expdp_dmp
scp /opt/oracle/oradata/pps/ts_user_client_log2.ora oracle@10.55.4.45:/opt/oracle/oradata/xxx

目标库
8：使用impdp导入传输来的表空间
impdp system/system DUMPFILE=TS_USER_CLIENT_LOG2.dmp DIRECTORY=EXPDP_DIR TRANSPORT_DATAFILES=/opt/oracle/oradata/xxx/ts_user_client_log2.ora
但是如下错误：
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-01422: exact fetch returns more than requested number of rows
Failing sql is:
CREATE INDEX "IDX_USER_CLIENT_LOG_TR2" ON "USER_CLIENT_LOG_TR2" ("LOGIN_TIME") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 8 SEG_BLOCK 140 OBJNO_REUSE 52097 INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_USER_CLIENT_LOG2" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-01422: exact fetch returns more than requested number of rows
Failing sql is:
CREATE INDEX "IDX_USER_CLIENT_LOG_T2_UID" ON "USER_CLIENT_LOG_TR2" ("USER_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 8 SEG_BLOCK 268 OBJNO_REUSE 52133 INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_USER_CLIENT_LOG2" PARALLEL 1
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"IDX_USER_CLIENT_LOG_TR2" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"IDX_USER_CLIENT_LOG_T2_UID" creation failed
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 4 error(s) at 14:31:22

查询数据，数据文件表空间和表都已经进入数据库，但是索引失败了，总结这个错误，我查来查去查不出相似案例，也不知道是否是自己哪里写的不对了，只得到一个疑似bug的说法（实际是不是我也不知道），这里只能说如果不涉及索引的话用expdp/impdp传输表空间，含有索引的话还是用exp吧，我改用exp/imp来完成表空间传输还是很顺利的。
6-1：使用exp来传输表空间
exp userid=\'sys/sys as sysdba\' file=/opt/oracle/pumpdata/TS_USER_CLIENT_LOG2.dmp  transport_tablespace=y tablespaces=TS_USER_CLIENT_LOG2
8-1:使用imp来实现
imp userid=\'sys/sys as sysdba\' file=/opt/oracle/expdp_dmp/TS_USER_CLIENT_LOG2.dmp transport_tablespace=y datafiles='/opt/oracle/oradata/xxx/ts_user_client_log2.ora' 
 

9：目标库——交换交换表空间
  alter table USER_CLIENT_LOG  exchange partition P_USER_CLIENT_LOG_4
with table USER_CLIENT_LOG_TR2 INCLUDING INDEXES without validation;

10：目标库——后续中间表空间和表的处理
删除中间表空间
drop tablespace TS_USER_CLIENT_LOG_2 including contents and datafiles; 

11：目标库——设置交换过来的表空间为读写
ALTER TABLESPACE TS_USER_CLIENT_LOG2 read write;
 
12：源数据库——表空间交换完毕，重新设置为读写
ALTER TABLESPACE TS_USER_CLIENT_LOG2 read write;

13：源数据库——交换表空间回到原分区表
  alter table USER_CLIENT_LOG  exchange partition P_USER_CLIENT_LOG_2
with table USER_CLIENT_LOG_TR2 INCLUDING INDEXES without validation;

14：源数据库——后续中间表空间和表的处理
删除中间表空间
  drop tablespace TS_USER_CLIENT_LOG_4 including contents and datafiles;

到此完毕