[HANA] 1977584 - Technical Consistency Checks for SAP HANA Databases

- https://launchpad.support.sap.com/#/notes/1977584%20%E2%80%93%20Technical%20Consistency%20Checks%20for%20SAP%0A%09%09%09%09%09%09%09%09%09HANA%20Databases

https://launchpad.support.sap.com/#/notes/1977584%20%E2%80%93%20Technical%20Consistency%20Checks%20for%20SAP%0A%09%09%09%09%09%09%09%09%09HANA%20Databases

 

Symptom

You are interested in an pro-active SAP HANA consistency check

You already face symptoms that can indicate SAP HANA inconsistencies and want to check if and to what extent corruptions exist.

Other Terms

Corruption wrong result set duplicate keys terminations inconsistencies

Reason and Prerequisites

This SAP Note covers options to check for technical SAP HANA inconsistencies.

This SAP Note doesn't cover pure application related inconsistencies or deviations between the application view and the actual state on SAP HANA side (like e.g. reported via report RSDU_TABLE_CONSISTENCY in BW).

See SAP Note 2116157 for general information about SAP HANA consistency checks and corruptions.

Solution

The following options exist to check for technical HANA consistency:

1. Metadata: CHECK_CATALOG procedure

The CHECK_CATALOG procedure can be used to check the consistency of the HANA catalog metadata. Its parameters are:

• CHECK_NAME: Type of action, e.g. 'CHECK' for performing all checks
• SCHEMA_NAME: Name of analyzed schema
• OBJECT_NAME: Name of analyzed object
• OBJECT_TYPE: Type of analyzed database object (e.g. 'TABLE', 'VIEW')

Be aware that you have to put a schema or table name with lower-case characters additionally in double quotes to make sure that the case is not implicitly changed to upper-case (e.g. '"abc"' instead of only 'abc').

A consistency check of the whole catalog can be done with the following command:

CALL CHECK_CATALOG('CHECK', NULL, NULL, NULL);

A consistency check of the metadata of table MARA in schema SAPSR3 is performed as follows:

CALL CHECK_CATALOG('CHECK', 'SAPSR3', 'MARA', 'TABLE');

The following individual checks and repairs are available:

Check name	Description
CHECK_OBJECT_REFERENTIAL_INTEGRITY	Consistency of references in catalog object
CHECK_VALUE_DOMAIN	Consistency check of value domains in catalog object (table type, field types, ...)
REBUILD_REMOTE_DEPENDENCY	Rebuilds all objects with remote dependencies (SAP HANA >= 2.00.035) Post copy / move task after CREATE DATABASE ... AS REPLICA, can also be helpful in other contexts (e.g. recreated remote tables, tenant restore) Objects that can't be rebuilt are marked as invalid so that they can be subsequently repaired with REPAIR_REMOTE_DEPENDENCY (see below)
REPAIR_REMOTE_DEPENDENCY	Repairs objects that are marked as invalid when running REBUILD_REMOTE_DEPENDENCY (SAP HANA >= 2.00.035) Post copy / move task after CREATE DATABASE ... AS REPLICA, can also be helpful in other contexts (e.g. recreated remote tables, tenant restore)

Be aware that this check only covers the metadata. The actual structure and data of tables and indexes is not covered.

See section "Catalog Consistency Check" in the SAP HANA Administration Guide for further details related to CHECK_CATALOG.

2. Row and column store: CHECK_TABLE_CONSISTENCY procedure

The CHECK_TABLE_CONSISTENCY procedure can be used to check the consistency of the structure and data of tables. Its parameters are:

• ACTION: Type of action, e.g. 'CHECK'
• SCHEMA_NAME: Name of analyzed schema (NULL for all schemas)
• TABLE_NAME: Name of analyzed object (NULL for all objects)

With the action 'CHECK' all available checks are executed. With 'REPAIR' all available repairs are performed.

Be aware that you have to put a schema or table name with lower-case characters additionally in double quotes to make sure that the case is not implicitly changed to upper-case (e.g. '"abc"' instead of only 'abc').

For a complete check you can execute the following command:

CALL CHECK_TABLE_CONSISTENCY('CHECK', NULL, NULL);

It is also possible to execute the following individual checks and repair actions on SP8 and higher:

