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?
To clarify I use parameter consistent=Y in export and it gives ORA-1555 error unless no application users are on the system. When application users are using the system export does not work with CONSISTENT=Y or without it.
Could never figure out how to get rid of this error duing export other than doing export during monthly maintenance when users are off the system. My database size is only 200GB.
I will be eaglery looking for your blog on this topic. What is URL for your blogs.
I did more checking into the UNDO in multitenant environment and the UNDO can only be create in CDB$ROOT and I do not see that there is anyway to control the amount of undo segments that each PDB can use. I can see that the TEMP can be created for each PDB or can share from the CDB$ROOT.
I feeling is as we would do the same in a non-CDB we will need to test and adjust the UNDO as needed to handle the required UNDO segments.
I will try to do some testing with the UNDO in a multitenant 12C database.
My blog is below where I have several post on Multitenant Configuration.
As most of the views in 12c, v$undostat contains a con_id column showing the ID of the container to which the data pertains.
This can be a resource to check the undo space required for the current workload. The view contains data for 4 day cycle.
Also, for temporary tables, in 12c we can set TEMP_UNDO_ENABLED to TRUE which will separate undo for temporary tables from undo for persistent ones.
The benefits are immediate:
- reducing the size of redo log;
- less undo in the UNDO tbs;