Oracle 清理跟踪、警报、审计、监听器和其他日志文件
0、注意事项
数据库产生的运行日志不是随便可以清理的,特别是有故障或者长期分析一个妖孽的问题时,清理日志之前一定要做好备份工作。
1、数据库各项日志文件路径
查看oracle各类日志的方法:如果是rac,asm实例下查询方法一样
1)查看诊断日志:
11g以及以上版本全新的诊断信息架构,查看v$diag_info信息,展示日志目录:
show parameter diag;
select * from v$diag_info
输出例子如下:
1 ADR Base
/u01/app/oracle
1 ADR Home
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g
1 Diag Trace
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace
1 Diag Alert
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/alert
1 Diag Incident
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/incident
1 Diag Cdump
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/cdump
1 Health Monitor
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/hm
1 Default Trace File
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_61174.trc
11g以下查看方式:
sqlplus / as sysdba
SYS@orcl1 > show parameter dump_dest;
NAME TYPE VALUE
background_dump_dest string /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/cdump
user_dump_dest string /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace
说明:
background_dump_dest /* 存放数据库后台进程调试信息,与记录影响实例、数据库的trace文件和alert日志文件,11gR1之后,缺省忽略此参数设置,根据diagnostic_dest存放
diagnostic_dest /* 11gR1之后,引入,后台进程与alert告警日志会根据ADR base目录存放
user_dump_dest /* 存放用户进程调试trace文件,11gR1之后,引入新的诊断架构这个参数将被忽略,由diagnostic_dest控制trace文件生成目录
core_dump_dest /* 存放Oracle核心转储文件
2)查看审计日志:
SYS@orcl1 > show parameter audit_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
audit_file_dest
路径下存放的是数据库审计信息文件,如果设置audit_trail为NONE,则默认此目录只存放SYSDBA身份登陆审计信息。
注意,asm实例下面看到的路径可能不一样,比如:
audit_file_dest string /u01/app/12.2.0/grid/rdbms/audit
3)查看监听日志位置:
[oracle@onetest ~]$ lsnrctl status
Listener Parameter File /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/onetest/listener/alert/log.xml
注意,除了监听xml日志,还有listener.log日志位于/u01/app/oracle/diag/tnslsnr/onetest/listener/trace目录下
2、手工方式清理日志文件
情况1:清理单个较大日志文件,alert_sid.log、listener.log等
SQL> select * from v$diag_info where name='Diag Trace';
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@orclr01 trace]$cd /u01/app/oracle/diag/rdbms/orcl/orcl1/trace
[oracle@orclr01 trace]$ ls -lrt alert*
[oracle@orclr01 trace]$cp alert_orcl1.log /data/backlog/oralog/alert_orcl1.log -`date +"%Y%m%d"`
[oracle@orclr01 trace]$echo 0 > alert_orcl1.log
CPU空闲时操作:
cd /data/backlog/oralog
gzip 刚才备份的alert日志文件
说明:通过echo的方式清理,可以保持文件的属性,不影响zabbix监控alert日志。
情况2:清理上万个小的日志文件,trc日志、aud审计日志等
asm实例下:
show parameter audit_file_dest
cd /u01/app/11.2.0/grid/rdbms/audit
清理前可选择备份:
find . -mtime +30 -name \*.aud -exec mv {} /oralog_bak \;
或者直接删除:
find . -name "*.aud" -mtime +30 -type f | xargs rm -rf
其它类型的日志文件类似,替换为*.trc即可
如果开启了数据库审计日志,那需要在数据库中清理aud$审计表
3、使用oracle自带工具清理
使用adrci清理trace和acident日志
$ adrci
adrci> show home
adrci> set homepath diag/rdbms/orcl/orcl1
adrci> help purge # 可根据清理多少分钟前的数据,也可以show problem查看日志中错误信息
adrci> purge -age 14400 -type incident #14400的单位是分钟
adrci> purge -age 14400 -type trace
也可以在adrci中设置trace文件保留的策略:
adrci> show control #查看可以设置的策略
adrci> help set control
set control (SHORTP_POLICY = 720) #720的单位是小时
12c以及以上版本部署purgeLogs工具
可能是oracle自己也知道从9i ~ 19c版本,日志位置不断变化,日志也越来越多,翻不了了,官方出了专门清理日志的工具。
MOS文档:purgeLogs: Cleanup traces, logs in one command (Doc ID 2081655.1)
工具使用参考:
Usage:
purgeLogs [-days <days> [-aud] [-lsnr]] |
[-orcl <days> [-aud] [-lsnr]] |
[-tfa <days>] |
[-osw <days>] |
[-oda <days>] |
[-extra '<folder>':<days> | [, '<folder>':<days>]]
[-automigrate]
[-dryrun]
purgeLogs OPTIONS
-days <days> Purge orcl,tfa,osw,oak components logs & traces older then # days
-orcl <days> Purge only GI/RDBMS logs & traces (Default 30 days)
-tfa <days> Purge only TFA repository older then # days (Default 30 days)
-osw <days> Purge only OSW archives older then # days (Default 30 days)
-oda <days> Purge only OAK/DCS logs and trace older then # days (Default 30 days)
-extra '<folder>':<days> Purge only files in user specified folders (Default 30 days)
-aud Purge Audit logs based on '-orcl <days>' option
-lsnr It will force the cleanup of listeners log independently by the age
-dryrun It will show the purge commands w/o execute them
-automigrate It will run the adrci schema migrate commands in case of DIA-49803
-h Display this help and exit
Example:
purgeLogs
purgeLogs -days 20
purgeLogs -orcl 50
purgeLogs -orcl 50 -aud -lsnr
purgeLogs -tfa 50
purgeLogs -osw 20 -oda 10
purgeLogs -orcl 20 -osw 20 -oda 10
purgeLogs -orcl 20 -osw 20 -oda 10 -extra /tmp:10,/var/log:20