How to clean diag and Trace file in Oracle Database

Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

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

Leave a Reply

Your email address will not be published. Required fields are marked *