利用交换分区和传输表空间技术,对历史数据实施分离

Tags

交换分区:exchange partition

表空间传输:transport tablespace

 

标题:

利用交换分区和传输表空间技术,对历史数据实施分离。

 

第一部分:准备测试环境

一、首先模拟源库(JCR10G):

CREATE TABLESPACE TBS_DATA_0906 DATAFILE

  'F:\ORACLE\ORADATA\JCR10G\TBS_DATA_0906.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON

/

 

CREATE TABLESPACE TBS_indx_0906 DATAFILE

  'F:\ORACLE\ORADATA\JCR10G\TBS_indx_0906.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON

/

 

CREATE TABLESPACE TBS_DATA_0907 DATAFILE

  'F:\ORACLE\ORADATA\JCR10G\TBS_DATA_0907.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON

/

 

CREATE TABLESPACE TBS_indx_0907 DATAFILE

  'F:\ORACLE\ORADATA\JCR10G\TBS_indx_0907.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON

/

create user edu identified by jyedu

  DEFAULT TABLESPACE jing

  TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO edu;

  GRANT RESOURCE TO edu;

    ALTER USER edu DEFAULT ROLE CONNECT, RESOURCE;

      GRANT SELECT ANY DICTIONARY TO edu;

/

 

 

 

Edu用户下存储各历史数据,这些数据按表存放,均为分区表。

一个月一个分区,存储在不同的表空间中,索引和数据分开存储。

 

二、目标库(HIS)

CREATE TABLESPACE tbs_his DATAFILE

  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HIS\tbs_his01.DBF' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

 

create user his identified by his

  DEFAULT TABLESPACE tbs_his

  TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO his;

  GRANT RESOURCE TO his;

    ALTER USER his DEFAULT ROLE CONNECT, RESOURCE;

      GRANT SELECT ANY DICTIONARY TO his;

/

create directory jcr as 'f:\';

用户his,默认表空间tbs_his

 

 

第二部分:在源库准备测试用表

 

create table message

(id number(5),name varchar2(30),createdate date)

tablespace jing

partition by range(createdate)

(partition part0906 values less than(to_date('20090701','yyyymmdd')) tablespace tbs_data_0906 compress,

partition part0907 values less than(to_date('20090801','yyyymmdd')) tablespace tbs_data_0907)

/

create index idx_message_createdate

on message(createdate)

local

(partition part0906 tablespace tbs_indx_0906,

partition part0907 tablespace tbs_indx_0907)

tablespace indx;

/

create index idx_message_id

on message(id)

tablespace indx;

/

create table sms_sent

(id number(5),name varchar2(30),createdate date)

tablespace jing

partition by range(createdate)

(partition part0906 values less than(to_date('20090701','yyyymmdd')) tablespace tbs_data_0906 compress,

partition part0907 values less than(to_date('20090801','yyyymmdd')) tablespace tbs_data_0907)

/

create index idx_sms_sent_createdate

on sms_sent(createdate)

local

(partition part0906 tablespace tbs_indx_0906,

partition part0907 tablespace tbs_indx_0907)

tablespace indx;

/

 

 

 

select t.TABLE_NAME,t.PARTITION_NAME,t.PARTITION_POSITION,t.TABLESPACE_NAME

from user_tab_partitions t

order by 1,3

 

TABLE_NAME

PARTITION_NAME

PARTITION_POSITION

TABLESPACE_NAME

MESSAGE

PART0906

1

TBS_DATA_0906

MESSAGE

PART0907

2

TBS_DATA_0907

SMS_SENT

PART0906

1

TBS_DATA_0906

SMS_SENT

PART0907

2

TBS_DATA_0907

 

 

select i.TABLE_NAME,i.INDEX_NAME,i.TABLESPACE_NAME,i.STATUS

from user_indexes i

order by 1

 

TABLE_NAME

INDEX_NAME

TABLESPACE_NAME

STATUS

MESSAGE

IDX_MESSAGE_CREATEDATE

