False Oracle tablespace alarm or no alarm is fired when an Oracle tablespace ran out of room (4230758)

A false alarm or no alarm is triggered when an Oracle tablespace runs out of space.

The rule "DBO - Tablespace Used Percentage" may not fire.

Investigating Oracle tablespace alarm issues.

CAUSE 1:

For tablespaces with autoextensible tablespaces, OS monitoring is required for the DBO Tablespace Usage to be accurately calculated.

If Foglight cannot collect the OS data for an autoextendable tablespace, the alert will not function because it does not have enough information to calculate to result.

1. If AUTOEXTEND is ON then the alarm knows to take the minimum between the MAXSIZE and the disk size. For example, you may have a Tera-bytes of available disk space, but if the MAXSIZE is set to 32GB and the data file size is 10GB, it can only extend in 22GB more.
   Without having OS monitoring we don't take into account the other properties (AUTOEXTENSIBLE, MAX SIZE, DISK SIZE) and then the alarm will be triggered based on "%Space Usage" column
2. We take into account the MAXSIZE and Disk SIZE only for data files with AUTOEXTEND set to on. For example, if tablespace 'users' contains 2 data files: one with AUTOEXTEND set to ON and the other set to OFF, we will take into account the MAXSIZE property and the available disk space only to the data file with the AUTOEXTEND set to ON.
3. Starting from Cartridge for Oracle version 5.6.11, OS collection is no longer required for the alarm but, however, OS collection is still recommended, without having OS collection the alarm will be less "sophisticated" and it won't take into account the data files/disks properties.
4. OS level Logical Disk collections are required from both an IC Agent (i.e. UnixAgent, UnixAgentPlus, WindowsAgent) and Oracle agent OS collections (osEnableOsCollections) for the rest of the available disk space to be taken into account. If either fails to collect, the rule doesn't have enough information to determine the free space available.

CAUSE 2:

The parameter "Is ASM RAC" has been changed from "True" to "False" via "Administration | Agents | Agent Status". This causes duplicate topology objects and the needed table space metric will not be submitted to the correct "new" topology object. The rule is still reading from the old topology object which is empty now.

TO TROUBLESHOOT ORACLE TABLESPACE ALARM ISSUES

Check the collection parameters from the Error in the Agent log to confirm that the collection ran

Check that the ASM agent and OS collection are both running (when the datafile is autoextensible on the disk or ASM)

Check the datafile number in the database using SELECT COUNT(*) FROM DBA_DATA_FILES;

Check the max row limit in the collection (this can cause some datafile information to not be retrieved from the database)

Set the FglAM or database agent to enable debug log writing to check the results for the "DBO - Tablespace free space ASM" with the result from SELECT *  FROM DBA_DATA_FILES; to ensure that all datafiles are included in the calculation.

RESOLUTION 1:

Turn off the autoextend capability on the Oracle tablespace or configure and activate OS monitoring (IC agent and Oracle agent) for the Oracle instance.

RESOLUTION 2:

Drop the agent and recreate it with the correct settings.

1. Go to the "Databases Global Overview" dashboard and select the "ASM" tab
2. Find the problematic ASM agent and select it
3. Click on "Agent Settings" and then on "Remove from monitoring"
4. Now go to "Management Server | Servers | Data Management"
5. Find the problematic agent under "ASM_Data_Model | Cluster"
6. Click in "Delete" in the "Actions" column
7. Finally recreate the agent using the Oracle Installer wizard

Please Note

The DBO cartridge is querying ASM views that resides on monitored instance to retrieve basic information about ASM I/O activity, however the data is not always reported correctly due to multiple Oracle ASM related bugs.

The following are the Oracle ASM known bugs:

• Bug 9943557: V$ASM_DISK SHOWS ZERO FOR TOTAL_MB AND FREE_MB
• Bug 5856871: INCONSISTENCY BETWEEN V$ASM_DISK AND V$ASM_DISKGROUP
• Bug 7110564: V$ASM_DISK ON RDBMS INSTANCE LISTS ALL THE DISKS
• Bug 7274813: V$ASM_DISK.FREE_MB IS ZERO FROM RDBMS INSTANCE
• Bug 9213652: V$ASM_DISKGROUP FROM RDBMS INSTANCE NOT UPDATED USED_MB

RESOLUTION 3:

By default the normal sensitivity template has the warning condition of the tablespace rule disabled. To enable it follow the steps below:

1. Open the database overview dashboard and select the needed agent
2. Click on "Configure Alarms" which will open the agent administration dashboard.
3. On the right hand side, click on the tab "Sensitivity Level"
4. Click on the link "Define sensitivity levels" and select eh "Normal" tab
5. Find the rule "DBO - Tablespace Used Percentage" and click on "Set level"
6. Now enable (or disable) the conditions which should be triggered

Note:

If a condition is not configured in the rule itself, you can enable it in the sensitivity level template, but it has not affect and no alarm will be triggered.

Find more details about sensitivity levels in this blog post.

RESOLUTION 4

Please refer to KB 4281739 for additional resolutions for this issue.

RESOLUTION 5

The calculation is correct but due to the size of the DB the thresholds needs to be adjusted because it is based on percentage and not on numbers. When having 4 TB free space, 10% remaining free space is a high number, but as the rule is based on percentage an alarm will be triggered even there is still a lot of space available.