Universal Logging System

This is a monitoring script for Oracle 9i, 10g, 11g and 12c database instances. It only does SELECTs on system tables, definitely no changes to anything! No *_HIST_* tables are used.

“watch_oracle” may not cover everything that YOU are interested in, especially not any RAC specifics (currently), but it gives quite an overview of e.g.:

• the instance status, SGA, PGA and tablespaces.
• the shared pool and the buffer pools,
• the dictionary and library caches,
• sessions, processes and database system activity,
• errors in the alert file
• and more…

The script does especially not give any information about:

• session specific content or performance of SQL commands, but you may have a look at ora_statspack
• any defined schemas
• any assigned privileges

“watch_oracle” consists of:

• the Perl script containing the source of the monitoring script: “watch_oracle.pl”
• the bash script to start the “watch_oracle.pl”: “watch_oracle”

It uses sections of the

• general configuration file for the ULS-Client for Oracle Databases: “oracle_tools.conf”

Typically, the bash script “watch_oracle” is invoked regularly thru a cron job (or task on Wind*ws), which sets the correct environment before executing the Perl script “watch_oracle.pl”.

The “watch_oracle.pl” generates a log file and several work files during execution to keep its output and work values for the next run(s). The directory defined by WORKING_DIR in the [GENERAL] section is used as the destination directory for those files, the default is “<oracle_tools_dir>/var”.

The configuration of “watch_oracle” is done by editing the file “oracle_tools.conf”,

oracle_tools.conf

# -------------------------------------------------------------------
# oracle_tools.conf
# -------------------------------------------------------------------
#
# This is the single configuration file for the ORACLE_TOOLS.
# All definitions are made here for all scripts.
 
[GENERAL]
 
WORKING_DIR = `pwd`/var
 
TEST_BEFORE_RUN = /usr/local/sccl/bin/sccl_test_res oracle_%%ORACLE_SID%%
 
[WATCH_ORACLE]
 
IDENTIFIER = _watch_oracle
 
OPTIONS = SCHEDULER

You may want to change the above parameters something different, e.g.:

oracle_tools.conf

WORKING_DIR = /var/log/watch_oracle

You can start the “watch_oracle” manually as user “oracle”:

$ cd <oracle_tools_dir>/
$ ./watch_oracle

The log and work value files are placed in directory which is defined for WORKING_DIR in the “oracle_tools.conf”, the default is “<oracle_tools_dir>/var/”.

The execution in regular time intervals is controlled by an entry in a crontab, similar to:

/etc/cron.d/oracle_tools_orcl

  
# -----
# Monitoring script for Oracle
1,11,21,31,41,51 * * * * oracle /oracle/admin/<orcl>/oracle_tools/watch_oracle

The script gathers the following metrics (in alphabetical order) of watch_oracle.pl, version 0.55:

The alert.log of the Oracle database instance is checked for occurences of the expressions:

• ORA- (any Oracle error)
• Errors in file (try to read first line from the given trace file)
• Corrupt block (is produced by RMAN in case of corrupt blocks)
• Process .. died (when a process died unexpectedly)

The search always starts at the last checked position within the alert file. The output will be restricted to a reasonable number of lines. You must check the alert log for the full error message(s).

Teststep “alert.log” is only present in interactive analysis, if error entries were found in the selected time period!

Oracle uses the buffer cache to store blocks read from disk. By default, there is only one buffer cache defined for the default block size. Other default buffer caches using different block sizes may be defined, also a keep and a recycle buffer cache.

For more information, see the Oracle® Database Performance Tuning Guide.

SELECT physical_reads, db_block_gets + consistent_gets 
  FROM V$BUFFER_POOL_STATISTICS;

The dictionary cache is part of the shared pool. Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

For more information, see the Oracle® Database Performance Tuning Guide.

 SELECT (SUM(gets - getmisses - fixed)) / SUM(gets) FROM v$rowcache;