[NULL]

N/A

MESSAGE

IDX_MESSAGE_ID

INDX

VALID

SMS_SENT

IDX_SMS_SENT_CREATEDATE

[NULL]

N/A

 

 

select p.INDEX_NAME,p.PARTITION_NAME,p.TABLESPACE_NAME,p.STATUS

from user_ind_partitions p

order by 1,2

 

INDEX_NAME

PARTITION_NAME

TABLESPACE_NAME

STATUS

IDX_MESSAGE_CREATEDATE

PART0906

TBS_INDX_0906

USABLE

IDX_MESSAGE_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

IDX_SMS_SENT_CREATEDATE

PART0906

TBS_INDX_0906

USABLE

IDX_SMS_SENT_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

 

 

1、  MESSAGE表,一个全局索引,一个本地索引(global and local

2、  SMS_SENT表,只有一个本地索引local

 

两个表均为两个分区,67月份的数据以分区的形式分别存放在tbs_data_0906tbs_data_0907表空间中。本地索引也分别存放在tbs_indx_0906tbs_indx_0907两个表空间中。全局索引存放在Indx表空间中。这两个表的默认表空间均为jing

 

数据定义好了,接下来造数据:

begin

for i in 1..100 loop

insert into message values(i,i||'a'||i,decode(power(-1,i),1,to_date('20090704','yyyymmdd'),-1,to_date('20090608','yyyymmdd')));

end loop;

end;

/

begin

for i in 1..100 loop

insert into sms_sent values(i,i||'a'||i,decode(power(-1,i),1,to_date('20090704','yyyymmdd'),-1,to_date('20090608','yyyymmdd')));

end loop;

end;

 

 

第三部分:历史数据分离(把6月份数据迁移到目标数据库HIS中)

 

 

采用传输表空间,虽然说tbs_data_0906tbs_indx_0906两个表空间中只存有6月份的数据,但由于6月份的数据和其他月份的数据是保存在一个逻辑表中的,因此这两个表空间不是自包含的。需要特殊处理一下。

 

处理方法:先通过交换分区,把6月份的分区交换出去,成为一个独立的逻辑表。

 

一、准备中间表

create table message_0906

(id number(5),name varchar2(30),createdate date)

tablespace jing;

/

create table sms_sent_0906

(id number(5),name varchar2(30),createdate date)

tablespace jing;

 

create index idx_sms_sent0906_createdate

on sms_sent_0906(createdate)

tablespace indx;

 

二、Exchange Partition

6月份分区和中间表进行交换(交换操作是直接修改数据字典,因此会很快)

1、不带update indexes

alter table sms_sent exchange partition part0906 with table sms_sent_0906;

alter table message exchange partition part0906 with table message_0906;

                           

                           

TABLE_NAME

PARTITION_NAME

PARTITION_POSITION

TABLESPACE_NAME

MESSAGE

PART0906

1

JING

MESSAGE

PART0907

2

TBS_DATA_0907

SMS_SENT

PART0906

1

JING

SMS_SENT

PART0907

2

TBS_DATA_0907

 

 

 

 

TABLE_NAME

INDEX_NAME

TABLESPACE_NAME

STATUS

MESSAGE

IDX_MESSAGE_CREATEDATE

[NULL]

N/A

MESSAGE

IDX_MESSAGE_ID

INDX

UNUSABLE

SMS_SENT

IDX_SMS_SENT_CREATEDATE

[NULL]

N/A

SMS_SENT_0906

IDX_SMS_SENT0906_CREATEDATE

INDX

UNUSABLE

 

 

 

 

INDEX_NAME

PARTITION_NAME

TABLESPACE_NAME

STATUS

IDX_MESSAGE_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_MESSAGE_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

IDX_SMS_SENT_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_SMS_SENT_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

 

 

2、带update indexes

alter table sms_sent exchange partition part0906 with table sms_sent_0906 update indexes;;

alter table message exchange partition part0906 with table message_0906 update indexes;;

 

                           

TABLE_NAME

PARTITION_NAME

PARTITION_POSITION

TABLESPACE_NAME

MESSAGE

PART0906

1

JING

MESSAGE

PART0907

2

TBS_DATA_0907

SMS_SENT

PART0906

1

JING

SMS_SENT

PART0907

2

TBS_DATA_0907

 

 

 

 

TABLE_NAME

INDEX_NAME

TABLESPACE_NAME

STATUS

MESSAGE

IDX_MESSAGE_CREATEDATE

[NULL]

N/A

MESSAGE

IDX_MESSAGE_ID

INDX

VALID

SMS_SENT

IDX_SMS_SENT_CREATEDATE

[NULL]

N/A

SMS_SENT_0906

IDX_SMS_SENT0906_CREATEDATE

INDX

UNUSABLE

 

 

 

 

INDEX_NAME

PARTITION_NAME

TABLESPACE_NAME

STATUS

IDX_MESSAGE_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_MESSAGE_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

IDX_SMS_SENT_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_SMS_SENT_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

 

 

结论:

1、  加不加update indexes都会使交换分区上的本地索引失效,但奇怪的是dml操作却正常(很疑惑)。

2、  update indexes可使分区表上的global indexes保持正常。

3、  交换分区的时候,索引并不会跟着交换。

 

根据以上测试情况,我们可以把数据按月分区,并按月存储在不同的表空间中(索引无需分表空间)。这样,通过传输表空间技术可大大提高历史数据的分离和加载效率。另外,在对历史数据进行备份的时候,我们可以按月份分表空间进行备份。

 

三、Transport Tablespace

(由于索引表空间不进行传输,先把刚才在中间表上创建的索引删除)

drop index idx_sms_sent0906_createdate

 

目前状态如下:

                           

TABLE_NAME

PARTITION_NAME

PARTITION_POSITION

TABLESPACE_NAME

MESSAGE

PART0906

1

JING

MESSAGE

PART0907

2

TBS_DATA_0907

SMS_SENT

PART0906

1

JING

SMS_SENT

PART0907

2

TBS_DATA_0907

 

 

 

 

TABLE_NAME

INDEX_NAME

TABLESPACE_NAME

STATUS

MESSAGE

IDX_MESSAGE_ID

INDX

VALID

MESSAGE

IDX_MESSAGE_CREATEDATE

[NULL]

N/A

SMS_SENT

IDX_SMS_SENT_CREATEDATE

[NULL]

N/A

 

 

 

 

INDEX_NAME

PARTITION_NAME

TABLESPACE_NAME

STATUS

IDX_MESSAGE_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_MESSAGE_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

IDX_SMS_SENT_CREATEDATE

PART0906

TBS_INDX_0906

UNUSABLE

IDX_SMS_SENT_CREATEDATE

PART0907

TBS_INDX_0907

USABLE

 

1、检查表空间是否自包含(6月份数据表空间tbs_data_0906

begin

dbms_tts.TRANSPORT_SET_CHECK('tbs_data_0906',true);

end;

select *from transport_set_violations;

查询临时表结果为空,说明表空间自包含,可以进行传输表空间操作。

 

2、把tbs_data_0906表空间设为只读

alter tablespace tbs_data_0906 read only;

 

 

3、在目标库创建公共数据库链接

目的是为了直接把元数据导入到目标库。

create public database link system_jcr10g

connect to system identified by sys

using '(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.180)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = JCR10G)

    )

   )'

 

