AWS Database Blog

Using Amazon EBS elastic volumes with Oracle databases (part 1): Introduction

August, 2024: This post has been reviewed and updated for accuracy.

With Amazon EBS feature Amazon EBS Elastic Volumes  you can increase your EBS volume size, change IOPS or volume type while the volume is in use. You can make this change without having an impact on operations.

In this series of three blog posts, we look at the advantages of using elastic volumes with Oracle databases. We also demonstrate how to use elastic volumes to increase the database storage and change the IOPS provisioned without an impact on database availability or performance.

In this first post, we cover using elastic volumes with Oracle databases using an operating-system file system without a Logical Volume Manager (LVM) for database storage management. In the second post, we cover Oracle databases that use an LVM for database storage management. In the third post, we cover Oracle databases that use Oracle Automatic Storage Management (Oracle ASM).

Overview of elastic volumes on Amazon EBS

With elastic volumes, you can increase the EBS volume size, adjust the IOPS provisioned, or change the EBS volume type while the EBS volume is in use and there’s no need to detach it. Your database remains online and available for use while the change takes effect.

You can request a volume modification (, more on Supported instance types) by using a simple API call, from the AWS Management Console, or by using the AWS Command Line Interface (AWS CLI). An EBS volume being modified goes through a sequence of states. After you request a volume modification, the volume enters the modifying state, more on how to Monitor the progress of EBS volume modifications, then the optimizing state, and finally the complete state.

EBS volume size changes usually take a few seconds to complete and take effect after a volume is in the optimizing state. Performance (IOPS) changes can take from a few minutes to a few hours to complete and depend on the configuration change being made. While an EBS volume is in the optimizing state, the volume performance is in between the source and target configuration specifications.

Oracle database-storage layout on Amazon EC2

When running an Oracle database on Amazon EC2, you use EBS volumes for database storage. Consider, if your workload is IO sensitive (sub-millisecond latency, consistent and high throughput), choosing EBS Provisioned IOPS SSD (io1/io2 Block Express) as the preferred option if it is available in the region, more details on EBS optimized performance. Throughput depends on average IO size and EC2 limits. Both io1 and io2 block express volumes allow modification of size and IOPS.

For prod and general-purpose workloads not sensitive to IO (single digit millisecond latency) choose GP3 wherever possible. GP3 volume allows you to modify IOPS and offers a good balance between price and performance.

The physical storage for an Oracle database consists of a set of files (data, temp, redo, control files, and so on) that are stored on disk. You can either use an operating system file system, a LVM, or Oracle ASM for creating and managing these files.

Storage operations for simple databases

In this section, we briefly discuss the storage layout on Amazon EC2 for simple Oracle databases that use a single EBS volume and an OS file system (without LVM) for database storage. Then we discuss how Oracle database storage modifications like increasing the storage provisioned or changing the IOPS provisioned were done before elastic volumes were introduced. We cover the associated challenges with this type of changes. Finally, we demonstrate how to address some of these challenges using elastic volumes with an example.

Storage layout for simple databases

For simple databases, you might just use a single EBS volume for database storage. To store the database files, you partition it and create file systems. When you create an EBS volume, it is automatically replicated within its Availability Zone to prevent data loss due to failure of any single hardware component. The following diagram shows this simple database storage layout.

A simple database storage architecture

Storage operations with 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. When you do, specify the new volume size and IOPS. If you are modifying only the IOPS provisioned without changing the volume size, no changes are required at the operating system level. If you are modifying the EBS volume size, then you need to resize the file system after the volume modification.

The EBS volume size cannot be reduced. However, you can create a smaller volume and then migrate your data to it using an application-level tool such as rsync (Linux instances) or robocopy (Windows instances). More details on Modify-volume-requirements.

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

Example: Increasing the storage for a simple database without LVM

In this section, we demonstrate how to increase the storage provisioned for an Oracle database that uses the OS file system for storage management, without any downtime. For this demonstration, we use an Oracle 19c database running on Red Hat Enterprise Linux (RHEL). A 50 GiB EBS volume is attached to the instance, and we create a file system on it for Oracle database storage. During this demonstration, we increase the storage provisioned from 50 GiB to 100 GiB without downtime.

To demonstrate that the resize was performed without any database downtime, we have created a database stored procedure called evtestproc. This procedure inserts 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.

Step 1: Verifying the current setup

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

Note: Refer to the Amazon EC2 documentation for detailed instructions on creating an Amazon EBS volume and attaching it to your EC2 instance.

To store the data files, we create a directory called customdf as shown following.

