Problem

Databases, even when configured and used properly, grow over time. Frequently, during high season, the database reaches the point when all the storage is consumed which is not needed for normal operation during low season and the database size have to be manually decreased. SecureTransport provides some functionalities towards reducing tables size. However since Oracle changed the default LOB type to SecureFile in version 12c some customers began to observe what is considered an unexpected database growth. This is due to the way Oracle manages LOBs and stores their undo data.

Resolution

There are three main ST consumers of space:

• File Tracking Data
• Server Log Data
• non-partitioned tables for which there are no Maintenance applications provided by SecureTransport and are not supposed to grow much.

For the first two Axway SecureTransport has the option to store them in separate schemas/databases and also has Maintenance applications which if configured can remove old partitions to automatically free the space occupied by historic table data.

For the non-partitioned tables a manual approach may be needed.

For Oracle versions before 12c

For old ST installation where LOBs are of BASICFILE type it may be enough to make a script with the output of the following queries and execute it:

select 'ALTER TABLE '||TABLE_NAME||' ENABLE ROW MOVEMENT;' from user_tables where tablespace_name='ST_DATA';

select 'ALTER TABLE '||TABLE_NAME||' SHRINK SPACE;' from user_tables where tablespace_name='ST_DATA'

select 'ALTER TABLE '||TABLE_NAME||' DISABLE ROW MOVEMENT;' from user_tables where tablespace_name='ST_DATA';

SecureTransport must not be using the database when the script is executed (i.e. the application must be stopped on all cluster nodes).

For Oracle 12c or later

For newer installation (where ST was installed using Oracle 12c or later) there is a specific shrink issue with the way Oracle stores SecureFiles.

Secure File LOBs (LOB columns of non-partitioned tables in our scenario) may grow quite a lot when the undo_retention is high (and the database parameter db_securefile is set to PREFERRED or ALWAYS, for example when the database is 12c+). The reason is that the undo data of LOBs is stored in the same tablespace. If a row is updated several times within the undo_retention interval the same amount of old row images will be present in order to support potential undo transactions. SecureFile LOBs however cannot be shrunk using a normal ALTER TABLE .. SHRINK operation. This is by design - Oracle made them to grow only, not to shrink since the encryption type does not permit it.

Oracle database does not support shrinking SecureFile LOBs so generally the Oracle community does not recommend the usage of high values for the undo_retention database parameter in combination with SecureFiles to avoid space issues.

To shrink the database in this scenario, you can use the SQL script attached to this article (script_to_shrink_segments.sql).

SecureTransport must not be using the database when the script is executed (i.e. the application must be stopped on all cluster nodes).

After the tables are shrunk it will be possible to decrease the size of the data files. This can be done by an experienced DBA and the ST application can be online at the time.