Why I Love Oracle Multitenant Architecture

Why I Love Oracle Multitenant Architecture

I have written multiple article on Multitenant. I hope you have gone through all these. If not, you can refer the same as below,

Multitenant Part 1 : Multitenant Overview

Multitenant Part 2 : PDB Hot Clone

Multitenant Part 3 : PDB Refresh

Multitenant Part 4 : PDB Relocation

Multitenant Part 5 : PDB Snapshot Carousel

This is my last article in this series. I would like to share my experience about Multitenant and Why I love Oracle Multitenant.

Ask a group of DBAs what's their biggest pain point in their DBA world, and eight out of ten, will say, Creating new DBs for Developer/Tester, Maintenance of Existing databases and Monitoring and Troubleshooting of all the databases (including patching, upgrading etc).

With Oracle Multitenant Architecture, One Container Database (CDB) can have multiple Pluggable Databases (PDBs). This makes DBAs life easier by provisioning new Pluggable databases with single command, Maintenance of Single DB server and One Database Instance of CDB, Monitoring and troubleshooting of database at ease. All databases are up to date with latest patches.

There are more development requirements, Different application type require specialized databases. For example, Spatial applications need spatial databases, Graph applications require Graph DB, some of Payments and fraud protection applications require Blockchain databases, to read/store different documents require specialized databases to store documents and so on.

Oracle Database natively supports all modern data types into one product. Oracle supports Spatial data for location awareness, JSON for document stores, IoT for device integration, in-memory technologies for real-time analytics, Blockchain native tables.

Hence, we called Oracle Database as Converged database.

Today's modern application development runs on Microservice architecture. Each microservice should have its own database that contains data to that relevant  microservice specifically. This helps you to deploy individual services independently. Also Individual teams can have their own databases for the corresponding microservice.

Oracle Multitenant is right fit into the Microservice development environment. Each microservice will have it's own pluggable database. Also in the Development scenario individual teams can have their own database (PDB).

"Oracle Multitenant enables an Oracle Database to function as a container database (CDB). A CDB consolidates multiple pluggable databases (PDB), a portable collection of schemas, schema objects, and non-schema objects. Whether deployed on-premises or in the cloud, with Oracle Multitenant, applications run unchanged in self-contained PDBs, improving resource utilization, management, and overall security." - Oracle Docs

With Multitenant, one can have many benefits, I am just highlighting few here,

• Efficient Consolidation
• Rapid Provisioning and Cloning
• Faster Upgrades and Patching
• Manage Many Databases As One
• Plug Into The Cloud

Why I love Multitenant?

There are many reasons why I love Multitenant, But here I am sharing Three Top Most Reasons and  my experience with Multitenant and gradually you will come to know about the same.

Reason 1 : Manage Many Database As One

Multitenant enables me to manage single database server to run multiple databases. I had created one CDB and I have multiple pluggable database inside the same. 

• One server to Manage
• One Database to Manage
• One Database Resource Manager to Manage PDBs
• One-time Patching
• One-time Upgradation

Reason 2 : Simple Administration

Multitenant helped me to manage multiple database with very ease. Very simple administration.

With Multitenant,

Earlier Provisioning/Creating of Database was taking 1.5 hours & Now, I am able to finish this in 10mins.

Earlier Database Cloning was taking more than 2 hours, & Now, I am able to finish this task in 30mins.

Earlier Patching multiple single instance database was taking around 12+ hours with Multiple down time windows for different databases & Now, in single downtime window of 2 hours I am able to complete patching my database.

Earlier database refresh was taking almost 1+ hours of time & Now, I have automated PDB refresh and developers are getting Refreshed database at any point of time.

With Multitenant Database Administration, Now, I am able to save lot of my time. I hope you also save your time by adopting Multitenant Architecture.

Reason 3 : Ease of Maintaining Dev/Test Environment.