Increase the amount of memory available to the data dictionary cache by increasing the value of the initialization parameter SHARED_POOL_SIZE (if not automatic memory management is enabled).

Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

This shows the statistics for a particular data dictionary item.

Starting in Oracle 11g release 2, Oracle has re-named the flash recovery area to be the fast recovery area FRA.

The FRA is a unified storage location for all Oracle Database files related to recovery. When you set the parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE, RMAN backups, archive logs, control file automatic backups, and database copies can be written to the FRA. RMAN automatically manages files in the FRA by deleting obsolete backups and archive files no longer required for recovery.

Use ora_dbinfo to find the parameter settings.

“Flashback database” is a feature new in Oracle 10g that allows a DBA to revert an entire database back to an earlier point in time. Depending upon the length of time of the required flashback, it is often significantly faster and easier to flashback the database (recover backwards) than perform a point-in-time recovery (forward from last full backup).

General info about the state and version of the Oracle database.

You can schedule routines (jobs) to be run periodically using the job queue. To schedule a job you submit it to the job queue, using the Oracle supplied DBMS_JOB package, and specify the frequency at which the job is to be run. Additional functionality enables you to alter, disable, or delete a job that you previously submitted.

Job queue (Jnnn) processes execute jobs in the job queue. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process.

The JOB_QUEUE_PROCESSES initialization parameter controls whether a coordinator job queue process is started by an instance. If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed.

Jobs are deprecated since Oracle 10.2, use now DBMS_SCHEDULER.

If the execution interval of a job is smaller than the execution interval of this monitoring script, not all job executions may be reported and some figures may be wrong. Check 'last execution before' to find out if all job executions are catched.

All jobs that have not been executed for more than 32 days are ignored.

Oracle collects statistics for the activity of all latches and stores these in this view. Values are only gathered if gets or misses are greater than 10 since the last script run.

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes.

The library cache is nothing more than an area in memory, specifically one of three parts inside the shared pool. The library cache is composed of shared SQL areas, PL/SQL packages and procedures, various locks and handles, and in the case of a shared server configuration, stores private SQL areas.

Whenever an application wants to execute SQL or PL/SQL (collectively called code), that code must first reside inside Oracle's library cache. When applications run and reference code, Oracle will first search the library cache to see if that code already exists in memory. If the code already exists in memory then Oracle can reuse that existing code (also known as a soft parse). If the code does not exist, Oracle must then load the code into memory (also known as a hard parse, or library cache miss). There are various criteria as to whether code being requested actually matches code already in the library cache.

Be aware that a configured library cache area, since it is allocated a specific amount of memory, can actively only hold so much code before it must age out some to make room for code that is required by applications. This is not necessarily a bad thing but we must be aware of the size of our library cache as well as how many misses or hard parses that are occurring. If there are too many, we may need to increase the amount of memory allocated to the library cache.

For more information, see the Oracle® Database Performance Tuning Guide.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, the parameter OPEN_CURSORS is set.

OPEN_CURSORS sets the maximum number of cursors each session can have open. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

Raise the OPEN_CURSORS parameter if sessions reach the current limit. If a session continuously gets an ORA-1000, it may indicate a leak in the application code.

See also: Monitoring Open and Cached Cursors.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse.

The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.

The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it.

For more information, see the Oracle® Database Performance Tuning Guide.

The automatic PGA Memory Management is enabled, if the PGA_AGGREGATE_TARGET initialization parameter is set to a non-zero value. It cannot be used for shared server connections!

The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

For more information, see the |AdminGuide|.

<member> = <status>

<member>
  The path and filename of the redo log member.

<status>:
  INVALID: File is inaccessible
  STALE  : File's contents are incomplete
  DELETED: File is no longer used

The Oracle Scheduler enables users to schedule jobs running inside the database such as PL/SQL procedures or PL/SQL blocks as well as jobs running outside the database such as shell scripts.

The number of sessions and processes. Processes include the background processes of the Oracle instance. You may check if you are running out of processes if you compare the number of current processes against the number of defined (in init.ora/spfile) number of processes.

