Introduction
This extension help DBA to check db's health and write reports.
For healthcheck task, one can utilize these tools to make work easy:
- dbcollect, help collecting database info from many v$_* views, alert log, etc.
dbtools
- help exporting many health check reports automatically;
- use rule-based check(dbtools-rule), check database's health and show problems on vscode IDE.
For example, dbcollect will dump some data like this
<DB_HEALTH_CHECK_DATA versoin="0.3.0">
<DATABASE type="Oracle">
<INSTANCE>
<HC_DB_NAME v="TESTDB"/>
<HC_DB_ID v="8810916561"/>
<HC_DB_VERSION v="11.2.0.4.0"/>
...
dbtools use these xml data to check db's health(show in gif):
Installation
After installation, a dbtools icon will show on left activity bar.
dbtools v0.3.x best match with dbcollect v0.3.0.
And if use with dbcollect v0.2 data:
- Some check rule can't take affect because dbcollect not dump it.
- Export function is seldom affected.
Few xml tag changed in doc template, as <DB_BACKUP_STAT>, now use <DB_BACKUP_DB>.
How to
(1) How to export a database health check reports(.docx)?
- (1) Firstly you must have some raw data, so run dbcollect scripts on database host, and get some data packed such as
erpdb.tar.gz.
- (2) Create a folder on you notebook, say
repo_test, put *.tar.gz in it, and unzip.
- (3) When click dbtools icon on vscode's activity bar, it open a sidebar.
- (4) On sidebar click
Select Repository button, open a dailog, now chose repo_test directory.
- (5) Finally click
export hcc docs button on explorer title, dbtools will create 'output/' directory on repo_test, and export reports within there.
For example, repo_test tree view like this, and must be in this format(otherwise dbtools can't get correct xml files):
repo_test/
|----erpdb/
| |----1-host.xml
| |----2-database.xml
| |...
| |attachment/
| |----...
|----crmdb/
|----xxxdb/
|----...
If you directly select 'erpdb' as repository, then export erpdb failure.
Also, if path like 'repo_test/someday/erpdb', then export repo_test failure.
The exported files are put in 'repo_test/output' directory like this
|----output/
| |---- CMCBANK_ERPDB_HC_REPORT.docx # these files are what we wanted.
| |---- CMCBANK_CRMDB_HC_REPORT.docx
| |...
(2) How to auto check db's health ?
Firstly select a unchecked repository, then:
- (1) click
auto check health menu on explorer title (hidden, on export right-side)
- (2) wait for check ended, and all dbs' doubtful xml files will be marked by a red number
- (3) from right-bottom
'PROBLEMS' panel, you can find all the health problems
Checking is a time-consuming task, check dozens databases will need dozens seconds, depends on rule's quantity and complexity.
Auto check would create a new 'issues.txt' file in 'repo/output' directory, same to exported doc files.
So a checked repo should have a issues.txt, which will be loaded automatically when reopen repo later.
After modify the rule.ini or xml data, you have to run check again to get new issues info.
(3) How to setting extension to use different doc template or rule file?
Through 'vscode -> settings -> extensions -> DBTools' interface.
One can also modify template or sample rule for use.
Remember the default doc template filesize is about 50 KBytes and exported filesize is less than 100K generally. Large filesize maybe means some problem in report or doc template.
FAQ
(1) Execute export but not docx file created, or docx has lots of {HC_XXX} wrong value ?
The most possibility is the wrong repo directory architecture. Because dbtools search a db's xml file is through path 'repo -> dbname -> 2-database.xml'. The wrong path often happened when unzip the *.tar.gz files.
Another big possibility is *.xml file have some special chars cann't process by XML parser, for example, '&' and '<'.
So, if some sql in <HC_SQL_NOT_BIND> section have a condition as " where id < 200 ", then export failure.
This case already be handled by dbtools, which can auto convert '<' as (<) and '&' as (&) before write to xmlfile. But maybe have other condition that cann't predicted.
To easely find xml's wrong char, suggest install a XML tool (RedHat or other company), then open a wrong xml file you will get clearly warning hint.
(2) Exported filename like 'undefined_xxxxxx.docx'?
This is because v0.3.x dbtools require 'customer' parameter but found 'custom' in xxdb/attachment/config.toml file.
In v0.2.x version of dbcollect and dbtools, there was a typo both in *.toml and doc template, which use 'custom' rather than 'customer'.
Version after v0.3.x dbcollect and dbtools all use 'customer'.
(3) Can i add more check rule?
Yes, add useful rule to rule.ini by oneself. Give some advice to dbtools-rule project are appreciated.
Default rule.ini is '${workspaceFolder}/config/ora_full_rule.ini', you can modify it, or select another ini file through 'vscode -> settings -> extensions -> DBTools' interface, as well as what doc template can do.
On Windows platform, the extension's workspaceFolder is like:
'C:\Users\<username>\.vscode\extensions\dbworker.dbtools-0.3.1'.
On macos, the path is under '/Users/<username>/.vscode/extensions/...'
Be careful no to use too complex rule expression, or dbtools maybe fail to run it.
(4) How to analyze problem when dbtools fail to do something?
The most useful log is on 'Log(Extension Host)' view, which in dropdown menu within 'OUTPUT' panel.
Here is a example error log when selected a wrong folder as repository:
'[exthost] [error] Error: ENOENT: no such file or directory, open xxxxx failed.'
Extension Settings
This extension contributes the following settings:
dbtools.*: some command with this extension
Known Issues
(1) Rule-based checking's speed is not very fast, test time is:
- (50 dbs , basic rule) about 16.5s
- (50 dbs , full rule) about 17.5s
(2) Few rules has mistake
Such as HCC-134(spfile) on oracle12c, HCC-114(HC_DATAFILE_COUNT).
These rule need be refined later, and upload to dbtools-rule.
Release Notes
Currently is v0.3.x, match dbcollect v0.3.0
Release history
v0.3.7 at 7-Jun-2021
v0.3.3 at 4-Jun-2021
- add function for toggle rulelevel/diaginfo
- add sample repository
v0.3.2 at 3-Jun-2021
- reuse existed issues without run check every times
v0.3.1 at 3-Jun-2021
- tuning for auto check speed, now less than 0.4s per db
v0.3.0 at 1-Jun-2021
- support lots of check rules (divid by basic/ advanced/ strict levels)
- revised check function, can handle AWR data
- revised process log, operations can be viewed in
'OUTPUT' pancel
- fix setting's problems, some mistake in windows OS.
v0.2.1 at 30-Apr-2021
more early version
