ORA-01555 on standby

If you see “ORA-01555: snapshot too old” from Select what is been run on Physical standby, but same select runs on primary just fine, and all relevant init.ora parameters and undo tablespace are exactly identical between primary and standby,- its odd.  But its easy to explain.

First check if DB is below DB it could be bug 10320455.

If not – it most probably just undo retention problem.

On Primary instance Increase undo_retention to value (in seconds) higher than time query runs, and it should fix the problem. (You can increase the parameter on standby too, but it really take effect on primary only)

Why with same undo_retention problem does not show up on primary?

There is underscore parameter _undo_autotune. With it been set to TRUE (default in 11g) Oracle would track oldest running query, and will try to keep undo from time of start of that oldest query, even if its older than undo_retention. If undo space affords.  So it basically overwrites undo_retention and can be much higher than undo_retention. The overwritten/actual undo retention can be seen in tuned_undoretention column in v$undostat.

But primary DB have no vision on what queries are running on standby, so even if long running query running on standby – undo autotune does not kick in, and real undo retention on primay (and so standby) equal to what is set in undo_retention parameter.

So setting undo_retention on Primary to higher value would fix the problem. And note it had to be set on primary. For standby DB undo tablespace is Read-Only so it has no control on it.

Setting undo_retention higher should not hurt much. It can grow your tablespace to what autoextend allows, but if it hits the limit and more undo been generated what is really needed by uncommitted transactions, it will purge old undo what is only needed by selects. It is soft limit.

Check us out on http://avmconsulting.net

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 4 other followers

This entry was posted in Oracle, SQL and tagged , , , , , . Bookmark the permalink.

4 Responses to ORA-01555 on standby

  1. abinas says:

    good info.

  2. Thanks – This helped me to resolve a testing issue today – Had been upping on undo retention on standby and not on prod, couldnt understand why it was still failing, doubled on prod and hey presto! You’re a good lad you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s