How to Resolve ORA-09925 Oracle Error Message

  • by

ORA-09925: Unable to create audit trail file

There're two error scenarios of ORA-09925 in this post, one is for single-instance databases, the other is for RAC databases.

Single-instance Databases

Tried to startup a test database that was duplicated from the production one.

SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

ORA-09925 told us that it cannot find the audit destination (AUDIT_FILE_DEST) specified in the initialization parameters to put new audit files. Usually, it's because the audit directory does not exist or due to a permission problem.

Before we inspect the problem more deeply, we should know current audit file destination.

Check Audit File Destination

We can check the parameter file which is a plain-text file that we can check. Suppose SPFILE is at the default location:

SQL> create pfile='/home/oracle/pfile' from spfile;

File created.

Please note that, if SPFILE is not at the default location, you might get ORA-01565: error in identifying file '?=/dbs/spfile@.ora'

A simple grep can know it.

[oracle@test ~]$ grep -i audit_file_dest /home/oracle/pfile
*.audit_file_dest='/u01/app/oracle/admin/ORCLCDB/adump'

No Such Directory

Now let's see the condition of the path.

[oracle@test ~]$ ll /u01/app/oracle/admin/ORCLCDB/adump
ls: cannot access '/u01/app/oracle/admin/ORCLCDB/adump': No such file or directory

There's no such directory. Possibly, it has been moved to another place or the permission of the directory has been changed, then you should inspect more:

[oracle@test ~]$ cd $ORACLE_BASE/admin
[oracle@test admin]$ ll
...

No Space Left

If this is a database running for a long time, you may see errors like this:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

There's could be that the mount point is 100% used:

[oracle@test admin]$ df -h /u01
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-u01   100G  100G     0 100% /u01

Solutions to ORA-09925

For cases with no space left, we should either clear some audit files to release some space or add more space to the mount point. For cases with no such directory, we should create one for the audit file destination.

[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/ORCLCDB/adump

We startup again.

SQL> startup
...
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

We solved it.

RAC Databases

Tried to start a newly created and empty standby RAC database to NOMOUNT, but it failed with ORA-09925.

[oracle@standby01 ~]$ srvctl start database -d orclstb -o nomount
PRCD-1332 : failed to start database ORCLSTB
PRCR-1079 : Failed to start resource ora.orclstb.db
CRS-5017: The resource action "ora.orclstb.db start" encountered the following error:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/standby02/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orclstb.db' on 'standby02' failed
CRS-2632: There are no more servers to try to place resource 'ora.orclstb.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.orclstb.db start" encountered the following error:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/standby01/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orclstb.db' on 'standby01' failed

As you can see, there're a lot of errors in stack, but the most useful error message is ORA-09925: Unable to create audit trail file.

Check Audit File Destination

If you don't have any parameter file, you should make one for further inspection. In addition, you should specify the location of SPFILE in the statement. For examples:

Create PFILE From ASM

SQL> create pfile='/home/oracle/pfile' from spfile='+DATA/ORCLSTB/spfile';

File created.

No matter the database is idle or not, you can always create PFILE anytime as long as ASM instance is running in this node.

Create PFILE From Local OS

SQL> create pfile='/home/oracle/pfile' from spfile='/tmp/spfile';

File created.

Then we can check the parameter file that we created above by a simple grep.

[oracle@standby01 ~]$ grep -i audit_file_dest /home/oracle/pfile
*.audit_file_dest='/u01/app/oracle/admin/ORCLSTB/adump'

OK, we set a path for audit files in SPFILE.

Solutions to ORA-09925

We should make the directory on both nodes.

[oracle@standby01 ~]$ mkdir -p /u01/app/oracle/admin/ORCLSTB/adump [oracle@standby02 ~]$ mkdir -p /u01/app/oracle/admin/ORCLSTB/adump

We startup again.

[oracle@standby01 ~]$ srvctl start database -d orclstb -o nomount
[oracle@standby01 ~]$ srvctl status database -d orclstb
Instance ORCLSTB1 is running on node standby01
Instance ORCLSTB2 is running on node standby02

We passed NOMOUNT state.

Leave a Reply

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