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

来源：网络时间：2022-10-29 01:47:49

导读接续上一个文章：记oracle日志挖掘实操&查询归档不正常增长情况(一)https://www.toutiao.

接续上一个文章：记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