AWS Database Blog

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

Last year, we launched a new Amazon EBS feature called elastic volumes. Using elastic volumes for Amazon EBS, you can increase your EBS volume size or 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. Your database remains online and available for use while the change takes effect.

You can request a volume modification 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, 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. You typically choose io1 volumes for high-performance database workloads and gp2 volumes for other, less-demanding workloads. IO1 volumes are designed for latency-sensitive transactional workloads and provide up to 32,000 IOPS per volume. GP2 volumes offer a good balance between price and performance and provide a baseline IOPS of 3 IOPS/GB, up to a maximum of 10,000 IOPS per volume.

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 operating-system 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. You don’t use a LVM in this scenario. The following diagram shows this simple database storage layout.

A simple database storage architecture

Storage operations without elastic volumes

You can increase the storage or change the IOPS provisioned for systems using a single EBS volume (without LVM) for database storage. To do so, you create a new EBS volume with the desired size and IOPS from a snapshot of the current EBS volume and swap the EBS volumes. You can use the following steps to perform this operation, which requires database downtime:

  • Stop the database and create a snapshot of your EBS volume.
  • Create a new EBS volume, with the desired size and IOPS, from the snapshot.
  • Detach the old EBS volume from the EC2 instance and then attach the new EBS volume to the EC2 instance
  • Resize the file system (if the EBS volume size has changed) and start the database.

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.

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 operating-system file system for storage management, without any downtime. For this demonstration, we use an Oracle 12c database running on Amazon Linux. A 30-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 30 GiB to 60 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 30 GiB, as seen in the following screenshot.

Console screenshot verifying the 30GB volume size

Note: You can 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-55-62 ~]# mkdir -p /u01/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-59-102 ~]# mkfs -t ext4 /dev/nvme2n1
mke2fs 1.42.12 (29-Aug-2014)
Creating filesystem with 7864320 4k blocks and 1966080 inodes
Filesystem UUID: f9702847-e6c5-4409-a710-139c15ee5043
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000

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

 [root@ip-172-31-55-62 ~]# mount /dev/nvme2n1 /u01/app/oracle/oradata/cdb1/pdb1/customdfs/

Now, we create a 20-GB tablespace called EVTestTableSpace using SQL*Plus as shown following.

SQL> create tablespace  EVTestTableSpace datafile '/u01/app/oracle/oradata/cdb1/pdb1/customdfs/evtest.dbf' size 20000M;
Tablespace created.

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

[root@ip-172-31-59-102 ~]# df –h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         16G   48K   16G   1% /dev
tmpfs            16G     0   16G   0% /dev/shm
/dev/nvme0n1p1   40G   16G   24G  39% /
/dev/nvme2n1 30G 20G 8.4G 71% /u01/app/oracle/oradata/cdb1/pdb1/customdfs

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

Screenshot verifying the size and location

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 60 GiB from 30 GiB using the AWS CLI.

$ aws ec2 modify-volume --region us-east-1 --volume-id vol-07c198d593a9dfae5 --size 60
{
    "VolumeModification": {
        "TargetSize": 60, 
        "TargetVolumeType": "io1", 
        "ModificationState": "modifying", 
        "VolumeId": "vol-07c198d593a9dfae5", 
        "TargetIops": 1000, 
        "StartTime": "2018-03-01T13:11:33.224Z", 
        "Progress": 0, 
        "OriginalVolumeType": "io1", 
        "OriginalIops": 1000, 
        "OriginalSize": 30
    }
}

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 us-east-1 --volume-id vol-07c198d593a9dfae5
{
    "VolumesModifications": [
        {
            "TargetSize": 60, 
            "TargetVolumeType": "io1", 
            "ModificationState": "optimizing", 
            "VolumeId": "vol-07c198d593a9dfae5", 
            "TargetIops": 1000, 
            "StartTime": "2018-03-01T13:11:33.224Z", 
            "Progress": 0, 
            "OriginalVolumeType": "io1", 
            "OriginalIops": 1000, 
            "OriginalSize": 30
        }
    ]
}

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

Use the resize2fs command to resize the file system.

[root@ip-172-31-59-102 ~]# resize2fs /dev/nvme2n1
resize2fs 1.42.12 (29-Aug-2014)
Filesystem at /dev/nvme2n1 is mounted on /u01/app/oracle/oradata/cdb1/pdb1/customdfs; on-line resizing required
old_desc_blocks = 2, new_desc_blocks = 4
The filesystem on /dev/nvme2n1 is now 15728640 (4k) blocks long.

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

[root@ip-172-31-59-102 ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs         16G   48K   16G   1% /dev
tmpfs            16G     0   16G   0% /dev/shm
/dev/nvme0n1p1   40G   16G   24G  39% /
/dev/nvme2n1 59G 20G 37G 35% /u01/app/oracle/oradata/cdb1/pdb1/customdfs

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 20-GB data file called evtest_02.dbf to the EVTestTableSpace tablespace to increase the database storage as shown in the following screenshot.

The database storage now is 40 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 io1 to gp2) 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.