[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.
[20191129]oracle Audit文件管理3.txt
-
lfree
2019-11-29 09:39:45 -
Oracle
- 原创