Identifying Important Database Monitoring Categories

I l @ ve RuBoard

Depending on a company's organization, various people may be controlling different aspects of the databases. Database administrators, database and system operators, application managers, system administrators, and network managers each may have roles in either monitoring or configuring the company databases. In smaller companies, multiple roles are filled by a single person. Database operators keep the database up and available, solve performance problems, and back up the data. Database administrators manage security issues, handle load balancing and capacity planning issues, and deploy new database applications. Each may also have different needs for database monitoring tools.

These different monitoring activities can be grouped into the following categories:

• Configuration management

• Fault management

• Performance and resource management

• Security

• Backup

These groups are described in more detail next . The appropriate level of monitoring in each of these areas depends on your specific database responsibilities.

Configuring the Database

Before you can effectively monitor a database, you should verify the proper database configuration. Both system and database configuration files should be checked for correctness. An invalid database configuration can prevent database access. Each database relies on several configuration files. For example, Oracle depends on the following configuration files:

• tnsnames.ora

• init.ora

• listener.ora

• sqlnet.ora

These files can be corrupted if configured manually or not distributed consistently to all systems in the computing environment using the database. The database administrator should ensure that the initial configuration and subsequent configurations are correct. Appropriate event notification should be put in place so that the operator can be aware of any configuration changes. The events can serve as an audit history when later trying to track down the cause of a problem.

Network General's Database Module for Oracle7 is one example of a tool that can monitor configuration information. This module is used as an add-on to a network analyzer to study database traffic. SQL packets are decoded, enabling the analyzer to scan configuration information contained in connection packets to see whether or not a configuration problem exists. Other approaches are to check periodically the modification dates of the system and database configuration files to see whether it has made changes. An older copy of the configuration should be kept for comparison purposes.

Note that the correct database configuration depends on how the database will be used. Online transaction processing applications have different workload characterizations than decision support applications, for example.

Watching for Database Faults

The database operator must continually watch for failures in the database environment. This includes system and networking components, in addition to the database. System failures can prevent access to the database, and the loss of system components could severely degrade performance. Monitoring the database server system is not sufficient, because the network connecting the clients to the server can also fail. Techniques for monitoring the system, disks, and networks are discussed in other chapters.

Various database software or hardware components can fail that, in turn , cause the database to fail. The operator may simply want to log on to the database application periodically to ensure that it is still running. For example, critical Oracle components to check include the Oracle Names servers, Oracle servers, SQL*NET Listeners, and the Oracle MultiProtocol Interchange (MPI), which is used to translate between different network transport stacks, such as TCP/IP and IPX/SPX.

Managing Database Resources and Performance

Monitoring database resources and performance can ensure that the database is being used effectively. The most common database problems are related to resource management. Some common errors are:

• Out of disk space

• Log file full

• Performance overload

System and database resources should be studied periodically to detect trends. The operator should check the free space for tables, tablespaces, and indexes. Continual modifications and updates to a database can lead to database fragmentation, which means that the available storage for the database is scattered . Each available storage area is too small to be used and thus is wasted . Eventually, increased database fragmentation can lead to performance problems or even database failure. Some database monitoring tools help detect database fragmentation so that corrective action can be taken, such as restructuring the database objects.

Performance problems can be difficult to diagnose. Many database performance problems are caused by a poorly designed database, or programs that are not coded efficiently . For these cases, a simple solution is unlikely . Therefore, the database operator needs monitoring tools that can predict performance problems and provide corrective actions when appropriate.

Keeping the Database Server Secure

You should also be able to detect security intrusions or access violations related to the database server. Monitoring tools can track unsuccessful logins, privileged statement executions, and other events that are important for maintaining database security.

You may also want to use the network monitoring tools discussed in Chapter 6 to help keep your systems secure. Network firewalls can stop intruders before they are able to access a computer system.

Ensuring Successful Database Backups

You also need to back up the systems and ensure that the backups run successfully. Depending on how the database is being used, different types of backup tools may be needed. A system operator responsible for the backup and restore procedures for hundreds of systems may not have the expertise to handle the procedures associated with a database vendor's backup tool. Automated tools for managing and monitoring database backup can be helpful in lowering the skill level needed for the job.

As mentioned earlier, the database administrator is responsible for trending analysis and capacity planning. Ideally, the administrator is focused on these strategic and planning activities, but often they spend time helping operators troubleshoot database problems. Automated tools for database operations help free the administrator to focus on these longer-range planning tasks . The administrator is likely to use some of the same tools as the operator for performance and resource management. For example, PerfView can be used to forecast future performance bottlenecks, and can also help to analyze existing resource problems.

The remainder of this chapter describes a variety of tools and products that can be used to provide monitoring capabilities for databases. The emphasis is on the fault and performance and resource management categories; not many tools are available in the other important areas. Generic system and application tools, as well as database-specific tools, are covered in this chapter.

Oracle is, by far, the dominant vendor for UNIX database servers and thus gets the most attention in this chapter. Informix is another important UNIX database vendor and is used for comparison purposes.

I l @ ve RuBoard