Pluggable Database Management with Toad 12.1

Multitenant Database

Multitenant or pluggable database is probably the most discussed feature in Oracle 12c. For many of us DBAs or Developers in can be a dramatic shift as it requires different thinking of how the “database” is working or how we define a database from now on. Many articles are available in the web and on ToadWorld there are already several blogs discussing this feature.

In one of my previous blogs I already mentioned the ability of managing Pluggable Databases in Toad 12. But Toad 12.1 brings some major enhancements and it’s unbelievable how easy the management of PDBs can become.

Create Pluggable Database

As the Database Browser in Toad more and more becomes the central point of administration it’s obvious that you find all necessary tasks to create, manage, plug and unplug a pluggable database here.

So let’s add a pluggable database to our existing CDB.

With the “+” a wizard shows up and allows you to clone an existing PDB or simply use the seed database as source for your new pluggable database. If you want to clone an existing PDB this one has to be opened read only. My intention is to clone the TUK1 PDB to a TW1 PDB. So first I have to open the TUK1 database read only.

Now I can use that PDB as the source for the new PDB named TW1.

Normally the fields are automatically filled as soon as you add the name of the new PDB. As I’m using Oracle Managed Files (OMF) that’s not necessary or in contrast it can create some failures so I’ll remove that content.

The output looks like this:

Create pluggable database TW1 from TUK1      Storage ( Maxsize unlimited             Max_shared_temp_size unlimited)      File_name_convert = NONE      Path_prefix = NONE      Tempfile reuse;

Hitting the button <Execute> will immediately create the new database.

An information screen pops up and explains to me how I can add the pluggable database to the database browser.

Before I’m able to connect to the new PDB I’ve to open it first. The green check icon will again allow me to open the PDB read only or read write.

As every pluggable database comes with his predefined service name I can now simply use the direct connect within the Database Login Windows.

In addition to the global schemas SYS and SYSTEM you can define local DBAs as well. But in my case I had not created any additional DBAs so I’m going to use SYSTEM (as always).

Back in the Database Browser I can now work with the pluggable database like with any other database. Do you see any difference in this screenshot compared to an “ordinary” database?

Answer: A PDB does not have an UNDO tablespace or better to say: the UNDO-Tablespace is not shown as it belongs to the CDB. But at the end there is no difference in working with a PDB versus working with a NON-PDB database.

Unplug and Plug

As the next step we will use the brand new command “UNPLUG”. With this it should be possible to unplug a PDB from one CDB and to plug it into another CDB afterwards. You only have to specify an XML file where the metadata of the PDBs data dictionary is stored.

This file is necessary to “plugin” the pluggable database later on. Unplugging requires a shutdown – or better closing – of the pluggable database. As we are using Toad we do not have to care about. One question is arising and Toad will close the database automatically.

But after unplugging the database it’s still shown up in the list of pluggable databases for that CDB.

And if you try to open that database you will get the error message “ORA-65086” as the pluggable database is no longer available.

Is this a Toad bug? Not really: The reason is that Oracle does not remove the description of the pluggable database from the CDB if it is unplugged. Unfortunately as long as this description exists it’s not possible to plug in the PDB again. Even more there is no command “PLUG” or “PLUGIN” for a PDB at all. Instead you have to use the command “CREATE PLUGGABLE Database” even if you simply want to plug in a pluggable database.

But “CREATE” always assumes that something similar does not exist. As like CREATE TABLE the table must be dropped before it can be recreated. Have you ever dropped a production database? Probably not. So this is the most worrying command I have to do:

DROP PLUGGABLE DATABASE TW1

But again Toad helps because when I push the button “-“ a window shows up and asks if I want to keep the data files.

Of course I want to keep the data files. And oh wonder the PDB is instantaneously removed from the list. It’s not a big task as only the metadata is now removed from the CDB.

Now everything is prepared to recreate the PDB “TW1” using the existing XML-File. And Toad explains to me that this is similar to “Plug in”.

This is the script Toad generates:

SQL> Create pluggable database TW1 /* from xml file */       using 'D:\Temp\TW1.xml'      Source_file_name_convert = NONE      nocopy      Storage ( Maxsize unlimited                Max_shared_temp_size unlimited)      Path_prefix = 'D:\ORADATA\JA12C\DATAFILE\TW1\'      Tempfile reuse;

And after a few seconds the pluggable database TW1 is available again and can be opened.

Conclusion

Even though it’s a very expensive option Multitenant is a great new feature within Oracle 12c. Toad makes management of PDB pretty simple but I’m wondering if Oracle should change some behaviors like dropping PDBs before you can create them again in the next release.