The OpenEdge: DBTOOL, IDXFIX and IDXCHECK utilities can be run online or offline. They and are useful in identifying physical data and index database corruption.
DBTOOL will check data (table) corruption, the index utilities (IDXFIX and IDXCHECK ) check indexes in the record field keys and index blocks:
- Neither validate Large Objects(LOBs).
- Neither assure Logical data integrity, can only be verified by running application specific ABL code. Typically there should be no need to do so, provided that the database has never been forced into by either skipping crash recovery (-F) or running PROSTRCT REPAIR/UNLOCK operations. The discussion of Logical Data Integrity checks is out of scope in this Article.
- Neither will provide a fully comprehensive check when run online
- DBTOOL does not report field content corruption, it assures the record can be read and all fields are there (not the content)
- Whenever PROUTIL is run, the -cpinternal of the database must be used
Method A (Online) - In a production environment, the following tools and Options should be used:
When dealing with the online production database, it is essential to limit the performance impact of the scans being run. For this reason, the following recommendations are broken out by environment:
- For verification of database blocks use: DBTOOL
dbtool dbname
dbtool - option 5 (Read or Validate Database Block(s))
- This dbtool option reads all data blocks in the database and validates the block headers, similar to a full probkup but only for data blocks.
- Select a verbose and validation level of 0 for the scan. This is a minimal impact production performance.
Another method of verifying all Block level corruption is to run a mock online PROBKUP with private buffers, to /dev/null (NUL on windows). This will not create a backup but will read all database blocks under the HWM of each area:
Windows: $ probkup [online] <dbname> NUL -Bp 32
UNIX: $ probkup [online] <dbname> /dev/null -Bp 32
While DBTOOL Option 5, checks for data block level corruption, Option 3 to look for record level corruption should still be executed offline at some point or in parallel against a copy (This option impacts performance if executed online).
- For verification of index block and content use: IDXCHECK
proutil dbname -C idxcheck [all | table [owner-name.]table-name | area area-name | schema schema-owner] -TB 24 -TM 32 [-cpinternal <>]
- IDXCHECK online functionality will perform a cursory index block level check for the entire database, or a table, area or schema owner level.
- As this utility can be run online, it can also be run in parallel to the DBTOOL command above.
The IDXCHECK utility defaults to the following validation checks:
- Select Option: ‘o’ to change Validation Options in the main menu
* 1 - Validate physical consistency of index blocks
* 2 - Validate keys for each record
3 - Validate record for each key
* 4 - Validate key order
For a comprehensive IDXCHECK, validation Option 3 can also be selected. There are associated performance ramifications which is why it is not one of the default options:
- If you run validation Option 3 online, there is the additional Option: L - Lock tables, during the check to assure there are no changes
- It is recommended to use validation Option 3 against a copy of the production environment so as not to impact db performance (see below)
- What are the implications of the online IDXCHECK locking options?
- When rolling forward ai files, the -MemCheck and -DbCheck parameters can be used to ensure that there are no problems applying the ai transaction notes themselves.
rfutil dbname -C roll forward -a /pathnametoaifile/aifile -MemCheck -DbCheck
The -MemCheck parameter performs a memory consistency check.
The -DbCheck option enables a database consistency check for all index and record blocks except BLOB blocks for the entire database.
Should a problem be detected when rolling forward an ai file, an error will be presented and the roll forward option will fail as a result without applying the notes to the hot standby/backup.
When corruption issues persist, it is advisable to enable the Database with these -MemCheck -DbCheck parameters
If further corruption results, reported errors highlight that new corruption is being introduced which needs further attention. At least having this parameters enabled will assure the problem is caught before it propagates.
Method B (Offline or copy of db) - When dealing with a copy of a production database or while the production database is offline, the following tools should be used:
1 - For verification of database records and data blocks use:
dbtool <dbname>
For verification of database data blocks use:
dbtool - Option 5 (Read or Validate Database Block(s))
For verification of database records use:
dbtool - Option 3 (Record Validation).
- Record Validation performs a record level validation by putting records together in memory in an effort to ensure that the record is properly formatted and accessible in every block
- This option of dbtool can impact database performance and that is why it is typically reserved for scanning a backup or a copy of a production database
- It does not validate missing record fragmentation in Large Objects(LOBs) or touch indexes
2 - For verification of indexes, use IDXFIX
proutil dbname -C idxfix -rusage -silent [-cpinternal]
IDXFIX Option 3 does both:
- 1. Scan records for missing index entries and Option 2. Scan indexes for invalid index entries.
- IDXFIX Option 3 will check database records and indexes to determine whether an index is corrupt or a record has a missing or incorrect index.
- This utility can also repair corrupted indexes when "Fix indexes on Scan" is activated and will enable disabled indexes when IDXFIX is run offline.
- It is advisable to first run IDXFIX in report mode first to ascertain the extent of corruption before running to fix indexes.
- Running IDXFIX this against a hotspare database will require a rebaslined as the idxfix utility goes through crash recovery. AI files can no longer be applied with RFUTIL -C roll forward once crash recovery has completed (errors 831 and 832)
- It may need to be run multiple times to first ascertain the extent of corruption and then with the 'fix' option again until it eventually reports that 0 index entries were fixed, at which point either the utility can't fix problems key-index corruption any longer or there are no problems that it can fix. This is because each time is it run every entry is fixed on the first pass and not revisited in the same run.
Note: Both IDXCHECK and IDXFIX by default, only scan SHARED indexes in a Multi-Tenant enabled database.
In order to select all tenant partitions, all the tenants need to be checked under the "Multi-Tenancy (m/M) - Choose tenants or groups" menu option.
