How to fix ORA-09925: Unable to create audit trail file

The Dreaded "No Space Left on Device" Error

If you're an Oracle DBA or developer, you've likely encountered this heart-stopping error at some point in your career:

ERROR:
ORA-09925: Unable to create audit trail file
SVR4 Error: 28: No space left on device
Additional information: 9925
ORA-01075: you are currently logged on

This error message can appear during critical database operations, potentially disrupting business operations and causing unnecessary downtime. In today's blog post, we'll dive deep into what causes this error and provide step-by-step solutions to resolve it.

Understanding the Error

The error message is quite descriptive: Oracle is unable to create an audit trail file because there's no space left on the device. Oracle Database maintains audit trails to record database activities for security and compliance purposes. When the disk hosting these audit files runs out of space, Oracle can't write new audit records, triggering this error.

The error consists of several components:

ORA-09925: The primary Oracle error code indicating an issue with audit trail file creation
SVR4 Error: 28: The underlying system error (No space left on device)
ORA-01075: Indicates you're currently logged into the database

A production database suddenly became unresponsive during peak hours. Users reported they couldn't connect to the application, and those who were connected experienced timeouts. The application logs showed Oracle connection errors, and when the DBA attempted to connect to the database, they were greeted with the error message shown above.

Step-by-Step Solution

1. Verify Disk Space Usage

First, confirm that disk space is indeed the issue. Log in to your database server and run the appropriate command for your operating system.

For Linux/Unix:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             100G   95G  5.0G  95% /
/dev/sdb1             500G  485G   15G  97% /u01/app/oracle
/dev/sdc1             200G  200G     0  100% /u01/app/oracle/audit

For Windows:

PS> Get-Volume
DriveLetter FileSystemLabel FileSystem DriveType HealthStatus SizeRemaining      Size
----------- --------------- ---------- --------- ------------ -------------      ----
C           System          NTFS       Fixed     Healthy            10.2 GB    100 GB
D           Oracle Data     NTFS       Fixed     Healthy             2.5 GB    500 GB
E           Oracle Audit    NTFS       Fixed     Healthy                0 B    200 GB

In this example, we can see that the /u01/app/oracle/audit directory (or the E: drive in Windows) has 0 bytes of space remaining, causing our error.

2. Immediate Space Recovery

To get the database back online quickly, we need to free up space immediately:

Option A: Clean up old audit files

# Find the location of audit files
$ sqlplus / as sysdba
SQL> SHOW PARAMETER audit_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/audit

# Exit SQL*Plus and remove old audit files
$ cd /u01/app/oracle/audit
$ ls -ltr *.aud | head -20
# Review and remove old audit files
$ find . -name "*.aud" -mtime +30 -exec rm {} \;

Output after cleanup:

$ df -h /u01/app/oracle/audit
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc1             200G  150G   50G  75% /u01/app/oracle/audit

Option B: Move audit files to another location

$ mkdir -p /backup/audit_backup
$ mv /u01/app/oracle/audit/*.aud /backup/audit_backup/

3. Restart the Database

Once space is available, restart the database if it's not already running:

$ sqlplus / as sysdba
SQL> STARTUP
ORACLE instance started.
Database mounted.
Database opened.

4. Long-Term Solutions

To prevent this issue from recurring, implement these long-term solutions:

A. Increase Disk Space

Add more storage to your audit file destination:

# Example of adding a new disk and mounting it (Linux)
$ sudo fdisk /dev/sdd
$ sudo mkfs.ext4 /dev/sdd1
$ sudo mount /dev/sdd1 /u01/app/oracle/audit

# Then move data to the new location

B. Implement Audit File Rotation

Create a cron job or scheduled task to regularly archive and remove old audit files:

# Example cron job entry
0 2 * * * find /u01/app/oracle/audit -name "*.aud" -mtime +30 -exec gzip {} \; -exec mv {}.gz /backup/audit_archive/ \;

C. Modify Audit Settings

Review and potentially adjust your audit policy to reduce the volume of audit records:

-- Check current audit settings
SQL> SELECT * FROM dba_stmt_audit_opts;

-- Modify audit settings if needed
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE;
SQL> AUDIT SELECT TABLE BY ACCESS;

D. Configure Audit Purging

Set up automatic purging of audit records:

-- Create a procedure to purge audit records
SQL> CREATE OR REPLACE PROCEDURE purge_audit_records IS
  BEGIN
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      last_archive_time => SYSTIMESTAMP - INTERVAL '30' DAY);
      
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      use_last_arch_timestamp => TRUE);
  END;
/

-- Schedule it to run weekly
SQL> BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'PURGE_AUDIT_WEEKLY',
    job_type => 'STORED_PROCEDURE',
    job_action => 'purge_audit_records',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'freq=weekly; byday=SUN; byhour=1;',
    enabled => TRUE);
END;
/

Monitoring to Prevent Future Occurrences

Implement proactive monitoring to catch disk space issues before they become critical:

-- Create a tablespace usage monitoring procedure
CREATE OR REPLACE PROCEDURE check_disk_space IS
  v_space_used NUMBER;
  v_threshold NUMBER := 85; -- Alert when 85% full
BEGIN
  -- Get space usage for audit file directory
  SELECT (USED_SPACE / TOTAL_SPACE) * 100 INTO v_space_used
  FROM TABLE(GET_DISK_SPACE('/u01/app/oracle/audit'));
  
  IF v_space_used > v_threshold THEN
    -- Send alert email or trigger notification
    send_alert('Audit disk space at ' || v_space_used || '% - Please take action!');
  END IF;
END;
/

Conclusion

The "No space left on device" error when creating audit trail files is a common but serious issue that can disrupt database operations. By understanding the root cause and implementing both immediate and long-term solutions, you can resolve this error quickly and prevent it from recurring.

Remember that proper monitoring and maintenance procedures are key to avoiding these types of issues. Regular cleanup of audit files, adequate disk space planning, and appropriate audit policies will help ensure your Oracle database runs smoothly.

Have you encountered this error before? What solutions worked best for you? Share your experiences in the comments below!

🚀 Automating Oracle Database Patching with Ansible: A Complete Guide

Oracle database patching has long been the bane of DBAs everywhere. It's a critical task that requires precision, expertise, and often results in extended maintenance windows. What if I told you that you could automate this entire process, reducing both risk and downtime while ensuring consistency across your Oracle estate? 💡 In this comprehensive guide, I'll walk you through a production-ready Ansible playbook that completely automates Oracle patch application using OPatch. Whether you're managing a single Oracle instance or hundreds of databases across your enterprise, this solution will transform your patch management strategy! 🎯 🔥 The Challenge: Why Oracle Patching is Complex Before diving into the solution, let's understand why Oracle patching is so challenging: 🔗 Multiple dependencies : OPatch versions, Oracle Home configurations, running processes ⚠️ Risk of corruption : Incorrect patch application can render databases unusable ⏰ Downtime requirements : Da...

DBA NAVIGATOR

Search This Blog