Our Development team introduce bug fixes and application patches every quarter. Hence, every new development and bug fix environment is required to maintain at certain period of time, say for example one bug fix environment need to maintain almost 18 months and every quarter new application & database environment is needed to maintain Application/Database Fixes and new development.

Every Quarter, creating new database with latest version of database and maintaining old database is tedious task for DBAs.

Multitenant has helped me a lot in this development scenarios and reduces my lot of work and saved my time as well.

Earlier Provisioning/Creating of Database was taking 1.5 hours & Now I am able to finish this in 10mins.

Also Rather maintaining 15+ Database servers, Now I am only maintaining 5 database servers.

Now you can imagine how much work has been reduced by Multitenant Architecture.

Did You Know?

• No Application changes required to adopt Multitenant Architecture. Application remains Unchanged. Nothing to worry for Developers.
• You will have TNS entry for each PDBs. You can define PDB listener with different port.
• AutoUpgrade Automates Upgrade and Conversion of Non-CDB to PDB
• To simplify upgrades to Oracle Database 21c AutoUpgrade automates steps to convert a database from a non-CDB to a PDB as part of a database upgrade.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 5 : PDB snapshot Carousel

 Multitenant Part 5 : PDB Snapshot Carousel

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 4 : PDB Relocation

Multitenant Part 4 : PDB Relocation

Online PDB relocation,

PDB Relocation is the fastest way to move a PDB with minimal or nearer to no down time. Otherwise, plugging and unplugging PDBs require outage means downtime is required.

Target Database Server

• Add tns entry of source database and create database link to connect to source database.
• There should be a common user in Source Database
• Grant privileges to common user i.e. create session and create pluggable database to all.

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

On target database server run following command,

CREATE PLUGGABLE DATABASE PROPDB1 FROM PROPDB1@prod_link RELOCATE;

When you run this command, it executes a hot clone of source PDB from its original location to its target location. The source PDB copies data files, undo blocks, and redo blocks to the target PDB upto the SCN marked for it.

When this step completes, two transactional consistent copies of this PDB exist: one in the source CDB and one in the target CDB . during this operation, transaction continues uninterrupted on the source PDB. Users or applications connected to the source PDB are unaware that a relocation is happening behind the scene.

All existing application connections, and new connections created during this step, continue to connect to the source PDB.

When you issues ALTER PLUGGABLE DATABASE OPEN command

• The target PDB implicitly sets the end SCN marker, and applies any redo or undo required to complete media recovery to satisfy the implicit end SCN marker.
• When media recovery occurs on the target PDB, Oracle Database initiates active session draining on the source PDB.
• PDB services are registered with the listener and are available on the target CDB.
• The source PDB is closed.
• The target PDB opens in read/write mode.

Relocation in Different Listener Networks

AVAILABILITY MAX

Here, at additional step, you need to reconfigure the original listener.

If a local listener redirects to a Single Client Access Name (SCAN) listener in an Oracle RAC configuration, thn you need to add parameter in each node listener file.

And parameter name is,

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN1=YES

I hope, this helps you to perform PDB relocation in very simple steps.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 3 : PDB Refresh

Multitenant Part 3 : PDB Refresh

The refreshable PDB at target will be in read only mode and 

The read-only PDB can be used for reporting purposes.

Later point of time, You can change a refreshable PDB to a non-refreshable PDB, but  remember not vice versa.

Now, let see how it works via this graphic,

From Prod CDB, we do clone of the PRODPDB1, once it done, at Dev site it should be in read only mode. 

Technology behind this refreshable PDB is, PDB hot clone only. Very first you need to do hot clone with refresh mode parameter in create pluggable command.

After that, whenever you need latest version of your production data, you just need to refresh it. And as additional step you can make new PDBs from your refreshable PDB

Step by Step to perform Refreshable PDB

Here I would like to share step by step commands to perform Refreshable PDB

Let’s define what could be our database name for Production and Development and CDB/PDB name.

Throughout our scenario I will follow below names to be performed.

