\nWhen Oracle Multitenant came out in 12c<\/em>, with pluggable databases, it was easy to draw them as USB sticks that you can plug and unplug to\/from your Container Database (CDB). I don’t like this because it gives the false idea that an unplugged database is detached from the container.
\n<\/p>\n
Containers<\/h3>\n
In the Oracle documentation, the Concept book<\/a>, the description of the multitenant architecture starts with an illustration of a CDB. Let me list what I don’t like with this description:<\/p>\n If I had to show pluggable databases as USB sticks it would be like that: Here CDB$ROOT is a container like the pluggable databases, except that you cannot unplug it. PDB$SEED is a pluggable database but that you don’t unplug. The CDB is a container but do not look like a database. It’s the controlfile and the instance, but there’s no datafiles directly attached to the CDB.<\/p>\n However with this illustration, we can think that an unplugged pluggable database is detached from the CDB, which is wrong.<\/p>\n Here PDB is unplugged, but still pertains to the CDB.<\/p>\n The CDB controlfile still addresses all the PDB datafiles:<\/p>\n The datafiles 12 and 13 are the ones from my unplugged PDB, still known and managed by the CDB.<\/p>\n An unplugged PDB has data, and data should have backups. Who is responsible for the unplugged PDB backups? It’s still the CDB:<\/p>\n The unplugged CDB is not detached at all and the CDB is still referencing its files and is responsible for them. This is very different from an unplugged USB stick which has no link anymore with the hosts it was plugged-in before.<\/p>\n If you wonderwhether it’s good to backup an unplugged PDB with each CDB backup, don’t worry. RMAN knows that it is in a state where it cannot be modified (like read-only tablespaces) and do not backup it each time. Of course, you need to have BACKUP OPTIMIZATION is configured to ON:<\/p>\n From what we have seen, an unplugged PDB is like a closed PDB. There’s a difference through: an unplugged PDB is closed forever. You cannot open it again:<\/p>\n So, if you want to keep the USB stick analogy, unplugged do not mean ‘physically unplugged’ but something like what you should do before removing a USB stick: In summary:<\/p>\n ALTER PLUGGABLE DATABASE … UNPLUG is like the logical ‘eject’ you do to be sure that what you will remove physically was closed forever. Because DROP is the only thing that can be done on an unplugged PDB, SQL Developer do the both when you click on ‘unplug’: The idea to drop it just after the unplug is probably there to prevent the risk to drop it ‘including datafiles’ after it has been plugged into another CDB. Because then it is lost. There’s a last one more difference. A USB stick can be read-only. A plugged PDB cannot. You may want to share a database from a read-only filesystem, like you can do with transportable tablespaces. but you can’t:<\/p>\n The plugged PDB must be opened in read\/write mode at least once:<\/p>\n And only then, it can be opened read-only:<\/p>\n Here is the way I visualize pluggable databases:<\/p>\n
\n![\"CDB](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/GUID-BC43D44A-C696-4216-9D9A-F34FD1F8D709-default.png\")
where the text description starts like:
\nThis graphic depicts a CDB as a cylinder. Inside the CDB is a box labeled “Root (CDB$ROOT).” Plugged into the box is the Seed (CDB$SEED), hrpdb, and salespdb. <\/em><\/p>\n\n
\n![\"CaptureUSBStick\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureUSBStick.png\")
<\/a><\/p>\nUnplugged<\/h3>\n
\nSQL> show pdbs\n \n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 2 PDB$SEED READ ONLY NO\n 4 PDB READ WRITE NO\nSQL> alter pluggable database PDB close;\nPluggable database altered.\n \nSQL> alter pluggable database PDB unplug into '\/tmp\/PDB.xml';\nPluggable database altered.\n \nSQL> show pdbs\n \n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 2 PDB$SEED READ ONLY NO\n 4 PDB MOUNTED\n<\/code><\/pre>\n\nRMAN> report schema;\n \nusing target database control file instead of recovery catalog\nReport of database schema for database with db_unique_name CDB\n \nList of Permanent Datafiles\n===========================\nFile Size(MB) Tablespace RB segs Datafile Name\n---- -------- -------------------- ------- ------------------------\n1 829 SYSTEM YES \/u02\/app\/oracle\/oradata\/CDB\/system01.dbf\n3 1390 SYSAUX NO \/u02\/app\/oracle\/oradata\/CDB\/sysaux01.dbf\n4 4771 UNDOTBS1 YES \/u02\/app\/oracle\/oradata\/CDB\/undotbs01.dbf\n5 270 PDB$SEED:SYSTEM NO \/u02\/app\/oracle\/oradata\/CDB\/pdbseed\/system01.dbf\n6 2 USERS NO \/u02\/app\/oracle\/oradata\/CDB\/users01.dbf\n7 540 PDB$SEED:SYSAUX NO \/u02\/app\/oracle\/oradata\/CDB\/pdbseed\/sysaux01.dbf\n12 280 PDB:SYSTEM NO \/u02\/app\/oracle\/oradata\/CDB\/3969397A986337DCE053B6CDC40AC61C\/datafile\/o1_mf_system_ctcxz29m_.dbf\n13 570 PDB:SYSAUX NO \/u02\/app\/oracle\/oradata\/CDB\/3969397A986337DCE053B6CDC40AC61C\/datafile\/o1_mf_sysaux_ctcxz2bb_.dbf\n<\/code><\/pre>\nBackup<\/h3>\n
\nRMAN> backup database;\n \n...\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00013 name=\/u02\/app\/oracle\/oradata\/CDB\/3969397A986337DCE053B6CDC40AC61C\/datafile\/o1_mf_sysaux_ctcxz2bb_.dbf\ninput datafile file number=00012 name=\/u02\/app\/oracle\/oradata\/CDB\/3969397A986337DCE053B6CDC40AC61C\/datafile\/o1_mf_system_ctcxz29m_.dbf\n...\n<\/code><\/pre>\nBackup optimization<\/h3>\n
RMAN> backup database;\n \nStarting backup at 15-AUG-16\nusing channel ORA_DISK_1\nskipping datafile 12; already backed up 2 time(s)\nskipping datafile 13; already backed up 2 time(s)\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set<\/code><\/pre>\nUnplug and DROP<\/h3>\n
\nSQL> show pdbs\n \n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 2 PDB$SEED READ ONLY NO\n 4 PDB MOUNTED\nSQL> alter pluggable database PDB open;\nalter pluggable database PDB open\n*\nERROR at line 1:\nORA-65086: cannot open\/close the pluggable database\n \nSQL> host oerr ora 65086\n65086, 00000, \"cannot open\/close the pluggable database\"\n\/\/ *Cause: The pluggable database has been unplugged.\n\/\/ *Action: The pluggable database can only be dropped.\n<\/code><\/pre>\n
\n![\"Screenshot](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2016-08-15-10.44.46.png\")
<\/a><\/p>\n
\nALTER PLUGGABLE DATABASE … DROP … KEEP DATAFILES is the physical removal from the CDB<\/p>\n
\n![\"CaptureUNPLUGDROP\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureUNPLUGDROP.png\")
<\/a><\/p>\n
\nHowever, keep in mind that when unplugged and dropped, nobody will backup the PDB datafiles until it is plugged into a new CDB.<\/p>\nRead-Only<\/h3>\n
\nSQL> drop pluggable database PDB keep datafiles;\nPluggable database dropped.\n \nSQL> create pluggable database PDB using '\/tmp\/PDB.xml';\nPluggable database created.\n \nSQL> alter pluggable database PDB open read only;\nalter pluggable database PDB open read only\n*\nERROR at line 1:\nORA-65085: cannot open pluggable database in read-only mode\n<\/code><\/pre>\nSQL> host oerr ora 65085\n65085, 00000, \"cannot open pluggable database in read-only mode\"\n\/\/ *Cause: The pluggable database has been created and not opened.\n\/\/ *Action: The pluggable database needs to be opened in read\/write or\n\/\/ restricted mode first.<\/code><\/pre>\n\nSQL> alter pluggable database PDB open;\nPluggable database altered.\n \nSQL> alter pluggable database PDB close;\nPluggable database altered.\n \nSQL> alter pluggable database PDB open read only;\nPluggable database altered.\n<\/code><\/pre>\nSo what…<\/h3>\n
![\"CaptureCON_ID\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureCON_ID.png\")
<\/a><\/p>\n