Re: Oracle 12c container database and ORA-1555 snapshot too old error

On Sun, 01 Sep 2013 11:59:16 -0700, zigzagdna wrote:

> Currently I have databases in Oracle 10g and Oracle 11g which sometimes
> give above error despite a large undo tablepsace (24G) and large
> undo_retenetion_time (1 day). My export backups run for 4 hours and then
> fail with this error. I cannot do consistent export backups at all when
> users are running the system. Only when users are off, export backups
> are successful.
>
>
> Now in Oracle 12c multi-tenant database where several pluggable
> databases will share same undo table (because undo and redo have to be
> shared for all the pluggable databases
>
> in a container database), won't this problem get worse because different
> applications connecting to different pluggable databases can give this
> error which will affect everyone?

It is the same database, with the same database files. Redo log files and the UNDO tablespaces (one per instance) are shared. The solution which you are looking for is a container database which spans several instances and each instance uses its own UNDO tablespace and its own log thread. Fortunately, Oracle sells such solution as well.

And yes, propagating the "snapshot too old" across the pluggable databases is a real danger. Diagnosing it will become practically impossible because another database is another universe, with its own SYSTEM and SYSAUX tablespaces. The only way to find out who is using the UNDO tablespace is to use V$UNDOSTAT and V$TRANSACTION from the CDB$ROOT. If one PDB launches a huge transaction needing a gazillion of the UNDO blocks and another transaction on another PDB needs those blocks to reconstruct  the read-consistent version of some block, there will be an ORA-1555. Also, there is a new type of checkpoint, scoped only for the particular PDB. Different PDB's can have different values of LOG_CHECKPOINT_TIMEOUT and do their own checkpoints. I haven't yet had time to investigate whether that will register as a wait event in all PDB's, but my guess is that it will. At any rate, I would advise against even testing PDB feature in development as of yet.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com