数据库清理归档1

          数据库开启归档模式后，如果业务量大，通俗解释就是对数据库数据的修改多，那么归档日志就会很多，如果不及时清理归档，那么归档目录满了的话，归档无法写入，会引起数据库宕机。所以数据库清理归档脚本十分重要。
         就我所知道的，清理归档脚本主要分为两种，一种是基于归档时间的清理，一种是基于目录占用空间的清理。
         在选择哪种方式清理前，要先有个概念：即归档目录的大小是怎么确定的。这个基于统计平常数据库的归档量和应用的需求。比如，应用和领导要求保存5天的归档，那么你要根据每日的归档量，挑一个最大值，乘以7或者8的样子。可以根据以下脚本进行查询：
######################################################

SELECT trunc(first_time) "Date",

to_char(first_time, 'Dy') "Day",

count(1) "Total",

SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",

SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",

SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",

SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",

SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",

SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",

SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",

SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",

SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",

SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",

SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",

SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",

SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",

SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",

SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",

SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",

SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",

SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",

SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",

SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",

SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",

SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",

SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",

SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23",

round(count(1) / 24, 2) "Avg"

FROM V$log_history

group by trunc(first_time), to_char(first_time, 'Dy')

Order by 1;
###########################################
在明确了这点概念后，我们就能大概知道，如果一个数据库未来业务持续增长，或者某一天归档会突然增大，就会倾向于选择基于空间的清理。至于后面申请扩展存储，就是后话。
什么时候倾向选择基于时间的清理呢？当数据库的归档有特殊用途的时候，比如搭建ogg就需要抽取归档。

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

转载于:http://blog.itpub.net/31404823/viewspace-2131173/