Skip to main content
Pure Technical Services

Reclaiming Unused Space in an Oracle Database

Currently viewing public documentation. Please login to access the full scope of documentation.

KP_Ext_Announcement.png

One of the core features of Pure Storage’s Flash Array is something called Thin provisioning. Thin provisioning is a method of dynamically allocating storage capacity on-demand across multiple applications in a flexible and efficient manner, thus reducing storage utilization rates. What that means is the space consumed on the Flash Array will be a function of the actual size of the data contained in the database, and not the size of the database files.

The Problem

Over a period of time and as part of a database's normal course of operations, data files may be dropped or resized, tables truncated, archived redo logs deleted and so forth. Ideally, one would expect space previously consumed by the dropped objects to be returned back to storage. However, this is not generally the case, and the ability to reclaim back the freed space depends on how the storage has been configured. So it is possible that even though there may be a large amount of free space in the database, it is not returned to the storage layer and therefore unavailable to storage for reallocation.

Here is an example that illustrates the problem:

Oracle considers ASM disk group FRA to be using 99 GB of storage, with 401 GB free:

orcaleasm1.png

However, the storage array sees over 200 GB in use.

oracleasm2.png

 

The Solution

An Oracle database can be created on an operating system file system, or on an Oracle proprietary database file system and volume manager known as Automatic Storage Management (ASM). Both these storage options can suffer from this problem and the solution for the different scenarios is presented below. 

 

Oracle Database on File System

EXT4 and XFS are the most popular file systems for creating Oracle databases. Both of them provide a mount option called discard. A file system on thin-provisioned volume, when mounted with the discard flag will recognize when space becomes unused after a file is deleted, and issue trim commands to UNMAP the unused space.  

/etc/fstab entry for ext4 file system

/dev/mapper/oracle-oraprod-data     /u01     ext4    discard,noatime 0 0

/etc/fstab entry for xfs file system

/dev/mapper/oracle-oraprod-data    /u01     xfs     discard,noatime 0 0

 

Oracle Database on ASM

