数据清理的遗留问题处理(r6笔记第87天)

最近处理一个遗留问题,感觉手动修复真是让人抓狂,所以花了点力气写了一个半自动的脚本,总算从这个繁琐的工作中解放出来了。

问题的背景如下图所示。

存在一个很大的统计库(有容灾备库),还有一个历史统计库,历史统计库中都是相对较老的数据。

统计库中的数据相对要新一些,但是数据持续增长,空间使用太多,业务中使用历史数据的频率不高,把历史数据清理了又怕影响业务,就需要把数据暂时挪到历史库中,所以历史库中的数据都是几年前的老数据,而统计库中的都是近几年相对较新的数据。

比如一个分区表test,有2011年的分区数据在历史库中,有2014年的数据在统计库中,统计库中的数据太多,空间不足就需要把数据从统计库清理掉,同事保证历史库中存在这份数据。

可能之前的同事把有些表空间和分区绑定了起来,所以涉及的表空间非常多,需要检查这些表空间中所对应的数据文件,表空间所在的段中的分区数据情况,然后再 在历史库中检查一遍,确定两边查到的数据条数是一样的(历史数据不会有dml改动),如果数据在两边都存在,就删除现网统计库中的,然后删除对应的数据文 件,对应的表空间。

之前是每隔一周或者两周左右就会做一次这样的检查和清理工作,所以得时常惦记着,想多删点,因为手动校验检查处理着实费神费力,所以也删不了太多。

所以下了决心改进这个情况,至少做成半自动化,人工审核还是需要的,为了保证不误删,检查出现偏差。

我写了一个简单的脚本,运行内容如下:

清理之前,查看有多少含有DATA字样的表空间可清理,清理年份为2012年

check genaral status of data from year 2012

SIZE_MB

----------

308410

检查可清理的段情况,都是分区表和分区索引段。

check segement size summary from year 2012

SEGMENT_TYPE SIZE_MB

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

INDEX PARTITION 255325.188

TABLE PARTITION 288483

检查可清理的段情况,有多少可清理

check segment count summary from year 2012

SEGMENT_TYPE COUNT(*)

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

INDEX PARTITION 4148

TABLE PARTITION 1505

然后就得到了计划清理的表空间,数据文件和文件大小

CONSUMEID_DATA_20121008 +DATA/sgstatdb3/datafile/consumeid_data_20121008.758.840501581 200

CONSUMEID_DATA_20120705 +DATA/sgstatdb3/datafile/consumeid_data_20120705.752.840501565 210

CONSUMEID_DATA_20120403 +DATA/sgstatdb3/datafile/consumeid_data_20120403.742.840501535 230

CONSUMEID_DATA_20120704 +DATA/sgstatdb3/datafile/consumeid_data_20120704.743.840501541 230

CONSUMEID_DATA_20121009 +DATA/sgstatdb3/datafile/consumeid_data_20121009.746.840501549 230

CONSUMEID_DATA_20121007 +DATA/sgstatdb3/datafile/consumeid_data_20121007.741.840501535 240

然后统计有多少表空间可清理。

CONSUMEID_DATA_20121008 200

CONSUMEID_DATA_20120705 210

CONSUMEID_DATA_20121009 230

CONSUMEID_DATA_20120403 230

。。。

然后根据条件生成查看表分区数据的sql语句。

select 'TEST:TEST_SERVER_LOG_SERVER_LOG_20120410', count(*) from TEST.TEST_SERVER_LOG partition (SERVER_LOG_20120410);

select 'TEST:TEST_SERVER_LOG_SERVER_LOG_20120411', count(*) from TEST.TEST_SERVER_LOG partition (SERVER_LOG_20120411);

select 'TEST:TEST_SERVER_LOG_SERVER_LOG_20120412', count(*) from TEST.TEST_SERVER_LOG partition (SERVER_LOG_20120412);

。。。。

在统计库和历史统计库中查看。

统计库中查看

TEST:TEST_SERVER_LOG_SERVER_LOG_20120410 2118970

TEST:TEST_SERVER_LOG_SERVER_LOG_20120411 2145005

TEST:TEST_SERVER_LOG_SERVER_LOG_20120412 2128818

历史统计库中查看

TEST:TEST_SERVER_LOG_SERVER_LOG_20120410 2118970

TEST:TEST_SERVER_LOG_SERVER_LOG_20120411 2145005

TEST:TEST_SERVER_LOG_SERVER_LOG_20120412 2128818

比对两边的数据情况,如果一致则删除

alter table TEST.TEST_SERVER_LOG drop partition (SERVER_LOG_20120410);

alter table TEST.TEST_SERVER_LOG drop partition (SERVER_LOG_20120411);

alter table TEST.TEST_SERVER_LOG drop partition (SERVER_LOG_20120412);

清理完成之后开始确认表空间中是否存在其它的段,然后开始尝试删除数据文件。

初版脚本如下,后面需要不断完善,不过目前所列的这些基本步骤都做到了,很多繁琐的检查工作都给提炼出来了,不用重复执行,费时费力了。

