BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring File System Free Space in Oracle UNIX Administration

Oracle UNIX/Linux Tips by Burleson Consulting

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.

 


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter