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.2Enterprise 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: |