[20191128]11GR2 asm实例audit文件.txt

来源：这里教程网时间：2026-03-03 14:38:50 作者：

[20191128]11GR2 asm实例audit文件.txt --//例行检查,我发现asm实例产生大量audit文件,一直没有清理.感觉oracle在这方面设计不好. # cd /u01/app/11.2.0/grid/rdbms/audit $ ls -ltr | grep "2019-11-28" | awk '{print $6,$7}' | xargs -I{} date -d "{}" "+%Y-%m-%d:%T %s" | awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}' | tail 2019-11-28:08:23:19 - 2019-11-28:08:08:18 901 2019-11-28:08:38:20 - 2019-11-28:08:23:19 901 2019-11-28:08:53:20 - 2019-11-28:08:38:20 900 2019-11-28:09:08:21 - 2019-11-28:08:53:20 901 2019-11-28:09:23:22 - 2019-11-28:09:08:21 901 2019-11-28:09:38:23 - 2019-11-28:09:23:22 901 2019-11-28:09:53:24 - 2019-11-28:09:38:23 901 2019-11-28:10:08:25 - 2019-11-28:09:53:24 901 2019-11-28:10:23:26 - 2019-11-28:10:08:25 901 2019-11-28:10:38:27 - 2019-11-28:10:23:26 901 --//很明显15分钟产生1个audit文件. --//注意我定义ls显示时间部分与别人的系统不同,我喜欢显示的格式如下. $ alias ls alias ls='ls --color=auto --time-style=+"%Y-%m-%d %H:%M:%S"' --//如果按照这个规律一天大约1440/15 = 96.不过我也遇到一些奇怪的情况: --//先清除2016年之前的文件,不然ls -ltr感觉有点慢. $ ls -ltr | grep "2017-01" | awk '{print $6}' | sort | uniq -c 99 2017-01-01 98 2017-01-02 65 2017-01-03 59 2017-01-09 98 2017-01-10 99 2017-01-11 98 2017-01-12 165 2017-01-13 99 2017-01-14 96 2017-01-15 110 2017-01-16 100 2017-01-17 112 2017-01-18 108 2017-01-19 83 2017-01-23 96 2017-01-24 96 2017-01-25 96 2017-01-26 96 2017-01-27 96 2017-01-28 96 2017-01-29 96 2017-01-30 96 2017-01-31 --//没有2017-01-04 到 2017-01-08号的audit. $ ls -ltr | grep "2017-11" | awk '{print $6}' | sort | uniq -c | head 98 2017-11-01 86 2017-11-02 5 2017-11-03 5 2017-11-04 3 2017-11-05 3 2017-11-06 4 2017-11-07 1 2017-11-08 7 2017-11-09 1 2017-11-10 --//2017.11.03 号减少许多.oracle这个版本产生的文件是进程号+时间戳的,不可能出现重名情况. $ ls -ltr | grep "2017-11-03" -rw-r----- 1 grid oinstall 750 2017-11-03 10:52:09 +ASM1_ora_8436_20171103105209597022143795.aud -rw-r----- 1 grid oinstall 752 2017-11-03 11:02:10 +ASM1_ora_20672_20171103110210032622143795.aud -rw-r----- 1 grid oinstall 752 2017-11-03 14:52:18 +ASM1_ora_31213_20171103145218987768143795.aud -rw-r----- 1 grid oinstall 752 2017-11-03 20:02:32 +ASM1_ora_22418_20171103200232746081143795.aud -rw-r----- 1 grid oinstall 750 2017-11-03 22:02:37 +ASM1_ora_9347_20171103220237811439143795.aud .. --//继续1个月1个月查询.. $ ls -ltr | grep "2018-05-" | awk '{print $6}' | sort | uniq -c | head -13 1 2018-05-01 2 2018-05-02 3 2018-05-03 3 2018-05-04 4 2018-05-05 1 2018-05-06 2 2018-05-07 1 2018-05-08 6 2018-05-09 53 2018-05-10 97 2018-05-11 97 2018-05-12 96 2018-05-13 --//奇怪到2018-05-10有开始回复一天96个的情况.不知道什么原因激活这样的操作. --//观察另外1个实例: # ls -ltr | grep "2017-01" | awk '{print $6}' | sort | uniq -c 1 2017-01-01 21 2017-01-03 17 2017-01-09 1 2017-01-11 73 2017-01-13 1 2017-01-14 2 2017-01-15 27 2017-01-16 7 2017-01-19 10 2017-01-23 --//另外的实例依旧没有2017-01-04 到 2017-01-08号的audit. # ls -ltr | grep "2017-11" | awk '{print $6}' | sort | uniq -c | head 5 2017-11-01 47 2017-11-02 96 2017-11-03 96 2017-11-04 95 2017-11-05 96 2017-11-06 96 2017-11-07 97 2017-11-08 97 2017-11-09 97 2017-11-10 # ls -ltr | grep "2018-05-" | awk '{print $6}' | sort | uniq -c | head -13 98 2018-05-01 97 2018-05-02 97 2018-05-03 96 2018-05-04 98 2018-05-05 97 2018-05-06 97 2018-05-07 95 2018-05-08 96 2018-05-09 68 2018-05-10 10 2018-05-11 3 2018-05-12 3 2018-05-13 --//噢,跑到另外1个实例做这些操作. --//像这样的文件命名格式不能使用logrotate清理.只能采用原始的方式: 2.建立脚本如下: # cat /usr/local/bin/purge_oracle_aud.sh #! /bin/bash # purge oracle audit log echo echo "start purge oracle audit asm at : " $(/bin/date +'%Y/%m/%d %T') /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit/ -mtime +90 -name "+ASM1_ora_*.aud" -print -delete echo "end purge oracle audit asm at : " $(/bin/date +'%Y/%m/%d %T') echo --//注意另外实例要修改为-name "+ASM2_ora_*.aud". # chmod 750 /usr/local/bin/purge_oracle_aud.sh --//crontab.d目录建立一个文件加入如下: 32 6 * * * root /usr/local/bin/purge_oracle_aud.sh >> /var/log/purge_oracle_aud.log 2>&1 --///var/log/purge_oracle_aud.log的清理由logrotate完成. # cat /etc/logrotate.d/oracle /var/log/purge_oracle_aud.log { size=20M rotate 5 copytruncate compress notifempty missingok } 3.我感到奇怪的是管理的exadata的机器竟然仅仅有2014年的audit文件,以后零星的出现几个,真不知道问题在哪里.也不知道为什么产生这样的情况.仔细查看才明白.... --//exadata asm实例配置参数如下: SQL> show parameter audit NAME TYPE VALUE -------------------- ----------- ------------------------------ audit_file_dest string /u01/app/11.2.0.4/grid/rdbms/audit audit_sys_operations boolean FALSE audit_syslog_level string LOCAL0.INFO --//噢明白了,exadate oracle的实施人员修改参数audit_syslog_level指向了LOCAL0.INFO.不过audit_sys_operations=false --//而且实施人员并没有定义在/etc/rsyslog.conf配置参数中,有机会我给自己测试看看. # grep -i local0 /etc/syslog.conf # grep -i local0 /etc/rsyslog.conf --//无显示. --//当前有问题的系统如下: SQL> show parameter audit NAME TYPE VALUE -------------------- ----------- ------------------------------ audit_file_dest string /u01/app/11.2.0/grid/rdbms/audit audit_sys_operations boolean FALSE audit_syslog_level string --//这部分内容参考:http://blog.itpub.net/267265/viewspace-2646161/=>[20190530]oracle Audit文件管理.txt --//我仔细检查exadata的alert文件发现如下: Fri Oct 10 21:23:09 2014 ALTER SYSTEM SET cluster_interconnects='10.10.10.70:10.10.10.71' SCOPE=SPFILE SID='+ASM1'; ALTER SYSTEM SET cluster_interconnects='10.10.10.72:10.10.10.73' SCOPE=SPFILE SID='+ASM2'; ALTER SYSTEM SET sga_target='2048M' SCOPE=SPFILE SID='*'; ALTER SYSTEM SET pga_aggregate_target='400M' SCOPE=SPFILE SID='*'; ALTER SYSTEM SET memory_target='0' SCOPE=SPFILE SID='*'; ALTER SYSTEM SET processes=1024 SCOPE=SPFILE SID='*'; ALTER SYSTEM SET audit_syslog_level='local0.info' SCOPE=SPFILE SID='*'; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ALTER SYSTEM SET asm_power_limit=1 SCOPE=SPFILE SID='*'; Fri Oct 10 21:23:10 2014 ALTER SYSTEM SET memory_max_target='0' SCOPE=SPFILE SID='*'; ALTER SYSTEM RESET memory_max_target SCOPE=SPFILE SID='*'; Fri Oct 10 21:24:05 2014 --//很明显实施人员有文档执行上面的步骤,确忘记了修改/etc/rsyslog.conf或者/etc/syslog.conf配置.导致我仅仅看见2014年的audit. --//在exadata执行以上操作: # grep "local0" /etc/rsyslog.conf local0.info /var/log/oracleaudit.log daemon.* /var/log/messages # service rsyslog restart Shutting down system logger: [ OK ] Starting system logger: [ OK ] --//在asm实例上登录执行如下: SYS@+ASM1> select sysdate from dual ; SYSDATE --------- 28-NOV-19 # cat /var/log/oracleaudit.log 2019-11-28T16:09:29.980476+08:00 dm01dbadm01 Oracle Audit[63191]: LENGTH : '143' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' --//仅仅看到登录,没有执行命令的审计. --//修改/etc/logrotate.d/oracle,追加如下内容,定期清理审计,实际上这个大小足够保持很久的内容. /var/log/oracleaudit.log { size=40M rotate 4 copytruncate delaycompress notifempty }