Oracle Automatic Storage Management (ASM) is essentially an Oracle-managed file system which manages all database data files, redo logs, control files, backup sets – essentially everything except for the Oracle software and configuration files. It is commonly used in RAC environments, but it is also relevant (and Oracle's recommendation) for single-instance databases. ASM files are not visible at the o/s level although the underlying devices are.  Oracle provides a CLI (asmcmd) that provides limited visibility and functionality to the ASM files.

ASM needs device names and permissions to persist across Linux reboots. Historically, it was done either by setting up UDEV rules or by using an Oracle supplied library and kernel module called ASMLib. When using one of these methods for device persistence, we do not have a good solution for our problem and some of the workarounds are described in the next section.

However, in Oracle 12.1, Oracle released a new kernel module called ASM Filter Driver (ASMFD) that sits in the I/O path of ASM disks. ASMFD provides additional functionality over what was provided by ASMLib. The one that interests us in the context of our problem is a new attribute that was introduced in 12.2 called THIN_PROVISIONED.

 

Method 1 - ASMFD THIN_PROVISIONED Attribute

The THIN_PROVISIONED attribute enables or disables the functionality to discard unused storage space after a disk group rebalance is completed. The attribute value can be set to true to enable, or set to false to disable the functionality. The default value is false.

SQL > sqlplus / as sysasm
SQL > alter diskgroup DATA set attribute 'thin_provisioned'='TRUE';

When the COMPACT phase of a rebalance operation has completed, Oracle ASM informs the Flash Array which space is no longer used and can be repurposed.

ASM Disk Group rebalance can be triggered using the following command.

SQL > alter diskgroup DATA rebalance with balance compact wait;

 

Things to be aware of before implementing ASM Filter Driver. 

  1. Check the ASM Filter Driver Certification Matrix for supported Linux kernel versions.
  2. If ASMLib is installed and configured for an existing Oracle ASM installation, then it must be explicitly uninstalled before installing and configuring Oracle ASMFD.
  3. Make sure you are on a database version that includes a fix to Bug 28378446 - Using AFD and Attribute "thin_provisioned=true" Can Cause Database Corruption.

 

ASMFD Thin_Provisioned Demo

The entire process is demonstrated below by way of an example. Here are the high-level steps that will be performed. 

  1. Create an ASM Disk Group using the ASM Filter Driver. Set the thin_provisioning attribute to TRUE.
  2. Create a tablespace in this diskgroup and populate it with some random data. 
  3. Check the space allocated at the database and array level.
  4. Drop the tablespace and run ASM rebalance.
  5. Check the space again. It should get returned back to storage. 

 

We start with a new database that has a 2T Disk Group ORA01PRD_DATA.

SQL > SELECT g.group_number,g.name ,g.state ,g.type ,g.total_mb/1024 ,g.free_mb/1024
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number 
and g.name ='ORA01PRD_DATA';

     Group Group Name                     State       Type     Total GB    Free GB
---------- ------------------------------ ----------- ------ ---------- ----------
         1 ORA01PRD_DATA                  CONNECTED   EXTERN       2048       1992
pureuser@sn1-x70-f05-27> purevol list --space oracle-rt-ora01prd-data01
Name                       Size  Thin Provisioning  Data Reduction  Total Reduction  Volume  
oracle-rt-ora01prd-data01  2T    97%                2.9 to 1        >100 to 1        18.90G  

 

Set the thin_provisioned attribute to TRUE.

SQL > alter diskgroup ORA01PRD_DATA set attribute 'thin_provisioned'='TRUE';
SQL > SELECT dg.name, a.name, a.value, read_only
FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a
WHERE dg.group_number = a.group_number
  AND dg.name ='ORA01PRD_DATA'
  AND a.name = 'thin_provisioned';
  
DISKGROUP                      ATTRIBUTE                      VALUE 
------------------------------ ------------------------------ -------
ORA01PRD_DAT                   thin_provisioned               TRUE  

 

Create a 1T tablespace called THIN_PROV_TS.

SQL>  create bigfile tablespace THIN_PROV_TS datafile '+ORA01PRD_DATA' size 1024g ;

Check the space consumed by this tablespace on the database as well as on the Flash Array.

We can see that on the database the entire 1T space is allocated, but on the array no additional space is allocated since the tablespace is empty.

     Group Group Name                     State       Type     Total GB    Free GB
---------- ------------------------------ ----------- ------ ---------- ----------
         1 ORA01PRD_DATA                  CONNECTED   EXTERN       2048        969
pureuser@sn1-x70-f05-27> purevol list --space oracle-rt-ora01prd-data01
Name                       Size  Thin Provisioning  Data Reduction  Total Reduction  Volume  
oracle-rt-ora01prd-data01  2T    97%                2.9 to 1        >100 to 1        18.91G  

Create a user and assign THIN_PROV_TS as the default tablespace for this user.

Create a bunch of tables and populate it with random data. In this example, we have built a TPCC schema with 5000 warehouses. 

 

Check the space again on the Flash Array. We can see that the space consumed on the Flash Array has increased from 18.91G to 304.68G.

 

pureuser@sn1-x70-f05-27> purevol list --space oracle-rt-ora01prd-data01
Name                       Size  Thin Provisioning  Data Reduction  Total Reduction  Volume 
oracle-rt-ora01prd-data01  2T    47%                3.3 to 1        6.3 to 1         304.68G

 

Now, drop the THIN_PROV_TS tablespace.

 

SQL> drop tablespace THIN_PROV_TS including contents;

Tablespace dropped.

 

Space is released from the database, but not (yet) from the Flash Array.

     Group Group Name                     State       Type     Total GB    Free GB
---------- ------------------------------ ----------- ------ ---------- ----------
         1 ORA01PRD_DATA                  CONNECTED   EXTERN       2048       1992
pureuser@sn1-x70-f05-27> purevol list --space oracle-rt-ora01prd-data01
Name                       Size  Thin Provisioning  Data Reduction  Total Reduction  Volume 
oracle-rt-ora01prd-data01  2T    47%                3.3 to 1        6.3 to 1         304.07G

 

Log into the ASM instance and run rebalance on the Disk Group.

[grid@orademo1 ~]$ sqlplus / as sysasm
 
SQL> alter diskgroup ora01prd_data rebalance with balance compact wait; 

Diskgroup altered.

 

Within a few minutes, we should be able to see that space previously consumed by tablespace THIN_PROV_TS is now reclaimed by the Flash Array. 

pureuser@sn1-x70-f05-27> purevol list --space oracle-rt-ora01prd-data01
Name                       Size  Thin Provisioning  Data Reduction  Total Reduction  Volume  
oracle-rt-ora01prd-data01  2T    97%                2.9 to 1        >100 to 1        18.91G  

 

 

Method 2 - ASM Disk Group Reorganization

As discussed above, using the ASM Filter Driver with the THIN PROVISIONED attribute set to true can reclaim space when the tablespace is dropped. However, ASM Filter Driver is relatively new and there are many Oracle database installations that either have not or, for various reasons can not make the switch to using ASM Filter Driver. Moreover, there could be cases where the database has a large amount of free space due to dropped or truncated objects but it is not practical to drop the tablespace. For such scenarios, the method described below can be used.

 

Add a new disk and drop the old one

Identify the disks that have a large amount of free space that can be reclaimed, and the disk group they are part of.

Execute the following command to replace the existing bloated disk in the disk group with a new disk. This SQL also initiates a ASM rebalance of the blocks from the old disk to the new disk. As the wait clause is used, control will be returned only after the operation is complete.

SQL > alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA2' name DATA2
      drop disk DATA1
      rebalance power 16 wait;

So at the end of this command, the old disk will no longer be part of the disk group and can be safely deleted on the Flash Array. 

Note: Oracle recommends that all the disks in a disk group be of the same size. Starting with version 12.2, it enforces this condition for disk groups created with Normal or High redundancy.