Date Published: October 25, 2014

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.


Write a Reply or Comment

Your email address will not be published. Required fields are marked *