Reclaiming Unused Space in an Oracle Database
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:
However, the storage array sees over 200 GB in use.
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.
- Check the ASM Filter Driver Certification Matrix for supported Linux kernel versions.
- If ASMLib is installed and configured for an existing Oracle ASM installation, then it must be explicitly uninstalled before installing and configuring Oracle ASMFD.
- 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.
- Create an ASM Disk Group using the ASM Filter Driver. Set the thin_provisioning attribute to TRUE.
- Create a tablespace in this diskgroup and populate it with some random data.
- Check the space allocated at the database and array level.
- Drop the tablespace and run ASM rebalance.
- 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.