About DBCollect

Why DBcollect

In the past, I frequently got workload data from customers in all kinds of strange formats (unusable AWR formats, reports in text format, with far too large AWR intervals, etc). Unix info was usually either not available at all or a random dump of output of some commands. By standardizing the reporting in a single tool, we get consistent, reliable database and host information.

This is needed for creating workload assessments where the data must be machine readable in a standardized format.

In most cases it automatically detects all running Oracle instances on the host.

Problem

Customers find all kinds of creative ways to provide system data, for example:

  • Excel sheets, Word docs, strange RAR or TAR files, AWR reports with insanely long or short intervals (6 weeks 😮) or weird language settings, etc.
  • Not easily processed by automated tools (Try to load Excel into a database table)
  • Still missing capacity and system data (how big is the database? What processor is being used? Compression? Encryption? Backups?)
  • Usually only for one or few databases (customer may have hundreds)
  • Often unreliable (outdated, non-deterministic metrics, and so on)

Solution

Provide a standard way of getting OS and Database performance/config data

  • Oracle AWR, Linux/UNIX SAR, Database configuration, OS configuration
  • Retrieves historic data (which is already stored on the OS and in the databases)
  • All (running) Oracle instances, at least 10 days (Oracle) or 1 month (OS) performance
  • Be as secure and safe as possible
  • Cannot change anything on the databases
  • Only reads/writes files in the /tmp directory
  • Does not pick up security sensitive data (passwords, etc)
  • Honor customer licenses (in particular, Diagnostics Pack is required to generate AWRs)
  • Make it as easy as possible for the administrator to run it
  • Tool consists of one file, requires running one command only, simple installation
  • Generates only one output (ZIP) file in the /tmp directory
  • Open-Source (GitHub, GPLv3 licensed)
  • Users can review the code (Python, SQL*Plus scripts and commands)
  • Users are encouraged to help testing, writing improvements (via Git pull requests or just by email)

DBcollect architecture

DBcollect is written in Python. It supports Python 2 and 3 (mainly because a lot of legacy systems only have Python 2 installed). It also contains a few SQL scripts that are executed by Oracle SQL*Plus. It is distributed as a ZipApp package. This means it is not binary compiled, but all Python and SQL files are bundled in a ZIP file in such a way that it can be executed as a single command but users can inspect the contents by simply running unzip:

unzip -v /usr/local/bin/dbcollect

Collected data

  • Host CPU, memory, network interface and disk info
  • Running processes, file systems, installed packages, os version and release etc.
  • Hardware vendor, product etc where possible
  • SAR reports
  • AWR or Statspack reports for the given period (default 10 days)
  • Additional database information (such as capacity of tablespaces and disks, settings, compression analysis, backup, ...)

For detailed info on what data is collected, most commands and files are in the config section: config.py

Removing SQL code

DBcollect generates HTML formatted Oracle AWR reports. These reports usually contain snippets of SQL queries. These snippets may sometimes help us in finding the root cause of slow queries, but if it is prohibited to send SQL queries outside of the organization, the SQL sections can be completely removed from the DBcollect ZIP files:

dbcollect --strip

Note that this only works for HTML-formatted AWR reports, it does NOT work for Statspack.

Safety

DBcollect is designed to run on production systems and great effort has been spent on making it robust. The Security section on the github page explains in great detail how this works. Note that dbcollect has an option to remove SQL code from AWR reports, in case this is condidered a security or confidentiality issue.