Using Amazon EBS elastic volumes with Oracle databases (part 3): databases using Oracle ASM

In parts 1 and 2 of this blog series, we discuss the elastic volumes feature for Amazon EBS and how it works with Oracle database storage layouts. We discuss elastic volumes with databases that use operating-system file systems and that use Logical Volume Managers (LVM) for database storage management. In this post, we discuss the storage layout on Amazon EC2 for Oracle databases that use Oracle Automated Storage Management (Oracle ASM). We demonstrate how you can scale the database storage without impact on availability. We also look at some of the advantages of using elastic volumes with Oracle databases.

Storage operations for databases using Oracle ASM

In this section, we briefly discuss the storage layout on Amazon EC2 for Oracle databases that use Oracle ASM for storage management. Then we discuss how Oracle database storage modifications like increasing the storage or changing the IOPS provisioned were done before the elastic volumes feature was introduced. We also discuss the associated challenges. Finally, we demonstrate how to address some of these challenges using elastic volumes with an example.

Storage layout for databases using Oracle ASM

Oracle ASM is a volume manager that is used to manage storage for Oracle databases. It includes a file system designed specifically for databases. Oracle ASM distributes data across disks to ensure uniform performance. It can also rebalance the data automatically after storage configuration changes like addition or removal of disks.

When using Oracle ASM, you create ASM disk groups that contain one or more ASM disks. An ASM disk is a storage device like an EBS volume. The ASM disk groups are exposed as file interfaces to the Oracle database instances for storing database files like data, control, redo log files, and so on. Oracle ASM uses an Oracle ASM instance. This instance is a special Oracle instance used to configure and manage the disk groups. It also provides file layout information to the Oracle database instance. The Oracle database instance performs storage I/O operations directly, without going through an Oracle ASM instance.

The following diagram depicts a database layout using Oracle ASM. There are two ASM disk groups—DATA and RECO, with five and three ASM disks (EBS volumes) respectively.

Oracle database storage operations without elastic volumes

To increase the storage or IOPS provisioned for your database, you create new ASM disks (EBS volumes). You add them to the ASM disk group using the following steps:

• Create a new EBS volume and attach it to the EC2 instance
• Create a primary partition on the device
• Create an ASM disk and make it available to Oracle ASM by using the oracleasm createdisk command
• Add the ASM disk to the ASM disk group using the ALTER DISKGROUP … ADD DISK command

After any storage configuration change, Oracle ASM automatically initiates a rebalance operation to distribute the data evenly across the disks. The power setting parameter controls the speed of the rebalancing operation . You can set the default power limit by using the ASM_POWER_LIMIT database initialization parameter, or you can specify it using the POWER clause of the ALTER DISKGROUP statement.

Oracle database storage operations using elastic volumes

To modify an EBS volume, use the modify-volume command from the AWS CLI or the Modify Volume option from the AWS Management Console. Specify the new volume size and IOPS.

If you modify only the IOPS provisioned without changing the volume size, no changes are required at the operating system or Oracle ASM level. If you modify the EBS volume size, then you need to resize the ASM disks using the ALTER DISKGROUP … RESIZE ALL command.

When you modify the size or IOPS of an EBS volume, the data is automatically spread across multiple backend devices. This approach helps to avoid hot spots and ensure that you get the IOPS provisioned.

Example: increasing the storage for a database that uses Oracle ASM

In this section, we demonstrate how to increase the storage provisioned for an Oracle database that uses Oracle ASM for storage management, without any downtime. For this demonstration, we use an Oracle 12c database running on Red Hat Enterprise Linux. We have attached six EBS volumes of 50 GiB each to the EC2 instance, which are presented as ASM disks to Oracle ASM. We have created an ASM disk group DATA with a total size 300 GB from these six ASM disks. During this demonstration, we increase the storage provisioned to the database from 300 GiB to 600 GiB without downtime.

To deploy Oracle Database 12c Enterprise Edition on AWS in a reliable and automated way, use the Oracle Database on AWS Quick Start. This deployment includes Oracle ASM for storage management.

To demonstrate that the resize was performed without any database downtime, we have created a database stored procedure called evtestproc. This procedure insert records into a table called evtesttab at 10-second intervals. We run this procedure while we perform the resize operation. We can confirm that the resize was done without any database downtime by verifying that records were inserted into the evtesttab table at 10-second intervals without any gap. For the definition of the evtestproc stored procedure, see part 1 of this blog series.

Step 1: Verifying the current setup

