Monitoring File System Free Space in Oracle UNIX Administration

Monitoring file system free space in UNIX

We have some very important scripts for monitoring UNIX file free space in Chapter 12, but this is a good time to see how simple commands can be written to monitor the free space within any UNIX mount point.

One of the most important jobs of the Oracle DBA in a UNIX environment is ensuring that none of the UNIX file systems becomes full. When we create tablespaces with the autoextend on option, we move the monitoring from Oracle to UNIX.  Whenever tablespaces are allowed to grow within the UNIX mount point, we must be always vigilant to make sure that the file system does not become full.

When a UNIX file system for an Oracle datafile cannot extend, you entire database will “hang”, waiting for room to extend. Given the importance of this monitoring, most Oracle DBAs in UNIX write scripts to monitor all of their filesystems or pre-allocate space for datafiles to ensure that they will not auto-extend beyond space for a filesystem.

Let’s take a look at how this works.

Display all filesystems

We will start by issuing a df –k command to get a listing of all UNIX mounts points.  This example is from the Solaris dislect of UNIX.

root> df -k

Filesystem            kbytes    used   avail capacity  Mounted on
/dev/dsk/c0t0d0s0    4032504  104381 3887798     3%    /
/dev/dsk/c0t0d0s4    4032504  992890 2999289    25%    /usr
/proc                      0       0       0     0%    /proc
fd                         0       0       0     0%    /dev/fd
mnttab                     0       0       0     0%    /etc/mnttab
/dev/dsk/c0t0d0s3    4032504  657034 3335145    17%    /var
swap                 4095176       8 4095168     1%    /var/run
swap                 4095192      24 4095168     1%    /tmp
/dev/dsk/c0t0d0s5    1984564  195871 1729157    11%    /opt
/dev/dsk/c0t0d0s7   14843673 1619568 13075669   12%    /helpdesk
/dev/oradg/u02vol   12582912 8717924 3744252    70%    /u02
/dev/oradg/u01vol    8796160 5562586 3132548    64%    /u01
/dev/oradg/u04vol   10035200 1247888 8519534    13%    /u04
/dev/oradg/u03vol   12582912 2524060 9744542    21%    /u03
/dev/dsk/c0t0d0s6    1984564  931591  993437    49%    /export/home
/vol/c0t/orcl901_3    270364  270364       0   100%    /cdrom/orcl901_3

Here we see the following display columns:

1 – Filesystem name           

2 – The kbytes in the filesystem  

3 – Kbytes used in the filesystem  

4 – Kbytes available in the filesystem  

5 – File system capacity

6 – The mount point associated with the filesystem

Our goal is to filter this output to see the available space for the Oracle file systems. We also see rows in this server that are not associated with Oracle files. 

Display Oracle filesystems

Our next step is to eliminate all file systems except the Oracle file systems.  In this system, we are using the Oracle Optimal Flexible Architecture (OFA), and all Oracle filesystems begin with /u0.  Hence, we can use the UNIX grep utility to eliminate all lines except for those containing the string /u0:

root> df -k|grep /u0

/dev/vx/dsk/oradg/u02vol 12582912 8717924 3744252    70%    /u02
/dev/vx/dsk/oradg/u01vol 8796160 5563610 3131556     64%    /u01
/dev/vx/dsk/oradg/u04vol 10035200 1247888 8519534    13%    /u04
/dev/vx/dsk/oradg/u03vol 12582912 2524060 9744542    21%    /u03

Extract the available space for each filesystem

Now that we have the Oracle file systems, we can use the UNIX awk utility to extract the fourth column, which is the available space in the filesystem.

root> df -k|grep /u0|awk '{ print $4 }'

3744252
3132546
8519534
9744542

Create the script to check space in all file systems

Now that we see the command, we can place this command inside a loop to evaluate the free space for each filesystem.  Note that our command is placed inside the Korn shell for loop, and the command is enclosed in back-ticks (the key immediately above the tab key).

check_filesystem_size.ksh
#!/bin/ksh 

