分析oracle日志挖掘实操&定期清理归档日志脚本(二)

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

https://www.toutiao.com/article/7109732381657334305/

问题：最近几周经常手动删除归档日志，归档日志报空间不足（预留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/bash
day=7
backdir=/u01/rmanbak
backtime=`date +"%Y%m%d"`
deldir=`date -d "${day} days ago" +%Y%m%d`
source /home/oracle/.bash_profile
if [ ! -d ${backdir}/${backtime} ];then
mkdir -p ${backdir}/${backtime}
else
rm -rf ${backdir}/${backtime}/*
fi
rman target / log=${backdir}/${backtime}/Full_$backtime.log << EOF
run{
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;
}
EOF
if [ -d ${backdir}/${deldir} ];then
rm -rf ${backdir}/${deldir}
fi