12c PDB Multitenancy, Schema Consolidation, and whatever

Many of you have probably have heard me speak over the years (at OOW, local user groups and at the local bars) about the virtues of simplification, rationalization, and consolidation. I mentioned the different database consolidation and multi-tenancy models: Virtualization based, Database Instance and Schema consolidation.

The following papers I wrote [when I was at Oracle] touch in detail on this topic –
http://www.oracle.com/technetwork/database/database-cloud/database-cons-best-practices-1561461.pdf

And here’s a more current version of that paper., updated for 12c and PDB.
http://www.oracle.com/us/products/database/database-private-cloud-wp-360048.pdf

For those who have done consolidation via Virtualization platforms such as VMWare or OVM know its fairly straightforward and its a simple “drag and drop”, as I say. Similarly consolidation of many databases as separate database instances on platform is also fairly straightforward. Its the consolidation of many disparate schemas into a common database that makes things interesting. Couple of key points on “why schema consolidation” from the paper:

  • The schema consolidation model has consistently provided the most opportunities for reducing operating expenses, since you only have a single big database to maintain,monitor, mange and maintain.
  • Though schema consolidation allows the best ROI (w.r.t CapEX/OPex), you are sacrificing flexibility for compaction. As I’ve stated in my presentations and papers, “…consolidation and isolation move in opposite directions” The more you consolidate the less capabilities you’ll have for isolation; in contrast, the more you try to isolate, the more you sacrifice benefits of consolidation.
  • Custom (home-grown) apps have been best fit use cases for schema consolidation, since application owners and developers have more control on how the application and schema is built.

Well, with the 12c Oracle Database feature Pluggable Database (PDB) , you now have more incentive to lean towards the schema consolidation. PDB “begins” to eliminate the typical issues that come with schema consolidation; such as namespace collisions, security, granularity of recovery.

In this 1st part of the three part series on PDB, I’ll illustrate the installation of the 12c Database with Pluggable Database feature. The next upcoming parts of the series will cover management and user isolation (security) with PDB.

But first a very, very high-level primer on terminology:

  • Root Container Database – Or the root CDB (cdb$root) is the real database (if you will), and the name you give it will be name of the instance. The CDB database owns the SGA and running processes. I can have many CDBs on the same database server (each with its own PDBs). But the cool thing is that you can have a more than one CDB, allowing DBAs to have a Database Instance consolidation model coupled a schema consolidation. For best scalability, mix in RAC and leverage all the benefits of RAC Services, QoS, and Workload Distribution. The seed PDB (PDB$SEED) is a Oracle supplied system template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. One cannot add or modify objects in PDB$SEED.
  • Pluggable Database – The PDB databases are sub-containers that serviced by CDB resources. The true beauty of the PDB is its mobility; i.e., I can unplug and plug 12c databases into and out of CDBs. I can “create like” new PDBs from existing PDB, like full snapshots.

So, now I’ll illustrate the important/interesting and new screens of 12c Database Installer:

PDB12c 2013 08 19 17 22 42

We chose Server Class

PDB12c 2013 08 19 17 23 09

It will single instance ..for now 🙂

PDB12c 2013 08 19 17 23 37

Choose Advanced Install

PDB12c 2013 08 19 17 24 07

And now for the fun step. We choose a Enterprise Edition, as Pluggable Database feature is only available in EE

PDB12c 2013 08 19 17 24 47

The next couple of screens ask about the Oracle Home and Oracle Base location, nothing new, but look at screen for Step 11. This where the fun is. We specify the Database name, but also specify if we want to create a Container Database. If we check it, it allows us to create our first PDB database in the Container Database (CDB). In my example I speficied Yoda as my CDB name and (in keeping with Star Wars theme) I said PDB is PDBOBI

PDB12c 2013 08 19 17 27 19

We obviously choose ASM as the storage location

PDB12c 2013 08 19 17 28 18

And we have the opportunity to register EM Cloud Control this new target database.

PDB12c 2013 08 20 17 46 20

The rest of the steps/screens are standard stuff, so I won’t bore you with it. But here’s an excerpt from the database alert that shows magic underneath:

create pluggable database PDB$SEED as clone  using '/u02/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates//pdbseed.xml'  source_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.823892297',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.823892297') file_name_convert=NONE  NOCOPY
Mon Aug 19 18:58:59 2013
….
…. 
Post plug operations are now complete.
Pluggable database PDB$SEED with pdb id - 2 is now marked as NEW.


create pluggable database pdbobi as clone  using '/u02/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates//sampleschema.xml'  source_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/temp01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/pdbobi_temp01.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/example01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/system01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/SAMPLE_SCHEMA_users01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/sysaux01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813') file_name_convert=NONE  NOCOPY
Mon Aug 19 19:07:42 2013
….
….
****************************************************************
Post plug operations are now complete.
Pluggable database PDBOBI with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdbobi as clone  using '/u02/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates//sampleschema.xml'  source_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/temp01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/pdbobi_temp01.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/example01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/system01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/SAMPLE_SCHEMA_users01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813',
'/ade/b/3593327372/oracle/oradata/seeddata/SAMPLE_SCHEMA/sysaux01.dbf','+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813') file_name_convert=NONE  NOCOPY
alter pluggable database pdbobi open restricted
Pluggable database PDBOBI dictionary check beginning
Pluggable Database PDBOBI Dictionary check complete
Database Characterset is US7ASCII
….
….

XDB installed.

XDB initialized.
Mon Aug 19 19:08:01 2013
Pluggable database PDBOBI opened read write
Completed: alter pluggable database pdbobi open restricted

I will cover more of PDB creation and management in the next blog. But I’ll leave you with this teaser of DBCA screen:

PDB12c 2013 08 20 17 46 20