[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
}