Universal Logging System

Aka the ORACLE_TOOLS, is the GPLv3-based ULS-client for gathering runtime metrics and status information, logging configuration settings and executing regular administrative jobs on Oracle database instances which are running on Unix-like operating systems.

The ORACLE_TOOLS are now splitted up into two packages, see:

• ULS-Client-for-Oracle-Databases and
• Oracle-OpTools

on gitlab.

The ULS-Client for Oracle Databases consists of several perl scripts and modules, bash scripts and configuration files to:

• gather runtime metrics of an Oracle database instance
• do regular fully configurable database backups
• collect database relevant information as a configuration report
• create STATSPACK snapshots and reports (if STATSPACK is available)
• provide database start and stop scripts, used in SCript-CLuster packages
• grant schema-to-schema access privileges
• execute nightly and hourly scripts to accomplish maintenance tasks

It requires Perl to run and the ULS-Agent for Linux for data transmission to the ULS-server.

The monitoring script does only SELECTs against the database or call Oracle built-in utilities. No extra schema is needed but for most SELECTs the SYSDBA privileges are necessary. Usage of a more restrictive user (schema) is currently not considered.

All results and/or reports are generated as ULS value files in the ULS Value File Format which are sent to the ULS-server by using the ULS-Agent for Linux.

This description of the ULS-Client for Oracle Databases often uses “<orcl>” as a placeholder for an example ORACLE_SID, you have to replace it with the actual ORACLE_SID in your environment.

The ULS-Client for Oracle Databases provide:

• metrics about sessions, processes and PGA
• several system statistics like db block changes, physical reads and writes, sorts, user calls and commits
• information about tablespace usage
• figures of the SGA, buffer cache and shared pool usage
• metrics about fast recovery area, wait events, redo logs

The ORACLE_TOOLS are only available as a zip archive and only has to be unzipped in a destination folder, no rpm, no deb.

be sure to have the ULS-Agent for Linux installed. Try:

$ whereis flush_test_values
flush_test_values: /usr/local/bin/flush_test_values

to check.

Download the oracle_tools_2013-10-13.zip and copy it to a destination folder (<oracle_tools_dir>).

Check the checksums:

$ sha1sum oracle_tools*.zip
914f04461a1a734066e7c0c8ba900be92ea59931  oracle_tools_2013-10-13.zip

Extract them:

$ unzip oracle_tools*.zip

Find or create a script to set the proper Oracle environment for your Oracle database instance, e.g. ~oracle/oracle_env_<orcl>. Should be similar to:

oracle_env_orcl

ORACLE_BASE=/oracle
export ORACLE_BASE
 
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_HOME
 
ORACLE_SID=<orcl>
export ORACLE_SID
 
PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin
export PATH
 
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH

Create a link to it:

$ ln -s ~oracle/oracle_env_<orcl> oracle_env

The scripts of the ORACLE_TOOLS:

• watch_oracle: gather runtime performance and status information
• orarman: does a RMAN backup of the database or the redo logs
• orabackup: does an “old fashioned” backup of the database
• ora_dbinfo: collect inventory information about the database
• ora_statspack: take snapshots and generate statspack reports
• ora_grants: set schema privileges for database users

In conjunction with the SCript-CLuster:

• sccl_startstop
• instance_start, instance_stop

For regular execution of scripts:

• nightly
• hourly

Miscellaneous:

• *.pm: the needed Perl modules to run the Perl scripts
• send_ipprot: script to transfer logged ip connection protocols
• test_before_run.pl: test script to check whether a bash script od the ORACLE_TOOLS is continued or not
• cron_oracle_tools: pattern for a crontab

The normal usage:

• A crontab is defined, the scripts “watch_oracle”, “hourly” and “nightly” are executed regularly
• “watch_oracle” gathers every 10 minutes the performance and current state information and generates a file in the ULS Value File Format which is transferred to ULS by the ULS-Agent for Linux
• “nightly” is executed at 22:00h and
  • generates a statspack report
  • does a backup of the database (full on sundays, incremental on weekdays)
  • if there is a “nightly_custom” script it will be executed
• “hourly” is executed shortly after the full hour and
  • takes a statspack snapshot
  • does a backup of all archived and the current redo logs
  • if there is an “hourly_custom” script it will be executed

The hourly and nightly backups are delayed by a random number of minutes to avoid starting all database backups at the same time in the whole IT environment.

A number of scripts is executed in regular time intervals to perform all actions. That is defined in the crontab.

Create the crontab entries. Adjust the ORACLE_SID in the crontab file:

$ sed -i "s/orcl/$ORACLE_SID/g" cron_oracle_tools

Check the contents of the “cron_oracle_tools” if it corresponds to the syntax of your flavor of operating system:!:

Become root and copy that file to its destination directory (that may differ depending on your operating system):

# cp cron_oracle_tools /etc/cron.d/oracle_tools_<orcl>

It should look like:

/etc/cron.d/oracle_tools_orcl

# -----
# Monitoring script for Oracle
1,11,21,31,41,51 * * * * oracle /oracle/admin/<orcl>/oracle_tools/watch_oracle
#
# -----
# Hourly actions (e.g. backup of archived redo logs)
2 * * * * oracle /oracle/admin/<orcl>/oracle_tools/hourly
#
# -----
# Nightly jobs, MON to SAT (database backup, removal of trace files, etc)
2 22 * * * oracle /oracle/admin/<orcl>/oracle_tools/nightly

Check the regular execution of the scripts

Configuration of all oracle_tools scripts is done in the “oracle_tools.conf”, which should be a customized copy of the “oracle_tools.conf.default”.

$ cp oracle_tools.conf.default oracle_tools.conf

Customize the “oracle_tools.conf” to your needs.

See the Detailed Script Descriptions for the script-specific customizations of the configuration file.

The detailed descriptions about the scripts can be found in specific documents:

• watch_oracle is a monitoring script to determine the database status and gather runtime metrics
• ora_dbinfo is a script to collect inventory information about the database software and instance
• orarman executes RMAN backups
• orabackup performs “old style” database backups by copying the datafiles to a destination directory
• ora_statspack takes statspack snapshots and generates statspack reports

When using the ORACLE_TOOLS together with the SCript-CLuster:

• sccl_integration