Recently I worked on a large database on a X-4 full rack Exadata for a few months. I am using sqlplus command every day on this database without any issue. Then suddenly, I got the following error on db node 1 when trying to run sqlplus.
$ sqlplus / as sysdba
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
I know this is the issue at OS level, not at database level. The audit trail directory should be under /u01. Run the df command. Interesting, I see it still have about 12G available on /u01, just like the parking space image below.
$ df-kh /u01Filesystem Size Used Avail Use% Mounted on/dev/mapper/VGExaDb-LVDbOra1 99G 82G 12G 88% /u01 |
|---|
I know /u01 takes some space, but 82G out of 99G seem too much. Check out the same /u01 on db node 2. Yes, it has only 22G used. Try sqlplus there without any issue. At this moment I roughly know the cause of the issue. I saw similar space issue in another client in the past and the database there had serious performance issue. The cause of the issue was millions of audit files under the audit directory.
When we run df or du command, majority of time we are only interested in how much space we use and how much space is available. This is the space limit in the file system. There is another limit: inode limit. Inode is the metadata of a file, containing information like file size, owner, group, file access/modify/change time and much more. When a file is created, the metadata of the file is stored in an inode (or inode number). Each file has a unique inode number that is used internally by the file system. When accessing a file, the system first seraches inode table for the unique inode number. With the information from the inode, the file can be found and accessed.
We usually don’t see the inode reach to its limit quite often. Use df -i command can help to identify inode limit issue. Here is the result after running df -i command.
$ df-h -i /u01Filesystem Inodes IUsed IFree IUse% Mounted on/dev/mapper/VGExaDb-LVDbOra1 13M 13M 0 100% /u01 |
|---|
Obviously we used up 100% of all 13M inodes in /u01 file system.
At this moment, I know where Oracle database can generate many files. It’s audit directory. Run the following command to find out space usage for audit folder and run for almost 6 minutes without the result back. Had to kill the process.
Then use another way to find out the top 10 usage. As expected, audit directory took a lot of space there with over 50G.
$ cd/u01/app/oracle/product/11.2.0.4/dbhome_1$ du`-a . |
`sort`-n -r |
`head-n 1057566392 .52851540 ./rdbms52722128 ./rdbms/audit788268 ./lib768116 ./bin559916 ./owb335520 ./oc4j309500 ./assistants303920 ./assistants/dbca303596 ./ctx |
|---|
There is another way to find out whether the directory is big. Run ls command from parent directory.
$ cd/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms$ ls-ltotal 1039920drwxr-xr-x 2 oracle oinstall 49152 Aug 22 10:33 admindrwxr-xr-x 2 oracle oinstall 1063747584 Oct 20 19:20 auditdrwxr-xr-x 2 oracle oinstall 4096 Jun 27 14:47 demodrwxr-xr-x 2 oracle oinstall 4096 Jun 27 14:47 docdrwxr-xr-x 5 oracle oinstall 4096 Jun 27 14:48 installdrwxr-xr-x 2 oracle oinstall 4096 Jun 27 14:47 jlibdrwxr-xr-x 2 oracle oinstall 4096 Jul 31 11:45 libdrwxr-xr-x 2 oracle oinstall 4096 Aug 22 18:30 logdrwxr-xr-x 2 oracle oinstall 4096 Jun 27 14:47 mesgdrwxr-xr-x 2 oracle oinstall 4096 Jun 27 14:47 publicdrwxr-xr-x 5 oracle oinstall 4096 Jun 27 14:46 xml |
|---|
So the solution seems easy. Just remove aud file under the audit directory. Then I run rm command. It runs for a few minutes and finally gave an error message below.
$ **rm *.aud**
-bash: /bin/rm: Argument list too long
It seems having a lot of files in this directories and I would like to find out the total number of files in the directory. Tried to run ls -l | wc -l to get file count. It had never finished and taken forever to run. The reason why ls -l is so slow is that by default, ls command sorts the file alphabetically. So if you’re interested in listing some files quickly, you could use ls -f | head -100 command to get a list of files.
At this moment, I had to use rm -rf from its parent directory and it worked. Even with this method, it took more than 8 hours to completed.
In the middle of this delete process, I stopped the delete process after the free inode reached to 1%. As I have some free inodes available for other processes and don’t worry about this space issue in a short time, I would like to find out how long it take to calculate the space usage under audit directory and how many audit files under the same directory.
Here are the results:
$ cd/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms$ df-i /u01Filesystem Inodes IUsed IFree IUse% Mounted on/dev/mapper/VGExaDb-LVDbOra1 13107200 12955396 151804 99% /u01$ timedu-khs /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/audit50G /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/auditreal 26m6.220suser 0m5.299ssys 2m25.058s$ timels`-l |
`wc-l12839426real 29m16.458suser 2m47.544ssys 2m9.860s |
|---|
The above result shows it took 29 minutes to find out 12.8 million files in the directory and 26 minutes to get the space usage for the audit directory. So basically forget about the idea to use ls -l command when there are millions of files in a directory.
There are a few other useful command to find out inode information.
$ ls-i4653873 admin 4949095 demo 4653875 install4653881 lib 4653883 mesg 4653885 xml4734977 audit 4653874 doc 4653880 jlib 4653882 log 4653884 public$ stat audit File: audit'` Size: 1063747584 Blocks: 2079672 IO Block: 4096 directoryDevice: fc03h/64515dInode: 4734977 Links: 2Access: (0755/drwxr-xr-x) Uid: ( 4085/ oracle) Gid: ( 1140/oinstall)Access: 2014-10-21 14:39:39.000000000 -0400Modify: 2014-10-21 17:30:32.000000000 -0400Change: 2014-10-21 17:30:32.000000000 -0400 |
|---|
As ls -l command is painfully slow to list files in a directory with millions of files, there are other ways to retrieve the files faster.
find . -type f -printf ‘%T+ %pn’ | sort -r | head -100
This commands finds all files in the current directory, lists them from newest to oldest, then prints only the first 100 files.
find . -type f -mtime -3 -printf ‘%T+ %pn’ | sort -r | head -100
This commands finds only files created in the last day (-mtime -3), and sorts only these files.
find . -type f -mmin -20 -printf ‘%T+ %pn’ | sort -r | head -100
For finer control, this command selects only files created/modified less than 20 minutes ago. Advertisements### Share this:
Like
](https://widgets.wp.com/likes/#)Be the first to like this.### _Related_Cold Failover Single Instance DB to Different Node on ODAIn "Exadata"
Install Oracle Big Data SQL on ExadataIn "BDA"
DBMCLI UtilityIn "Exadata"
Posted in [Exadata](https://weidongzhou.wordpress.com/category/exadata/) |
Tagged [Audit](https://weidongzhou.wordpress.com/tag/audit/), [Exadata](https://weidongzhou.wordpress.com/tag/exadata/), [File System](https://weidongzhou.wordpress.com/tag/file-system/)