A few years ago, Oracle released a multitenant database architecture that featured a concept called Pluggable Databases. The basic idea behind this architecture is a set of containers. A multitenant database is comprised of:
- One root container which acts as the main repository for metadata and common users
- One seed pluggable database that acts as a template
- One or more portable databases based on the seed template, created as needed
In the past, a whole new database with all its base resource allocation requirements was created when there was a demand for it. Pluggable databases can be used to remove this complexity while still giving end users the experience of having a separate environment.
Another advantage of the pluggable database is its portability. Moving a pluggable database is as simple as issuing an “ALTER PLUGGABLE DATABASE” command to create an XML file with the necessary metadata for the physical structure. Data files can then be moved to another container database on that server or a different server. Once the data files and XML files are in place, issuing a “CREATE PLUGGABLE DATABASE” command with the appropriate CREATE_FILE_DEST and SOURCE_FILE_NAME parameters “plugs” the database in for immediate use.
Data migration using this concept eliminates several of the pitfalls of more traditional methods. For instance:
- All metadata is contained within the database so no users, objects or code will be lost
- Plugging a database into a container on the same server is nearly instantaneous, eliminating the time previously needed for RMAN backup/recovery or Data Pump export/import
- Pluggable databases are generally smaller so moving the data files across the network to the new location can be very quick
This architecture also makes it possible to create and manage databases according to logical boundaries. Pluggable databases can be created to separate data into specific areas for:
- User communities
- Client specific environments
- Life cycle stages
- Regulatory compliance requirements
It is important to demystify the multitenant architecture by viewing it as a tool for secure and successful data separation and migration. Experimenting with this feature will no doubt convince anyone of its value and necessity to meet today’s fast paced demands for more databases.
Written by Michael Paddock, Principal Oracle DBA, Cintra Texas – May 2017