Contains the most recently used SQL statements and parse trees along with PL/SQL blocks.

Increasing the amount of memory for the shared pool increases the amount of memory available to both the library cache and the dictionary cache.

To ensure that shared SQL areas remain in the cache after their SQL statements are parsed, increase the amount of memory available to the library cache until the V$LIBRARYCACHE.RELOADS value is near zero. To increase the amount of memory available to the library cache, increase the value of the initialization parameter SHARED_POOL_SIZE.

The Reserved Pool is 5% of the shared pool by default, that limits used to 95%, except SHARED_POOL_RESERVED_SIZE is otherwise configured manually.

You may change the shared pool size, if there is still free memory in the sga by issueing e.g.:

  ALTER SYSTEM SET shared_pool_size = 200M;

In newer Oracle databases, the parameter SGA_TARGET or MEMORY_MAX_TARGET (dynamically manage both the SGA and PGA) is set and Oracle manages all pools and the PGA by itself (Automatic Memory Management).

Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.

You may change buffer cache, shared pool and large pool without bouncing the instance, as long as there is free memory available.

(this is obsolete: all this info has moved to ora_dbinfo)

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:

• permanent tablespaces
• undo tablespaces
• temporary tablespaces

For more information, see the |AdminGuide|.

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE.

Prior to Oracle8i, all tablespaces were created as dictionary-managed. Dictionary-managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you were able to create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. These locally managed tablespaces provide better performance and greater ease of management.

You can also create a special type of tablespace called an undo tablespace. This tablespace is specifically designed to contain undo records. These are records generated by Oracle that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement.

Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps. All tablespaces, including the SYSTEM tablespace, can be locally managed.

See the DBMS_SPACE_ADMIN package for maintenance procedures for locally managed tablespaces (this may differ over Oracle versions):

CREATE TABLESPACE xyz DATAFILE '/oracle/admin/<sid>/oradata/xyz01.dbf' SIZE 2048M
  EXTENT MANAGEMENT LOCAL
  - AUTOALLOCATE       (default, preferred)
  - UNIFORM SIZE 128K  (only if necessary for best control of object growth)
  SEGMENT SPACE MANAGEMENT
  - MANUAL   (default!)
  - AUTO     (preferred)

See also: CREATE TEMPORARY TABLESPACE

Use the preferred options if possible.

Usage information about all tablespaces.

Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Wait events are grouped into classes.

When a database is up and running, every connected process is either busy performing work or waiting to do so. The 'idle' and 'other' wait classes are ignored.

Search for “Classes of Wait Events” in Oracle's Database Reference.

Wait events are statistics that are incremented by a server process or thread to indicate that it had to wai t for an event to complete before being able to continue processing. Wait event data reveals various symptom s of problems that might be impacting performance, such as latch contention, buffer contention, and I/O cont ention. Remember that these are only symptoms of problems, not the actual causes.

Generally, the analysis of wait events is very sophisticated, good documentation is found for the different Oracle versions in the Oracle® Database Performance Tuning Guide.

For some wait events, Oracle Note 223117.1 and 34558.1 may also be of interest. TIMED_STATISTICS must be set to TRUE, which is not the default for some operating systems.

These are the recommended auto-monitoring settings for the ORACLE_TOOLS. These include the recommended aggregations (or compressions) for several gathered metrics.

Use “main menu –> administration –> auto-monitoring –> <domain>” and paste the following definitions into the text box after having replaced the placeholder <notification destination> by your actual notification destination:

auto_settings.conf

# ===================================================
#
# AutoLimits, ORACLE
#
# ===================================================
 
# ===================================================
# Limits
 
[limits]
 
server=
section=Oracle DB*
teststep=alert.log
detail=error entry
unit=
function=last
over=1
relation=!==
infolevel=
warnlevel=_NO_ERROR_
errorlevel=
notifymode=S10
message=Error in alert.log: $VALUE
destination1=<notification destination>
dest1levels=IWF
destination2=
dest2levels=IWF
remark=
 