[root@ip-172-31-18-8 ec2-user]# mkdir -p /u02/app/oracle/oradata/cdb1/pdb1/customdf

We create a file system and mount it at /u01/app/oracle/oradata/cdb1/pdb1/customdf/ as shown following.

[root@ip-172-31-18-8 ec2-user]# mkfs -t ext4 /dev/nvme6n1
mke2fs 1.45.6 (20-Mar-2020)
Creating filesystem with 13107200 4k blocks and 3276800 inodes
Filesystem UUID: c5f8299e-a504-4cb5-a2f1-98796fd8f32b
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424

Allocating group tables: done
Writing inode tables: done
Creating journal (65536 blocks): done
Writing superblocks and filesystem accounting information: done

[root@ip-172-31-18-8 ec2-user]# mount /dev/nvme6n1 /u02/app/oracle/oradata/cdb1/pdb1/customdf/
	
[root@ip-172-31-18-8 ec2-user]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         16G     0   16G   0% /dev
tmpfs            16G  1.1G   15G   8% /dev/shm
tmpfs            16G   33M   16G   1% /run
tmpfs            16G     0   16G   0% /sys/fs/cgroup
/dev/nvme0n1p3  100G   22G   79G  22% /
/dev/nvme0n1p2  200M  5.8M  194M   3% /boot/efi
/dev/nvme2n1    150G   25G  126G  17% /u01
tmpfs           3.1G     0  3.1G   0% /run/user/1000
/dev/nvme6n1 49G 24K 47G 1% /u02/app/oracle/oradata/cdb1/pdb1/customdf

Now, we create a 30 GB tablespace called evvoltablespace using SQL*Plus as shown following.

SQL> create tablespace evvoltablespace datafile '/u02/app/oracle/oradata/cdb1/pdb1/customdf/evvol.dbf' size 30G;
Tablespace created.

You can verify the disk utilization at the OS level using the df command as shown following.

[root@ip-172-31-18-8 ec2-user]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         16G     0   16G   0% /dev
tmpfs            16G  1.1G   15G   8% /dev/shm
tmpfs            16G   33M   16G   1% /run
tmpfs            16G     0   16G   0% /sys/fs/cgroup
/dev/nvme0n1p3  100G   22G   79G  22% /
/dev/nvme0n1p2  200M  5.8M  194M   3% /boot/efi
/dev/nvme2n1    150G   25G  126G  17% /u01
tmpfs           3.1G     0  3.1G   0% /run/user/1000
/dev/nvme6n1 49G 30G 18G 64% /u02/app/oracle/oradata/cdb1/pdb1/customdf

Now, we verify the size and data file location of the newly created tablespace as shown in the following screenshot.

Step 2: Setting up the stored procedure

The following is the script to create and initialize the evtesttab and other related tables, and the definition of the evtestproc stored procedure.

//create table to store the test data
CREATE TABLE evtesttab(counter NUMBER, seconds_elapsed NUMBER, data VARCHAR2(50));

//create table to store error messages if any
CREATE TABLE evtesterrortab(err_msg VARCHAR2(2000), time DATE);

//create table storing a flag to interrupt the execution of PL/SQL procedure from another SQL session 
CREATE TABLE flagtab(delflag VARCHAR2(2));

INSERT INTO flagtab VALUES('N'); // inserting the initial record

COMMIT;

/*****************************************************
PL/SQL stored procedure to test the live resizing of Amazon EBS volumes used for Oracle database storage using the 'Elastic Volumes' feature.

Name: evtestproc
******************************************************/

CREATE OR REPLACE PROCEDURE evtestproc
IS
  l_flag varchar2(2);
  l_cntr number default 1;
  l_sec number default 10;
  l_errmsg varchar2(350);
BEGIN
  WHILE true LOOP
    SELECT delflag into l_flag FROM flagtab;
    IF l_flag = 'Y'
    THEN
      EXIT;
    END IF;
    INSERT INTO evtesttab VALUES(l_cntr, l_sec, 'Record inserted at ' || to_char(SYSDATE, 'DD-MM-YY HH:MI:SS'));
    COMMIT;
    l_cntr := l_cntr + 1;
    l_sec := l_sec + 10;
    DBMS_LOCK.SLEEP(10);
  END LOOP;
EXCEPTION
  WHEN others THEN
    l_errmsg := SUBSTR(SQLERRM, 1, 300);
    INSERT INTO evtesterrortab VALUES(l_errmsg , SYSDATE);
    COMMIT;
END;

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 volume

We now increase the size of the EBS volume to 100 GB from 50 GB using the AWS CLI.

