接续上一个文章：记oracle日志挖掘实操&查询归档不正常增长情况(一)

问题：最近几周经常手动删除归档日志，归档日志报空间不足（预留800G空间已用完），基于此查询归档日志情况（近期业务有所上涨），优化备份脚本保留时间。

1、查看logtab表中的数据格式

desc logtab;select * from logtab where rownum<11;SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO，SEGMENT_NAME,TABLE_NAME FROM logtab WHERE username IN ('PDBZ, 'JW');

查询后的结果，table_name是以object的id显示的如：'OBJ# 55830',

#号后面就是时间的OBJECT_ID;

可通过user_objects，或者dba_objects查找对应表叫什么。

select * from user_objects where OBJECT_ID ='55830

2、根据table_name和sql_undo的内容查询：

和归档相关的操作一般是增删改（INSERT,UPDATE,DELETE）

select table_name,count(1) from logtab where username is not nul and  sql_undo like '%UPDATE%'  group by table_name;select table_name,count(1) from logtab where username is not nul and sql_undo like '%INSERT%'  group by table_name;select table_name,count(1) from logtab where username is not nul and sql_undo like '%DELETE%' group by table_name ;

有些情况挖掘出来的数据里面有username,可以通过username进一步筛选，缩小范围

select table_name,count(1) from logtab where username = 'HR' and  sql_undo like '%INSERT%' group by table_name;;select table_name,count(1) from logtab where username = 'HR' and sql_undo like '%DELETE%' group by table_name;

3、结合OBJECT_ID,去查询对应的table_name是什么；

select * from user_objects where OBJECT_ID in （'55830','9143','82324')

最后就是把查询到的表及每个表的操作反馈给开发，去反向查找对应涉及到的程序是否需要优化。

--------------------------------------------------分享下我们的备份脚本--定时删除归档和过期的备份集---------------------------------------------------------------------

#!/bin/bashday=7backdir=/u01/rmanbakbacktime=`date +"%Y%m%d"`deldir=`date -d "${day} days ago" +%Y%m%d`source /home/oracle/.bash_profileif [ ! -d ${backdir}/${backtime} ];thenmkdir -p ${backdir}/${backtime}elserm -rf ${backdir}/${backtime}/*firman target / log=${backdir}/${backtime}/Full_$backtime.log << EOFrun{allocate channel c1 device type disk;allocate channel c2 device type disk;sql 'alter system archive log current';backup as compressed backupset full database format '${backdir}/${backtime}/DB_%d_%T_%U';sql 'alter system archive log current';backup archivelog all format '${backdir}/${backtime}/ARCH_%d_%T_%s_%p' delete input;backup current controlfile format '${backdir}/${backtime}/CTL_%d_%T_%s_%p';release channel c1;release channel c2;crosscheck backup;delete noprompt expired backup;}EOFif [ -d ${backdir}/${deldir} ];thenrm -rf ${backdir}/${deldir}fi