DBCollect is a performance and metadata collection tool for servers running Oracle databases, providing workload and config data from database hosts
= Overview =
'''''It is NOT (yet?) an official DELL tool''''' - but written by [https://dirty-cache.com/ Bart Sjerps] as an independent Open-Source project
DBcollect is a tool that collects database and host information from Oracle database servers in a standardized way, so it can easily be processed by workload analysis tools. It supports Enterprise Linux (RHEL, OEL, CentOS) as well as AIX and Solaris (SPARC).
It collects database and host information from Oracle database servers in a standardized way, so it can easily be processed by workload analysis tools. It supports Enterprise Linux (RHEL, OEL, CentOS) as well as AIX and Solaris (SPARC).
[https://github.com/outrunnl/dbcollect DBcollect on Github]
[https://github.com/outrunnl/dbcollect DBcollect on Github]
<div style="clear: both"></div>
== How does it work ==
# System or Database Administrator runs DBCollect on a database host
#* Can be a VM or bare metal host
#* Should be run on all database nodes in scope of the project
#* Supported are Linux (EL6 or higher, runs out of the box), AIX, HP-UX or Solaris (may require some steps to make it work)
In most cases it automatically detects all running Oracle instances on the host.
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
** 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 architecture =
Line 31:
Line 85:
* AWR or Statspack reports for the given period (default 10 days)
* 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, ...)
* 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: [https://github.com/outrunnl/dbcollect/blob/master/src/dbcollect/lib/config.py DBcollect config]
= 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:
<syntaxhighlight lang=bash>
dbcollect --strip
</syntaxhighlight>
Note that this only works for HTML-formatted AWR reports, it does NOT work for Statspack.
= Safety =
= Safety =
DBcollect is designed to run on production systems and great effort has been spent on making it robust. The [https://github.com/outrunnl/dbcollect/blob/master/SECURITY.md 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.
DBcollect is designed to run on production systems and great effort has been spent on making it robust. The [https://github.com/outrunnl/dbcollect/blob/master/SECURITY.md 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.
== root user ==
DBcollect does NOT run as root (ever). If you execute it as root, it will re-launch itself as another user, usually 'oracle' or 'nobody' on systems without Oracle. You can force another user:
DBCollect is a performance and metadata collection tool for servers running Oracle databases, providing workload and config data from database hosts
It is NOT (yet?) an official DELL tool - but written by Bart Sjerps as an independent Open-Source project
It collects database and host information from Oracle database servers in a standardized way, so it can easily be processed by workload analysis tools. It supports Enterprise Linux (RHEL, OEL, CentOS) as well as AIX and Solaris (SPARC).
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
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: DBcollect config
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.
root user
DBcollect does NOT run as root (ever). If you execute it as root, it will re-launch itself as another user, usually 'oracle' or 'nobody' on systems without Oracle. You can force another user:
dbcollect--useroraprod
Example
# Install dbcollect via the quick install metohd
curlhttps://raw.githubusercontent.com/outrunnl/dbcollect/master/scripts/download|python
%Total%Received%XferdAverageSpeedTimeTimeTimeCurrent
DloadUploadTotalSpentLeftSpeed
100215410021540040180--:--:----:--:----:--:--4011
Githubdbcollectdownloader-downloadthelatestdbcollectrelease
->RetrievingGitHubmetadatafromhttps://api.github.com/repos/outrunnl/dbcollect/releases/latest
->Downloadingbinaryversion1.16.3fromhttps://github.com/outrunnl/dbcollect/releases/download/v1.16.3/dbcollect
->md5hash:6d080140269bf9fe14039cfbfbd6806e
->Savingbinaryas/usr/local/bin/dbcollect
->Settingpermissionstomode755# Test version[root@db08~](-)# dbcollect --version
dbcollect1.16.3-collectOracleAWR/Statspack,databaseandsysteminfo
Author:BartSjerps<info@dirty-cache.com>
Copyright:Copyright2023,BartSjerps
License:GPLv3+,https://www.gnu.org/licenses/gpl-3.0.html
Version:1.16.3
Builddate:2024-07-0115:04
Buildhash:1e08f98a7ca3b546d166c964300be221f952c7a0
Verify dbcollect source code
# Verify dbcollect contents# As dbcollect is actually a ZIP file, you could unzip it and verify the code
unzip-v/usr/local/bin/dbcollect
Archive:/usr/local/bin/dbcollect
LengthMethodSizeCmprDateTimeCRC-32Name
----------------------------------------------------
0Stored00%07-01-202415:0100000000lib/
0Stored00%07-01-202415:0100000000modules/
0Stored00%07-01-202415:0100000000sql/
7816Stored78160%06-10-202413:06267803f6dbcollect.py
0Stored00%10-31-202319:1400000000lib/__init__.py
257Stored2570%07-01-202415:0434d07d31lib/buildinfo.py
2767Stored27670%05-30-202415:33d95147e0lib/user.py
1984Stored19840%05-30-202415:345272c204lib/archive.py
2322Stored23220%05-30-202415:34fdd6efa2lib/functions.py
---snip---
209Stored2090%02-05-202408:38446743e2sql/splunk/readme.txt
79Stored790%02-05-202408:383b1af7bdsql/splunk/splunk_header.sql
58Stored580%07-01-202415:048fed2ee0__main__.py
-------------------------
1661631661630%81files
Run dbcollect
# Run dbcollect (default 10 days of AWR, user 'oracle'
dbcollect1.6.0-collectOracleAWR/Statspack,databaseandsysteminfo
INFO:dbcollect1.6.0-databaseandsysteminfocollector
INFO:Currentuserisoracle
INFO:Zipfileis/tmp/dbcollect-db01.lan.zip
INFO:CollectingOSinfo(Linux)
INFO:CollectingLinuxSARfiles
INFO:CollectingOracleinfo
INFO:FoundinstanceDEMOon/u01/app/oracle/product/12.1.0/dbhome_1
WARNING:SkippedDEMO(DOWN)
INFO:FoundinstanceDEMO2on/u01/app/oracle/product/12.1.0/dbhome_1
WARNING:SkippedDEMO2(DOWN)
INFO:Foundinstancedemoon/u01/app/oracle/product/12.1.0/dbhome_1
WARNING:Skippeddemo(DOWN)
INFO:FoundinstanceDB01on/u01/app/oracle/product/12.1.0/dbhome_1
INFO:DB01:AWRusagedetected,generatingreports
INFO:GeneratingOracleAWR/Statspackreports,with1workers
INFO:processinginstanceDB01
report15of230(6.5%done),elapsed:0:00:34,remaining:0:08:18
INFO:Zipfile/tmp/dbcollect-db01.lan.zipiscreatedsuccesfully.
INFO:Finished
Page 