Check name	Revision level	Description	Store
CHECK_COLUMN_TABLES	all available checks restricted to column store tables	Column
CHECK_COMBINED_KEY_COLUMN	consistency of key columns to combined key column	Column
CHECK_DATA_CONTAINER	consistency of row store page and data container	Row
CHECK_DATA_LENGTH	check for actual data length of variable length field	Row Column (>= 2.00.030)
CHECK_DELTA_DICTIONARY	>= 2.00.030	check the consistency of the delta dictionary	Column
CHECK_DELTA_LOG	>= 1.00.122.14 >= 2.00.010	check for delta log format	Column
CHECK_FOREIGN_KEY	>= 2.00.040	check foreign key constraints	Both
CHECK_FULLTEXT_INDEXES CHECK_FULLTEXT_INDEXES_FAST CHECK_FULLTEXT_INDEXES_FULL	>= 2.00.020	check fulltext indexes (SAP Note 2800008) with medium / low / high comprehensiveness Attention: Not part of general CHECK option, needs to be run individually	Column
CHECK_HYBRID_LOB_OVERHEAD	>= 2.00.024.09 >= 2.00.037.00 >= 2.00.040	Check if packed LOBs contain unnecessary overhead pages Attention: Not part of general CHECK option, needs to be run individually	Column
CHECK_INDEXES	consistency of indexes	Row
CHECK_LOB_DATA	>= 2.00.020	Check for full LOB data including disk LOBs (includes CHECK_LOBS, see below) Attention: Not part of standard CHECK action, needs to be run individually	Row (for column store check is already integrated in CHECK_LOBS)
CHECK_LOBS	>= 1.00.100	consistency of LOBs	Both
CHECK_MAIN_DICTIONARY	>= 1.00.110.06 >= 1.00.112.02	check for correct ordering of main dictionary	Column
CHECK_MAIN_DOCUMENT_COUNT	>= 1.00.122.03	comparison of real row count with row count stored in attribute statistics	Column
CHECK_MAIN_INVERTED_INDEX	>= 2.00.030	check consistency of inverted indexes	Column
CHECK_MAIN_MULTI_VALUE	>= 2.00.030	check consistency of multi-valued columns	Column
CHECK_MAIN_PAGED_COLUMNS_ATTRIBUTES	>= 2.00.030	check consistency of attribute paging against table (partition) specification Attention: Not part of standard CHECK action, needs to be run individually	Column
CHECK_MAIN_PAGED_DATA	>= 2.00.040	check consistency of column main paged data	Column
CHECK_MAIN_PAGED_INDEX	>= 2.00.040	check consistency of column main paged index	Column
CHECK_METADATA_SEPARATION	<= 2.0 SPS 01	check if metadata and data are stored separately, not necessarily an issue, but can impact row store reorganization Attention: With SAP HANA >= 2.0 SPS 02 this check is no longer part of the standard checks. If executed explicitly, it can return false positive warnings that can be ignored.	Row
CHECK_NOT_NULL_CONSTRAINT	check for NULL value in NOT NULL fields	Both
CHECK_PARTITIONING	consistency of partitioning related metadata	Column
CHECK_PARTITIONING_DATA	check assignment of rows to partitions	Column
CHECK_PERSISTENT_MEMORY	>= 2.00.030	check consistency of persistent memory (SAP Note 2700084)	Column
CHECK_PERSISTENT_MEMORY_CHECKSUM	>= 2.00.030	check consistency of checksum calculated on persistent memory blocks with create time checksum (missing block errors are ignored) (SAP Note 2700084)	Column
CHECK_PERSISTENT_MEMORY_CHECKSUM_STRICT	>= 2.00.030	check consistency of checksum calculated on persistent memory blocks with create time checksum (missing block errors are included) (SAP Note 2700084)	Column
CHECK_PRIMARY_KEY	consistency of the primary key	Column
CHECK_RECORD_COMMIT_TIMESTAMP	>= 2.00.030	check minimum consistency of record commit timestamp values	Row
CHECK_REPLICATION	>= 2.00.000	consistency of metadata of replicated tables	Column
CHECK_REPLICATION_DATA_FULL	>= 2.00.000	full check of consistency of replicated data	Column
CHECK_REPLICATION_DATA_LIGHTWEIGHT	>= 2.00.000	lightweight check of consistency of replicated data	Column
CHECK_ROWID	consistency of internal $rowid$ column	Column
CHECK_ROW_TABLES	all available checks restricted to row store tables	Row
CHECK_SAVEPOINT_VERSION_GLOBAL	>= 1.00.122.16 >= 2.00.012.05 >= 2.00.024	Check if there’re any data pages which are not correctly reflected in the last savepoint image. Attention: this provides a database-level check. you can’t specify SCHEMA_NAME and TABLE_NAME.	Row
CHECK_SINGLE_VALUE_CACHE	>= 1.00.120	check single value cache	Column
CHECK_TABLE_CONTAINER	>= 2.00.020	check table container	Column
CHECK_TABLE_CONTAINER_LEAKS	>= 2.00.040	check persistent descriptor leaks	Column
CHECK_TABLE_CONTAINER_NO_LOAD	>= 2.00.033 >= 2.00.024.05	check the consistency of loaded column metadata of currently loaded table partitions	Column
CHECK_TABLE_CONTAINER_PERSISTENCE	>= 2.00.030	check consistency of persisted column metadata of the table (partition)	Column
CHECK_UNIQUE_CONSTRAINTS	>= 2.00.020	checks unique constraints	Column
CHECK_VALUE_INDEXES	consistency of internal value indexes	Column
CHECK_VARIABLE_PART_BINDING	check for variable part of table mixed with other tables	Row
CHECK_VARIABLE_PART_DOUBLE_REFERENCE	check for table-wise double reference to variable part	Row
CHECK_VARIABLE_PART_DOUBLE_REFERENCE_GLOBAL	>= 1.00.100	check for table-wise and inter-table double reference to variable part	Row
CHECK_VARIABLE_PART_SANITY	check for sanity of logical pointers	Row
REPAIR_HYBRID_LOB_OVERHEAD	>= 2.00.024.09 >= 2.00.037.00 >= 2.00.040	repair unnecessary overhead pages of packed LOBs	Column
REPAIR_PARTITIONING_DATA	repair assignment of rows to partitions	Column
REPAIR_TABLE_CONTAINER_LEAKS	>= 2.00.030	repair table container leaks	Column

