Get started with the Multi-Tenant feature of Amazon RDS for Oracle

Many organizations have a large number of Oracle databases running on multiple physical servers or virtual machines. They invest substantial resources in the operation and administration of these databases, incurring expenses for infrastructure, management, and licensing. In many cases, these physical servers and virtual machines are under-utilized due to improvements in hardware technology, increase in number of CPUs, and enhancements in database engine.

Consolidation for increased resource utilization can be achieved by one or more of the following:

Virtualization and running a single database per virtual machine
Running multiple databases per physical server or virtual machine
Having multiple schemas in a single database serving different applications
Using Oracle Multitenant

Out of these options, Oracle Multitenant offers consolidation and manageability benefits as well as provides better isolation between applications. The Oracle Multitenant architecture alters a database, allowing it to act as a container database (CDB) with one or many pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and non-schema objects that are attached to a container and appears to an Oracle Net client as a standalone database. Oracle Multitenant helps with challenges related to resource utilization by offering consolidation without making changes to existing schemas and applications. Oracle Multitenant provides the benefits of manageability where multiple PDBs can be managed as one CDB. You can have up to 3 user-created pluggable databases (PDBs) per container database (CDB) without requiring additional multitenant license. According to Oracle, Oracle internal tests have demonstrated that Oracle Multitenant increases consolidation density compared to single-instance databases running on dedicated virtual machines.

In this post, we discuss the Oracle Multitenant architecture in Amazon Relational Database Service (Amazon RDS) for Oracle and how it is used to support the creation of multiple PDBs in a CDB. These PDBs are called tenant databases in Amazon RDS for Oracle. The new features allow you to run multiple logically isolated PDBs in a single RDS for Oracle database (CDB) instance.

Note that the Amazon RDS feature for Oracle Multitenant is referred to as Multi-Tenant rather than multitenant because it is a capability offered by multiple other RDS DB engines, not just the Oracle DB engine. The term Oracle Multitenant refers exclusively to the Oracle Database architecture, which is compatible with both on-premises and Amazon RDS for Oracle deployments.

Solution overview

Organizations using Oracle Multitenant in their self-managed Oracle environments, on premises, or on Amazon Elastic Compute Cloud (Amazon EC2) can migrate these databases to the fully managed database service Amazon RDS for Oracle with the Multi-Tenant feature.

Also, Amazon RDS for Oracle Multi-Tenant can help customers who are looking for more ways cost-optimize their RDS for Oracle instances by allowing consolidation of multiple instances as tenant databases of one CDB. Having multiple tenant databases in one RDS for Oracle instance allows you to reduce compute costs and increase efficiency and utilization of available compute by using a single instance to run multiple databases. Having fewer instances also simplifies database management and maintenance because you have fewer instances to monitor, patch, and maintain.

Each tenant database will have its own primary user, other database users, tablespaces, and more. Amazon RDS for Oracle will continue to provide features like managed backups and restores, including point-in-time restore (PITR) for the entire instance, which includes the CDB and tenant databases. You can now add, remove, or modify PDBs to a CDB instance using the new Amazon RDS tenant database APIs within your RDS for Oracle multi-tenant instance. Amazon RDS for Oracle allows you to simplify the management of your multi-tenant database as a single parameter group, and a single option group will be applied to the tenant databases on the instance. You can treat each individual tenant as a separate logical database, with different primary users, passwords, users, schemas, and tablespaces.

Beginning with Oracle Database 21c, a multitenant container database is the only supported architecture. Notably, Oracle Database 19c marks the final long-term support release from Oracle that permits the creation of non-CDBs. When Oracle ceases support for Oracle Database 19c, the option to create non-CDBs in Amazon RDS for Oracle will become unavailable.

The new multi-tenant configuration of the Oracle Multitenant architecture in Amazon RDS for Oracle is an alternative to the existing single-tenant configuration. As the name suggests, the Amazon RDS for Oracle single-tenant configuration has a single PDB per CDB. Amazon RDS for Oracle has launched the Oracle Multitenant architecture with multi-tenant capabilities for both Standard Edition 2 and Enterprise Edition. The Standard Edition 2 is limited to a maximum of three customer-created PDBs by design from Oracle and doesn’t have the option to add more. Amazon RDS for Oracle offers the technical capability to house more than three PDBs in Enterprise Edition (30 PDBs as of this writing). You should review your Oracle entitlements and contracts to determine whether you may create more than three PDBs.