From the AWS Management Console, verify the size of the EBS volumes. Currently, it is 50 GiB as seen in the following screenshot for one of the six EBS volumes.

Next, we query the v$asm_diskgroup view. We see that the DATA disk group has a total size of 300 GB and contains six ASM disks (backed by the six EBS volumes) of 50 GB each, as shown in the following screenshot.

We have created a big file tablespace, 250 GB in size, called EVTestTablespace as shown in the following screenshots.

From the following screenshot, we can see that a little over 250 GB has been used from the 300 GB provisioned for the ASM disk group called DATA.

Step 2: Setting up the stored procedure

Start the evtestproc stored procedure to insert records into the evtesttab table while we increase the storage provisioned to the database.

begin
  evtestproc();  //PLSQL procedure to insert records into the EVTESTTAB table at 10-second intervals
end;

We query the table from SQL Workbench to verify that records are being inserted.

Step 3: Resizing the EBS volumes

We now increase the size of the EBS volumes to 50 GiB from 100 GiB using the AWS CLI.

$ aws ec2 modify-volume --region us-west-2 --volume-id vol-0c8a98c35d4126d50 --size 100
{
    "VolumeModification": {
        "TargetSize": 100, 
        "TargetVolumeType": "io1", 
        "ModificationState": "modifying", 
        "VolumeId": "vol-0c8a98c35d4126d50", 
        "TargetIops": 2000, 
        "StartTime": "2018-03-17T12:53:20.000Z", 
        "Progress": 0, 
        "OriginalVolumeType": "io1", 
        "OriginalIops": 2000, 
        "OriginalSize": 50
    }
}

We do this for each of the six EBS volumes. After a short while, we check the status of the volume modification request using the AWS CLI. Now you can see that the volume has entered the optimizing state, and the new size is reflected in the AWS Management Console, as seen in the following screenshot.

$ aws ec2 describe-volumes-modifications --region us-west-2 --volume-id vol-0c8a98c35d4126d50
{
    "VolumesModifications": [
        {
            "TargetSize": 100, 
            "TargetVolumeType": "io1", 
            "ModificationState": "optimizing", 
            "VolumeId": "vol-0c8a98c35d4126d50", 
            "TargetIops": 2000, 
            "StartTime": "2018-03-17T12:53:20.689Z", 
            "Progress": 0, 
            "OriginalVolumeType": "io1", 
            "OriginalIops": 2000, 
            "OriginalSize": 50
        }
    ]
}

Note: If you need to determine the EBS volume that is mapped to an ASM disk, see the following example. This example shows how to find the EBS volume mapped to the DATA1 ASM disk.

Log in as the OS user (Oracle in this example) used to set up ORACLE_HOME. Use the oracleasm querydisk command to find the device major and minor number as shown following.

[oracle@ip-10-0-0-5 ~]$ /etc/init.d/oracleasm querydisk -d DATA1

Disk "DATA1" is a valid ASM disk on device [202,81] 

When you get the disk major and minor number (202 and 81 in this example), use the following command to find the device name at the OS level.

oracle@ip-10-0-0-5 ~]$ ls -la /dev/ | grep 202 | grep 81
brw-rw----.  1 root disk    202,   81 Mar 16 01:37 xvdf1

As you can see from the output, /dev/xvdf1 is the device name at the OS level. The EBS volume attached at /dev/xvdf1 corresponds to the DATA1 ASM disk.

Step 4: Resizing the disk partitions on the EBS volumes

When you add a disk to an ASM disk group, you create a partition on the disk by using the oracleasm createdisk command. This command also makes the partition available for use by Oracle ASM. The partition must be resized before Oracle ASM recognizes the new size. You can use the following steps to resize the partition online. For detailed information about online resizing of disk partitions, see this Red Hat Enterprise Linux Knowledge Base article.

Use the fdisk command to resize the partition as shown.

[root@ip-10-0-0-5 ~]# fdisk /dev/xvdf
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Command (m for help): d
Selected partition 1
Partition 1 is deleted

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): 
Using default response p
Partition number (1-4, default 1): 
First sector (2048-209715199, default 2048): 
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-209715199, default 209715199): 
Using default value 209715199
Partition 1 of type Linux and of size 100 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

Although the on-disk partition table has been updated, the on-memory kernel partition table hasn’t yet been updated with the new size. We use the partx command to update the in-memory kernel partition table from the on-disk partition table, as shown following.

[root@ip-10-0-0-5 ~]# partx -u /dev/xvdf

We repeat the preceding steps to resize the partitions on all the six EBS volumes.

