有时候Oracle告警日志文件过大导致数据库访问问题,现将脚本贴出来
linux shell:
#!/bin/sh
# 关闭监听的日志功能,一般出现问题再打开
lsnrctl set log_status off
# move alertlog when size 1G
sqlplus -S /nolog <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn / as sysdba
spool tmp.txt
select value pvalue from v\$diag_info where name='Diag Trace';
select value pvalue from v\$diag_info where name='Diag Alert';
spool off
exit
EOF
#将两个告警日志文件赋予两个变量
PATH1=$(sed -n '1p' tmp.txt)
PATH2=$(sed -n '2p' tmp.txt)
# 判断如果trace文件夹内的文件超过1G则移动到上个目录,当然这里是覆盖,可以自行修改为一个备份目录并日期格式命名
cd $PATH1
for filename in $( ls -l alert* |awk '{if($5>1000000000) print $9}')
do
if [-f $filename ];then
mv $filename ..
fi
done
# 判断如果alert文件夹内的文件超过1G则删除掉,xml的形式跟上面log形式重复,一般用不上
cd $PATH2
for filename1 in $( ls -l *.xml |awk '{if($5>1000000000) print $9}')
do
if [-f $filename1 ];then
rm -f $filename1
fi
done
windows bat,思想类似实现基本雷同:
echo set heading off feedback off pagesize 0 verify off echo off > sql.txt
echo conn / as sysdba >> sql.txt
echo spool tmp.txt >> sql.txt
echo select value pvalue from v$diag_info where name='Diag Trace'; >> sql.txt
echo select value pvalue from v$diag_info where name='Diag Alert'; >> sql.txt
echo spool off >> sql.txt
echo exit>> sql1.txt sql.txt
sqlplus -S /nolog @sql.txt
(Set /p Line1=&Set /p Line2=)<tmp.txt
cd /d %Line1%
for %%i in ("*.log") do (
echo %%i:文件大小=%%~zi字节
if %%~zi gtr 1000000000 move %%i ..
)
cd /d %Line2%
for %%i in ("*.xml") do (
echo %%i:文件大小=%%~zi字节
if %%~zi gtr 1000000000 del %%i
)