Here’s a quick Friday tip for you. This is the story of the Magic Secret Pluggable Database 😂
When we first introduced pluggable database back in 12c, there was pretty much a mixed view. Some people didn’t embrace it and are still running non-CDB today, whilst other people embraced it with open arms. Note that those in the former group are going to have to move when they go to 26ai. Those in the latter group worked that out the concept of shared metadata from a root container being available down within a pluggable child is a pretty cool piece of tech. And understandably were a little bit upset that only Oracle and our own dictionary got to take advantage of that!
So in 12.2 we introduced the concept of an application container, effectively giving the root and child pluggable database concept available to you as Oracle customers for your own data dictionaries. These are called an application container root and therefore application pluggables can sit underneath an application container. It is admittedly not a highly used feature, but a very cool one nonetheless. However, what does tend to happen is we see a little bit of anxiety when people discover “secret magic hidden pluggable databases” 🙂 appearing in their data dictionary. Let’s explore why this is the case.
I’ll create an application container root and install an application into it.
SQL> create pluggable database app_root as application container 2 admin user pdbadmin identified by "mypassword" 3 file_name_convert=('/u01/app/oracle/oradata/DB19/pdbseed/', 4 '/u01/app/oracle/oradata/DB19/APP_ROOT/');Pluggable database created.SQL> alter pluggable database APP_ROOT open;Pluggable database altered.SQL> alter session set container = APP_ROOT;Session altered.SQL> alter pluggable database application app1 begin install '1.0';Pluggable database altered.SQL> create user myapp identified by myapp container=all;User created.SQL> grant resource, connect to myapp;Grant succeeded.SQL> create table myapp.mytable sharing=data (c1 number, c2 varchar2(120));Table created.SQL> alter pluggable database application app1 end install;Pluggable database altered.
if you want a more detailed worked example then check our Tim’s post or the standard documentation set.
Now that I’ve built my application root container, I can create a pluggable database that sits underneath that container in the same way that a standard pluggable database would sit under our root container in the Oracle Data Dictionary.
SQL> create pluggable database app_pdb1 2 admin user pdbadmin identified by "mypassword" 3 file_name_convert=('/u01/app/oracle/oradata/DB19/pdbseed/', 4 '/u01/app/oracle/oradata/DB19/APP_PDB1/');Pluggable database created.SQL> alter pluggable database app_pdb1 open;Pluggable database altered.SQL>SQL> alter session set container = CDB$ROOT;Session altered.SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DB19PDB1 READ WRITE NO 4 APP_ROOT READ WRITE NO 5 APP_PDB1 READ WRITE NOSQL>
I’ve now got 4 pluggables in total in my database. When I want to transfer the contents of my application from the root into the pluggable I can run a sync command which gets my pluggables all up to date.
SQL> alter session set container = APP_ROOT;Session altered.SQL> alter session set container = APP_PDB1;Session altered.SQL> alter pluggable database application app1 sync;Pluggable database altered.
But of course we don’t just install applications, we occasionally uninstall them as well. So let me commence an uninstall command now in my application container.
SQL> alter session set container = APP_ROOT;Session altered.SQL> set timing onSQL> alter pluggable database application app1 begin uninstall;Pluggable database altered.Elapsed: 00:00:17.12
You’ll notice that that command seemed to take a long time. This would seem odd given that we’re effectively just turning on a switch, letting the database know we’re about to undergo some metadata changes.
But if I jump back to my container root for the whole database and then query the list of PDBs that I have in my database, you’ll see something very interesting.
SQL> select 2 con_id, 3 name, 4 application_root, 5 application_pdb, 6 application_root_con_id 7 from v$pdbs ; CON_ID NAME APP APP APPLICATION_ROOT_CON_ID---------- ------------------------------ --- --- ----------------------- 2 PDB$SEED NO NO 3 DB19PDB1 NO NO 4 APP_ROOT YES NO 5 APP_PDB1 NO YES 4 7 F3694151257_3_1 YES YES 4
Lo and behold, a system-named pluggable database has been created for us. You can see that the APPLICATION_ROOT_CONID is set to 4, so it appears to be a pluggable that sits underneath my application container.
What is this mysterious pluggable? Well, you can find that straight Out of the documentation.
Destructive changes to application objects are allowed during application uninstallation. Applications running in an application PDB continue to function during uninstallation and after the application is uninstalled from the application root. The application can continue to function in the application PDB because the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement creates a clone of the application root called an application root clone. An application root clone serves as a metadata repository for old versions of application objects, so that application PDBs that have not been synchronized with latest version of the application can continue to function. Because the clone is created while the application PDB is open, local undo must be configured at the CDB level before an application can be uninstalled.
So there is no need to panic and the database will clean up this pluggable at the appropriate time once you’ve finished uninstalling or if you choose to drop a pluggable etc. But just be aware that temporarily you will have additional pluggables floating around if you are doing uninstallation of applications using the application container root technology.
Happy plugging! 😂
Leave a Reply