Exadata Sparse Clones and monitor Sparse Disk Group Size

June 29, 2022 | 11 minute read
Maruti Sharma
Senior Principal Product Manager
Text Size 100%:

 Exadata Sparse Clones and Sparse Disk Groups

Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM is Oracle’s recommended storage management solution that provides an alternative to conventional volume manager, file systems and raw devices. ASM Disk Groups are fundamental storage objects (logical volumes) that Oracle ASM (Automatic Storage Management) manages. A disk group comprises of multiple underlying disks, files, and allocation units. Any Oracle ASM file is completely contained within a single ASM disk group. However, a disk group may contain files belonging to several databases. Oracle ASM was introduced with version Oracle 10g onwards. Exadata Sparse clones (thin provisioning) was introduced starting Oracle Database and ASM version BP5 and higher. Exadata Sparse clones do require Exadata System Software and higher as well. Exadata Sparse Clones utilizes specialized disk group called Sparse disk group.

Default Disk Groups: By default, Oracle recommends creating two disk groups, DATA and RECO. DATA disk group is for each database to store datafiles and RECO disk group for redo and fast recovery area for all databases. Other specialized disk groups can be created in addition to these two disk groups. We will talk about one such disk group “Sparse” and its sizing.

Physical Database Clone: All blocks from source database are copied over to target resulting in zero storage efficiency.

Block to Block Copy

Sparse Clone: In the following diagram when we create a Sparse Clone, all data files point to the data files in test master (no copy of data files). The blocks that are changed gets written to the Sparse Clone.

Changed Blocks

Disk Group for Sparse files: When a physical clone of a database is done, the copied database is essentially the same size as the source database providing no extra storage benefits. Exadata Sparse Clones are ideal for creating space-efficient read-only or read-write clones of an Oracle database. A specialized disk group for storing sparse files is created using specially created grid disks. These disks are created with a virtual size attribute. The Sparse Clones are used in development, testing and other non-production purposes. Unlike ACFS Snapshots that uses filesystem snapshots, Exadata Sparse Clones are sparse files that point back to read-only datafiles as shown in the above diagram. Another difference from Exadata ACFS Snapshots is that Exadata Sparse Clones support all Exadata native performance features including smart flash, smart scan, hybrid columnar compression.

Exadata Sparse Clones are created on a specially created disk group with sparse attributes. Sparse disk group uses ASM Sparse grid disk based on thin provisioning, and it supports CDB, non-CDB and PDB snapshot copy. Sparse disk group is created with a similar command as a regular DATA or RECO disk group. The difference is in how an underlying sparse grid disk is created. Sparse grid disk is created with a virtual size as well as a physical size parameter. Exadata Sparse Clones utilize Oracle ASM sparse disk groups. Sparse data files can be created only in Oracle ASM sparse disk groups, although sparse disk group can house physical database files as well.

Creating a Sparse Grid Disk and Sparse Disk Group: Oracle recommends the use of OEDACLI to modify existing disk groups and to create new disk groups (and new grid disks). OEDACLI provides all the requisite steps (validating free space, creating new grid disk etc).

To create Sparse Clones, you must have sparse grid disks created with an Oracle ASM disk group (SPARSE in our example) created based on those disks. It is not possible to directly modify an existing disk group and convert it into a sparse disk group; grid disks must be created with the sparse attributes before you can create a Sparse disk group. You need to keep in mind that Sparse disk group can house both sparse and non-sparse database files. When calculating the size of Sparse disk group, you need to determine the physical space required for the Sparse clones.

The total physical space can be calculated based on these attributes. Add 15% space as a cushion for disk balancing operations.

  1. Size of all test masters in the sparse ASM disk group.
  2. Approximate size of the changes/updates to the sparse databases.
  3. ASM redundancy (Normal or High) If it is Normal (mirror) consider this value to be 2 and if it is HIGH (triple mirror) consider this value to be 3

Total physical space = (SUM(1) + SUM(2)) * (3)

For example, if there are 3 test masters (250 GB each) in the Sparse ASM disk group created with a normal redundancy, and each test master have 10 Exadata Sparse clones and approximate changes in blocks is 20% then total physical space needed for the grid disks will be:

Space for 3 test masters = 3 * 250 GB                                       =    750 GB
Space for 30 sparse for each test master = 30 * 250 * 20% =   1500 GB
Subtotal                                                                                            =   2250 GB
Normal redundancy = 2 * 2250 GB                                             =   4500 GB

Check amount of free space in cell disks. The following command will provide you the size of each disk and free space available for each free disk.

oedacli > dcli -g cell_group -l root "cellcli -e list celldisk attributes name, size, freespace where disktype = 'HardDisk'"

