Is There a Way to Delay the APPLY Process?

Problem Description

Some customers want to have the target database delayed to a certain time frame behind the source. This could be for the benefit of having an ETL process running at a certain interval behind the source or you may want the target to be delayed so that you can recover from certain user errors like a table dropped in error.

Solution

While there is no delay parameter per se, there are two options to effectively make the APPLY delay.

The first option is to look into the parameter REDO_READ_METHOD. The default option is REDOFIRST. This will cause the MINE process to read from the on-line redo logs and only go into the archive logs if needed. The option ARCHONLY will only read from archive logs. This will effectively put your target database at least one archive log behind the source database. Depending on how often you switch redo logs this may be enough of a delay.

The second option is using the PAUSE APPLY command. You would need to set up some scripting to make this work. Let's say that you want to have the target database one hour behind the source database. You could write a shell script to look into the target database and see what time the last APPLY was. If the time was less than 1 hour you could have another script call the dbvrep command PAUSE APPLY. When the script looked and saw that the target database was over 1 hour it could make another dbvrep call to RESUME APPLY. While this is not an elegant solution, it is effective.

Dbvisit is working on implementing an enhancement for a delayed parameter. (No release date as of as of November 2014 for this feature).

Chris Lawless November 24, 2014 09:22