In continuation of Part 1 of our recent blog entry on Oracle GoldenGate 12.2 processes and their start and restart capabilities on Windows, once again I will be working with Mack Bell of the Oracle GoldenGate Product Management team to describe the behaviors and requirements needed to get your GoldenGate processes up and running.
In our first entry, we talked about how to install the Manager process as a Windows service and how to set GoldenGate process to start and restart automatically, but we also mentioned a scenario and the reason why processes might not automatically restart when you would expect them to.
For our Part 2 entry of this topic, we will show you two additional Manager features that can help delay the startup of GoldenGate processes in order to allow time for the database to come online, thus hopefully avoiding the situation where processes would need to restart due to database unavailability.
First, a quick recap. You can use the AUTOSTART and AUTORESTART parameters in Manager but depending on the last good checkpoint of a GoldenGate process, if the database that the Extract or Replicat will be connecting to is not yet online, the AUTORESTART feature may not try to restart the process.
So what can be done that can give an adequate amount of time to allow the database to become available, especially on server startup or reboot, before attempting to start a GoldenGate process that connects to it?
This parameter can be added to the Manager parameter file and set with a given number of minutes that instructs the Manager to delay honoring the AUTOSTART feature, thus allowing time for the SQL Server instance and database to come online before startup of other processes that are set with the AUTOSTART parameter.
For example, let’s say your source or target database normally only takes a few minutes to come online once the server is up and running. With BOOTDELAYMINUTES, we can set the minutes to some average time value that we would expect for SQL Server and the databases to all be up and running, such as 5 minutes.
GGSCI>edit params mgr
One very important note is that BOOTDELAYMINUTES must be the first parameter in the list of Manager parameters.
At this point, you can restart your Windows system and then monitor the delay that the Manager service will have before starting the other GoldenGate processes. Hopefully by this time the SQL Server instance and database will all be online.
Another option that is part of the INSTALL program when registering the Manager as a service, is to add the WAITFORSERVICE option which will create a dependency for the Manager service that must be satisfied before the Manager service will start.
You can use the SQL Server instance Service Name (don’t use the Display Name), such as “MSSQL$SQL2008R2”, as a dependency and that way, the Manager will not start until the SQL Server instance has started.
In Part 1 of this article we already installed the Manager as a service, and in order to add the new WAITFORSERVICE option, we need to uninstall and reinstall the Manager service, adding the new option.
GGSCI>shell install deleteservice
GGSCI>shell install addservice waitforservice MSSQL$SQL2008R2
You can check the Manager’s service properties and see now that it has a dependency on the SQL Server instance, and the next time the server starts, the Manager will be delayed until the SQL Server instance has started.
Although there isn’t a specific method via GoldenGate to only start a process when the source or target database is online, by using either BOOTDELAYMINUTES or WAITFORSERVICE, or the combination of both, you can delay the timing of GoldenGate process startup in order to allow both the SQL Server instance and database to come online, thus avoiding the scenario presented in Part 1, wherein the GoldenGate process can’t start initially due to an offline database and will not attempt a restart since the last known state of running was not ABENDED.