Yesterday, I was having a conversation with a peer about using Real Time Apply for his physical standby database. This DBA was adamant that Real Time Apply was the only way to configure his standby. This sparked a conversation about different considerations when using Real Time Apply, which I will share in this blog post.
The primary motivation for using Real Time Apply was so that the time to failover to his physical standby database would be reduced. The DBA was right. If the standby is constantly being updated, then the there is less redo to apply when a failover is performed. I then asked him if he implemented a DG Observer to automate failover. His response was that the Observer was not in the plans. They would manually initiate a failover. This DBA's plans are exactly what I see in many implementations. If there is no Observer, then there is no automated Fast Start Failover (FSFO). I then asked, if there is no FSFO, then does using Real Time Apply make a difference? There was a long pause with no answer so I continued with the next line of question. If the DBA gets a call in the middle of the night that the primary is down and it takes the DBA time to get out of bed, fire up his laptop, manually perform a failover, then does Real Time Apply really save a lot of time in the grand scheme of things? The answer is no. It may take up to an hour or more for all of that to take place. Saving a few minutes by having Real Time Apply doesn't really help here. So this line of thinking brought me to my first two best practices I shared with the DBA.
- If you have automated failover, and you want to failover to the standby as quickly as possible, then make sure you are using Real Time Apply.
- If you do not have automated failover, then consider an Apply Delay.
So I think by asking the DBA a few questions, I managed to convince him that because there is no automated failover, Real Time Apply was not providing much benefit to him. I then asked him to consider implementing an Apply Delay instead. Typically when I implement an Apply Delay, it is either a 4-hour delay or a 12-hour delay, depending on the ability for DBAs to respond to events off hours. The longer the delay, the more time it will take for a failover operation to get the standby database open for business, especially in a system with a high rate of redo generation.
So why the Apply Delay? The apply delay gives us another tool in our recoverability from errors. If someone runs a script that changes a lot of data, and it is discovered that the script caused damage, then if I catch the issue I can stop redo apply on the standby, open it in READ ONLY mode, and see the data as it existed before the change. If my delay is four hours, then I need to stop redo apply before those four hours are up. So this brings up another best practice.
- In the absence of automated failover, implement Apply Delay of sufficient time to give the DBA the opportunity to stop redo apply and potentially help recoverability from errors.
By now, the DBA was seeing the benefit of Real Time Apply when configured for FSFO, and an apply delay when there is no automated failover. I then threw the DBA a curveball by talking about Active Data Guard. One of the primary reasons I may have this optionally licensed product implemented is to set up a reporting database, a database is up kept up-to-date by applying redo while being open as READ ONLY. In this case, I see two situations that may occur.
- If your reporting database needs to be able to generate reports in near-real time, then with Active Data Guard, Real Time Apply is a must.
- Some companies like the idea of the reporting database to show me how the data looked yesterday. In that case, a 24-hour Apply Delay is desirable.
That last option does not need Active Data Guard as we can start managed recovery tonight, let the physical standby get up-to-date, then in the morning open the database as READ ONLY with no redo apply.
Remember that this discussion started with a DBA who thought that Real Time Apply was the only way to go. He thought it was the best approach. For some situations, it is certainly a proper configuration. In other situations, an Apply Delay is the way to go. Give it some thought before deciding one way or the other.