Aug 29

It’s In The Details

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;

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
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.

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.

bpeasland