UCMDB Support Tip: Authorization of CIs fails due to history execution timeout
If Server version: 10.01.CUP3 build: 235 is being used, and the following error is encountered: Authorization fails for some Nodes with UI error "Authorization failed due to unknown reason"
Having determined that such problem is due to an execution timeout against the history tables. In addition if a query is ran directly on the database it takes 0,01. Though, running it from the uCMDB jmx-console is much faster.
Why the execution times out, even though the query runs very fast directly onto the database and from uCMDB jmx-console?
If such occurrence is met, it can be concluded that the database configuration doesn't comply with HP UCMDB Database Guide (please refer to Chapter 10 * "Oracle Server Configuration and Sizing Guidelines"):
1. db_file_multiblock_read_count having 8 should have 128
2. cursor_sharing having FORCE should have EXACT
3. pga_aggregate_target having 25M should have 400M or more
4. log_checkpoint_interval 100000 (instead of 0)
5. log_checkpoint_timeout 900 (instead of 1800)
6. optimizer_index_caching 50 (instead of 0)
7. undo_retention 0 (instead of 900)
Because the Oracle database isn't using automatic memory management (AMM), the db_cache_size should be increased manually because more than 40% of DB time is physical reads.
Unfortunately, Buffer Cache Advisor is OFF (despite HP UCMDB Database Guide also), there is no option to check how the parameter should be increased:
8. db_cache_size having 608M should be increased to 1536M
9. There are multiple parameters including hidden ones having non-default values that was not tested by UCMDB:
- db_block_checking TRUE (instead of FALSE)
- db_block_checksum TRUE (instead of TYPICAL)
- query_rewrite_integrity stale_tolerated (instead of enforced)
- audit_sys_operations TRUE (instead of FALSE)
- audit_trail DB_EXTENDED (instead of DB or NONE from DB Guide)
- 4 hidden parameters
These parameters could influence onto the database behavior.
One of the main issue of queries slowness including the failed one is statistics, for the temporary tables. The statistics should be dropped immediately.
If you find this or any post resolves your issue, please be sure to mark it as an accepted solution."
Click the KUDOS star on the left to say 'Thanks'