Source Database Server (Production)

CDB Name : PRODCDB

PDB Name : PROPDB1

Target Database Server (Development)

CDB Name : DEVDCDB

PDB Name : DEVPDB1

Prerequisites of Scenario

You can follow same set of commands and replicate this in your environment.

These prerequisites are common for all our scenarios, like, hot clone, refreshable PDB, PDB relocation and snapshot carousel, other scenarios you can found in my blog or follow the link embedded above. 

Source Database Server

At source we need to configure database in archive log mode if not, it is mandatory to do it and local undo mode, so each PDB will have their own undo tablespace.

Database should be in Archive log mode and local undo mode.

Target Database Server

• Add tns entry of source database and create database link to connect to source database.
• There should be a common user in Source Database
• Grant privileges to common user i.e. create session and create pluggable database to all.

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

Here, I am giving two example to create refreshable PDB 1) Manual 2) Automatic as below,

Example 1

In this example, database refresh mode is manual, it means that you can refresh database at your own peace whenever  you need. 

Create manual refreshable PDB

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link_pub REFRESH MODE MANUAL;

After creating refreshable PDB there may be some change happens to your main database and you want to refresh again to reflect it in your PDB so there are few steps to refresh it.

Manually Refresh your PROPDB1_RF from Source PROPDB1.

First, set your container to refreshable PDB

alter session set container=PROPDB1_RF;

Second, close your PDB

alter pluggable database PROPDB1_RF close immediate;

Third, refresh your current PDB

alter pluggable database PROPDB1_RF refresh;

Fourth, And finally, you can open it in read only mode.

alter pluggable database open read only;

Example 2

In this in second command refresh mode is every 10 min, so your refreshable PDB will be refresh automatically in every 10mins. 

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link REFRESH MODE EVERY 10 MINUTES;

Create clone from Refreshable PDB

Addition to that, you can create one or more PDBs from your refreshable PDB.

A refreshable clone PDB must be either close or read only mode. A refreshable PDB must be closed when a refresh is performed. 

In automatic refresh, If it is not closed when automatic refresh is attempted, then the refresh is deferred until the next scheduled refresh.

Command to create New PDB from Refreshable PDB

sql> create pluggable database PDB1CLONE from PROPDB1_RF;

sql> alter pluggable database PDB1CLONE open;

Refreshable PDB Switchover

In Oracle 12.2, Refreshable clone PDB was introduced.  In Oracle 18c, now it allows you to switch the roles of a source PDB and a refreshable PDB. This functionality is known as Refreshable PDB Switchover.

It means that you can make your refreshable PDB as primary one. And here is the command for the same.

alter pluggable database PROPDB1_RF refresh mode every 10 minutes from c##clone_user@prod_link_pub switchover;

I hope this article will help you to create refreshable PDB seamlessly.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 2 : PDB Hot Clone

 Multitenant Part 2 : PDB Hot Clone

• Introduction
• Histroy about PDB clone
• Step by Step to Perform Hot Clone
• How Stuff Works?

Introduction

Developer and testing team always require fresh database to identify bug or issues.

This is just a graphical representation of what we are going to do now.

At PRODCdb container database we have ProPdb1, this we are going to clone at our DEVCdb container database as HOTPorPdb1

History about PDB clone

Very quickly there is some history about clone pdbs.

In 12cR1, pdb clone was introduced; this was a feature with some restrictions. And restriction is like you have to make your pdb in read only mode. Basically this is cold cloning, not a hot clone. Because our pdb in read only mode. 

Later part in 12cR2, new feature is introduce as Hot clone, you can do cloning when your database / pdb is in read/write mode.

And later in 19c, remote clone can be done via dbca too in silent mode.

Step by Step to perform Hot Clone

Here I would like to share step by step commands to perform Hot Clone

Let’s define what could be our database name for Production and Development and CDB/PDB name.

Throughout our scenario I will follow below names to be performed.

Source Database Server (Production)

