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.