tmp_year=2012
conn_dba=testdba/testdba
hist_conn_dba=tesetdba/testdba@statdb_hist
sqlplus -s $conn_dba <<EOF
prompt check genaral status of data from year $tmp_year
select sum(bytes/1024/1024) size_MB from dba_data_files where tablespace_name like '%DATA%${tmp_year}%' ;
prompt check segement size summary from year $tmp_year
select segment_type,sum(bytes/1024/1024) size_MB from dba_segments where  tablespace_name in (select tablespace_name from dba_data_files where  tablespace_name like '%${tmp_year}%' ) group by segment_type;
prompt check segment count summary from year $tmp_year
select segment_type,count(*) from dba_segments where tablespace_name in  (select tablespace_name from dba_data_files where tablespace_name like  '%${tmp_year}%' ) group by segment_type;
set linesize 200
col file_name format a70
set pages 0
select tablespace_name,file_name,sum(bytes/1024/1024) size_MB from  dba_data_files where tablespace_name like '%DATA%${tmp_year}%' group by  tablespace_name,file_name order by size_MB;
select tablespace_name,sum(bytes/1024/1024) size_MB from dba_data_files  where tablespace_name like '%DATA%${tmp_year}%' group by tablespace_name  order by size_MB;
set feedback off
set linesize 200
spool get_tab_part_cnt.sql
select 'select '||chr(39)||owner||':'||segment_name||'_'||partition_name  ||chr(39)||', count(*) from '||owner||'.'||segment_name||' partition  ('||partition_name||');' 
from dba_segments where tablespace_name in (select tablespace_name from  dba_tablespaces  where tablespace_name like '%DATA%${tmp_year}%') and  segment_type in ('TABLE PARTITION') and rownum<10;
spool off
EOF
sqlplus -s $conn_dba <<EOF
set pages 0
set feedback off
spool get_tab_part_cnt.log_statdb2
@get_tab_part_cnt.sql
spool off
EOF
sqlplus -s $hist_conn_dba <<EOF
set pages 0
set feedback off
spool get_tab_part_cnt.log_statdb2_hist
@get_tab_part_cnt.sql
spool off
EOF
sdiff get_tab_part_cnt.log_statdb2 get_tab_part_cnt.log_statdb2_hist > tab_cnt_summary.lst
diff_cnt=`diff get_tab_part_cnt.log_statdb2 get_tab_part_cnt.log_statdb2_hist`
sqlplus -s $conn_dba <<EOF
set feedback off
set pages 0
spool drop_tab_part.sql
select 'alter table  '||owner||'.'||segment_name||' drop partition ('||partition_name||');' 
from dba_segments where tablespace_name in (select tablespace_name from  dba_tablespaces  where tablespace_name like '%DATA%${tmp_year}%') and  segment_type in ('TABLE PARTITION') and rownum<10;
spool off;
EOF

有的朋友可能疑惑为什么不用db link直接比较,因为使用db link来指定具体的分区就不支持了。

select count(*) from TEST.TEST_SERVER_LOG@db_link partition (SERVER_LOG_20120410);

ORA-14100: partition extended table name cannot refer to a remote object

要想突破,搜到的解决方法有两种,但是都果断放弃了,因为确实也没有太好的效果。

解决方式有两种:

1、 不使用partition选项,而在where的条件里进行数据筛选

2、 在源库设立视图,指向分区数据,通过dblink访问该视图

另外通过创建远程表分区的同义词可以达到避过该错误的目的,但效果和全表扫描一样,并不能实现分区扫描,所以并无意义

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

原文发表时间:2015-10-13

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

维护索引(3)——通过重建索引提高性能

前言: 重建一个索引只是在内部删除并重建索引,使得碎片消失、统计信息更新、物理顺序重新排列组织。它会压缩数据页,按照填充因子填充适当的数据。如果有需要,也会添加...

26840
来自专栏数据和云

【安全警告】Oracle 12c 多租户的SQL注入高危风险防范

在使用Oracle多租户选件时,由于Container容器和PDB融合共存,则权限控制必将更加重要,在之前的文章中我们提到,Oracle 12.2 的 loc...

37960
来自专栏数据和云

一个不懂业务的DBA不是好的DBA

编辑手记:懂业务,懂系统逻辑,你才能做一个更好的DBA。 在数据库巡检中发现一个MES生产信息数据库中一个存储过程中一条SQL单次逻辑读为2100,且执行很频繁...

30660
来自专栏数据和云

解锁不可见索引新特性,处理ORA-01555故障

何国亮 云和恩墨交付部技术顾问,获得 Oracle 11g OCM 认证。有超过 6 年超大型数据库专业服务经验,曾为通信运营商、银行、保险、政府、制造业...

13950
来自专栏Hadoop数据仓库

HAWQ技术解析(八) —— 大表分区

一、HAWQ中的分区表         与大多数关系数据库一样,HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表,外部分区表在后面“外部数据”篇讨...

28370
来自专栏帘卷西风的专栏

创建角色随机名字(mysql抽取随机记录)和mysql游标的使用

1、现在创建游戏角色的时候,基本上都是支持角色名字随机的,以前此功能在客户端用代码实现,然后向服务器请求并验证,后来发现有时候连续几次都失败,所以改成在服务器...

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

MySQL 5.7中锁的一个通用问题

前几天分析了一个死锁的问题,有一个网友看了以后,就发了邮件给我问一个问题。一般来说,能够发送邮件提出问题的同学,都是很认真的,因为他要准备好日志,准备好操作过...

39090
来自专栏idba

死锁案例之三

一 前言 死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能...

8320
来自专栏学习有记

深入非聚集索引:SQL Server索引进阶 Level 2

13030
来自专栏idba

如何阅读死锁日志

一 前言 工欲善其事必先利其器,前面分析了很多死锁案例,并没有详细的介绍如何通过死锁日志来诊断死锁的成因。本文将介绍如何读懂死锁日志,尽可能的获取信息来辅助我...

19530

扫码关注云+社区

领取腾讯云代金券