For an overview of available checks you can also run:

CALL GET_CHECK_ACTIONS('CHECK_TABLE_CONSISTENCY');

SAP Note 2116157 ("What are common errors reported by CHECK_TABLE_CONSISTENCY?") provides an overview of error messages that are reported by CHECK_TABLE_CONSISTENCY.

Up to SPS 07 tables are locked during CHECK_TABLE_CONSISTENCY runs. In case of lock timeouts due to concurrent changes a "table is busy" error is thrown. Starting with SPS 08 a lock is no longer required.

Rev. 71 and below can result in a significant number of false alerts, i.e. error messages that don't indicate a real issue.

Up to Rev. 80 CHECK_TABLE_CONSISTENCY can result in a crash if virtual tables are analyzed (SAP Note 2052419).

See section "Table Consistency Check" in the SAP HANA Administration Guide for further details related to CHECK_TABLE_CONSISTENCY.

In SAP ABAP environments (>= 7.40) you can schedule CHECK_TABLE_CONSISTENCY with transaction DB13 (Action = 'Consistency Check').

As of SAP HANA SPS 10 CHECK_TABLE_CONSISTENCY can be executed with the embedded statistics server (SAP Note 2147247). See SAP Note 2116157 for more information.

3. Persistence checks

Data backups (i.e. 'complete data backup') automatically check the persistence pages for correctness, e.g. proper checksums. If an inconsistency is recognized, the backup fails with an error. Only referenced pages are checked, corruptions in unused pages will not result in an error. This is okay, because corrupted unused pages are initialized when they are used the next time without looking at the recent corrupted content.

Backups based on storage snapshots (i.e. 'data snapshot') don't provide this consistency check.

Starting with SAP HANA 2.0 SPS 03 log backups are checked for consistency (SAP Note 2628775).

See SAP Note 2843934 for more details about manually checking the consistency of the persistence using the hdbpersdiag tool (SAP Note 2272121).

In rare cases (e.g. the scenario described in SAP Note 2370160) it can happen that the pages look fine on persistence level, but they are no longer consistent with the memory. In the worst case this can result in corruptions when data is loaded into memory (e.g. after a restart). Neither a backup nor a memory based consistency check like CHECK_TABLE_CONSISTENCY is able to detect these issues. Instead you can restore a backup to another system, start the database and run CHECK_TABLE_CONSISTENCY there.

4. Backups: hdbbackupcheck tool

The consistency of database backups can be checked using the hdbbackupcheck tool. See SAP Note 1869119 for more details.

5. Backups: hdbbackupdiag --check

The tool hdbbackupdiag can be used to check if the available backups can be used to restore the database in a consistent state (--check option). See SAP Note 1873247 for further information.

6. Existence of page dumps

The existence of page dumps can indicate page corruptions. See SAP Note 1977242 for more information.

7. SAP HANA binaries

The installed set of SAP HANA binaries can be compared with the default delivery using the tools provided via SAP Note 2279313.

8. SAP HANA installation

Starting with SAP HANA 1.0 SPS 12 the installation details (like file system, permissions and settings) can be checked with the SAP HANA lifecycle manager:

hdblcm --action=check_installation

See SAP Note 2365649 for more information.

9. Dynamic Tiering: CHECK_ES

Starting with SAP HANA 2.0 SPS 00 the consistency of extended storage in dynamic tiering contexts (SAP Note 2140959) can be checked using the CHECK_ES procedure. Its parameters are:

• ACTION: Type of action (e.g. CHECK, VERIFY, ALLOCATION, ALLOCATION_LEAKED_BLOCKS, ALLOCATION_DUPLICATE_BLOCKS, ALLOCATION_UNALLOCATED_BLOCKS, DROPLEAKS)
• TYPE: Object type (e.g. INDEX, DATABASE, TABLE, PARTITION, COLUMN, DBSPACE)
• NAME: Name of analyzed object (NULL for all objects)
• RESOURCE_PERCENTAGE: Number of threads per CPU in percent (default: 100 %, i.e. 1 thread per CPU)

Example: (check for all tables with 50 % threads)

CALL CHECK_ES('CHECK', 'TABLE', NULL, 50);

Be aware that you have to put a schema or table name with lower-case characters additionally in double quotes to make sure that the case is not implicitly changed to upper-case (e.g. '"abc"' instead of only 'abc').