Oracle 12c’s new feature is pluggable databases (PDBs). This has been introduced to improve support for cloud & multi-tenancy, and keep up with SQL Server. However, our trial found Oracle’s PDBs less than ready for production use.
PDBs and Containerized Databases
In Oracle 12c, the new concept is pluggable databases. PDBs are intended to hold application data, but be movable between servers. This can better support cloud & multi-tenant applications.
Oracle’s statement is that usage within the PDB, is intended to be transparent & equivalent to running on a non-containerized database (such as Oracle 11g). Sessions see only the PDB they are connected to, but a server can consolidate many PDBs.
However, containerization introduces limitations at the root container itself. Usernames in the root container must be prefixed with
c$, and we can no longer create normal-named users in the root container. Existing scripts & applications are no longer capable of working here.
Coupled with this, the Oracle Installer wants to create a containerized database & PDB for you by default. So the feature is being heavily promoted, and the message we get is that your application should be running in a PDB.
The Problem with PDBs
Trialling application compatibility & install into PDB, however, everything felt somewhat unfinished. It appears to be a “beta” feature rushed out to compete.
The first real problem was, having a PDB created for us (either by the Oracle installer, or manually) was mounted but not yet open. You can’t run DDL against it.. Great, a default behavior of not being usable.
After extensive browsing of hefty reference docs, finally some useful syntaxes were unearthed. These need to be run as SYSDBA:
connect / as sysdba -- start SQL Plus as SYSDBA alter session set container=myapp1; -- switch to the PDB alter pluggable database myapp1 open; -- open it. -- if you need to: alter session set container=cdb$root; -- switch back to the root container.
Having found these “magic syntaxes” to switch containers & open the database, the rest of the install proceeded mostly as expected. However, a few days later after restarting the machine..
Dead application. Zip. Nada. Nothing working. SQL failures throughout the log.
It turns out the @#%! freaking PDBs don’t even start up after power-off?!
But my application needs to restart after power loss!
When my customers or I buy a database, they expect durability & reliability. There’s no R in ACID, but our applications are expected to recover after power loss or server crash, so our database is required to do so!
Earlier “non-containerized” versions of Oracle do this fine, no problem. Oracle has always been a good in this regard. So what’s wrong here?
Back to the manual.
Both following PDB creation by the installer, and after restarting Oracle/ your machine, the PDB will be in “mounted” state but not “open”. You will not be able to run SQL statements against it, nor run your application 🙁
Starting it manually requires SYSDBA access:
connect / as sysdba alter pluggable database myapp1 open; -- or: alter pluggable database all open; -- opens all.
But — that’s just not good enough. Manual DB manipulations shouldn’t be required to bring an application up after restart!
Some people have taken this further. Apparently it’s possible to create a trigger to attempt to automate PDB startup. And the 22.214.171.124 patches introduce additional (!!) new syntax to try & fix this problem.
But my preferences are for database portability, to avoid the requirement of hacks & magic tricks, and keep things easy to diagnose and debug.
KISS, in other words.
So for our non-multitenant application, I kissed Oracle 12c’s “containerized databases” goodbye and created a traditional non-containerized database. Don’t worry, you can still do that — either by unchecking the box in the installer, or manually.
Cloud and multi-tenant environments are important platforms to support. But this feature seems to have been rushed to release too early.
While PDBs are sold on the premise of transparent compatibility — to the applications running within them — there is a significant & non-transparent administration & development overhead, required to deal with containerization.
Documentation is (as is traditional with Oracle) heavy & stodgy. This makes finding necessary syntaxes to work with the new technology slow & awkward. There are too many broken defaults, with PDBs starting in an unusable “mounted but unopen” state.
Oracle are also lacking an effective quick-start or migration guide — producing one of these, might well have highlighted the PDB restart issue to them!
Restart & coming up automatically after power-loss or crash is a critical requirement. Oracle failed absolutely in the first 12c release, and even after patches have let themselves down here.
While startup can be “worked around” with triggers or extra commands, administration overhead & hacks should not be necessary on a top-level enterprise database.
Finally, there remains a large migration gap for any organization considering PDB. Syntaxes can be documented & adaptation somewhat patchwork, but reliability & availability should never be.
Are you considering Oracle 12c or pluggable databases? Add your thoughts now.
- DaDBm: Oracle 12c Pluggable Database vs. Microsoft SQL Server database
- Oracle 12c: Overview of the Multitenant Architecture
- Oracle-Base: Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle 12c
- Brendan Tierney: Auto-Starting your pluggables in 12c
- Yazici’s Blog: Automatic PDB Start in 12c