Using Standard Database Commands and Tools
| I l @ ve RuBoard |
| This section describes the commands and tools that are either included with a database product or are part of the operating system. UNIX CommandsUNIX commands and tools, such as ps and pstat, can be used to check on some of the important database processes. The database listeners and names servers are examples of some of these critical database processes. Listing 8-1 shows an example of using ps to find the Oracle processes on the system. Note that Oracle appends the database instance name to the end of the process names associated with each instance. The who command can be used to list the UNIX users logged in to the system. This gives you an idea of who is using the system, but doesn't necessarily help you to determine who is using the database. Other tools, also described in this section, can be used to display information about current database users. SQL CommandsSQL commands can be used to check database status or the available space. Sometimes, the best way to know whether a database is working is to try to use it. This is a manual process, unless the commands are put into scripts. If a database problem exists, determining who is currently using the database may be useful. Listing 8-2 shows how you can make an SQL query to obtain this information in an Oracle environment. Listing 8-3 shows an example of using this SQL query on an Oracle 7.3 system with multiple active database users. Listing 8-1 Output showing Oracle database processes.# ps -efa grep oracle oracle 21488 1 0 Dec 13 ? 7:36 ora_lgwr_CINDY oracle 23446 1 0 Dec 13 ? 62:44 dbsnmp oracle 21492 1 0 Dec 13 ? 0:32 ora_smon_CINDY oracle 23165 1 0 Dec 13 ? 0:50 ora_smon_ora2 oracle 23154 1 0 Dec 13 ? 6:56 ora_pmon_ora2 oracle 21494 1 0 Dec 13 ? 0:04 ora_reco_CINDY oracle 23459 1 0 Dec 13 ? 0:43 oracleora2 (LOCAL=NO) oracle 21912 1 0 09:50:42 ? 0:00 oracleora2 (LOCAL=NO) oracle 21483 1 0 Dec 13 ? 10:34 ora_dbwr_CINDY oracle 23438 1 0 Dec 13 ? 0:00 /rdbms/oracle/8.0.3/bin/tnslsnr oracle 23156 1 0 Dec 13 ? 14:17 ora_dbwr_ora2 oracle 21490 1 0 Dec 13 ? 6:42 ora_ckpt_CINDY oracle 28717 28614 0 Dec 15 pts/2 0:00 ksh oracle 21481 1 0 Dec 13 ? 6:27 ora_pmon_CINDY oracle 23161 1 0 Dec 13 ? 7:30 ora_lgwr_ora2 oracle 23163 1 0 Dec 13 ? 6:04 ora_ckpt_ora2 oracle 21908 23446 0 09:50:41 ? 0:04 dbsnmp oracle 23167 1 0 Dec 13 ? 0:04 ora_reco_ora2 root 21974 21936 2 10:21:42 pts/4 0:00 grep oracle # Explaining SQL statements is beyond the scope of this book. For more information, you may want to read SQL For Dummies, Third Edition , by Allen G. Taylor (IDG Books Worldwide, 1998). SNMP MIB MonitoringIf you're using the HP OpenView Network Node Manager, then the OpenView MIB Browser can be used to get additional information about the database and database server. Because your database server is dependent on the underlying system and network, you may want to access the MIB-II and the HP-UNIX MIB, which contain generic system information, such as filesystem information and network interface status. HP OpenView IT/O enables you to set up ongoing monitoring of MIB variables . Management frameworks, such as Unicenter TNG, also provide a MIB Browser. Public-domain MIB Browsers are available, too. Listing 8-2 Using SQL SELECT to find the current database users.SELECT nvl(S.OSUSER,S.type) OS_Usercode, S.USERNAME Oracle_Usercode, S.sid Oracle_SID, S.process F_Ground, P.spid B_Ground FROM sys.V_$SESSION S, sys.V_$PROCESS p WHERE s.paddr = p.addr ORDER BY s.sid Listing 8-3 Output showing active Oracle database users.SQL> SELECT nvl(S.OSUSER,S.type) OS_Usercode, 2 S.USERNAME Oracle_Usercode, 3 S.sid Oracle_SID, 4 S.process F_Ground, 5 P.spid B_Ground 6 FROM sys.V_$SESSION S, 7 sys.V_$PROCESS p 8 WHERE s.paddr = p.addr 9 ORDER BY s.sid 10 ; OS_USERCODE ORACLE_USERCODE ORACLE_SID F_GROUND B_GROUND ----------- --------------- ---------- -------- ---------- oradba 1 1322 1322 oradba 2 1324 1324 oradba 3 1331 1331 oradba 4 1334 1334 oradb 5 1338 1338 oradba 6 1342 1342 george JUNGLE 7 277:412 3056 sam SAMS 10 181:245 28975 sam SAM 12 187:282 1686 lily LILY 13 29902 21430 sam SAM 16 292:197 28959 sam SAM 19 187:282 28989 12 rows selected. SQL> Database vendors, such as Oracle, Sybase, and Informix, each support the Relational Database Management System (RDBMS) MIB, which provides information such as the database status. (The entire contents of this MIB is provided in Appendix A.) Because the database vendors have standardized their representation of some key information, an operator can access this information in the same way regardless of which database is being used. This information can then be combined with information learned from MIB-II and the HP-UNIX MIB, to get a broader understanding of the condition of the databases. The Gartner Group estimates that every Fortune 1000 organization has an average of five database sources, so the use of a standard MIB for monitoring can provide some much needed consistency and reduce the amount of database operator training. Although the RDBMS MIB defines nine tables, only five are truly vendor-independent. These tables are shown in Table 8-1. The rdbmsSrvParamTable is also served by the major database vendors, but the server configuration information contained in this table is database-specific and may contain customized parameters. Informix and Oracle do not support the other three tables defined in the MIB: rdbmsDbParamTable, rdbmsDbLimitedResourceTable, and rdbmsSrvLimitedResourceTable. Two SNMP traps, rdbmsStateChange and rdbmsOutOfSpace, are defined in the RDBMS MIB, but only rdbmsStateChange is consistently implemented. The trap is sent when the database or database server's state changes to restricted or unavailable. You may need to modify trap configuration files on your management station to receive new traps. The RDBMS MIB is primarily used for monitoring, not administration. However, much of the information is static configuration information, which is not useful for continual monitoring. The major database vendors also support the Network Services MIB, defined in RFC 2248, as a repository for database process information. The Network Services MIB is casually referred to as the "Application MIB," although the Internet Engineering Task Force (IETF) has now defined a different MIB with that name. Table 8-2 contains a list of useful fields from the RDBMS MIB, as well as the application table (applTable) in the Network Services MIB. Only MIB variables available for both Oracle and Informix are included in this table. Many of the variables listed in Table 8-2 are used to monitor the status of the database or database server. The applOperStatus field can be used to indicate the operational status of the database server: up, down, congested , or restarting. The applInBoundAssociations variable can be used to determine the number of active connections to the database. rdbmsRelState indicates the status of the database: active, available, restricted, unavailable, or other. Some of the database monitoring variables can also be used for resource control. The rdbmsDbInfoSizeAllocated and rdbmsDbInfoSizeUsed variables can be used to determine the amount of free disk space available for the database, indicating whether more space should be allocated. applInboundAssociations can be compared to the maximum allowed inbound associations from the server info table. You can use these variables in conjunction with Hewlett-Packard's Process Resource Manager (PRM) to increase or restrict system resource entitlements when too many users are active. The server info table includes several interesting statistics about the number of transactions handled, and the number of reads and writes made by the server. The ratio of logical to physical reads can be used to determine the appropriate cache size , for example. However, the information in the server info table is aggregated for the database server, so database-specific statistics are not available here (unless the database server is handling only one database). The database relation table is used to match a database to its server. Table 8-1. Vendor-Independent RDBMS MIB Tables
Table 8-2. Database Monitoring Variables
If you want to monitor Informix, the rdbmsSrvLimitedResource table is useful, because it contains current resource limits and thresholds reached. This table is not supported for Oracle. Other interesting variables in the RDBMS MIB not generally available for Oracle, but available for Informix, include:
Because MIB definitions rarely change, after you configure some MIB variables to be monitored , the configuration is likely to work with multiple database versions. This can help to reduce the burden of recertification for a new database software release. These MIBs provide information that can help with configuration management, resource management, and fault management. For performance management, I/O rates can be calculated by monitoring performance metrics in the RDBMS MIB. Collecting I/O rates over time can provide you with a simple way to track I/O performance. Although access to MIB data is readily available, MIB browsing is a manual process, usually requiring you to poll for the information you need. The manual query captures only a snapshot. Like many metrics, you really need to collect the information over a period of time, so that you can make comparisons. Database Vendor ToolsThis section describes some monitoring capabilities provided by the database vendors. Later sections discuss the sophisticated database monitoring tools provided by third parties. Oracle Enterprise ManagerOracle has developed a database management platform called the Oracle Enterprise Manager (OEM) for database administration. OEM includes utilities for monitoring network and database objects, scheduling jobs on multiple systems, and distributing software. Oracle Diagnostics Pack, Oracle Tuning Pack, and Oracle Change Management Pack are optional software packages that provide additional capabilities, such as database tuning and capacity planning. OEM has many different components , including the following:
The database objects need to be registered at the console to maintain their status. Status is shown graphically, with color indicating severity. Objects can be grouped together with a special container icon. The worst status of any of the members is propagated to the group icon. Hierarchies of groups can be created, and you can take actions on a group. Objects also have property sheets. The database property sheet can show status, or it can be used to start or stop a database. Database administration tools shown in the Tools Application menu can be launched on an object by first selecting the object and then selecting the tool. The OEM database administration tools can be used to manage schema objects, back up, recover, and restore a database, and distribute software. The events shown in the Event window contain a variety of information, including the name of the event, event severity, source node, type of event, and timestamp. Event severities are shown with different colors. Events also affect the color of the object in the Map window. Threshold parameters determine whether the event should be displayed as a serious alert or merely as a warning. Events can be acknowledged and then annotations can be associated with an event. OEM contains a predefined set of events that can report the status of nodes, databases, and listeners. You choose the events and then set the polling interval and threshold conditions that determine when the events should be generated. You do this from the Event menu at the OEM console. After you create an event set, you need to register it, to be able to monitor events on a specific system. During registration, you specify who to notify and how notification should be sent. To allow for different administrators to receive different events based on their responsibilities, each administrator's mail address or pager number, system responsibility, and availability need to be configured. E-mail and paging are two notification options. Notification can also be sent directly to the console or forwarded to SNMP-based management stations . OEM is also extensible. You can write your own applications that can be launched from the console. A scripting capability can be used to create customized events. Jobs are run by intelligent agents on the local system, so connectivity with the OEM console is needed only when a job is initially scheduled. If the agent is unavailable, the job request is queued until the agent becomes available. Jobs can be scheduled to run on a set of databases or nodes, or on a user-defined group of objects. Recovery actions can be configured to run as jobs when an event occurs. These are referred to as "fixit" jobs. The Job Scheduler can be used to create these jobs. This type of job runs as a recovery action in response to an event, instead of being scheduled for execution, which is the Job Scheduler's usual task. The job is submitted to an agent at the remote node where the event is being monitored. The job, for example, may execute a predefined SQL*Plus script file or a UNIX command or custom script that you created. The previously mentioned predefined fault management events that come with OEM provide status information on the database, listener, and computer system. Predefined resource and performance management events require the additional purchase of the Oracle Diagnostics Pack, which includes the following Oracle components:
All events discovered by the OEM agent can also be sent as SNMP traps (defined in Oracle's OEM MIB). The following is a list of the events that can be sent as SNMP traps from the OEM agent:
The Oracle Tuning Pack includes Oracle Expert, which is used for performance analysis and tuning and workload analysis, and Oracle Tablespace Manager, which can monitor and manage tablespace usage. In OEM 2, the product has been tightly integrated with HP IT/O. Each product can send events to the other. OEM can launch IT/O, and IT/O can launch OEM in the context of a reported problem from its Application Bank. Oracle Private MIBsOracle also provides several non-standard database MIBs for managing its RDBMS. Oracle supports a Private Database MIB for Oracle7 and later servers, an OEM MIB, and MIBs for the Oracle Network Listener, Oracle Multiprotocol Interchange, and Oracle Names components. The OEM intelligent agent can provide direct access to these database MIB variables. Oracle's Private Database MIB includes several useful metrics for performance monitoring. Here are some of the more important variables in this MIB:
These metrics are useful when measured over time, to calculate a rate, or in comparison with other metrics as a ratio. Information on how to use these metrics can be found in the section "Using Database Performance Data," later in this chapter, or in Oracle's Oracle Server Tuning Guide. Informix Private Database MIBInformix provides a Private Database MIB for its Informix-Online Dynamic Server product. It is meant to supplement the RDBMS MIB. Several of the following fields are useful for resource or performance management:
This MIB also has some fault management information, such as the mode (state) of the server, recovery and backup status for the tablespaces, and status of each chunk . |
| I l @ ve RuBoard |
EAN: 2147483647
Pages: 90
- The Second Wave ERP Market: An Australian Viewpoint
- Enterprise Application Integration: New Solutions for a Solved Problem or a Challenging Research Field?
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Context Management of ERP Processes in Virtual Communities
- Data Mining for Business Process Reengineering