Tag: Corrective Action ( CA )

In Oracle Enterprise Manager 13c ( 13.2.0.0.0),  auto extending tablespace corrective action is not working as expected, specially when calculating space usage.

*** BEGIN Parameters ***
Increase Bigfile Size (%): 5
Maximum Disk Usage (%): 95
How to Increase Space: Increase by %
...
..
.
ASM disk group is: DATA
Last created datafile: +DATA/RACTEST/3F9C860784456287E053530F040ADB20
                                  /DATAFILE/test.372.1018794017
Tablespace total size (MB): 899964.00
Largest numeric suffix: 0
Datafile filename: test.372
Datafile directory: +DATA/RACTEST/3F9C860784456287E053530F040ADB20/
                                                          DATAFILE/
Datafile suffix: .1018794017
ASM space usage for disk group DATA is free(MB): 5817030.000, 
                           total(MB): 28311415.000, required 
                     for mirroring(MB): 0.000, redundancy: 1
ASM projected safely usable free space (MB) is: 1317210.000
ASM projected Space Used (%) is: 95.35
Not enough disk space left in disk group DATA to extend datafile, 
                                                     95.35% > 95%
Disconnected from database

From the CA job trace file, we can see:

Increase Bigfile Size (%): 5
Maximum Disk Usage (%): 95
How to Increase Space: Increase by %
ASM space usage for disk group DATA is free(MB):  5817030.000,
                                      total(MB): 28311415.000
Current big tablespace size : 899964.00
ASM projected safely usable free space (MB) is: 1317210.000
ASM projected Space Used (%) is: 95.35
Not enough disk space left in disk group DATA to extend datafile, 
95.35% > 95%
Disconnected from database

ASM projected safely usable free space (MB) should be :

Current disk group free space - Current Tablespace Size * Increase % 
= 5817030.000 - 899964*5/100 = 5772031.8 MB

While the Corrective Action trace file shows below ASM projected safely usable free space (MB):

ASM projected safely usable free space (MB) is: 1317210.000

Let’s check the source code from Oracle Enterprise Agent, we can see the code missed Percentage ( %) for tablespace increase rate.

$cd /u01/app/oracle/product/agent/agent_13.2.0.0.0/plugins/
       oracle.sysman.db.agent.plugin_13.2.1.0.0/scripts/db

$ls -l dbAddSpaceTS.pl

...
..
.
 # Case 1: Increase existing datafile by %
    if ($bIncrByPct)
    {
      $diskFreeMB = $dirAvailMB - ($tsSizeMB * $incrPct);
    }
...
..
.

# Case 1: Increase by %
  if ($bIncrByPct)
  {
    $safelyUsableMB = ($dirAvailMB - ($tsSizeMB * $incrPct) 
                                   - $dgReqMirror) / $dgRedundancy;
  }
...
..
.

so the right code should be :

 # Case 1: Increase existing datafile by %
if ($bIncrByPct)
{
$diskFreeMB = $dirAvailMB - ($tsSizeMB * $incrPct)/100;
}
...
..
.

# Case 1: Increase by %
if ($bIncrByPct)
{
$safelyUsableMB = ($dirAvailMB - ($tsSizeMB * $incrPct)/100 
- $dgReqMirror) / $dgRedundancy;
}
...
..
.

After modifying the code, everything works fine for Corrective Action job.