Drop a tablespace in a PDB with a Guaranteed Restore Point being active

This was an interesting question I received from Alain Fuhrer a couple of weeks ago: “Why can’t I drop a tablespace in a PDB whit a Guaranteed Restore Point being active?”. It’s important to note that the GRP is not active in the same but in another PDB.

An SR resolved the issue. But in case you face the same situation, here’s the solution.

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Simple test scenario. These are my PDBs:

show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 UPGR 			  READ WRITE NO
	 4 PDB3 			  READ WRITE NO
	 5 HUGO 			  READ WRITE NO

Now I change into the pluggable database UPGR and create a guaranteed restore point:

SQL> alter session set container=UPGR;
Session altered.

SQL> create restore point GRP1_UPGR guarantee flashback database;
Restore point created.

Afterwards I switch into container HUGO and try to drop the TEST tablespace. But I receive an error:

SQL> drop tablespace TEST including contents and datafiles;
drop tablespace TEST including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace TEST on primary database due to guaranteed
restore points.

This looks very strange. Why should I be not allowed to drop a tablespace in PDB-X when I set a Guaranteed Restore Point in PDB-Y.

Thanks to Alain Fuhrer from Die Mobiliar for the hint to the solution: There’s – as often in Multitenant – an underscore parameter allowing a standard task.

The solution: An Underscore

With an underscore parameter set in CDB$ROOT you can drop a tablespace within a PDB when a GRP is set.

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter system set "_allow_drop_ts_with_grp"=true;
System altered.

Once I set this, I can drop my tablespace now:

SQL> alter session set container=HUGO;
Session altered.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

Background Information

Actually this behavior is independent from Oracle Multitenant. It will be the same in a non-CDB. The reason is that dropping a tablespace won’t be reversed by a FLASHBACK DATABASE operation. Therefore, if you set an Guaranteed Restore Point, it is expected that you plan potentially to FLASHBACK to it. And as this flashback won’t reverse a DROP TABLESPACE command, the FLASHBACK operation would fail.

But in a Multitenant environment you’ll have now a different situation. You can have a GRP in PDB-X, and at the same time wanting or needing to drop a tablespace in PDB-Y.

With:

• _allow_drop_ts_with_grp

set, I can now flashback to my initially created restore point:

SQL> flashback pluggable database to restore point GRP1_UPGR;
Flashback complete.

SQL> shutdown

And then from the CDB$ROOT:

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter pluggable database UPGR open resetlogs;
Pluugable database altered.

But be aware:
When you set the GRP in the CDB$ROOT and then drop the tablespace from a PDB, the FLASHBACK operation will succeed. Even your PDB you’ve dropped the tablespace inside will open without any sign of error. But when you check the alert.log and V$DATAFILE you’ll see that the file status is OFFLINE. Obviously the FLASHBACK couldn’t recover the dropped tablespace.

That is the danger of the parameter.

Further Information

• Bug 14163359: ORA-38881: CANNOT DROP TABLESPACE DUE TO GUARANTEED RESTORE POINTS
• Fallback Strategy: Flashback Pluggable Databases (Aug 30, 2017)

–Mike