Oracle database creates various types of files for logging each has its role. For example, Trails, trace files, and logs. These files are important and provide a lot of information, it will quickly consume server space handicapping server performance. That if they were not maintained properly. This article goes through the process of finding what eats Server space and provides a clear explanation of free oracle server disk space.
While I was monitoring Oracle 12c cluster for one of the clients, I noticed a fast decline in oracle server free disk space, and Oracle home is creating lots of log files, trace files, and Audit files.
If DBA neglect regular maintenance, the Oracle home directory can grow very large, as, the number of audit trail, logs, and trace files fills up the server’s disk-space, even if the system os online for less than a few months.
What eats my server’s space?
first, you need to investigate from which folder causing the problem by estimate file space usage for each folder by executing the du command
du -sh ./* | sort -h
-s display only a total for each argument
-h print sizes in human-readable format (e.g., 1K 234M 2G)
sort the output by size.
It is better to start digging from your oracle home, then identify which directory is eating your server disk-space. Next, navigate into that folder and repeat the procedure until you identify all folders that consume most of the free space (I call it the naughty list).
du -sh ./* | sort -h
Locations and Types of log files
#Oracle_database creates many types of files and does not track them. hence, in long run these files trouble. Usually, these files are related to Instance operation, security, backups, memory, instance errors, and others. in addition, these files stored in local file system.
Audit trail files
Oracle Database provides robust audit support in both the Enterprise and Standard Edition of the database. furthermore, the Audit records include information about the operation ,like , the user performing the operation, and the date and time of the operation. Audit records stored in the database audit trail or in files on the operating system. Standard auditing includes operations on privileges, schemas, objects, and statements.
You can configure Audit location by modifying database parameters.
AUDIT_FILE_DEST
specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set
AUDIT_SYS_OPERATIONS.
Enables the auditing of operations issued by user SYS, and users connecting with SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM and SYSDG privileges. The audit trail data writs to the operating system audit trail.set This parameter value to true.
The default location of the operating system audit trail on Unix/Linux is $ORACLE_BASE/admin/$ORACLE_SID/adump. The default on Windows is the event log.
For optimal performance, it should refer to a directory on Oracle server local disk.
Trace Files
Trace File is a text file ( called dump) that Oracle Database creates to help you diagnose and resolve operating problems. Each server and background process writes to a trace file. When a process detects an internal error, it writes information about the error to its trace file.
the Automatic Diagnostic Repository (#ADR) stores Trace files in the trace directory under each ADR home. To help you locate individual trace files within this directory, you can use data dictionary views. For example, you can find the path to your current session’s trace file or to the trace file for each Oracle Database process.
To find all trace files for the current instance:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
The output is the ADR trace directory path for the current instance. To control the number of these files, and manage it with operating system tools:
Part two will explain how to free oracle disk space automatically using cleanup scripts and oracle commands.
