Pat Shuff's Blog

  • PaaS
    June 21, 2016

database option - multi tenant part 2

Yesterday we looked at the concept behind multi-tenant and talked about the economics and operational benefits of using the option. Today we are going to look at examples and technical details. Fortunately, this is a hot topic and there are a ton of interviews, tutorials, and videos showing you how to do this. The best place to start is Oracle Technology Network - multitenant. This site lists seven offerings from Oracle Education, six online tutorials, and four video demos. Unfortunately, most books are a little light on this topic and cover it lightly in a chapter buried in the high number chapters. The most recent books cover this topic directly
Two of these books are pre-order and the third is only a few months old. The other books talk about it as an abstract term with little or no examples. Safari Books does not have many that cover this subject because the topic is so new and few books have been published on the topic.

The Oracle Base Blog has a series of postings about multitenant and does a really good job of showing diagrams and sample code. There is a significant amount of information at this site (24 posts) looking at the subject in depth. I normally provide a variety of links to other bloggers but I think that this work is good enough to deserve top billing by itself.

Internal to Oracle the GSE Demo pages have a few demos relating to multi-tenant.

  • PaaS - Data Management (Solutions Demo) has a hands on tutorial in the cloud
  • DB12c Multi-Tenant Workshop by William Summerhill is on retriever.us.oracle.com
  • Oracle Database 12c multitenant and in-memory workshop using OPC by Ramulu Posham on retriever.us.oracle.com

For the rest of this blog I am going to go through the workshop by Ramulu Posham because it is the most complete and does everything 100% in the cloud. We could do this on the Oracle Public Cloud using DBaaS, or a database installed in IaaS on Oracle, Amazon, or Azure. We can not do this on Amazon RDS because they disable multi-tenant and prohibit it from working.

The schedule for the workshop is

  • 9:00 - 9:15 intro
  • 9:15 -10:15 cloud intro
  • 10:30 - 2:00 multi-tenant workshop

The workshop consists of creating two pluggable database instances in the cloud and look at pluggable creation, cloning, and snap cloning. The assumption is that you have a public cloud account and can create two 12c databases in the cloud with less than 100 GB of disk space. You can do this on two 1 OCPU 7.5 GB instance but require High Performance or Extreme Performance Edition to get multi-tenant to work. The only software that we will need for our Windows 2012 IaaS instance will be Swing Bench which helps put a load on the database and allows us to look at utilization of resources for a container database and our pluggable instances.

The flow of the workshop is shown in the following slide. We are going to create a database with data in the container and another database and put both instances in a pluggable database on one instance.

Some of the more interesting slides from the presentation are shown below. The file location slide helped me understand where resources get allocated. The redo logs, for example, are part of the container database and not each pluggable. You setup Data Guard for all pluggables by configuring the container and replication happens automatically. The discussion on cloning a database is interesting because you don't need to copy all of the data. You only copy the header information and reference the same data between the original and the clone. Changes are tracked with file links as they are updated on both sides. The managing slide helped me understand that there is still a DBA for each pluggable as well as a master DBA for the container. Seeing that in a picture helped me understand it better. There are also multiple slides on resource management and shares. I pulled a representative slide as well as the summary benefits slide. This is what is covered in the first hour prior to the hands on labs starting.

To start the lab, we create a 12c High Performance instance called salessvc$GC where $GC is a substitute for each lab participant. We will use 01 as our example so we will create salessvc01.

Note that we call the database instance salessvc01, the ORACLE_SID salesc01, and the pluggable container sales01. We can not have the ORACLE_SID and the pluggable instance the same because it will confuse the listener so those names must be different. The creation takes between 30 minutes and an hour on our demo accounts. While we are waiting we will create a second instance with the name cmrsvc01 with similar parameters using the SID of crms01 and a pluggable container of crm01.

Once we have the ip address of the two instances we can create an ssh tunnel for ports 443, 5500, 80, and 1521. This is done by creating an ssh tunnel in our putty client. The presentation material for the labs go through with very good screen shots for all of these steps. We have covered all of this before and are just summarizing the steps rather than detailing each step. Refer to previous blog entries or the workshop notes on how to do this.

The sales instance looks like the screen shots below. We configure the ports and look at the directory structure in the /u02/app/oracle/oradata directory to verify that the sales01 pluggable database was created under the container database salesc01.

Once we have the database created and ports configured we download and launch SwingBench to load data into the database and drive loads to test response time and sql performance.

We need to download SwingBench and Java 8 to execute the code properly. Once we download SwingBench we unzip it and install it with a java command.

The only true trick in the install is that we must execute lsnrctl status on the database to figure out what the listener is looking for in the connection string. We do this then type in localhost:1521 and the connection string to populate the database with SwingBench.

We repeat this process for the cmrc01 instance, repeat the SwingBench install, and unplug the soe database from the crmc01 pluggable database to the salessvc01 database service and plug it in as a pluggable. The big issue here is having to unplug and copy the xml file. It requires uploading the private key and allowing ssh between the two instances. Once this is done the SwingBench is run against both instances to see if performance improves or decreases with two pluggables on the same instance. The instructions do a good job of walking you through all of this.

Overall, this is a good workshop to go through. It describes how to create pluggable containers. It describes how to unplug and clone PDBs. It is a good hands on introduction and even brings in performance and a sample program to generate a synthetic load.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.