DBAmon Historical Data

DBAmon

Historical Data

DBAmon Historical Data

Starting with DBAmon Version 3.30A, historical data is stored in an Oracle database. These tables are called the DBAmon Repository. The DBAmon Repository is only available if the dbamonrc Use_Oracle: parameter is Y.

The repository is used to store historical data about the DB/Application instances that DBAmon monitors. The data is collected from the instance being monitored using a once per day sampling method. The first DBAmon iteration every calendar day for each instance will sample the measures being collected and will store this data once per day in the repository table dbamon.history.

As new measures are added to DBAmon, this page will be updated with the new data format.

Table: dbamon.history Schema

Column Name                    Null?    Type
------------------------------ -------- ----
DBAMONMODE                     NOT NULL VARCHAR2(8)
INSTANCE                       NOT NULL VARCHAR2(32)
SYSTEM                         NOT NULL VARCHAR2(32)
DBMS                           NOT NULL VARCHAR2(16)
CATEGORY                       NOT NULL VARCHAR2(3)
PARM                           NOT NULL VARCHAR2(32)
VALUE                          NOT NULL NUMBER(15,2)
VALUE2                                  NUMBER(15,2)
TS                                      DATE
ZDATE                                   CHAR(10)

Table: dbamon.history Column Description

DBAMONMODE - test or prod Depending on DBAmon mode
INSTANCE - The name of the instance; a combination of server name and instance name; example: txtu09_ZANTIQE1
SYSTEM - The fully qualified name of the server; example: txtu09.company.com (from Host: value of DBC file)
DBMS - The DBMS value for this instance; example: OracleDB-UX or Informix-UX or MSSQL or OracleDB-NT
TS - The Oracle DATA value when this row was added
ZDATE - The date in YYYY/MM/DD when this row was added

CATEGORY Value	CATEGORY Desc.	PARM	VALUE	VALUE2
PER	Oracle Performance - Shared Pool Data Buffer Hit Ratio	bufhitratio	(Hit Ratio 0-100)	(N/A)
PER	Oracle Performance - Shared Pool Dictionary Cache Hit Ratio	dicthitratio	(Hit Ratio 0-100)	(N/A)
PER	Oracle Performance - Shared Pool Library Cache Hit Ratio	libhitratio	(Hit Ratio 0-100)	(N/A)
SPC	Space Utilization	Oracle: Tablespace Name Informix: DBSpace Name MSSQL: Database Name	(MB Allocated)	(MB Used)
STA	Oracle Statistics - Concurrent Sessions High Water Mark	sessionshwm	(Session Count)	(N/A)

Data Extraction

A program is provided to extract the data from the repository dbamon.history table into an ascii file. The program is designed to be run from cron. Here is a sample cron entry:

00 06 * * * /opt/dbamon/bin/dbamon_ora_histread.pl > /opt/dbamon/log/dbamon_ora_histread.log 2>&1

By default, this program will rcp a file called dbamon_ora_histread.txt to the server whose name you must hard code at the start of the program.

Reporting/Graphics

Here at ??? we have a collection of SAS programs which automate the process of creating graphs from this historical data for use on our WWW site. If you want a copy of these programs, Contact Me.

Old /opt/dbamon/dat/history/dbspaces Data

DBAmon still (3.30B) records Oracle-Tablespace/Informix-DBSpace/MSSQL-Database space utilization information in /opt/dbamon/dat/history/dbspaces with one file for each distinct date. At some time in the future this method of recording data will be discontinued.

DBAmon.com
This Document: http://dbamon.com/misc/history.shtml