I have an Oracle 12.1.0.2 Multitenant database that I’m trying to remove a PDB from. However, I mistakenly removed the storage from the database server and the PDB cannot access its files. When unplugging the PDB, I get the following error:
SQL> alter pluggable database GOLD2019_08_22_125953 unplug into '/tmp/GOLD2019_08_22_125953.xml';
alter pluggable database GOLD2019_08_22_125953 unplug into '/tmp/GOLD2019_08_22_125953.xml'
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 50277 - see DBWR trace file
ORA-01110: data file 50277:
'/u01/app/oracle/oradata/mt_golden_2019_08_22_125953/data03/datafile_20.dbf'
Well that is unfortunate but to be expected in my case. I mistakenly yanked out the storage for this PDB before I unplugged it. Most of the literature on Oracle’s support site says to restore the PDB from your backup. But this PDB is a clone of production and I do not care to back it up. It is 25+TB and if something goes wrong with it, I remove the PDB and create a new clone of production. No backup is needed except may to save me from my silly mistake of removing the storage before unplugging.
Since I could not unplug the PDB, I attempted to just drop the PDB but I get a different error:
SQL> drop pluggable database GOLD2019_08_22_125953 keep datafiles;One must take these pills with the help of water and viagra uk air to create vacuum. It also reports that almost 90% of lung cancer deaths in men and 80% of lung cancer deaths in men and 80% of lung cancer deaths in men and 80% of get levitra check out for more info lung cancer deaths in women are caused by smoking. Surely ginseng will prove to be more beneficial than simply effects of cialis giving a certain look. They are http://www.heritageihc.com/staff-carroll cheapest online cialis very rare occurrences in fact, but they do happen in spite of all that.
drop pluggable database GOLD2019_08_22_125953 keep datafiles
*
ERROR at line 1:
ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged
Now I feel as though I’m in a Catch-22 situation. I cannot unplug the PDB nor can I drop it.
I’ve seen this error on many occasions and always wished I could just drop the PDB without having to unplug it first. I have no desire to plug this into another CDB. I just want the thing gone for good. And that’s when I realized that it’s in the details. When I read that ORA-65179 error message, I focused on the latter part of it. The PDB is not unplugged first. I’ve read this error message at least 20 times in my work with Multitenant and I missed the critical detail that stopped me from dropping the PDB which I will now highlight below.
ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged
The detail I keep skipping was that I could not KEEP the datafiles. Does that mean I can drop the PDB if I also remove the datafiles?
SQL> drop pluggable database GOLD2019_08_22_125953 including datafiles; Pluggable database dropped.
Sure enough, I could easily drop the PDB. Sometimes, the most interesting things are found in the details if we just slow down a bit and make sure we see everything in front of us instead of jumping to the end.