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.
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.
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.
We create a file system and mount it at /u01/app/oracle/oradata/cdb1/pdb1/customdf/
as shown following.
Now, we create a 20-GB tablespace called EVTestTableSpace
using SQL*Plus as shown following.
You can verify the disk utilization at the OS level using the df
command as shown following.
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.
Start the evtestproc
stored procedure to insert records into the evtesttab
table while we increase the storage provisioned to the database.
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.
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.
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.
Verify that the file system has been resized by using the df
command.
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.