# dcli -g cell_group -l root "cellcli -e list celldisk attributes name, size, freespace where disktype = 'HardDisk'"
Name                                           size                     freespace
exademo01celadm01: CD_00_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_01_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_02_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_03_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_04_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_05_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_06_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_07_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_08_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_09_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_10_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm01: CD_11_exademo01celadm01     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_00_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_01_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_02_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_03_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_04_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_05_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_06_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_07_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_08_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_09_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_10_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm02: CD_11_exademo01celadm02     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_00_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_01_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_02_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_03_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_04_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_05_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_06_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_07_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_08_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_09_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_10_exademo01celadm03     12.4737091064453125T     4.157257080078125T
exademo01celadm03: CD_11_exademo01celadm03     12.4737091064453125T     4.157257080078125T

In the above output, you can see High-Capacity storage server with 36 disks, with 14 TB drives. Each of the drive has about 4.15 TB free space and we will use this available space to create a new disk group (and new grid disks).

oedacli> add diskgroup DISKGROUPNAME=SPARSE DISKGROUPSIZE=1T ocrvote=false REDUNDANCY=NORMAL SPARSE=true TYPE=OTHER where clustername=’Cluster-c1’;

SPARSE=true attribute signifies the diskgroup is a Sparse disk group and the underlying grid disks for this diskgroup have virtualsize attribute specified. How to Calculate Sparse Grid Disk Physical and Virtual Size – Refer to this document at Oracle Support. - Doc ID 2473412.1

Monitor Sparse space Usage: Normally when we want to query the size of a disk group, we run SQL against V$ASM and V$ASM_DISKGROUP views. These views contain information about the physical size and utilization of ASM disk group.  These views do not have the information about the Sparse Grid Disks and do not report the actual/virtual size for a Sparse disk group. To expose the Sparse capacity and consumption of grid disks and disk groups, two new views are introduced – V$ASM_DISK_SPARSE and V$ASM_DISKGROUP_SPARSE. These two new views contain information about the actual size and utilization of the sparse ASM disk group.

Details of V$ASM_DISK_SPARSE view:

Column Description


The number of the disk group containing the disk.


The number assigned to the disk within this disk group.


The incarnation number for the disk.


The total used physical and materialized capacity on the disk.


The total physical capacity on the disk.


The total number of I/O read requests on non-materialized regions of the disk.


The total number of bytes read from non-materialized regions of the disk.


The time taken by sparse read I/O operations.




Column Description


The cluster-wide number assigned to the disk group.


The total used physical and materialized capacity of the disk group.


The total physical capacity of the disk group.

The following query against these views can provide you the used (allocated) space and total space for disks in the specific disk group.

SQL> select
     DISK_NUMBER          dsk_num,
     ALLOCATED_MAT_MB     alloc,
     TOTAL_MAT_MB         total
where GROUP_NUMBER = 5;


-----------  ----------  ----------
         0   5536        57336 
         1   5424        57336
         2   5532        57336                    This reports the virtual size of each grid disk.
         3   5424        57336
         4   5424        57336

V$ASM_DISK_SPARSE view will show you the actual size of the sparse disk and subsequently of the sparse ASM disk group.  You can easily join the two views (V$ASM_DISK and V$ASM_DISKGROUP_SPARSE) to get the appropriate physical size and virtual size of the grid disks in the disk group.

Example query:

select ad.group_number, ad.disk_number, ads.ALLOCATED_MAT_MB as allocated_physical_sparse_mb, ads.TOTAL_MAT_MB as physical_sparse_size_mb, ad.OS_MB as asm_os_reported_size_mb, ad.total_mb as asm_reported_size_virt_mb 
from v$asm_disk ad, v$asm_disk_sparse ads 
where ad.group_number=ads.group_number and ad.disk_number=ads.disk_number;

Resources and Further Reading
Create new ASM Diskgroup
Sizing steps for Sparse Diskgroup
Exadata Sparse Standby
Exadata PDB Sparse Clones


Managing and sizing disk groups is fundamental to managing an Oracle database. By utilizing the specific V$ASM_DISK_SPARSE and V$ASM_DISKGROUP_SPARSE views in addition to the regular ASM views, it is easier to monitor and manage space utilization of Exadata Sparse grid disks and disk groups. Always keep in mind that Sparse disk group can house both physical and sparse files. When calculating the size of the Sparse disk group factor in the physical test masters’ space in addition to the changes/updates made to each sparse copy.

Maruti Sharma

Senior Principal Product Manager

Maruti Sharma is a Senior Principal product manager for mission critical database systems at Oracle with over 25 years of software experience focused on relational databases, Big Data, NoSQL data stores, server programming, microservices. Prior to joining Oracle, Maruti was a Chief Architect and Associate Technical Fellow at The Boeing Company where he was responsible for managing everything data related.

Previous Post

Exadata System Software Updates - June 2022

Gavin Parish | 2 min read

Next Post

Exadata System Software Updates - July 2022

Alex Blyth | 2 min read