Oracle Database Files and EMC Virtual Provisioning Strategies

Oracle database file initialization

Using EMC Virtual Provisioning in conjunction with Oracle database will provide the benefits such as reducing future server impact during LUN provisioning, increasing storage utilization, native striping in the thin pool, and ease and speed of creating and working with thin devices etc.  However, when Oracle initializes new files such as log, data and temp files, it writes metadata to each initialized block, and this will cause the thin pool to allocate the amount of space that is being initialized by the database.  As database files are added, more space will be allocated in the pool.  Due to Oracle file initialization, and in order to get the most benefit from a Virtual Provisioning infrastructure, a strategy for sizing files, pools and devices should be developed in accordance to application and storage management needs.

Oversubscription

An oversubscription strategy is based on using thin devices with a total capacity greater than the physical storage in the pools they are bound to.  This allows for optimizing storage capacity utilization.  Since Oracle database files initialize their space even though they are still empty, it is recommended that instead of creating very large data files that remain largely empty for most of their lifetime, smaller data files should be considered to accommodate near-term data growth.  As they fill up over time, their size can be increased, or more data files added, in conjunction with the capacity  increase of the thin pool.  The Oracle auto-extend feature can be used for simplicity of management, or DBAs may prefer to use manual file size manual file size management.

An oversubscription strategy is recommended for database environments when database growth is controlled , and thin pools can be actively monitored and their size increased when necessary in timely manner.

Undersubscription

An undersbuscription strategy is based on using thin devices with a total capacity smaller than the physical storage in the pools they are bound to.  This approach doesn’t necessarily improve storage capacity utilization but still use of wide striping , thin pool sharing, and other benefits of Virtual Provisioning.  In this case the data files can be sized to make immediately use of the full thin device size, or alternatively, auto-extend or manual file management can be used.

Undersubscription is recommended when data growth is unpredictable, when multiple small database share a large thin pool to benefit from wide striping, or when an oversubscriptioned environment is considered unacceptable.

Thin device preallocation

A third option exists where the DBA may like to benefit from oversubscription and application sharing of the pool, but at the same time guarantee that space is reserved for some or all of the thin devices.  This option used thin device preallocation.  A thin device can preallocate space in the pool, even before data was written to it.  When preallocation is used customers often preallocated the whole thin device (reducing the storage capacity optimization benefits).  In effect each thin device therefore fully claims its space in the thin pool, eliminating possible thin pool out-of-space condition.

Please refer to the white paper of 'Implementing Virtual Provisioning on EMC Symmetrix VMAX with Oracle Database 10g & 11g' for detail.