Oracle定期清理告警日志文件脚本

有时候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 
)