Your top five choices for Oracle on AWS

Will you use RDS, or manage the database yourself? 

Running an Oracle database in AWS offers three options: RDS, RDS Custom, and Self Management. 

RDS involves AWS handling database management in their own VPC, providing access via SQL*Net protocol. There are significant benefits and time saving features like automated backups, easy high availability setup, and built-in monitoring. However, there are limitations including; restricted patching, limited support for certain Oracle features, and no allowance for outdated Oracle versions. 

Self Management is similar to traditional on-premise DBA practices, requiring administrators to handle all operational aspects like backups, high availability, and monitoring. It provides full access to Oracle options, allows SYS schema object creation, and is suitable for closely integrated applications. However, it demands user-driven operations and significant development effort for high availability with shorter recovery time objectives.

RDS Custom strikes a balance between fully managed and self-managed setups, allowing more flexibility in features and OS access. It runs on an instance within the user's VPC. 

How will you manage your backups? 

If you take the RDS route managing backups is straightforward, involving a simple checkbox configuration with the only decision being the required retention period beyond the standard 30 days. 

In contrast, for self-managed databases, a backup strategy needs to be established. There are two approaches: either using Oracle's native tool, Recovery Manager (RMAN), or utilizing AWS tools for backup management. DBAs often favor RMAN for its ability to track changes at the block level, allowing it to process only altered blocks within the Oracle database. This feature also facilitates a daily check for block corruption within the database.

What about all the copies you’ll need?

In Oracle database environments, additional database copies (we sometimes call them clones) are often needed for purposes like development and testing, or for segregating databases used in data warehousing. It's common for non-production databases to reside in a separate AWS account to ensure a clear distinction from the production system. 

If the  storage layer is encrypted (as is recommended), consideration must be given to how the encryption key is shared with the non-production environment or how snapshots are re-encrypted with a key accessible to non-production accounts. It's crucial to note that AWS simplifies the process of duplicating infrastructure, potentially leading to the creation of database copies that may not be covered by the user's Oracle license.

Think about storage, it’s different on AWS!

In AWS, disks are logical representations of arrays spanning numerous physical disks. Their effectiveness is determined by two key attributes: Provisioned IOPS and available throughput. Provisioned IOPS is crucial for tasks like database sequential reads, writes, and checkpoint activities. Available throughput is significant for operations involving large index or table scans, as well as file copying. Different disk types can be selected to tune these attributes, with options like General Purpose 2 or 3 (GP2/3) and Provisioned IOPS (PIOPS IO1, IO2). Adjusting disk size can also impact PIOPS, but exceeding 32K IOPS requires PIOPS disks with associated costs. While throughput is less easily modified, it's essential to consider AWS instance class limitations.

As for striping, Oracle's Automatic Storage Management (ASM) provides a storage management layer within the database software. While it adds some configuration overhead and affects backup management, it allows for the allocation of multiple logical disks to the same instance. This aggregation of disks enhances speed for large table scan operations. Striping can also be used to combine the performance characteristics of multiple less expensive GP2/GP3 disks, avoiding the cost of Provisioned IOPS disks. However, striping tends to be most beneficial for very large databases, typically in the range of tens of terabytes.

Will it be quick enough!

Many organizations overlook the importance of defining performance criteria for their databases, often evident in a lack of familiarity with performance benchmarks or sporadic references to low-level disk block response times. 

In reality the most crucial metrics lie in the response times of key SQL queries under database load. Oracle's Automatic Workload Repository (AWR) facilitates the assessment of SQL query performance across multiple databases, with the ability to impact business outcomes, such as reducing abandoned shopping baskets on eCommerce sites. Generating load for performance testing is essential, and two methods are prevalent: tools like Oracle Real Application testing and open-source HammerDB that isolate the database, and creating virtual users within a full application stack to simulate load. In practice, tools testing the database in isolation with the capability to replay recorded SQL statements offer the greatest potential for iterative optimization on AWS.