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.
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.
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.
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.
- Bug 14163359: ORA-38881: CANNOT DROP TABLESPACE DUE TO GUARANTEED RESTORE POINTS
- Fallback Strategy: Flashback Pluggable Databases (Aug 30, 2017)