Oracle Database 12c: Oracle Multitenant Option
- by hamsun
1. Why ?
2. What is it ?
3. How ?
1. Why ?
The main idea of the 'grid' is to share resources, to make better use of storage,
CPU and memory. If a database administrator wishes to implement this idea,
he or she must consolidate many databases to one database. One of the concerns
of running many applications together in one database is: ‚what will
happen, if one of the applications must be restored because of a human error?‘ Tablespace
point in time recovery can be used for this purpose, but there are a few prerequisites.
Most importantly the tablespaces are strictly separated for each application.
Another reason for creating separated databases is security: each customer
has his own database. Therefore, there is often a proliferation of smaller
databases. Each of them must be maintained, upgraded, each allocates virtual
memory and runs background processes thereby wasting resources. Oracle 12c
offers another possibility for virtualization, providing isolation at the database
level: the multitenant container database holding pluggable databases.
2. What ?
Pluggable databases are logical units inside a multitenant container database,
which consists of one multitenant container database and up to 252 pluggable
databases. The SGA is shared as are the background processes.
The multitenant container database holds metadata information common for
pluggable databases inside the System and the Sysaux tablespace, and there
is just one Undo tablespace. The pluggable databases have smaller System and
Sysaux tablespaces, containing just their 'personal' metadata. New data dictionary
views will make the information available either on pdb (dba_views) or container
level (cdb_views).
There are local users, which are known in specific pluggable databases and
common users known in all containers.
Pluggable databases can be easily plugged to another multitenant container
database and converted from a non-CDB. They can undergo point in time recovery.
3. How ?
Creating a multitenant container database can be done using the database configuration
assistant: There you find the new option: Create as Container Database. If
you prefer ‚hand made‘ databases you can execute the command from
a instance in nomount state:
CREATE DATABASE cdb1 ENABLE PLUGGABLE DATABASE ….
And of course this can also be achieved through Enterprise Manager Cloud.
A freshly created multitenant container database consists of two containers:
the root container as the 'rack' and a seed container, a template for future
pluggable databases.
There are 4 ways to create other pluggable databases:
1. Create an empty pdb from seed
2. Plug in a non-CDB
3. Move a pdb from another pdb
4. Copy a pdb from another pdb
We will discuss option2: how to plug in a non_CDB into a multitenant container
database. Three different methods are available :
1. Create an empty pdb and use Datapump in traditional export/import mode
or with
Transportable Tablespace or Database mode. This method is suitable for pre
12c databases.
2. Create an empty pdb and use GoldenGate replication.
When the pdb catches up with the non-CDB, you fail over to the pdb.
3. Databases of Version 12c or higher can be plugged in with the help of
the new dbms_pdb Package.
This is a demonstration for method 3:
Step1: Connect to the non-CDB to be plugged in and create an xml File with
description of the database.
The xml file is written to $ORACLE_HOME/dbs per default and contains mainly
information about the datafiles.
Step
2: Check if the non-CDB is pluggable in the multitenant container database:
Step 3: Create the pluggable database, connected to the Multitenant container
database.
With nocopy option the files will be reused, but the tempfile is created
anew:
A service is created and registered automatically with the listener:
Step 4: Delete unnecessary
metadata from PDB SYSTEM tablespace:
To connect to newly created pdb, edit tnsnames.ora and add entry for new pdb.
Connect to plugged-in non_CDB and clean up Data Dictionary to remove entries
now maintained in multitenant container database. As all kept objects have to
be recompiled it will take a few minutes.
Step 5: The plugged-in database
will be automatically synchronised by creating common users and roles when
opened the first time in read write mode.
Step 6: Verify tablespaces and
users:
There is only one local tablespace (users) and one local user (scott) in the
plugged-in non_CDB pdb_orcl.
This method of creating plugged_in non_CDB from is fast and easy for 12c databases.
The method for deplugging a pluggable database from a CDB is to create a new
non_CDB and use the the new full transportable feature of Datapump and drop
the pluggable database.
About the Author:
Gerlinde has been working for Oracle University Germany as one of our Principal Instructors for over 14 years. She started with Oracle 7 and became an Oracle Certified Master for Oracle 10g and 11c.
She is a specialist in Database Core Technologies, with profound knowledge in Backup & Recovery, Performance Tuning for DBAs and Application Developers, Datawarehouse Administration, Data Guard and Real Application Clusters.
