ORA-09925
There're two error scenarios of ORA-09925 in this post:
Let's see some cases.
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/[email protected]'
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 the directory and make sure it's existing:
[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.