巧用外部表备份历史数据(r5笔记第62天)

在很多的系统中,随着时间的推移,都会沉淀大量的历史数据。一般数据量达到一定程度都会考虑使用分区表来处理。根据业务规则,可能有些历史数据隔一段时间就需要做清理了,这个时候历史数据就需要在分区级进行清理。在不同的系统,不同厂商都有不同的实现方案。但是从数据安全角度来说,都需要做备份工作,也是预防万一。 比如说我们存在一个表charge,就可能会有下面的几种分区规则, 一种是按照日期来分区,这样就能够很清楚的定位到哪些天的数据可以清理。 比如 6月9日的充值记录,分区表就为P_20150609,相关的一些分区如下: P_20150609 P_20150610 P_20150611 如果需要做清理就需要使用exp或者expdp来根据分区导出,这就完成了备份工作。 然后在分区层面使用truncate partition P_20150609或者drop partition P_20150609 来完成清理工作 还有一种方案是对于每个分区绑定一个对应的表空间,分区和表空间的情况如下。 P_20150609 TS_20150609 P_20150610 TS_20150610 P_20150611 TS_20150611 这样的情况下,就需要维护对应的表空间,如果数据量较大,就需要添加多个数据文件。 如果需要备份,还是采用exp或者expdp 对于清理工作,则可以直接删除数据文件或者使用truncate partition的形式。 可能分区规则不同,实现方式上都会有一些差别,但是总体来说,备份工作都是相对轻松的。清理工作的目标也很明确,要么清空分区,要么清理数据。 其实在这个时候,如果发生一些突发情况的时候,需要做数据恢复,就很郁闷了。 对于第一种方案来说,分区已经被清理之后,如果在特定的情况下需要恢复,就显得很困难。不行你可以试试,如果某些靠前的分区被删除之后,再想添加就不是那么容易的事情了。 而且就算行得通,imp,impdp的过程也会产生大量的归档文件,比如说数据量在100G,结果费了一番功夫恢复之后,可能对于开发来说,只是做一些数据确认而已。确认之后还是需要做分区的清理。 对于第二种方案,可能维护起来的范围较大,如果这种历史表很多的时候,维护大量的表空间就有些应接不暇了。但是也勉强能够接受。如果还是需要做数据恢复,可能开发也是做一些简单的数据校验和检查。 这个时候我们还是需要创建一些相关的数据文件,然后进行数据导入imp或者Impdp来完成。这个难度和第一种方案是一致的,归档的消耗,二次清理还有无形之中的性能影响。 所以对于历史表的这种处理,其实难度不在于备份和清理,难就难在一些恢复场景,比如备份了1T的表数据,在一些场景中需要做恢复,持续的时间,归档和性能,这种情况就会让人很抓狂了。 其实方法方式有很多,使用外部表就是一种思路。在这种情况下,外部表看起来就全是优点,exp/expdp做不到的它都能做到。 首先空间占用情况,在数据恢复的场景中,外部表不会占用额外的数据空间,创建一个外部表就如同创建一个同义词一样,没有额外的空间消耗。 其次来说说归档,外部表除了会生成极少量的日志文件(部分日志文件功能都可以禁用),对于归档几乎就是零贡献。 再次来说性能,这个部分就显得有些微妙,可能仁者见仁,智者见智了。比如在一些场景中需要做数据恢复,可能涉及的数据极少,这个时候就可以轻松使用一些过滤条件来完成一些复杂的数据过滤工作。 比如说表charge存在一个分区P_20150609 里面存放着100万条记录。 可能在数据恢复的时候需要检查在晚上8点到9点的数据,假设有10万条。这个时候假设我们基于分区P_20150609创建了外部表 charge_ext_20150609,则我们可以添加一些额外的过滤条件,创建出一个临时表什么的, create table xxxx nologging as select *from charge_ext_20150609 where charge_date between xxx_8pm to xxx_9pm; 这个时候这种热部署的优势就显现出来了,有了这些过滤后的数据,我们可以随时卸载外部表。本身来说对于系统的影响应该说降到了最低。 如果真要做全表数据恢复,外部表也不逊色,我们可以使用insert /*+append*/的方式做数据插入,速度也还是不错的。 我们来简单演示一个案例。 备份,我们可以使用oracle_datapump来生成对应的外部表文件

create table test.charge_ext_20150609
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY xxxxx 
     LOCATION (
     ‘charge_201506609.dmp’……….
    )
    )
    parallel 4    as
    select /*+ parallel(t 4) */ * from charge partition(P_20150609) t;
    
    drop test.mo1_memo_ext; --生成dump文件之后,删除外部表。

数据恢复 先加载外部表,这个过程就跟创建一个同义词一样快。

Create table charge_ext_20150609
   (    id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob xxxx,charge_date date
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "EXPDP_LOCATION"
      LOCATION(
‘charge_20150609.dmp’
       )
    )
   PARALLEL 2;

小量数据检查 create table charge_tmp_20150609 as select *from charge_ext_20150609 where charge_date between xxx_8pm to xxx_9pm;

全量恢复,对于数据全量恢复可以使用insert append的方式 Insert /*+append */ into xxx.charge select *from charge_ext_20150609;

Commit;

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-06-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏安全领域

5 分钟内造个物联网 Kafka 管道

原文地址:https://dzone.com/articles/creating-an-iot-kafka-pipeline-in-under-five-min...

47710
来自专栏逸鹏说道

SQL Server安全(3/11):主体和安全对象(Principals and Securables)

在保密你的服务器和数据,防备当前复杂的攻击,SQL Server有你需要的一切。但在你能有效使用这些安全功能前,你需要理解你面对的威胁和一些基本的安全概念。这篇...

2764
来自专栏王硕

原 Postgres-X2 MPP部署试验

3916
来自专栏web编程技术分享

【php增删改查实例】第十一节 - 部门管理模块(编辑功能)

1688
来自专栏杨建荣的学习笔记

一则报警信息所折射出来的诸多问题(r9笔记第14天)

在主备库环境中,如果出现数据文件级的一些不一致,后期修复会很麻烦,所以这种情况可以提前规避,减少后期的隐患,我定制了一个数据库监控选项,即数据文件状态的检查。 ...

3548
来自专栏祝威廉

如何提高ElasticSearch 索引速度

这篇文章会讲述上面几个参数的原理,以及一些其他的思路。这些参数大体上是朝着两个方向优化的:

1423
来自专栏张善友的专栏

MongoDB核心贡献者:不是MongoDB不行,而是你不懂!

近期MongoDB在Hack News上是频繁中枪。许多人更是声称恨上了MongoDB,David mytton就在他的博客中揭露了MongoDB许多现存问题。...

24510
来自专栏鸿的学习笔记

闲话聊聊事务处理(中)

上面提到了multi-object事务,但是要完美的处理multi-object事务并不容易。因为我们必须要面对并发问题导致的bug,而隔离性要求数据系统...

1092
来自专栏互扯程序

MyCat安装与测试教程 超详细!

MyCat基础知识 一、什么是MYCAT? 1. 一个彻底开源的,面向企业应用开发的大数据库集群 2. 支持事务、ACID、可以替代MySQL的加强版...

9106
来自专栏GopherCoder

专栏:010:SQL VS No SQL

1613

扫码关注云+社区

领取腾讯云代金券