数据库历史数据有效管理

1.1. 应用场景

业务系统运行一段时间后,与时间有关的数据库库表中保存有多年前的数据,这些数据平时基本很少需要变更,但数据记录数量巨大，读写性能逐月下降，同时备份的压力也增加（备份集越来越大，备份时间越来越长）。

[@more@]

1.2. 有效管理方法

首先,将历史数据与当前数据进行有效的分割，分割的方法很多，这里列举两个普遍的做法：

A.重新建立与当前数据表表结构相同的历史表，将历史的数据插入到这些历史表中，然后建立视图，将当前表与历史表union起来，这些历史表是建立在历史表空间上的。这种情况，应用程序尤其是查询统计的程序需要做一定的变更。

B.使用分区表，将历史数据相关的分区存放或者迁移到历史数据库表空间上。

其次,更改历史表空间的状态为readonly，历史表空间可以建立在低档的存储或者光盘库上，不需要访问时，也可以offline它。我们在将历史数据表空间状态设置为readonly状态后，对历史表空间进行一次全备份（怕麻烦的话，就直接做一次全库备份就可以了），在这之后我们使用rman进行数据库全库备份时，可以使用选项skip readonly(/offline)来跳过历史表空间的备份,大大减少了备份的压力。同时因为大部分数据从主力表迁移到历史表中,数据库库表的记录量回到了正常状态,数据库运行性能也有了很大的提高。

1.3. 操作实例

这里演示第一种数据切割的方法，建立相同表结构的历史表。

1.3.1. 创建相关的表,插入测试数据

SQL>Conn / as sysdba

SQL>select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE

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

AIDU USERS

建立历史数据存放用的表空间aidu_ls

SQL>create tablespace aidu_ls datafile '/oracle/oradata/aidu_ls01.dbf' size 10m extent management local segment space management auto logging

Tablespace created.

SQL>conn aidu/aiduteset

建立测试表,插入部分数据

SQL>Create table test01(id number(10,2),rq date,remark varchar2(100),primary key(id)) tablespace users;

SQL>alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS' ;

SQL>insert into test01 values(1,'2010-01-01','test for read only tablespace');

SQL>insert into test01 values(2,'2010-02-01','test for read only tablespace');

SQL>insert into test01 values(3,'2010-03-01','test for read only tablespace');

SQL>insert into test01 values(4,'2010-04-01','test for read only tablespace');

SQL>insert into test01 values(5,'2010-05-01','test for read only tablespace');

SQL>insert into test01 values(6,'2010-06-01','test for read only tablespace');

SQL>insert into test01 values(7,'2010-07-01','test for read only tablespace');

SQL>insert into test01 values(8,'2010-08-01','test for read only tablespace');

SQL>insert into test01 values(9,'2010-09-01','test for read only tablespace');

SQL>insert into test01 values(10,'2010-10-01','test for read only tablespace');

SQL>insert into test01 values(11,'2010-11-01','test for read only tablespace');

SQL>insert into test01 values(12,'2010-12-01','test for read only tablespace');

SQL>insert into test01 values(101,'2011-01-01','test for read only tablespace');

SQL>insert into test01 values(102,'2011-02-01','test for read only tablespace');

SQL>insert into test01 values(103,'2010-03-01','test for read only tablespace');

SQL>commit;

创建历史表,指定存储到历史数据表空间aidu_ls上.

SQL> create table test01_ls tablespace aidu_ls as select * from test01 where trunc(rq,'YY')

SQL>alter table test01_ls add primary key(id) using index tablespace aidu_ls;

SQL> select table_name,tablespace_name from all_tables where owner='AIDU';

TABLE_NAME TABLESPACE_NAME

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

TEST01 USERS

TEST01_LS AIDU_LS

通过上面的查询,可以看到历史数据表aidu_ls建立在历史数据表空间aidu_ls上了。

1.3.2. 创建视图，访问所有的数据

SQL>Create view test01_all as select * from test01 union all select * from test01_ls;

1.3.3. 设置历史数据表空间aidu_ls为只读(read only)

SQL>alter tablespace aidu_ls read only;

1.3.4. 执行全库备份(或者表空间备份,或者表空间文件拷贝备份)

Rman target /

Run{

Allocate channel d1 type disk;

Backup full database format ‘/oracle/rman/full_db_1_%U.bak’;

Release channel d1;}

1.3.5. 以后进行数据库全备份时,可以跳过read only的表空间

Rman target /

Run{

Allocate channel d1 type disk;

Backup full database skip readonly format ‘/oracle/rman/full_db_%U.bak’;

Release channel d1;}

备份的时候就自动跳过read only状态下的历史表空间,减少了备份的压力。

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

转载于:http://blog.itpub.net/32980/viewspace-1053140/