4copy表空间数据文件到目标数据库目标位置

SQL> host copy f:\oracle\oradata\jcr10g\tbs_data_0906.dbf  f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf

已复制         1 个文件。

 

5、使用impdp数据泵导入元数据

C:\Jcr>impdp system/sys directory=jcr network_link=system_jcr10g transport_tablespaces=tbs_data_0906 transport_datafiles=f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf transport_full_check=y remap_schema=edu:his

 

Import: Release 10.2.0.4.0 - Production on 星期二, 21 7, 2009 16:21:08

 

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

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=jcr network_link=system_jcr10g transport_tablespaces=tbs_data_0906 transport_datafiles=f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf transport_full_check=y remap_schema=edu:his

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

处理对象类型 TRANSPORTABLE_EXPORT/TABLE

处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 16:21:26 成功完成

 

6、更改目标表空间为读写状态

alter tablespace tbs_data_0906 read write;

 

7The End

SQL> conn his/his@his

已连接。

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

MESSAGE_0906

SMS_SENT_0906

 

SQL> select count(*) from message_0906;

 

  COUNT(*)

----------

        51

 

SQL> select count(*) from sms_sent_0906;

 

  COUNT(*)

----------

        50

 

 

 

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

转载于:http://blog.itpub.net/13890753/viewspace-610588/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
曾经有过四年的致远A6 2.83 维护经历。这中间,致远方面肯定不会提供相关信息,市场上任何ERP的维护、二开都是价格不菲,这个就是IT,跟所有智力行业一样,靠知识寻求价值!要不花钱,就自己专研!而你自己形成的成果,本身就可以转换为经济价值!当然,在很多外行看来,IT从业者都是服务员,呼之来使之去........并不是每件事都是愉快的,并不是每个地方都有发展的!在四川,除了成都,其它市县的IT从业并不乐观! 一晃几年过去了,偶然翻开自己存放在QQ群里的笔记,颇有感触,于是想着不如把它发出来吧! 回到正题,致远A6 2.83 系统中很多东西都是自己打开疑似文件进行分析,于是便总结了许多维护中的经验。这款OA比较老,大约是2005年的设计思想,JSP+MySQL组合。其中数据库在安装时使用了固定密码,这个文件中有!我是使用Navicat for MySQL链接数据库进行代码执行,或者日常数据修改。 为什么要分离呢?因为A6是一款小企业用的OA,而这家公司有2000个用户,工作日活跃用户350人左右,在没有与其他ERP数据对接的情况下,每天都会产生大量数据,其中最大的流程表在很短时间就突破6千万条数据,而每个用户,点开每条协同都会单独在这个张表中查询自己的数据,很快数据库就会进入查询排队,直到超时丢弃。查询缓慢,数据丢失就会发生。所以需要定期对数据库进行分离,部署成历史数据库和在用数据库,在用数据库清空了所有协同数据,就相当于重新安装的OA,运行数据当然会很快。这就为什么要做数据库分离操作的原因! 下载的压缩包中有两个文件,一个比较全,是自由部署A6时一些需要修改的文件或者你想修改来达到特定目的的文件,如安装路径修改、OA附件上传大小修改、端口修改等,并包含数据库分离代码。另一个文件是专门对分离代码进行解说,分离代码是对数据库分析后,对影响数据库性能的表单进行清除,保留有用信息,主要是清除了协同信息,保留了用户的表单模板、表单查询等有用信息。 作为DBA,分离数据库前做好备份是必须的,在测试服务器演练也是必须的!备份数据的难点在于附件文件的备份,即/upload文件夹........慢慢等吧!根据服务器配置,整个工程时间至少需要两天,其中大部分时间用于数据备份,数据库分离代码执行时间预计12个小时(我这个数据很大,所以.....)。分离之后的数据库,用来部署为在用服务器。分离前的数据库用来部署历史数据库,所以建议使用两台服务器分别部署,也就是要安装两套OA服务器,各自访问各自的数据,由于所有历史数据将会部署到一个服务器,也就是该服务器将安装多套AO,所以需要修改数据库端口和Web端口来避免冲突!部署时,某些图片文件需要复制到对应文件夹! 根据各自公司的情况来定分离时间吧,我这个是2年分离一次(时间选择年底是最佳哦)!通过对my.ini、xxsStartTomcat.bat、server.xml文件的优化,这个软件可以一直用到企业破产......A6其它版本或许略有差别,可以自己测试!

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值