To summarize, the following are some of the key benefits of Amazon RDS for Oracle multi-tenant:

Increase resource utilization
Potentially reduce compute costs
Simplify database operations and maintenance
Create, delete, modify, and describe PDBs and tenant databases within your RDS for Oracle DB instance by using the AWS Management Console or the AWS Command Line Interface (AWS CLI)
Convert RDS for Oracle CDB instances that use the single-tenant configuration to the multi-tenant configuration
Use Amazon RDS automation for managed backup and restore of CDBs
Apply a single parameter group or option group to PDBs within an RDS for Oracle DB instance
Manage each PDB as a separate logical database, with different primary users, passwords, users, schemas, and tablespaces

Create an RDS for Oracle multi-tenant DB instance using the console

To create an RDS for Oracle multi-tenant DB instance using the console, complete the following steps:

On the Amazon RDS console, choose the AWS Region in which you want to create the CDB instance.
In the navigation pane, choose Databases.
Choose Create database.
For Choose a database creation method, select Standard create.
For Engine options, select Oracle.
For Database management type, select Amazon RDS.
For Architecture settings, select Oracle multitenant architecture.
For Architecture configuration, select Multi-tenant configuration.
For Edition, you can choose either Enterprise Edition or Standard Edition Two. With Standard Edition Two, you will be restricted to a maximum of 3 PDBs.
Specify License as Bring Your Own License (BYOL) if you selected Oracle Enterprise Edition. For Oracle Standard Edition Two, you can choose either BYOL or License Included (LI).
For Templates, choose either Production or Dev/Test. A production template creates a Multi-AZ standby.
For DB instance identifier, enter a name for your DB instance. This name should be unique across all database instances owned by your AWS account in the current Region.
The Tenant database settings section allows you to specify settings for your initial PDB in the Multi-tenant database:
1. For Tenant DB name, enter the name of your initial PDB. Make sure the name is different from the name of your CDB, which has the default name RDSCDB.
2. For Tenant database master username, enter the name of a local user for your PDB. This is a primary user for the PDB.
3. Either enter a password for Tenant database master password or select Auto generate a password.
4. For Tenant database character set, choose a character set for the PDB. This value can be different from the CDB character set.
Note that you can’t create multiple tenant databases as part of the CDB creation process. You can only add PDBs to an already existing CDB.
Choose appropriate settings for the Instance configuration, Storage, Availability & Durability, Connectivity, and Monitoring sections.
In the Additional configuration section, you can change settings for your DB parameter or option group, backup, encryption, logs, maintenance, and deletion protection.
1. You will see System ID (SID) as RDSCDB. This is the CDB name. The CDB name cannot be changed.
2. You can change other settings as appropriate, such as enable automated backups, enable cross-Region automated backups, enable automatic minor version upgrade, and more.
Choose Create database.

After a few minutes, the RDS for Oracle instance will be available.

Create an RDS for Oracle Multi-Tenant DB instance using the AWS CLI

To create an RDS for Oracle Multitenant DB instance using the AWS CLI, use the following command:

aws rds create-db-instance —region us-east-1 \
—engine oracle-ee-cdb \
—db-instance-identifier my-cdb-inst \
—multi-tenant \
—db-name mypdb \
—master-username mypdb_admin \
—master-user-password masteruserpassword \
—allocated-storage 250 \
—db-instance-class db.t3.large \
—backup-retention-period 3

Add a tenant to the RDS for Oracle Multi-tenant instance using the console

To add a tenant to your instance using the console, complete the following steps:

On the Amazon RDS console, choose Databases in the navigation pane.
Select the RDS for Oracle instance to which you want to add a tenant database.
On the Actions menu, choose Add tenant database – New.
Under Instance settings, you will see the DB instance identifier to which you are adding a tenant.
For Tenant database name, enter the tenant database name, which is the PDB name. This name has to be unique in all of the tenants of that database instance.
Provide the tenant database primary user name and password. This user is a local user to the tenant being created. Each tenant database has its own primary user.
Choose the database character set to be used for this tenant.
Choose Add tenant.

After a few minutes, the PDB will be added.

Add a tenant to the RDS for Oracle Multi-tenant instance using AWS CLI

