According to the Oracle 12cR1 documentation and concepts, it is 100% clear that there can be only one UNDO tablespace in a multitenant architecture and it is at CDB level; thus, a PDB cannot have any UNDO tablespace.
Are we really sure about that? Let’s test it!
First, we need a PDB with few tablespaces:
FRED_PDB> select NAME, OPEN_MODE, CON_ID from v$pdbs ; NAME OPEN_MODE CON_ID -------------------------------------------------- ---------- ---------- FRED_PDB READ WRITE 4 FRED_PDB> select tablespace_name from dba_tablespaces ; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP USERS TBS_DATA 5 rows selected. FRED_PDB> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 FRED_PDB>
There we have an UNDO tablespace named UNDOTBS1 at CDB level and no UNDO at PDB level. Let’s try to create one :
FRED_CDB> create undo tablespace MY_PDB_UNDO ; Tablespace created. FRED_CDB>
It worked! Is the Oracle documentation wrong? Let’s verify this weird successful UNDO tablespace creation:
FRED_PDB> select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%' ; no rows selected FRED_PDB> select tablespace_name from dba_tablespaces TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP USERS TBS_DATA 5 rows selected. FRED_PDB>
No UNDO tablespace has in fact been created even if no error message has been raised by Oracle. Digging in the documentation, this is not a not a bug but a feature. Indeed, it is well specified that:
When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.
Please note that this is the behavior of the 12cR1 release; from my side, I think that this a “not yet feature” and we should see some real UNDO tablespaces in PDBs in the next release(s)!
Discover more about our expertise in Oracle.
9 Comments. Leave new
It might be for backward compatibility, so scripts written for non-pluggable databases will still work for PDBs.
You are right, this is probably another reason of that behavior but I like to believe that a real feature will come in the future releases instead of this “feature”.
Fred – do you feel there is value having a unique undo tablespace at the PDB level? How would that impact redo & archive logging since they are managed at the CDB level & granular activity within this is managed by the PDB_ID?
Would assigning undo at the PDB level eliminate the theorized economy of scale that containers are designed to provide?
Hi Shawn,
About the UNDO itself, my worry is not that much about the theorized economy of scale that containers are designed to provide but more about a restore scenario and the fact that is it not (yet ?) possible to restore a single PDB. Managing one big undo for X containers (then X undo_retention to keep your UNDO entries safe from the others) is very close to manage X undo tablespaces in X PDB located on a shared FS or shared DG; I do not see much difference here.
I am just a DBA and not an architecte from Oracle but I feel that Oracle has not finished the job here with the multitenant architecture. I think that Oracle wanted a feature like the attach/detach of SQLServer and well OK they did it : they created the TRANSPORTABLE DATABASE which is an extension of the TRANSPORTABLE TABLESPACE. That’s good for sure but I know companies where the rule is “code the applications only with TRANSPORTABLE features” then they could already achieve this kind of TRANSPORTABLE DATABASE before 12c so from a client perspective, I do not see a huge improvement here even if it is a cool feature.
Anyway, I like to hope that Oracle will release and improve a lot the multitenant architecture in 12cR2. IMHO, the fact that it is impossible to flashback / point in time restore a PDB is for me a CDB-killer non feature and this is probably why the clients I know that go to 12c are NOT using the CDB feature. If I have 100 applications consolidated in a CDB (thus 100 PDB), I cannot imagine losing data for 99 applications just to point-in-time restore 1.
So well we can consolidate with CDB but all then become more monolithic than before and I am not sure that this is what the client wants. Let’s see how it goes with 12cR2 !
Have a good day,
I know this is an old post but I am wondering if I am missing something, because DBPITR is possible on one or more PDB’s in 12.1. However Oracle needs the UNDO tablespace as it looked back at the target time so restores a copy of it (as well as system and sysaux tbs’s) to an auxiliary location to perform the recovery.
The one caveat here is the rest of the PDB’s or the CDB cannot be flashed back to a point in time beyond the time the PDB has been recovered to once this is done,
Hi Glen,
Thanks for your comment.
In 12cR1, PDBPITR is possible yes but in a way that looks more like a workaround than a feature (this is why I said that it not “easily” doable).
Another point is that it will be officially easily doable in 12cR2 with the LOCAL UNDO feature which will really make a PDB more independent from the CDB. This is an official 12cR2 feature which means that the UNDO in a PDB in 12cR1 was a “not yet” feature and the PDBPITR was not implemented as a feature in 12cR1.
Have a good day,
Fred
Thanks for the reply Fred – I haven’t had the chance to play with 12cR2 yet so that will be one of the first things I try.
It sounds like it will be very similar to MSSQL Server with R2 then, where restoring a “database” to PIT independent of the other “databases” is easily doable.
Hi Glen,
Correct, this was Oracle’s idea behind this CDB – PDB feature.
Have a good day,
Fred
Thank you for sharing this post.