{"id":4289,"date":"2015-12-05T10:55:20","date_gmt":"2015-12-05T16:55:20","guid":{"rendered":"https:\/\/www.poweradmin.com\/blog\/?p=4289"},"modified":"2015-11-30T13:35:28","modified_gmt":"2015-11-30T19:35:28","slug":"best-practices-for-monitoring-oracle-database","status":"publish","type":"post","link":"https:\/\/www.poweradmin.com\/blog\/best-practices-for-monitoring-oracle-database\/","title":{"rendered":"Best Practices for Monitoring Oracle Database"},"content":{"rendered":"

As administrators, the responsibility for keeping a database running smoothly extends to satisfying not only the demands of users but also the requirements of senior management and the enterprise as a whole.<\/span><\/p>\n

\u00a0<\/p>\n

In an Oracle environment, this requires setting up an infrastructure for monitoring the system that hinges on the objectives laid down by the business, and which also meets the conditions of often strict Service Level Agreements (SLAs) for the user community. This guide should help.<\/span><\/p>\n

An Overview of Oracle<\/b><\/span><\/h2>\n

An Oracle database consists of a collection of operating system files which hold metadata: information from applications or input by users, and structural data about the system itself.<\/span><\/p>\n

\u00a0<\/p>\n

In order for users or applications to access, view, and update information in the database, Oracle must initiate a set of background processes, and allocate the memory which will be used during database operations. An instance is the name given to each associated background process and memory allocation.<\/span><\/p>\n

\u00a0<\/p>\n

If you wish to read or write information from or to the database, an instance must be initiated. But the presence of a database isn\u2019t necessary, if you want to run an instance. Oracle\u2019s Real Application Cluster (RAC) is a feature whereby a single physical database can host multiple instances, concurrently.<\/span><\/p>\n

\u00a0<\/p>\n

Oracle is a relational database, consisting of information organised in tables of rows and columns. In these tables, data may be stored, updated, and retrieved. Automatic Storage Management or ASM is a custom-built vertical integration of Oracle\u2019s file system and volume manager. Database files can be added or removed from ASM disk storage, one ASM disk at a time.<\/span><\/p>\n

\u00a0<\/p>\n

The Oracle database server is the software which governs the database. It\u2019s made up of logical and physical structures where user, system and control information are stored. The Oracle database system is the name collectively given to the software which runs Oracle and its physical database.<\/span><\/p>\n

The Need for Monitoring<\/b><\/span><\/h2>\n

An alert log is generated every time an Oracle instance is run. Each log is a sequential stream of text messages about various Oracle operations such as database start-up and shut-down, and major events like defining tablespaces and archiving logs. Certain types of errors are also catalogued.<\/span><\/p>\n

\"oracle_alert\"
\n<\/span><\/p>\n

Database administrators (DBAs) must keep an eye on Oracle operations if the performance demands of database users are to be continuously met. Different working environments will impose different demands, to which the monitoring of database performance must adapt.<\/span><\/p>\n

\u00a0<\/p>\n

For example, some clients may require that a set percentage of transactions per second be completed by an Oracle database. Others may require database response times to be measured to coincide with set periods of a working day, or that database performance be proactively monitored, in real time.<\/span><\/p>\n

\u00a0<\/p>\n

All these requirements may have to be met, to comply with the terms of a Service Level Agreement. And it\u2019s up to the DBA to map out which performance aspects to measure, and how best to monitor them.<\/span><\/p>\n

Developing a Strategy<\/b><\/span><\/h2>\n

First, determine the time frame. Real-time data will have to be collected for a reactive monitoring approach, one which can manage crisis situations as they occur.<\/span><\/p>\n

\u00a0<\/p>\n

An ongoing series of database \u201csnapshots\u201d may need to be taken as the basis of a predictive approach, where proactive measures can be put in place to ensure system performance based on a global view of how instances are handled in the course of normal operations.<\/span><\/p>\n

\u00a0<\/p>\n

Your Oracle database is only one part of a network infrastructure, so external factors of the system as a whole will affect it. Processor power, disk access speeds and network availability issues all have an impact, and these will need to monitored as well.<\/span><\/p>\n

\u00a0<\/p>\n

It\u2019s easy to get lost in the multitude of statistics that Oracle provides <\/a>\u2013 some of which are undocumented. You\u2019ll need to decide which metrics to monitor to suit the needs of your enterprise, and which to provide reports for.<\/span><\/p>\n

\u00a0<\/p>\n

As an adjunct to this, you\u2019ll have to determine how detailed or granular is the appropriate level for measuring performance statistics and system health. Often, this will be a balancing act between obtaining enough data for a statistically viable sample and minimising the impact on overall system performance that the monitoring itself will impose.<\/span><\/p>\n

The Oracle Tool-kit<\/b><\/span><\/h2>\n

The Automated Workload Repository or AWR collects snapshots at hourly intervals over a time period determined by the database administrator. It\u2019s built into the Oracle kernel, and imposes little overhead on your database operations during its monitoring cycle. AWR is a proactive monitoring tool, for the longer term.<\/span><\/p>\n

\u00a0<\/p>\n

The Automated Session History(ASH) is an Oracle mechanism for collecting data in tandem with the AWR. Automated Session History keeps a highly detailed log of performance information at short periods (30 minutes or 1 hour, typically) \u2013 again, as laid down by the DBA. ASH represents a short-term monitoring solution for reactive strategies.<\/span><\/p>\n

\u00a0<\/p>\n

Predictive analysis of data provided by ASH and AWR may help predict fluctuations in system performance, and future outages.<\/span><\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

\"oracle_toolkit\"<\/a><\/span><\/p>\n

Oracle\u2019s v$ performance views (also called dynamic performance views or tables) are established on Oracle\u2019s x$ fixed tables, and continuously provide performance metrics and internal statistics. v$ data and metrics begin at start-up, and are best deployed over a set time period, giving repeat samples at intervals.<\/span><\/p>\n

\u00a0<\/p>\n

Oracle\u2019s Automatic Database Diagnostic Monitor (ADDM) is a self-diagnostic engine built into the database. It\u2019s set to run proactively by default, but may be configured as a reactive monitoring tool. ADDM provides impact and benefit analysis, identifies non-problem areas, and can help resolve past and current problems.<\/span><\/p>\n

\u00a0<\/p>\n

If you\u2019re willing to spend a little extra, Oracle Enterprise Manager is the add-on monitoring suite of choice. Its Configuration Pack, Tuning Pack, and Performance Pack are available for Oracle deployments of version 10g and later.<\/span><\/p>\n

Some Additional Tools<\/b><\/span><\/h2>\n

There are numerous third-party tools on the market.<\/span><\/p>\n

\u00a0<\/p>\n

Confio Ignite is a real-time monitor for Oracle which identifies performance issues without imposing a significant load on your database system, while doing so.<\/span><\/p>\n

\u00a0<\/p>\n

Proactive tools like Ion-DBA and StatsPackAnalyzer <\/a>(which is freeware) use data gathered by Oracle\u2019s own AWR and STATSPACK to identify issues \u2013 again, without imposing a load on your production environment.<\/span><\/p>\n

Buy or DIY?<\/b><\/span><\/h2>\n

There may be circumstances in which Oracle\u2019s native tool-kit or third-party software aren\u2019t enough \u2013 or cost too much. At this point, building a custom-made monitoring architecture is the database administrator\u2019s only option.<\/span><\/p>\n

\u00a0<\/p>\n

The do-it-yourself option may also come into play if the enterprise or a client wishes to have metrics that would not be monitored under a more \u201cconventional\u201d set-up.<\/span><\/p>\n

\u00a0<\/p>\n

If you do decide to self-build, be sure to provide documentation to support the approach you\u2019ve taken.<\/span><\/p>\n

\u00a0<\/p>\n

With a self-built monitoring structure, you\u2019ll have the benefits of customisation, and a system capable of being enhanced or altered to suit changing conditions. However, this approach may be time-consuming to construct, and requires an intimate knowledge of the inner workings of Oracle.<\/span><\/p>\n

Top Things to Monitor<\/b><\/span><\/h2>\n

Oracle\u2019s Listener function is essential to maintaining the availability of your database. It tracks new connections which are made to the database, as they occur \u2013 and if it goes down, then access to the database is lost. If it\u2019s available at operating system level, the Listener may be monitored by checking for TNS process errors. In addition, the \u201clsnrctl\u201d command line option reveals the status of the Listener, and the databases it\u2019s listening out for. The resulting log may be used in resolving access refusals and connectivity issues.<\/span><\/p>\n

\u00a0<\/p>\n

The \u201cORA-00020: maximum number of processes\u201d error can cause user logins to hang. If you need to log in yourself to do monitoring, this error can be especially tough to pin down. Your safest bet is to establish a performance threshold \u2013 say, 80% of the maximum. A monitoring alert at this level will give you time to establish what\u2019s happening, and pre-emptively shut down some processes so that users aren\u2019t completely locked out of the database.<\/span><\/p>\n

\u00a0<\/p>\n

Use the Alert Log or v$flash_recovery_area_usage to monitor your archive log space and recovery area, both of which may cause system hanging if storage limits are exceeded. A threshold of 80-85% for the archive log will give leeway to back up the archives and remove them to free up storage space. For the recovery area, Oracle itself sets a lower threshold at 85%, with 97% being the critical level.<\/span><\/p>\n

\u00a0<\/p>\n

The size of your Oracle filesystem and the way its tablespaces are distributed should be monitored <\/a>using thresholds based on acceptable percentages of full. With automatic extending of the tablespaces, these can grow very large \u2013 at which point low threshold percentages may actually indicate large amounts of storage space available. A combination of size and percentage threshold monitoring is advised, which may be implemented through simple shell scripts.<\/span><\/p>\n

\u00a0<\/p>\n

At a more basic level, be sure to verify the availability of your database, by logging in and performing a simple select operation. Assuming that process, tablespace or archive log issues aren\u2019t occurring, you can delve further by checking to make sure that smon, pmon and other processes are up and running.<\/span><\/p>\n

A Check-list for Database Administrators<\/b><\/span><\/h2>\n

\u00b7 Ensure that each Oracle instance is monitored for performance and file integrity.<\/span><\/p>\n

\"oracle_checklist\"<\/span><\/p>\n

\u00b7 Monitor the System Global Area (SGA) \u2013 the shared memory structures holding collective data and control information for multiple users concurrently accessing a single Oracle database instance.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Use Automatic Database Diagnostic Monitor (ADDM) to get a global view and diagnostics of database performance.
\n<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Use Automated Session History (ASH) for a more targeted analysis of database performance.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Do a baseline analysis with the Automated Workload Repository (AWR) to compare performance under varying conditions.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Real-time SQL Monitoring should be used to determine how each SQL instance is being executed at global, plan, and parallel execution levels.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 The SQL Performance Analyser (SPA) should be used on SQL workloads, to refresh their statistics with the PENDING option. The SQL Tuning Advisor will help optimise performance.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Temporary Tablespaces should be managed locally. A single Temporary Tablespace should be reserved for the entire RAC database.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Use local file headers to manage space, and reduce file fragmentation.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Permanent Tablespaces should be handled using Automatic Segment Space Management, and Auto-Allocate.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Remember to gather statistics for both user and dictionary objects. Statistics for optimisation should be gathered using automatic statistics collection.<\/span><\/p>\n

\u00a0<\/p>\n

\u00b7 Build up a knowledge base, from your observations and actions <\/a>in the monitoring \u201cfield\u201d.<\/span><\/p>\n

Be Accountable<\/b><\/span><\/h2>\n

Provide reports and documentation to support your monitoring approach. Have a valid reason for each monitoring action you take \u2013 and be prepared to defend it, to anyone who asks about it. When reporting to clients and stakeholders, be sure to specify exactly what\u2019s being monitored \u2013 and what isn\u2019t.<\/span><\/p>\n

\u00a0<\/p>\n

And be flexible. If management or a client wants to add something to your list of monitoring targets, be prepared to work it in.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

As administrators, the responsibility for keeping a database running smoothly extends to satisfying not only the demands of users but also the requirements of senior management and the enterprise as a whole. \u00a0 In an Oracle environment, this requires setting up an infrastructure for monitoring the system that hinges on the objectives laid down by […]<\/p>\n","protected":false},"author":8,"featured_media":4292,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6],"tags":[],"class_list":["post-4289","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general-it","category-tech"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/4289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/comments?post=4289"}],"version-history":[{"count":3,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/4289\/revisions"}],"predecessor-version":[{"id":4296,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/4289\/revisions\/4296"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media\/4292"}],"wp:attachment":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media?parent=4289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/categories?post=4289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/tags?post=4289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}