The following example creates a tenant database named MYPDB2 in the RDS for Oracle CDB instance named database-1:

aws rds create-tenant-database --region us-west-2 \
--db-instance-identifier database-1 \
--tenant-db-name MYPDB2 \
--master-username admin \
--master-user-password mypdb2-pwd

Connect to a tenant database

You can’t connect to the root container of the CDB database because Amazon RDS for Oracle multi-tenant does not allow the creation of common users. You can connect to individual PDBs within your CDB using either the corresponding primary user or other local users created on that tenant database.

To connect to the PDB named MYPDB in your database-1 RDS for Oracle instance, find the endpoint for the DB instance on the Connectivity & security tab. Also take note of the listener port.

You can use this endpoint, port, and tenant database name to connect to the respective tenant database. The following screenshot shows the SQL Developer create connection wizard. This will connect to the MYPDB tenant database using the admin (primary) user.

Convert non-Multi-tenant instances to Multi-tenant

You can convert RDS for Oracle non-multi-tenant instances to RDS for Oracle multi-tenant in the following steps:

First, convert your instance from RDS for Oracle non-multi-tenant to RDS for Oracle single-tenant.
For instructions, refer to Converting an RDS for Oracle non-CDB to a CDB. This conversion to CDB from non-CDB is done by modifying the instance to use the engine oracle-ee-cdb or oracle-se2-cdb, depending on what edition you are using. Note that this operation is immediate and cannot be reversed.
Now you can convert from RDS for Oracle single-tenant to RDS for Oracle Multi-Tenant.
On the Amazon RDS console, when modifying a single-tenant instance, select Oracle multitenant architecture or use the Amazon RDS API modify-db-instance with the --multi-tenant flag.

The existing single-tenant database will become the first tenant of the multi-tenant instance. This is an online operation with no downtime. Note that the single-tenant to multi-tenant conversion is immediate and can’t be reversed.

Beginning with Oracle Database 21c, a multitenant container database is the only supported architecture, so all RDS for Oracle database instances that need to be upgraded to 21c should be first converted to a Multi-Tenant architecture.

Convert single-tenant instances to Multi-Tenant using the console

Complete the following steps to convert a single-tenant instance to Multi-Tenant:

On the Amazon RDS console, choose Databases in the navigation pane.
The following screenshot shows database-2 is a single-tenant database instance.
Select the database and choose Modify.
For Architecture configuration, select Multi-tenant configuration.
Choose Modify DB instance.

As shown in the following screenshot, the original ORCL database becomes a tenant database.

Convert single-tenant instances to Multi-Tenant using the AWS CLI

You can use the AWS CLI to modify a database instance to multi-tenant:

aws rds modify-db-instance --region us-east-1\
--db-instance-identifier database-2 \
--multi-tenant \
--apply-immediately

Conclusion

In this post, we discussed how you can now create RDS instances with the multi-tenant capability. You can add, remove, or modify PDBs and tenant databases using the new Amazon RDS tenant database APIs within your RDS for Oracle multi-tenant instance where tenant databases are PDBs. This will allow you to simplify the management of your multi-tenant databases as a single parameter group, and an option group will be applied to all tenant databases on the instance. We also demonstrated how to create, connect, and convert to an RDS for Oracle Multi-tenant environment.

We welcome your feedback. If you have questions or suggestions, leave them in the comments section.

About the Authors

Vishal Patil is a Senior Database Specialist Solutions Architect with Amazon Web Services. Vishal helps customers migrate and optimize their Oracle databases to AWS, especially on managed services like Amazon RDS for Oracle and Amazon RDS Custom for Oracle. Apart from Oracle databases, Vishal has expertise in migration tools and services like Oracle GoldenGate and AWS DMS.

Manash Kalita is an AWS Senior Database Specialist Solutions Architect for APJ, with extensive experience in enterprise cloud architecture.

Arnab Saha is a Senior Database Specialist Solutions Architect at AWS, where he brings expertise in a range of key AWS services including Amazon RDS, Amazon RDS Custom, Amazon Aurora, and Amazon EBS. In his role, Arnab offers invaluable guidance and technical support to clients’ cloud migration and data modernization initiatives, facilitating their journey towards enhanced operational efficiency and innovation.

AWS Database Blog