ZT oracle 分区表数据定期迁移到其他数据库测试方案

源数据库保存在线数据和一周的日志数据以供查询，历史数据库保存三个月的历史数据以供查询，每天大概会产生1000万的日志记录，计划日志表通过按照周建立分区进行数据的迁移。[@more@]

--新数据库的规划的exp_test的设计：

--第一次使用，创建全部三个数据表空间和索引表空间，以后每周增加下周要使用的新分区，移出上上周的数据到31数据库
--计划表exp_test是按照日期建立的每周一个的范围分区，将每周的周一的日期拼在分区的名字里面，
--便于自动处理。

--第一次使用，创建三个的数据表空间和索引表空间，大小按照每周的容量建，减少表空间自动扩展对性能的消耗
--以后只是删除旧的分区，对应的表空间复用，这样不用每次创建表空间，可以节省创建表空间的时间，热备份的脚本也不用做成动态的。
--数据表空间，按照每个10G建立

CREATE TABLESPACE PEXPTEST_DATA_TB1 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb1.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_DATA_TB2 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb2.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_DATA_TB3 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_data_tb3.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

--索引表空间，按照每个4G建立
CREATE TABLESPACE PEXPTEST_INDEX_TB1 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb1.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_INDEX_TB2 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb2.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_INDEX_TB3 DATAFILE
'/home1/oracle/oradata/dwtest/pexptest_index_tb3.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

