DBCollect/Usage
Page 
Usage
Where to run
Where to run dbcollect
- All Database nodes (in scope of the project) including all RAC nodes and all Data Guard Standby nodes
- Optional: On all application nodes and hypervisors
- On non-database hosts, dbcollect will only pick up OS config and performance data
Short summary
If dbcollect is installed correctly:
# Simply run dbcollect
dbcollect
# Error: Zipfile already exists
# Overwrite previous zipfile
dbcollect --overwrite
# Error: No AWR or Statspack detected
# If you are sure the system is licensed with Diagnostics Pack
dbcollect --overwrite --force-awr
# If you want to retrieve more than 10 days worth of AWR files:
dbcollect --days 20
# Limit dbcollect to use only 2 tasks (CPUs) for AWR generation (default = 50% of CPUs, with a maximum of 8)
dbcollect --tasks 2
Strip SQL code
AWR reports by default contain snippets of Oracle SQL queries. These are not used for reports but can be useful in performance diagnosis. Some customers consider the SQL code confidential information and don't allow this to leave the company. dbcollect has the option to remove all SQL code from the AWR reports:
dbcollect --strip
Notes:
- This ONLY works for AWR, not for Statspack
- It removes the SQL Statistics (such as
SQL ordered by CPU Time) from AWR, as well as the entire ADDM section - It is based on XML parsing in Python, if the XML parsing fails, it will write the original AWR (HTML) report (but show a warning in the logfile).
- Mileage may vary. So far it works flawlessly but no guarantee can be made. Inspect the ZIP files if you want to make sure.
Resolve AWR reporting issues
dbcollect checks for prior AWR usage to make sure it will not trigger Oracle license violations ( More info)
If you get Error [DBC-E021] No AWR or Statspack detected:
# If you are sure you have Diagnostics Pack license:
dbcollect --force-awr
# If you want to ignore databases without AWR usage:
# Note that this will result in under-reporting of performance and capacity due to missing data
dbcollect --ignore-awr
# If the problem databases are not important, try excluding them:
dbcollect --exclude inst1,inst2
Oracle RAC
On RAC, dbcollect will generate AWR reports for all instances on all nodes. This means if you run dbcollect on all RAC nodes (which is recommended) then all AWR reports will be generated multiple times (2 times for a 2-node cluster). To avoid this, run with the --no-rac option. This will cause AWR reports ONLY to be generated for the actual node, and saves on diskspace and resource overhead.
Oracle Data Guard
An Oracle instance that runs as Data Guard Standby is usually in MOUNT mode and does not allow AWR reports to be generated. Standby databases can be running in Real-Time Query or Real-Time Apply mode, but running standard AWR reports causes the AWR reports to be generated for the PRIMARY instance (there is no specific Standby performance data).
Data Guard can be configured with RMF (Remote Management Framework) which causes performance statistics to be collected for the STANDBY database.
dbcollect will pick up AWR reports for the STANDBY database if this is enabled. To disable this feature, use --no-stby.
Monitoring progress
Once dbcollect is running, it will pick up OS info, and after a few seconds it will start generating AWR reports.
[root@db08 ~](-) # dbcollect -o
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
The remaining time is an estimate on how long is required to generate all reports.
Once complete, dbcollect should show a message that the ZIP file creation was succesful:
INFO : Report 25 of 25 (100.0% done), elapsed: 0:00:40, remaining: 0:00:00, reports/s: 0.63
INFO : Zip file /tmp/dbcollect-db08.lan.zip is created succesfully.
INFO : Finished
[root@db08 ~](-) #
Check the output for any critical errors.
Checking results
The output of dbcollect is written by default to /tmp/dbcollect-<hostname>.zip.
The zipfile should be readable with normal ZIP tools (unzip):
# Check if the ZIP file exists
ls -al /tmp/dbcollect-*.zip
-rw-r--r-- 1 oracle oinstall 2074253 Oct 10 14:37 /tmp/dbcollect-db08.lan.zip
# Verify contents
unzip -v /tmp/dbcollect-db08.lan.zip
Archive: /tmp/dbcollect-db08.lan.zip
dbcollect version=1.16.3 hostname=db08.lan
Length Method Size Cmpr Date Time CRC-32 Name
-------- ------ ------- ---- ---------- ----- -------- ----
761 Defl:N 385 49% 10-10-2024 14:36 1296357e db08.lan/meta.json
855 Defl:N 402 53% 10-10-2024 14:36 f4d6f4f2 db08.lan/hostinfo.json
11824 Defl:N 1536 87% 10-10-2024 14:36 83805993 db08.lan/diskinfo.json
-- snip --
47823 Defl:N 6520 86% 10-10-2024 14:36 0b993954 db08.lan/oracle/log/demo08_sqlplus_7658.log
2520 Defl:N 329 87% 10-10-2024 14:37 d4e5c89c db08.lan/oracle/log/demo08_sqlplus_8094.log
2730 Defl:N 342 88% 10-10-2024 14:37 5f3fd91a db08.lan/oracle/log/demo08_sqlplus_8095.log
4998 Defl:N 960 81% 10-10-2024 14:37 c2c4f51c db08.lan/dbcollect.log
-------- ------- --- -------
18105774 2054255 89% 125 files
# Note that the files in the ZIP always start with the hostname (to prevent a mess if you unzip it in your home dir).
# The first file in the archive should be the meta.json, the last file should be the dbcollect.log
# If dbcollect.log is missing, then the ZIP file was not written completely.
# The dbcollect.log is probably still in /tmp. Reading it may reveal what went wrong.
Do not change the generated ZIP archive (it has metadata and structures in place so it can be correctly processed by our reporting tools). Send the ZIP file as-is.
Using credentials file
With the option --dbcreds <credentials 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).
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
screenortmuxsession [1]. - Run in a nohup session:
nohup dbcollect <options> &
Another way (if there are multiple systems) is to run using Ansible.
Troubleshooting
dbcollect fails to execute
- Check if
/usr/bin/env pythonstarts up a Python session. It should show the version. Exit with CTRL-D. - If it does not work, check if Python is installed at all:
python - If Python is available but running dbcollect does not work, run
python /usr/local/bin/dbcollect <options>or trypython3or something similar
dbcollect runs but fails with errors
A list of all error messages and explanation can be found here DBCollect Error messages
Performance
- dbcollect takes very long to run
This is frequently the case if dbcollect runs against Oracle RAC. There are a number of known issues that cause it to be very slow. I have observed up to 20 seconds per AWR report (usually about 0.5 seconds) making dbcollect run for over an hour or more for a typical 10-day, 1-hour interval, single database cluster. Check Support notes for fixes and workarounds: 2404906.1, 2565465.1, 2318124.1, 29932310.8, 2148489.1, 29470291.8. Or be very patient.
Update: As of version 1.11, dbcollect runs multiple AWR reports in parallel for each instance, making it much faster, by default 50% of CPUs. To use all available CPUs, use --tasks 0 (note that CPU load will likely go to 100%, be careful)
- dbcollect seems to hang
- Check if the database itself works correctly. Can you switch logfiles, manually run a report?
- Timeouts
- Sometimes a SELECT on DBA_FREE_SPACE or CDB_FREE_SPACE in Oracle is very slow, causing the dbinfo scripts to run very long which in turn causes the job processors to timeout. Possible fix:
SQL> purge dba_recyclebin;
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;