Universal Logging System

This script collects a lot of useful facts about an Oracle database instance and builds a text report and an inventory file, which are sent to the ULS.

This script may not cover everything that YOU are interested in, especially no RAC specifics (currently), but it gives quite an overview. Collected facts are e.g.:

• name and some settings of the instance,
• all installed Oracle components,
• all installed features and their first and last usage,
• a list of installed patches as reported by opatch,
• a list of all parameters,
• the NLS settings of the database,
• the usage of the SGA, buffer caches, shared pool, PGA and tablespaces,
• the usage of the fast recovery area (if used),
• the contents of the [s]pfile, sqlnet.ora, listener.ora and tnsnames.ora,
• a textual output of the control file.

This script is run by a calling script, typically “ora_dbinfo”, that sets the correct environment before starting the Perl script ora_dbinfo.pl. The “ora_dbinfo” is started by default thru the “nightly” script. The script generates a log file and several files to keep data for the next run(s). The directory defined by WORKING_DIR in the [GENERAL] section of the “oracle_tools.conf” configuration file is used as the destination for all those files, the default is “<oracle_tools_dir>/var”.

The configuration of “ora_dbinfo” is done by editing the file “oracle_tools.conf”, check the [GENERAL] section and the [ORA_DBINFO] section.

oracle_tools_excerpt.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%%
...
[ORA_DBINFO]
 
# This script is typically called in the "nightly" script.
 
# -----
# Specify an identifier, "_ora_dbinfo" is the default
IDENTIFIER = _ora_dbinfo
...

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 “ora_dbinfo” manually as user “oracle”:

$ cd <oracle_tools_dir>/
$ ./ora_dbinfo

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

  
# -----
# Nightly jobs (database backup, removal of trace files, etc)
6 22 * * * oracle /oracle/admin/orcl/oracle_tools/nightly

Some inventory information about the Oracle database is saved in the server documentation section for that server. You find that at “main menu –> administration –> documentation –> server documentation –> <domain> –> <server>”. The csv file may look like:

inventory-oracle-hostname.csv

# Inventory of an Oracle Database:
# Oracle software version
# ORACLEVERSION;<version>
# ORACLEVERSION;11.2.0.2.0
# Name of the Oracle database instance
# ORACLEINSTANCE;<orcl>
# Hostname on which the Oracle database runs
# ORACLEHOST;<hostname>
# List of all installed Oracle components
# ORACLECOMPONENT;<component>;<version>
# ORACLECOMPONENT;Oracle Expression Filter;11.2.0.2.0
# List of all installed Oracle features
# ORACLEFEATURE;<feature>;<version>;<number of times used>
# ORACLEFEATURE;AWR Baseline;11.2.0.2.0;0
# List of all used, extra chargeable options, based on the option_usage.sql, ID 1317265.1
# ORACLEOPTIONUSAGE;<option>
# ORACLEOPTIONUSAGE;Provisioning and Patch Automation Pack for Database
# List of all installed patches as found by 'optach lsinventory'
# ORACLEPATCH;<patch_number>
# ORACLEPATCH;7154843

# DATE GENERATED;2013-02-10 09:57:20
ORACLEVERSION;11.2.0.2.0
ORACLEINSTANCE;orcl
ORACLEHOST;hostname
ORACLECOMPONENT;JServer JAVA Virtual Machine;11.2.0.2.0
ORACLECOMPONENT;Oracle Database Catalog Views;11.2.0.2.0
ORACLECOMPONENT;Oracle Database Java Packages;11.2.0.2.0
ORACLECOMPONENT;Oracle Database Packages and Types;11.2.0.2.0
ORACLECOMPONENT;Oracle Enterprise Manager;11.2.0.2.0
ORACLECOMPONENT;Oracle Expression Filter;11.2.0.2.0
ORACLECOMPONENT;Oracle Multimedia;11.2.0.2.0
ORACLECOMPONENT;Oracle Rules Manager;11.2.0.2.0
ORACLECOMPONENT;Oracle Text;11.2.0.2.0
ORACLECOMPONENT;Oracle Workspace Manager;11.2.0.2.0
ORACLECOMPONENT;Oracle XDK;11.2.0.2.0
ORACLECOMPONENT;Oracle XML Database;11.2.0.2.0
ORACLEOPTIONUSAGE;Tuning  Pack

The script produces a text report and runtime information about its own execution.

See this example report