[20191129]oracle Audit文件管理3.txt

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

[20191129]oracle Audit文件管理3.txt --//昨天我修改exadata的一些设置,参考链接:http://blog.itpub.net/267265/viewspace-2666054/=>[20191128]11GR2 asm实例audit文件.txt --//主要内容修改记录如下: --//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 --//对方设置audit_syslog_level,而没有在/etc/rsyslog.conf设定local0.info对应文件.补充设置如下: # 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 ] --//修改/etc/logrotate.d/oracle,追加如下内容,定期清理审计,实际上这个大小足够保持很久的内容. /var/log/oracleaudit.log { size=40M rotate 4 copytruncate delaycompress notifempty } 1.exadata检查记录: --//今天上午检查发现: # sed -n -e '1p' -e '$p' /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] '' 2019-11-29T08:28:56.472916+08:00 dm01dbadm01 Oracle Audit[105870]: LENGTH : '143' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' # wc /var/log/oracleaudit.log 336 8736 76941 /var/log/oracleaudit.log # ls -l /var/log/oracleaudit.log -rw------- 1 root root 76941 2019-11-29 08:34:15 /var/log/oracleaudit.log --//也就是在16小时产生336条记录,如果对应审计就是336个文件. --//估算一天大约336/16*24=504.现在想想oracle实施人员是否有故意为之的可能. # grep ASMSNMP /var/log/oracleaudit.log |wc 99 2574 23577 # grep -v ASMSNMP /var/log/oracleaudit.log |wc 238 6188 53589 # grep ASMSNMP /var/log/oracleaudit.log | awk '{print $1}' | cut -f1 -d' ' | tr "T" " " | xargs -I{} date -d "{}" "+%Y-%m-%d:%T.%N %s" | awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}' | cut -f4 -d' ' |sort| uniq -c 33 392 33 508 33 900 # grep -v ASMSNMP /var/log/oracleaudit.log | awk '{print $1}' | cut -f1 -d' ' | tr "T" " " | xargs -I{} date -d "{}" "+%Y-%m-%d:%T.%N %s" | awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}' | cut -f4 -d' ' |sort| uniq -c .. --//结果不贴了,没有规律. --//查询另外实例: # grep ASMSNMP /var/log/oracleaudit.log | awk '{print $1}' | cut -f1 -d' ' | tr "T" " " | xargs -I{} date -d "{}" "+%Y-%m-%d:%T.%N %s" | awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}' | cut -f4 -d' ' |sort| uniq -c 33 253 33 647 32 900 --//仅仅看出ASMSNMP用户登录存在某种规律.如果这样审计还是算比较多的. # grep ASMSNMP /var/log/oracleaudit.log | cut -f4 -d" " |cut -f2 -d"[" | cut -f1 -d"]" | sort | uniq -c --//结果不贴出,都是不重复的进程号. 2.logrotate设置问题: --//我当时设置如下: /var/log/oracleaudit.log { size=40M rotate 4 copytruncate delaycompress notifempty } --//我看了我的测试环境(我测试定义size=10M),发现问题: $ ls -l /var/log/oracleaudit.log -rw------- 1 root root 92180 2019-11-29 08:21:12 /var/log/oracleaudit.log $ ls -l /var/log/oracleaudit.log* -rw------- 1 root root 92180 2019-11-29 08:21:12 /var/log/oracleaudit.log -rw------- 1 root root 12878455 2019-11-27 04:02:11 /var/log/oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 /var/log/oracleaudit.log.2 -rw------- 1 root root 202837477 2019-11-19 04:03:26 /var/log/oracleaudit.log.3 -rw------- 1 root root 15695818 2019-11-05 04:02:18 /var/log/oracleaudit.log.4 --//也就是不会压缩.为什么,也许不理解delaycompress的含义. # man logrotate delaycompress Postpone compression of the previous log file to the next rotation cycle. This has only effect when used in combination with compress. It can be used when some program can not be told to close its logfile and thus might continue writing to the previous log file for some time. --//翻译: 将前一个日志文件的压缩推迟到下一个循环周期。这只在用于时产生效果与压缩组合。当某些程序无法被告知关闭其日志文件时，可以使用该程序，因此可能继续写入以前的日志文件一段时间。 --//难道一些句柄一直没有关闭吗?检查发现没有. # lsof |grep /var/log/oracleaudit.log syslogd 29288 root 7w REG 104,2 92678 10617441 /var/log/oracleaudit.log # grep compress /etc/logrotate.d/psacct compress delaycompress --//修改如下: # cat /etc/logrotate.d/oracle /var/log/oracleaudit.log { size=10M rotate 4 copytruncate compress delaycompress notifempty } --//注如果size=50K,会报错. # /usr/sbin/logrotate /etc/logrotate.conf error: oracle:17 unknown unit 'K' error: found error in /var/log/oracleaudit.log , skipping --//主要为了测试的需要.手工执行: # cat oracleaudit.log.1 >> oracleaudit.log # ls -l oracleaudit.log* -rw------- 1 root root 12971133 2019-11-29 09:08:11 oracleaudit.log -rw------- 1 root root 12878455 2019-11-27 04:02:11 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 # /usr/sbin/logrotate /etc/logrotate.conf [root@gxqyydg4 IP=100.78 /var/log ] # ls -l oracleaudit.log* -rw------- 1 root root 0 2019-11-29 09:09:39 oracleaudit.log -rw------- 1 root root 12971133 2019-11-29 09:09:39 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 126768 2019-11-29 09:09:39 oracleaudit.log.2.gz -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 # ls -l oracleaudit.log* -rw------- 1 root root 0 2019-11-29 09:09:39 oracleaudit.log -rw------- 1 root root 12971133 2019-11-29 09:09:39 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 126768 2019-11-29 09:09:39 oracleaudit.log.2.gz -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 # gzip -lv oracleaudit.log.2.gz method crc date time compressed uncompressed ratio uncompressed_name defla c706d476 Nov 29 09:09 126768 12878455 99.0% oracleaudit.log.2 --//噢,实际上这样的方式是压缩的是后缀为.2的文件.oracleaudit.log变成了oracleaudit.log.1.估计下次压缩的是oracleaudit.log.1. # cat oracleaudit.log.1 >> oracleaudit.log # cat oracleaudit.log.1 >> oracleaudit.log # ls -l oracleaudit.log* -rw------- 1 root root 25942266 2019-11-29 09:14:04 oracleaudit.log -rw------- 1 root root 12971133 2019-11-29 09:09:39 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 126768 2019-11-29 09:09:39 oracleaudit.log.2.gz -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 --//这些启动rman.不断执行一些命令看看: # ls -l oracleaudit.log ;sleep 1 ; ls -l oracleaudit.log -rw------- 1 root root 26919090 2019-11-29 09:15:55 oracleaudit.log -rw------- 1 root root 26931502 2019-11-29 09:15:56 oracleaudit.log --//可以发现oracleaudit.log在变大.发现使用的方式可以记录下rman执行的sql语句.可以用于调试rman的一些问题. --//不过要引起注意的是如果审计数据库估计会导致审计增加要快许多.asm实例估计问题不大. # /usr/sbin/logrotate /etc/logrotate.conf ; ls -l oracleaudit.log ;sleep 1 ; ls -l oracleaudit.log -rw------- 1 root root 556 2019-11-29 09:18:37 oracleaudit.log -rw------- 1 root root 10388 2019-11-29 09:18:38 oracleaudit.log --//没有问题.可以继续写入. # ls -l oracleaudit.log* -rw------- 1 root root 552494 2019-11-29 09:19:20 oracleaudit.log -rw------- 1 root root 28812779 2019-11-29 09:18:37 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 135969 2019-11-29 09:18:37 oracleaudit.log.2.gz -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 126768 2019-11-29 09:09:39 oracleaudit.log.3.gz -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 # gzip -lv oracleaudit.log.2.gz method crc date time compressed uncompressed ratio uncompressed_name defla 9e5871fc Nov 29 09:18 135969 12971133 99.0% oracleaudit.log.2 --//oracleaudit.log.2就是原来的oracleaudit.log.1.压缩率还很高... --//这就是delaycompress的真正含义. 3.更正logrotate设置问题: /var/log/oracleaudit.log { size=40M rotate 4 copytruncate compress delaycompress notifempty } --//留待以后观察.随便说一下,可以使用如下命令调式: # /usr/sbin/logrotate -d /etc/logrotate.d/oracle reading config file /etc/logrotate.d/oracle reading config info for /var/log/oracleaudit.log Handling 1 logs rotating pattern: /var/log/oracleaudit.log 10485760 bytes (4 rotations) empty log files are not rotated, old logs are removed considering log /var/log/oracleaudit.log log does not need rotating --//修改size=10K # /usr/sbin/logrotate -d /etc/logrotate.d/oracle reading config file /etc/logrotate.d/oracle reading config info for /var/log/oracleaudit.log error: /etc/logrotate.d/oracle:17 unknown unit 'K' error: found error in /var/log/oracleaudit.log , skipping removing last 1 log configs Handling 0 logs --//继续看了文档,要使用小写的k就ok了.修改size=10k.没想到OS的命令也有坑.大写的M可以.小写的m报错. size size Log files are rotated only if they grow bigger then size bytes. If size is followed by M, the size if assumed to be in megabytes. If the k is used, the size is in kilobytes. So size 100, size 100k, and size 100M are all valid. # /usr/sbin/logrotate -d /etc/logrotate.d/oracle reading config file /etc/logrotate.d/oracle reading config info for /var/log/oracleaudit.log Handling 1 logs rotating pattern: /var/log/oracleaudit.log 10240 bytes (4 rotations) empty log files are not rotated, old logs are removed considering log /var/log/oracleaudit.log log needs rotating rotating log /var/log/oracleaudit.log, log->rotateCount is 4 compressing log with: /bin/gzip renaming /var/log/oracleaudit.log.4.gz to /var/log/oracleaudit.log.5.gz (rotatecount 4, logstart 1, i 4), renaming /var/log/oracleaudit.log.3.gz to /var/log/oracleaudit.log.4.gz (rotatecount 4, logstart 1, i 3), renaming /var/log/oracleaudit.log.2.gz to /var/log/oracleaudit.log.3.gz (rotatecount 4, logstart 1, i 2), renaming /var/log/oracleaudit.log.1.gz to /var/log/oracleaudit.log.2.gz (rotatecount 4, logstart 1, i 1), renaming /var/log/oracleaudit.log.0.gz to /var/log/oracleaudit.log.1.gz (rotatecount 4, logstart 1, i 0), copying /var/log/oracleaudit.log to /var/log/oracleaudit.log.1 truncating /var/log/oracleaudit.log removing old log /var/log/oracleaudit.log.5.gz # ls -l oracleaudit.log* -rw------- 1 root root 1121227 2019-11-29 09:20:08 oracleaudit.log -rw------- 1 root root 28812779 2019-11-29 09:18:37 oracleaudit.log.1 -rw------- 1 root root 49836853 2019-11-26 04:02:23 oracleaudit.log.2 -rw------- 1 root root 135969 2019-11-29 09:18:37 oracleaudit.log.2.gz -rw------- 1 root root 202837477 2019-11-19 04:03:26 oracleaudit.log.3 -rw------- 1 root root 126768 2019-11-29 09:09:39 oracleaudit.log.3.gz -rw------- 1 root root 15695818 2019-11-05 04:02:18 oracleaudit.log.4 --//可以发现命令并没有真正执行. --//换成小写的m看看,修改size=10m # /usr/sbin/logrotate -d /etc/logrotate.d/oracle reading config file /etc/logrotate.d/oracle reading config info for /var/log/oracleaudit.log error: /etc/logrotate.d/oracle:17 unknown unit 'm' error: found error in /var/log/oracleaudit.log , skipping removing last 1 log configs Handling 0 logs --//size的单位仅仅是M,k.