-----创建表，假设从20050801上线，默认创建三个分区，这样定期执行的任务从第二周的数据写满以后开始执行
CREATE TABLE dwtest.EXP_TEST
(
SSN VARCHAR2(24 BYTE) NOT NULL,
...
)
PARTITION BY RANGE (CREATE_TIME)
(PARTITION PEXPTEST_DATA_20050801 VALUES LESS THAN (TO_DATE('2005/08/08','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB1,
PARTITION PEXPTEST_DATA_20050808 VALUES LESS THAN (TO_DATE('2005/08/15','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB2,
PARTITION PEXPTEST_DATA_20050815 VALUES LESS THAN (TO_DATE('2005/08/22','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB3)
;

-----建立索引
CREATE INDEX dwtest.PEXPTEST_SSN ON dwtest.EXP_TEST
(SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
LOCAL (
PARTITION PEXPTEST_DATA_20050801 tablespace PEXPTEST_INDEX_TB1,
PARTITION PEXPTEST_DATA_20050808 tablespace PEXPTEST_INDEX_TB2,
PARTITION PEXPTEST_DATA_20050815 tablespace PEXPTEST_INDEX_TB3)
NOPARALLEL;

--至此，应用可以向新表中填写数据。
--规划在第三周的周一开始执行增加新分区，移出老分区的任务，以下是手工执行的脚本，可以通过定义自动执行的任务完成。

--首先新增加分区，保证下周可用，不会因为数据转出有问题而耽搁了前台应用的填写
------增加新分区

----新增分区（这里有点疑惑，新增分区的时候不能指定索引分区，需要再rebuild，索性没有数据，rebuild也很快）
alter table dwtest.exp_test add partition PEXPTEST_DATA_20050822
VALUES LESS THAN (TO_DATE('2005/08/29','yyyy/mm/dd'))
tablespace PEXPTEST_DATA_TB1;

-----rebuild本地分区到新的表空间
alter index dwtest.PEXPTEST_SSN rebuild PARTITION PEXPTEST_DATA_20050822
TABLESPACE PEXPTEST_INDEX_TB1;

-----对新增分区进行表分析：
analyze table dwtest.exp_test partition (PEXPTEST_DATA_20050822)
estimate statistics sample 20 percent;

--第二步是将第一周的数据迁移到31数据库，关于数据迁移的方案，有三个可选，分别是可移动表空间、拖出表空间和数据泵倒入倒出
--前两个方案要求表空间是自包含的，不能对分区的单个表进行迁移，虽然也可以通过将分区的表空间先交换到一个临时表，然后将
--临时表的表空间通过移动表空间到目标数据库，然后再做一次分区交换到目标数据库的分区表中，应该会是速度上最快的一个方案，
--但是问题是在表空间从源数据库迁移到到目标数据库的时候，这一周的数据将是不能访问的。

--以下的试验是针对数据泵倒入倒出这个方案的：

--首先源数据库创建一个目录对象来存放转储的文件。（只需要创建一次）
CREATE DIRECTORY dumpout_dir AS '/home/oracle/backup/detail';

--用数据泵导出工具导出数据。 按照指定分区的方式导出该分区的所有数据
expdp system/****** TABLES=dwtest.exp_test:PEXPTEST_DATA_20050801 DUMPFILE=detail_out20050801.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY
指定只导出数据，不导出元数据。

----试验31的一个分区的expdp导出，记录142226992，需要11分钟，不到12个G。
[lisa@bj31 detail]$ expdp system/****** TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY

Export: Release 10.1.0.3.0 - Production on Monday, 15 August, 2005 9:58

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp DIRECTORY=dumpout_dir CONTENT = DATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.52 GB
. . exported "PCARD"."EXP_TEST":"PEXPTEST_P2" 11.22 GB 142226992 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home2/oracle/lisa/detail/detail_out20050815.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 10:09

--将文件detail_out20050801.dmp转移到远程系统中，放在目录指定目录下，可以导出的时候直接放置在nas设备上，这样目标数据库可以
--直接从nas设备上取，减少了数据文件传输的时间

--在目标数据库中创建一个目录对象。（只需要创建一次）
CREATE DIRECTORY dumpout_dir AS '/home/oracle/backup/detail';

--目标数据库上创建一个临时用户，用户做分区交换前的临时表
CREATE USER PCARD_TEMP
IDENTIFIED BY VALUES '******'
......;

--创建一个临时表，该表的名字和结构和分区表一样，但是是个普通表，表空间和目标分区使用的表空间一样。
--这个表需要每次重建，以保证使用的分区是正确的
drop table dwtest_temp.exp_test;
CREATE TABLE dwtest_temp.EXP_TEST
(
SSN VARCHAR2(24 BYTE) NOT NULL,
......
)
tablespace PEXPTEST_DATA_20050801
nologging;

--使用数据泵导入工具将该文件导入到该数据库中。
impdp system/****** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y
--指定将数据倒入到dwtest_temp.exp_test表中，不需要记录日志，不需要使用原有的表空间
----倒入只用了18分钟
[oracle@localhost local]$ impdp system/****** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y

Import: Release 10.1.0.2.0 - Production on Tuesday, 16 August, 2005 13:58

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=dumpout_dir DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "PCARD_TEMP"."EXP_TEST":"PEXPTEST_P2" 11.22 GB 142226992 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:16

--数据倒入以后创建索引，这个是所有的步骤中最耗时的部分，测试的时候是非并行的写日志的，如果采用并行不写日志，应该会再快一些
CREATE INDEX dwtest_temp.PEXPTEST_SSN ON dwtest_temp.EXP_TEST
(SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
tablespace PEXPTEST_INDEX_20050801
NOPARALLEL
nologging;

---测试的时候：创建索引，写日志的情况，用了1个半小时
SQL> CREATE INDEX dwtest_temp.PEXPTEST_SSN ON dwtest_temp.EXP_TEST
2 (SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
tablespace PEXPTEST_INDEX_20050801
NOPARALLEL;

--在数据倒入且索引创建完毕以后，交换表空间，包括索引表空间也可以交换过去，速度非常快
ALTER TABLE dwtest.exp_test
EXCHANGE PARTITION PEXPTEST_DATA_20050801
WITH TABLE dwtest_temp.exp_test
INCLUDING INDEXES
WITHOUT VALIDATION;

--在源数据库上删除已经迁移过去的分区，不删除对应的表空间，表空间是复用的
alter table dwtest.exp_test drop partition PEXPTEST_DATA_20050801;

--截至到这一步，一周的数据可以迁移到目标数据库的分区表中，测试的数据是1亿4千万条记录，大概需要的时间是2个小时，
--实际上我们一周的数据应该是7000万左右，所以时间还可以更少一些。

--对于历史数据的数据库，我们需要将3个月以上的记录移出数据库，操作实际上比在线数据库还更简单些，可以创建13个数据表空间和13个索引表空间，
--每周一在在线数据库的新数据倒入之前，将最前面一个分区删掉，创建一个新的分区使用原有的那个表空间，然后在临时表处理完毕后
--和临时表交换表空间，得到新的一周的数据，在这样的规划中，历史数据数据库默认保存13周的数据。

--另外在在线数据库中新增分区的脚本：add_new_partition_35.sh
--在线数据库使用数据泵导出最前面一个分区的脚本：expdp_old_partition_35.sh，调用parfile_out.sql生成expdp的参数文件
--历史数据库中删除第一个分区，使用数据泵倒入记录，分区交换，并在源数据库上删除第一个分区的脚本：exchange_data_31.sh,调用parfile_in.sql生成impdp的参数文件
--最后，因为表空间和数据文件不是动态重建的，所以对热备份没有影响，热备份的脚本不是动态的：dwtest_hot.sh

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

转载于:http://blog.itpub.net/82387/viewspace-1016820/