Finally, we query the v$asm_diskgroup view and verify that the ASM disks are reflecting the new size (100 GB), as shown in the following screenshot.

Step 5: Increasing the database storage

We now increase the database storage available by resizing the big file tablespace to 500 GB, as shown in the following screenshots.

From the following screenshot, we can see that a little over 500 GB has been used from the 600 GB provisioned for the DATA ASM disk group.

Step 6: Verifying that there was no database downtime while the storage was resized

We query the evtesttab table to verify that the PL/SQL procedure execution was uninterrupted. The query also verifies that records were inserted at 10-second intervals without any gaps, as shown in the following screenshot.

Using this example, we demonstrate how to increase the storage allocated to an Oracle database that uses Oracle ASM for storage management, without any impact on database availability. You can also change the IOPS provisioned for the database using elastic volumes without any impact on database availability or performance. In addition, you can change the EBS volume type (for example, from io1 to gp2) using elastic volumes without any impact on database availability or performance.

Advantages of using elastic volumes

• Ease of maintenance – You can use elastic volumes to increase the storage provisioned easily, without downtime or performance impact, by using simple API calls. Similarly, you can use elastic volumes to adjust the IOPS provisioned for your database easily, without downtime or performance impact, by using simple API calls. You can also automate these operations.
• Predictable spiky workloads – Some workloads have spikes that are predetermined and easily predictable, like month-end processing. We can use elastic volumes to dial up the IOPS provisioned for the database during the spikes and dial it down afterward. By doing this, you can meet the performance requirements of the database and keep costs to a minimum at the same time.
• Cost reduction – EBS io1 volumes offer the highest performance but are expensive compared to gp2 volumes, which offer a balance between price and performance. If you have a database that is used mostly during weekdays, you can dial down the IOPS provisioned during the weekend to save costs. Similarly, you can change the volume type from io1 to gp2 during the weekend to save costs.

Let’s look at the potential cost savings using the following example.

Suppose that you have a database that is deployed in the US-East (Ohio) Region, and it has a storage requirement of 1 TB and a peak IOPS requirement of 30,000. The database is mostly accessed during the weekdays (Monday–Friday) and has minimal usage during the weekend (Saturday–Sunday).

If you use only io1 volumes throughout the month for database storage, the EBS charges are $2075 per month as calculated in the following table.

*As of this writing, in the US-East (Ohio) Region.

The same configuration, with gp2 volumes instead of io1 volumes, costs only $100 per month as calculated in the following table.

*As of this writing, in the US-East (Ohio) Region .

The database is hardly accessed during the weekends. Thus, we can use elastic volumes to switch to gp2 volumes during the weekends (two days) and switch back to io1 volumes during the weekdays (five days) to save costs.

In this scenario, by switching between io1 and gp2 volume types, the monthly EBS charges reduce to $1512 as calculated in the following table. This is a savings of 27 percent each month compared to using only io1 volumes ($2075 per month).

• Debugging production issues – Suppose that you run into database performance issues in production (say, after an application patch release or an application version upgrade). In this case, you can use elastic volumes to temporarily increase the IOPS provisioned for your database till you debug and identify the problem. After the issue is resolved, you can dial down the IOPS to the normal level. You can perform this operation easily and without any application or database downtime.
• Performance testing – You can use the same QA environments for functional and performance testing. For example, you can design the QA environments to use gp2 volumes to optimize storage costs. For performance testing, you can simply change the storage type from gp2 to io1 for the duration of the performance testing activity. You can switch back to gp2 again once done. This approach eliminates the need for you to clone your functional testing environment for performance testing and saves costs at the same time.

Summary

Using the elastic volumes feature of Amazon EBS, you can increase the volume size, change the IOPS, or change the volume type while the volume is in use. In this three-part blog post, we show you how to scale the storage of your Oracle databases running on Amazon EC2 without impact on database availability or performance. We also discuss the various advantages of using elastic volumes feature with Oracle databases, including cost savings.

You can find more information on elastic volumes in the Amazon EBS documentation.

About the Authors

Ashok Shanmuga Sundaram is a partner solutions architect with the Global System Integrator (GSI) team at Amazon Web Services. He works with the GSIs to provide guidance on enterprise cloud adoption, migration and strategy.

Ejaz Sayyed is a partner solutions architect with the Global System Integrator (GSI) team at Amazon Web Services. His focus areas include AWS database services and, database and data warehouse migrations on AWS.

Nael Haddad is a senior product manager with Elastic Block Store (EBS) team at Amazon Web Services. He is driving new features associated with multiple EBS product offerings.