$ aws ec2 modify-volume --region eu-west-1 --volume-id vol-038a80f451ce15413 --size 100 
{
    "VolumeModification": {
        "VolumeId": "vol-038a80f451ce15413",
        "ModificationState": "modifying",
        "TargetSize": 100,
        "TargetIops": 4000,
        "TargetVolumeType": "io2",
        "TargetMultiAttachEnabled": false,
        "OriginalSize": 50,
        "OriginalIops": 4000,
        "OriginalVolumeType": "io2",
        "OriginalMultiAttachEnabled": false,
        "Progress": 0,
        "StartTime": "2024-07-17T00:52:38+00:00"
    }
}

After you issue a modify-volume command, the volume first enters the modifying state, then the optimizing state, and finally the complete state. At this point, the volume is ready to be modified again. Size changes usually take a few seconds to complete and take effect after a volume is in the optimizing state.

We can use the describe-volumes-modifications command from AWS CLI to check the state of the volume modification. You can see that the volume is in the optimizing state now.

$ aws ec2 describe-volumes-modifications --region eu-west-1 --volume-id vol-038a80f451ce15413
{
    "VolumesModifications": [
        {
            "VolumeId": "vol-038a80f451ce15413",
            "ModificationState": "optimizing",
            "TargetSize": 100,
            "TargetIops": 4000,
            "TargetVolumeType": "io2",
            "TargetMultiAttachEnabled": false,
            "OriginalSize": 50,
            "OriginalIops": 4000,
            "OriginalVolumeType": "io2",
            "OriginalMultiAttachEnabled": false,
            "Progress": 0,
            "StartTime": "2024-07-17T00:52:38+00:00"
        }
    ]
}

The AWS Management Console now reflects the new size (as seen in the following screenshot), and the volume is ready to be used.

Step 4: Resizing the file system

With the ext4 we use the resize2fs command to grow/extend the file system.

[root@ip-172-31-18-8 ec2-user]# resize2fs /dev/nvme6n1
resize2fs 1.45.6 (20-Mar-2020)
Filesystem at /dev/nvme6n1 is mounted on /u02/app/oracle/oradata/cdb1/pdb1/customdf; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 13
The filesystem on /dev/nvme6n1 is now 26214400 (4k) blocks long.

Verify that the file system has been resized by using the df command.

[root@ip-172-31-18-8 ec2-user]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         16G     0   16G   0% /dev
tmpfs            16G  1.1G   15G   8% /dev/shm
tmpfs            16G   33M   16G   1% /run
tmpfs            16G     0   16G   0% /sys/fs/cgroup
/dev/nvme0n1p3  100G   22G   79G  22% /
/dev/nvme0n1p2  200M  5.8M  194M   3% /boot/efi
/dev/nvme2n1    150G   25G  126G  17% /u01
tmpfs           3.1G     0  3.1G   0% /run/user/1000
/dev/nvme6n1 99G 31G 64G 33% /u02/app/oracle/oradata/cdb1/pdb1/customdf

Step 5: Increasing the database storage

You can increase the database storage available by creating and adding data files to the tablespaces or by increasing the size of an existing data file.

In this example, we add another 30-GB data file called evtest_02.dbf to the evvoltablespace tablespace to increase the database storage as shown in the following screenshot.

SQL> alter tablespace evvoltablespace add datafile '/u02/app/oracle/oradata/cdb1/pdb1/customdf/evvol_02.dbf' size 30G;

Tablespace altered.

The database storage now is 60 GB, split across two data files as shown in the following screenshot.

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. We also query this table to verify 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 a single EBS volume without LVM for storage. We make this increase without any impact on database availability. You can also change the IOPS provisioned for the database or change the EBS volume type (for example, from gp3 to io2 block express) using elastic volumes. You can do so without any impact on database availability or performance.

In the next post, we discuss the storage layout on Amazon EC2 for Oracle databases that use LVM for storage management. We demonstrate how you can scale the database storage for these databases without an impact on availability.

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.

Tomris Postaci is a Senior Database Specialist Solutions Architect with Amazon Web Services. She focuses on helping customers to design, deploy, and modernize various database workloads.

Manoj Duvva is a Technical Account Manager at Amazon Web Services. In his role, Manoj collaborates with enterprise customers to architect, implement, and scale cloud-based applications to meet their business objectives. He is a subject matter expert in Amazon RDS for Oracle. Manoj is passionate about cloud computing, databases, automation, and artificial intelligence.

Ibrahim Emara is a Solutions Architect at AWS.