[20191129]oracle Audit檔案管理3.txt

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