server=
section=Oracle DB*
teststep=fast recovery area
detail=%used
unit=
function=avg
over=3
relation=>
infolevel=89
warnlevel=90
errorlevel=
notifymode=24
message=The FRA is used for more than $VALUE $UNIT!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=
 
server=
section=Oracle DB*
teststep=info
detail=database status
unit=
function=last
over=1
relation=!==
infolevel=OPEN
warnlevel=OPEN
errorlevel=
notifymode=24
message=The database is not "$LIMIT"!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=
 
server=
section=Oracle DB*
teststep=redo logs
detail=status report
unit=
function=last
over=1
relation=!==
infolevel=
warnlevel=_NO_ERROR_
errorlevel=
notifymode=24
message=Incorrect state of at least one online redo log file:$VALUE
destination1=<notification destination>
dest1levels=IWF
destination2=
dest2levels=IWF
remark=
 
server=
section=Oracle DB*
teststep=tablespace usage:*
detail=%used
unit=
function=avg
over=3
relation=>
infolevel=89
warnlevel=90
errorlevel=
notifymode=24
message=This tablespace is used for more than $VALUE $UNIT!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=
 
server=
section=Oracle DB*
teststep=_*
detail=message
unit=
function=last
over=1
relation=!==
infolevel=OK
warnlevel=OK
errorlevel=
notifymode=24
message=Error when executing script for teststep "$TESTSTEP".
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=IWF
remark=
 
 
# ===================================================
# isAlive
 
[isalive]
 
server=
section=Oracle DB*
teststep=_backup*
detail=message
unit=
period=30
periodunit=hour
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all
 
 
server=
section=Oracle DB*
teststep=_rman*
detail=message
unit=
period=30
periodunit=hour
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all
 
 
server=
section=Oracle DB*
teststep=_watch*
detail=message
unit=
period=60
periodunit=min
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all
 
 
# ===================================================
# Combined Limits
 
[combilimits]
server=
section=Oracle DB*
teststep=sessions and processes
condition=$VALUE_OF(processes) >= $VALUE_OF(max processes) * 0.9
level=WARN
notifymode=24
message=The number of $VALUE_OF(processes) Oracle processes is getting close to the defined parameter 'processes' ($VALUE_OF(max processes)).
destination1=<notification destination>
destination2=
remark= 
 
 
# ===================================================
# Compression
 
[compression] 
 
server=
section=oracle db*
teststep=buffer cache:*,buffer cache
detail=hit ratio,size,used
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=dictionary cache
detail=overall hit ratio
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=fast recovery area
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all
 
server=
section=oracle db*
teststep=fast recovery area
detail=used
unit=
hourly=
daily=max,max-min
weekly=max,max-min
monthly=
access=all
 
server=
section=oracle db*
teststep=library cache
detail=*ratio
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=pga
detail=aggregate PGA target parameter
unit=
hourly=
daily=max
weekly=max
monthly=
access=all
 
server=
section=oracle db*
teststep=pga
detail=total PGA allocated
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=redo logs
detail=redo size
unit=
hourly=
daily=avg,sum
weekly=avg,sum
monthly=
access=all
 
server=
section=oracle db*
teststep=sessions and processes
detail=sessions
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=sga
detail=overall size,free memory
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=shared pool
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all
 
server=
section=oracle db*
teststep=shared pool
detail=used
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all
 
server=
section=oracle db*
teststep=system statistics
detail=user calls,user commits
unit=
hourly=
daily=avg,sum
weekly=avg,sum
monthly=
access=all
 
server=
section=oracle db*
teststep=tablespace usage
detail=size of all
unit=
hourly=
daily=max
weekly=max
monthly=
access=all
 
server=
section=oracle db*
teststep=tablespace usage:*
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all
 
server=
section=oracle db*
teststep=tablespace usage:*
detail=used
unit=
hourly=
daily=max,max-min
weekly=max,max-min
monthly=
access=all