for i in `df -k|grep /u0|awk '{ print $4 }'`
do
   # Convert the file size to a numeric value
   filesize=`expr i` 

   # If any filesystem has less than 100k, issue an alert
   if [ $filesize  -lt 100 ]
   then
      mailx -s "Oracle filesystem $i has less than 100k free."\
         don@burleson.cc\
         oracle_dba@my.company.com
   fi
done

This simple script will check every file system on the server and e-mail us as soon as any file system has less than 100k of free space.

Scheduling the file alert

I generally place this type of script in a crontab file, and execute it every three minutes as shown in the UNIX crontab entry below.

#****************************************************************
# This is the every 5 min. trace file alert report for the DBAs
#****************************************************************
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,
47,49,51,53,5,57,59 * * * * /home/oracle/check_filesystem_size.ksh >
dev/null >&1

Next, let’s take a look at how we can optimize Oracle tablespaces and UNIX data files.

Configuring Oracle Tablespaces and UNIX Datafiles

Since different application processes have different I/O patterns, hot disks may appear on different disks at different times during each day. The goal of disk load balancing is to eliminate disk I/O bottlenecks, but it is important to remember that these bottlenecks are transient in nature. Since Oracle transactions happen very quickly, a disk may experience an I/O bottleneck for a very short period, and this short-duration bottleneck may repeat itself thousands of times each day. However, many Oracle administrators make the mistake of summarizing I/O by the hour, and the disk will appear not to have bottlenecks since the I/O spikes will have disappeared in the hourly average, as shown in Figure 4-1.

Use STATSPACK book figure 8-10 here .

Figure 1: Short disk I/O spikes can be lost with long measurement intervals

To get the most accurate results, you should collect I/O statistics at frequent intervals—preferably no more than ten minutes between samples—over a representative time period, such as a week. Because individual application processes have different I/O patterns, bottlenecks may appear on different disks at various times during each day. And because Oracle transactions happen very quickly, a disk may experience an I/O bottleneck for a very short period—but a short-duration bottleneck may nonetheless repeat itself thousands of times each day. If you make the mistake of summarizing I/O by the hour, as many DBAs do, you won't see these bottlenecks because the I/O spikes will not be evident in the hourly average.

The point is simple: in order to accurately identify and correct disk I/O bottlenecks, you must measure in minor duration, preferably no more than 10 minutes between samples. We will discuss this technique in a following section where we will show how to extend STATSPACK to capture disk iostat information.

The goal of load balancing is to distribute the files across disks so as to achieve a single static optimal I/O throughput. Moving Oracle datafiles to other disks is not a trivial operation, and Oracle must be stopped before the file can be moved to another disk. However, the good news is that once the I/O subsystem is balanced, the files will not need to be moved unless new processes change the I/O pattern for the disks.

The goal is to find the optimal file placement where overall load balance is achieved for all of the many variations of disk access. Load balancing is essentially the identification of hot disks, and the movement of datafiles to less-used cool disks. As such, disk load balancing is an iterative process since it is possible that relocating a datafile may relieve contention for one process, only to cause I/O contention for an unrelated process. Also, for databases placed on a small number of disks, it is possible that I/O contention cannot be avoided. Consider a 30GB database spread across disks with 20 competing processes for data. On average, ten processes would be queued waiting for I/O from each of the two disks. Clearly, these types of systems will always experience I/O contention.

Within Oracle in any UNIX environment, we have a hierarchical relationship between entities. Each physical disk has many UNIX mount points, each mount point has many Oracle datafiles, and each datafile may have many Oracle tables, as shown in Figure 4-2.

8-11.

Figure 2: The hierarchy of Oracle file structures on UNIX

After using data collected by iostat to identify a hot disk, you would use data collected by the Oracle utilities to identify which mount point and file contain the table causing the excessive I/O activity.

Identifying the hot disk is only the beginning of the quest. We must then see what mount point on the disk is causing the problem, which datafile on the mount point, and finally, what Oracle table is causing the excessive I/O. Only with this approach can the Oracle administrator fully understand how to perform disk load balancing. With that in mind, let’s look at the first method for collecting Oracle I/O statistics. We will then move on to look at collecting UNIX I/O statistics.