Forum Stats

  • 3,852,683 Users
  • 2,264,128 Discussions
  • 7,905,117 Comments

Discussions

Oracle 12c multi-tenanacy and ORA-1555 snapshot too old error

user12021554
user12021554 Member Posts: 106 Blue Ribbon
edited Sep 2, 2013 10:03AM in Multitenant

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?

Answers

  • In Multitenant you will be able to control the undo usage per PDB. I will create a blog post about this type administration.

    Oracle Database 12c

  • One thing you stated you can not do a consistent export but seems like you are passing the SCN parameter for your export that would be considered a consistent export.

  • user12021554
    user12021554 Member Posts: 106 Blue Ribbon

    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 put the url in the last reply why can you don an inconsistent export?

  • user12021554
    user12021554 Member Posts: 106 Blue Ribbon

    I do not see any URL regarding consistent/inconsistent export in your reply, please provide again?

    Thanks.

  • 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.

    http://db12c.blogspot.com/

  • 1013008
    1013008 Member Posts: 1

    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;

This discussion has been closed.