Usage

Before you start

  • Note if the systems have Diagnostics Pack (for generating AWR reports)
  • Review retention and interval for Oracle AWR and Linux Sysstat

Where to run

  • All hosts (including all RAC nodes) in scope of the project
  • Optional: On all application nodes and hypervisors
    On non-database hosts, dbcollect will only pick up OS config and performance data

Basic operation

In the majority of cases, simply run dbcollect and it will run with default options. About 10 days of AWR reports will be created for each detected running Oracle instance (depending on AWR retention). SAR data is usually picked up for 30/31 days (1 month) where available.

Monitoring progress

In normal operation, dbcollect will calculate how long it expects to run and it will show the progress.

Progress output can be disabled with the -q|--quiet option (only errors will be shown).

You can always cancel with CTRL-C but it may cause the dbcollect ZIP file to be corrupt (clean it up using the --cleanup option).

# dbcollect
dbcollect 1.16.3 - collect Oracle AWR/Statspack, database and system info
INFO     : dbcollect 1.16.3 - database and system info collector
INFO     : Python version 3.6.8
INFO     : Current user is oracle
INFO     : Zip file is /tmp/dbcollect-db08.lan.zip
INFO     : Collecting OS info (Linux)
INFO     : Collecting Linux SAR files
INFO     : Collecting Oracle info
INFO     : Detecting Oracle instances
INFO     : Stopped instances: demo81
INFO     : Running instances: demo08
INFO     : demo08: AWR usage detected, generating reports 
INFO     : demo08: 25 reports
INFO     : demo08: Running opatch lspatches
INFO     : demo08: Running dbinfo scripts
INFO     : demo08: DBInfo processor finished, elapsed time 8.38 seconds
INFO     : demo08: Started 2 SQLPlus sessions
Report 16 of 25 (64.0% done), elapsed: 0:00:30, remaining: 0:00:17, reports/s: 0.53

Running unattended

If dbcollect runs a very long time and you don't want to keep a CLI session open, there are a few ways to do that:

  • Run in a screen or tmux session [https://linuxize.com/post/how-to-use-linux-screen/].
  • Run in a nohup session: nohup dbcollect &

Another way (if there are multiple systems) is to run using Ansible.

Checking results

You can see what data was collected simply by running unzip against the /tmp/dbcollect-*.zip file.

Diagnostics Pack license

Creating AWR reports requires Oracle Diagnostic Pack license. dbcollect tries to detect prior usage of AWR and if this is detected, AWR reports are generated. If prior AWR usage is not detected, dbcollect will abort with an error. If you have Diagnostic Pack license but not created AWR reports, you can force dbcollect to generate AWR reports using the --license-ok flag (see below).

Oracle RAC

By default, dbcollect picks up AWR reports from ALL RAC instances. This means if you run dbcollect on multiple RAC nodes, most of the AWR reports will be created multiple times. To avoid this, use the --no-rac option (see below). This will significantly reduce the time it takes to run dbcollect and the size of the generated ZIP file.

Only use this if you run dbcollect on ALL RAC nodes (which is strongly recommended).

Transferring DBCollect ZIP files

When complete, a ZIP file will be created in the /tmp directory. This file contains the database overview and, by default, the last 10 days of AWR or Statspack reports. All temp files will be either cleaned up or moved into the ZIP archive. It also contains the dbcollect.log file that can be used for troubleshooting in case something went wrong.

You can inspect the ZIP file using normal zip/unzip tools (if installed):

# List the contents of the ZIP file
unzip -v /tmp/dbcollect-hostname.zip

# Dump contents of a file on stdout (avoid binary files, they create a mess):
unzip -qc /tmp/dbcollect-hostname.zip hostname/dbcollect.log

The ZIP file can be sent to the author in several ways, but make sure the original dbcollect-*.zip files are UNMODIFIED!

This means, do not unpack and re-pack the file(s) as this causes problems with the file metadata. If you want to encrypt the files, use a container archive (ZIP, TAR, 7-zip or whatever) and pack the original, unmodified dbcollect files in the new archive.

If you transfer the files to Windows first, make sure to use BINARY transfers to avoid corrupting the ZIP file.

The files can be uploaded to a NextCloud dropbox: DBCollect Dropbox Also, if a project has been setup already on LoadMaster (the reporting engine), the owner of the project can generate a unique URL for uploading directly onto the engine, using a URL like https://loadmaster.dirty-cache.com/dropbox?uuid=<unique_uuid>

Both of these methods are HTTPS encrypted.

Using a logons file

With the option --logons <logons file>, dbcollect will make SQL*Plus connections using SQL*Net with any database user, instead of OS (OPS$) connections using SYSDBA privileges.

This allows dbcollect to run as any OS user (i.e., 'nobody'), because it no longer requires OS level authentication, as long as it has access to a valid Oracle SQL*Plus environment (ORACLE_HOME).

  • OS user must have access to a valid ORACLE_HOME for running SQL*Plus.
  • A credentials file must be provided with a (readable) file containing a valid connect url for each instance.
  • The privileges must be provided in the logons file where each line has the form user/password@hostname/instance.
  • The database user must have read access to v$, DBA_* and CDB_* tables (provided by the SELECT ANY DICTIONARY privilege)
  • This requires the listener to be available and listening for the provided service.

The DBSNMP user is predefined with these privileges. When using DBSNMP for this purpose, it must be unlocked and have a valid password on each instance.

The ORACLE_HOME will be retrieved from /etc/oratab but can be provided with --orahome if oratab is not valid or available for the OS user.

The database user/password must be provided in the logon definition as such:

user/password@hostname/instance

For example:

dbsnmp/topsecret@//example.local/orcl
johndoe/topsecret@//test.local/orcl2

Note that dbcollect will try to connect to each enabled and running instance, and will fail if any of the provided credentials are invalid or missing, or the connection cannot be made for whatever reason. There is a 10 second timeout for hanging connections.

The OS user does not even need to have a valid OS login, it can be executed as root using the runuser command:

echo '/usr/local/bin/dbcollect --logons /tmp/logons' | runuser nobody -s /bin/bash
# or (cleaner)
runuser nobody -s /bin/bash <<< "/usr/local/bin/dbcollect --logons /tmp/logons.txt <options>"

An example wrapper script is provided in the contrib directory.

More info

The dbcollect ZipApp package contains everything such as Python files and SQL scripts in a single file.

It is a standard ZIP file - only prepended with a Python "shebang":

#!/usr/bin/env python

For inspection you can unzip the package using a standard ZIP tool. It is not recommended to run dbcollect in any other way than via the distributed package. Although it works, avoid using git clone or other ways to run it from the github sources.