My Oracle Support Banner

Get Well Plan for customers experiencing unexpected database growth due to unexpected volume of attribute collection (Doc ID 1560464.1)

Last updated on JULY 20, 2023

Applies to:

Oracle Exalogic Elastic Cloud Software - Version 2.0.6.2.2 to 2.0.6.2.2
Enterprise Manager Ops Center - Version 12.1 and later
Information in this document applies to any platform.

Symptoms

WARNING!!!

The instructions in this document should only be use by experienced Database administrators.

In case you don't feel comfortable doing this please open a Service Request and an experienced support engineer will help you on this topic.


 

Here is a concise list of steps that can be taken to eliminate the problem of rollups that take too long on systems with limited space and with plenty of space.

The instructions in this document only apply to OpsCenter 12c.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
 WARNING!!!
 The instructions in this document should only be use by experienced Database administrators.
Changes
Cause
Solution
 TRIAGE
 Confirm that database space consumption is due to failure of Reporting Data Rollup completion
 Explanation of DBSpaceCheck output
 Corrective Action
 If there is no space to dump data or allocate a data file and rollups have fallen significantly behind
 1. Shutdown the EC and start database and listener outside of EC services
 2. Access sqplus and truncate historical data from RM_RESOURCE_DAILY_TREND
 3. Further reduce volume of incoming data by limiting number of attributes collected for certain asset types by one of the following:
 4. Turn of parallelization of database optimizer statistics collection for the Ops Center Schema.
 5. Turn off sql tuning advisor and space advisor
 
 5.5. Get the latest version of the Rollup program which contains an optimizer hint to use a more efficient index:
 6. Rebuild indexes on RM_RESOURCE_1HOUR_TREND to eliminate Fragmentation
 9. Shutdown the database and restart EC services
 Corrective action if there is plenty of free space on the filesystem to retain historical data
 1. Shutdown the EC and start the database outside of EC services:
 
 1.5. Reduce the inflow of data applying IDR for Live Mode Bug:
 2. Further reduce volume of incoming data by limiting number of attributes collected for certain asset types by one of the following:
 3. Turn of parallelization of database optimizer statistics collection for the Ops Center Schema.
 4. Turn off sql tuning advisor and space advisor
 4.5. Get the latest version of the Rollup program which contains an optimizer hint to use a more efficient index:
 5. Rebuild indexes on RM_RESOURCE_1HOUR_TREND to eliminate Fragmentation
 6. Export the data you want to keep from RM_RESOURCE_DAILY_TREND
 7. Truncate RM_RESOURCE_DAILY_TREND
 8. Disable indexes and constraints on the RM_RESOURCE_DAILY_TREND table.
 9. Import the data exported from Step 8 above, using the datapump import utility
 10. Rebuild indexes, re-enable constraints on the smaller tables
 11. Rebuild indexes on RM_RESOURCE_1HOUR_TREND to eliminate Fragmentation
 12. Shutdown the database and restart EC services
 [OPTIONAL] To compress the RM_RESOURCE_1HOUR_TREND table and indexes if table itself contains a lot of freespace after cleanup
 How To Execute an AWR report
 How To Change Location of OC_DUMP_DIR
 List of monitoring Scripts:

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.