Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 21, 2020 - 5:47 am UTC

Last updated: July 05, 2022 - 12:21 am UTC

Version: 19.6c

Viewed 1000+ times

You Asked

Hi all

We offload reading/long queries to physical dataguard standby

We also know that a fact that the physical standby is applying redo stream generated from primary

It also does seems that
- uncommitted undo generated from primary DMLs is not required in the standby
- unexpired undo entries use for long running queries in primary is not required in standby

q1) Does standby need to cater space / apply undo changes generated in Primary ?

If yes, can we still set a different / longer undo_retention in standby ?

Thank you

and Connor said...

We mostly ignore the "undo_retention" setting in the default setup for the database.

Originally when we built automatic undo, you might set (say) undo_retention to 900, and it was just a waste of resource for us to aggressively go through the undo tablespace and purge out stuff older than 900 seconds. It makes no sense to do so if there is free space there available to use. So we stopped doing that. Now we just use "all" the undo tablespace and recycle extents as required. (If we can't find extents to recycle, we'll either autoextend or give an error that we need more space).

So the undo retention on your standby is a factor of the size of your undo tablespace, which of course, is controlled by its size on the primary. If you're getting ora-1555 on your standby, you'll need a larger (or autoextensible) undo tablespace on the primary.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

standby undo retention

Mayur Chandankar, June 23, 2022 - 6:05 am UTC

So we can conclude that we can set different undo retention on primary and standby database by increasing the size undo tablespace on primary and standby database with different size. like for primary 100GB and for standby 200gb ? please make me correct if I am wrong?

we have requirement for same so i am asking for that ?
please suggest
Connor McDonald
July 05, 2022 - 12:21 am UTC

Given that a standby is a physical replica of a primary, then the undo tablespaces will be the same size.

As I said intially: "you'll need a larger (or autoextensible) undo tablespace on the primary."

standby undo retention

Mayur Chandankar, June 23, 2022 - 6:07 am UTC

So we can conclude that we cannot set different undo retention on primary and standby database by increasing the size undo tablespace on primary and standby database with different size. like for primary 100GB and for standby 200gb ? please make me correct if I am wrong?

we have requirement for same so i am asking for that ?
please suggest

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database