Avoiding ORA-01555 errors in Oracle Active Dataguard

Queries on the standby database rely on undo generated on the primary to rollback uncommitted changes. The undo_retention period set at the primary will determine how soon undo can be overwritten and thus will also determine the length of the timing window that a standby query may avoid running into an ORA-01555 error. The occurrence of ORA-01555 is rare when the active standby is operating in realtime apply mode, because the standby recovery will keep pace with the primary.

An ORA-01555 is more likely to be encountered if real-time apply is not enabled, or in situations where a network disconnect or standby outage has resulted an archive log gap. In these cases the elapsed time to complete a redo log file at the primary database can be longer than the time spent by recovery to apply it to the standby database. Lets use as an example the case where it takes the primary 10
minutes to generate 1000MB of redo and takes a standby only 30 seconds to apply (33MB/sec apply rate). If the undo_retention on the primary is set to 10 minutes, the standby has effectively reduced the retention period to 30 seconds for the standby query workload.  If a standby query runs for more than 30 seconds, it is
likely to run into the ORA-01555 error because recovery has already applied more redo that overwrites the rollback segment. If active standby queries experience ORA-01555 errors, first make sure the standby is in real-time apply mode, and if so, also increase the undo_retention period on the primary database