CDB Name : PRODCDB

PDB Name : PROPDB1

Target Database Server (Development)

CDB Name : DEVDCDB

PDB Name : DEVPDB1

Prerequisites of Scenarios

You can follow same set of commands and replicate this in your environment.

These prerequisites are common for all our scenarios, like, Hot Clone, Refreshable PDB, PDB Relocation and Snapshot Carousel, other scenarios you can found in my blog or follow the link embedded above. 

Source Database Server

At source we need to configure database in archive log mode if not, it is mandatory to do it and local undo mode, so each pdb will have their own undo tablespace.

Database should be in Archive log mode and local undo mode.

Target Database Server

Step 1 : Add tns entry of source database and create database link to connect to source database.

Step 2 : There should be a common user in Source Database

Step 3 : Grant privileges to common user i.e. create session and create pluggable database to all.

Create a database link which will be later used for cloning

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

I always say that, each task have five steps,  and three steps we already completed here so remaining two steps needs to complete it.

On Target Server

Hot Clone from ProdCdb to DevCdb, Now, there are only two steps remaining. 

Step 4 : to create pluggable database from target database using database link.

Create pluggable database from db link,

CREATE PLUGGABLE DATABASE PROPDBHTCLONE FROM PROPDB1@prod_link;

Step 5 : Now, just to open database using alter pluggable database open;

After creation, new PDB will be in MOUNTED state. Open it to change the state to READ WRITE.

ALTER PLUGGABLE DATABASE PROPDBHTCLONE OPEN;

How Stuff Works,

You may wonder how this stuff works, So here is some insight of hot clone. 

When we run create pluggable database command, We do parallel scan of data file and it will read each block and transfer from source database to target database.

 

Now you may have question like what about those on going transaction which are happening in target database which are still not part of datafile. 

The answer is, we ship redos and apply in target database, so all your current transactions will ship their.

But there may be some committed and some uncommitted transaction how to tackle that?

So answer is, undo segment is used for the same to rollback your uncommitted transactions.

I hope, this helps you to perform PDB Hot Clone in these simple five steps.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Multitenant Part 1 : Overview

Multitenant Part 1 : Overview 

Multitenant -- the container database architecture.. the first database designed for the Cloud.

Oracle has introduced Oracle Multitenant architecture with the release of Oracle Database 12c R1 on July 2013.  It has been eight year now by the time I am writing this Article. You can find many Articles on Multitenant and I am adding one more in the list of the same. 

I found few reasons to write this article as below,
1) Still many DBAs are not using Multitenant or haven’t got chance to work on it.
2) Many organizations are still on 11g or 12c with single instance database.
3) 20c Onwards, it is mandatory to have container database architecture at the time of installation itself.
4) Enabling DBAs to learn Multitenant architecture.
5) Helping DBAs to simplify their day to day activity and reducing administrative work.

Here, I am writing series of articles on Multitenant, you can also go through the same and see how easy to administrate CDB/PDBs.

Multitenant Part 2 : PDB Hot Clone

Multitenant Part 3 : PDB Refresh

Multitenant Part 4 : PDB Relocation

Multitenant Part 5 : PDB Snapshot Carousel

And finally, I would like to share,

Why I Love Multitenant

Now, let's understand Multitenant architecture and it's core concepts,

Multitenant Architecture

Before Oracle Database 12c, each database is having their own instance and each database requires its own processor, memory and other server resources. Most of the efforts were going to manage those individual instances, in terms of patching, migration, upgrades and performance tuning.

In addition to management issues, these databases often waste server resources because they use only a very small portion of dedicated server. Hence consolidation of these databases into a single database Oracle introduces Multitenant option, which run one or more Pluggable Databases (PDBs) within a single Oracle instance, called a multitenant Container Database (CDB).

Following image shows multitenant architecture.

Just to give brief understanding, This is multitenant architecture; you can found this poster in Oracle Documents.

