oracle clean up trace, alert, aud, listener and other log files
The path of each log file of the database
The method of viewing various Oracle logs: if it is rac, the query method under the asm instance is the same
1.View the diagnostic log:
The new diagnostic information structure of 11g and above versions, view v$diag_info information, display log directory:
show parameter diag; select * from v$diag_info The output example is as follows: 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
Viewing methods below 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
Description:
background_dump_dest /* Stores database background process debugging information, and records trace files and alert log files that affect instances and databases. After 11gR1, this parameter setting is ignored by default and stored according to diagnostic_dest
diagnostic_dest /* Introduced after 11gR1, the background process and alert log will be stored according to the ADR base directory
user_dump_dest /* Store user process debugging trace files. After 11gR1, this parameter will be ignored when a new diagnostic architecture is introduced, and the trace file generation directory is controlled by diagnostic_dest
core_dump_dest /* Store Oracle core dump files
2) View the audit log:
SYS@orcl1 > show parameter audit_file_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl/adump audit_file_dest
Stored in the path is the database audit information file. If audit_trail is set to NONE, this directory will only store SYSDBA identity login audit information by default.
Note that the path seen under the asm instance may be different, such as:
audit_file_dest string /u01/app/12.2.0/grid/rdbms/audit
3) View the monitoring log location:
[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
Note that in addition to monitoring the xml log, there is also listener.log located in the
/u01/app/oracle/diag/tnslsnr/onetest/listener/trace directory
2. Manually clean up log files
Case 1: Clean up a single large log file, alert_sid.log, listener.log, etc
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 Operation when the CPU is idle: cd /data/backlog/oralog gzip the alert log file just backed up
Note: By cleaning up by echo, the attributes of the file can be maintained without affecting the zabbix monitoring alert log
Situation 2: Clean up tens of thousands of small log files, trc logs, aud audit logs, etc.
Under the asm instance:
show parameter audit_file_dest cd /u01/app/11.2.0/grid/rdbms/audit Optional backup before cleaning: find . -mtime +30 -name \*.aud -exec mv {} /oralog_bak \; Or delete it directly: find . -name "*.aud" -mtime +30 -type f | xargs rm -rf Other types of log files are similar, just replace with *.trc
If the database audit log is turned on, you need to clean up the aud$ audit table in the database
3. Use oracle’s own tools to clean up
Use adrci to clean up trace and acident logs
$ adrci adrci> show home adrci> set homepath diag/rdbms/orcl/orcl1 adrci> help purge # According to the number of minutes ago to clean up the data, you can also show problem to view the error information in the log adrci> purge -age 14400 -type incident #14400 is in minutes adrci> purge -age 14400 -type trace
You can also set the trace file retention strategy in adrci:
adrci> show control #View the policies that can be set adrci> help set control set control (SHORTP_POLICY = 720) #720 is in hours