As you see, we have single instance which consist of common memory area and background process for all PDBs and common set of sys, sysaux tablespace, redo log files, archive log file.

But we have different set of files at PDB level. PDB have their own sys and sysaux tablespace to store PDB level meta data and user tablespaces, local undo and temp files.

In PDB, one users cannot access to other PDBs data, only common user have some credentials to manage PDBs all at the CDB level. Here you can have different set of duties assign at PDB level and CDB level.

Following is the detailed architecture of Multitenant.

Key Benefits of Multitenant 

If you are adopting multitenant architecture, your application will run without change. Also you will get benefits of rapid cloning. You can plug and unplug non-cdb database into container database as PDB.

As Operation perspective, you can manage many database as one. Less operation on DB like patching an upgrade.

Using this approach, you will get benefits of consolidation of your hardware + software i.e database.

Using Multitenant, you are consolidating all your database, my mean to say that database resides on multiple server will now be on one server as pluggable database. This directly benefits you in your capital expenditure.

Also operation wise you are managing many db as one so it will helps you to do upgrade or patching as one activity for all rather doing multiple times. Provisioning of new PDB is also simple

Dev and testing team will get database ready in couple of minutes via cloning or snapshot cloning. Database can be plug and unplug from one CDB to another CDB easily with few steps.

This is really very easy to adopt multitenant in your environment. Just need to understand the core architecture for the same.

Key Benefits for DBAs

There are several features introduce in multitenant gradually from 12c to 19c. Plug & unplug of PDBs, PDB cloning, Hot clones, flashback PDBs, PDB level AW reports, initially in 12c R1 AWR report was only at CDB level. But after that it is possible to take AWR report at PDB level. 

You can manage cpu/io/memory at PDB level with resource manager.  You can have Refreshable PDB, Dataguard at PDB level. And also you can have keystore for each PDBs. PDB lockdown profiles , that helps you to restrict user to run commands such as alter system, alter session, accessing of common objects, AWR reports etc  it is very useful from security perspective and separation of duties among users.

Multitenant Features specific to DBAs

PDB Level Operations

Plug and Unplug 

PDB Hot Clone

Refreshable PDB

Snapshot Carousel

Online PDB Relocation

High Availability with PDBs

Flashback PDB

Dataguard at PDB level

PDBs Level Monitoring and Diagnoses

PDB Level AWR Report

PDB Level Resource Management

Keysotres for PDBs

CDB Fleet Management

Security

PDB Lockdown Profile

Oracle Applications Certified with Multitenant

Here are list of Applications that are certified with Multitenant. Other custom application build different programming languages are also supported for Multitenant. Also Multitenant support Microservice architecture. 

Fusion Applications

Fusion ERP

Fusion HCM

Fusion CRM

NetSuite

Applications Unlimited

Siebel

PeopleSoft

JD Edwards

Oracle Cloud Applications

Taleo Business Edition

RightNow

Oracle Argus Analytics 

Oracle Utilities Mobile Workforce Management 

Oracle FLEXCUBE Direct

Oracle Argus Mart

Supply Chain

Oracle Utilities Customer Care and Billing

Oracle Argus Insight

Oracle Utilities Work and Asset Management

Retail Analytics

Oracle Argus Safety

Planning Non-RPAS 

Oracle Utilities Smart Grid Gateway

Merch Suite

Oracle Utilities Meter Data Management System

Stores Suite

And Many More...

Multitenant Announcement so far…

• Multitenant came up with 12c release. Oracle is more focusing on Cloud and Multitenant deployments and hence oracle has deprecated non-cdb architecture with its 12.1.0.2
• And 19c onwards, Oracle provides you 3 PDBs without multitenant license. This will encourage all of us to use CDB/PDB architecture here.
• And now 20c onwards, non-cdb architecture will be de-supported. So here it is mandatory for all of us to learn more about multitenant and its features and how to use it.

References

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/introduction-to-the-multitenant-architecture.html

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert