Closure
Closure
Home » Databases » Oracle Database » How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem

How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem

How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem
, , , , , , , Leave a comment
How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem
Latest posts by Stratos Matzouranis (see all)

When we synchronize tables through Oracle GoldenGate can either to stop it extract of the source to write trail files to the target in the tables, either to stop it replicat of the target to apply the trail files. So in either case it would help us to get emails whenever replication is a few minutes behind.

So in this article we will see step by step how we create this mechanism through SQL Server Agent Job.

The email we will receive will be of the following format informing us of any lag from the source or apply lag of the trail files:

GoldenGate SQL Server replication warning at: SMATZOURANISLP

DESCRIPTION: At Mar  7 2023  3:58PM on SQL Server " SMATZOURANISLP", GoldenGate replication warning for group: "INI_REP", lag from the source is -2720 minutes, apply lag is 0 minutes!!!!

The footsteps

First we should have enabled Database Mail in SQL Server. We have seen how this is done in this article.

The Transact SQL Script

With the following script that I have prepared, a SQL Server Agent Job will be created which will run every 15 minutes and check the CHECKPOINTTABLE of GoldenGate. If the prices audits_ts (lag from source) or last_update_ts (apply lag) exceed the threshold we set in the code will send email with the profile we set.

The only information we need to change on a case-by-case basis is the following defined as bold:

set @bod = (select top 1 ''DESCRIPTION: At '' + cast(getdate() as nvarchar) +'' on SQL Server "'' + @@servername +''", GoldenGate replication warning for group: "'' +group_name + ''", lag from the source is ''+cast(datediff(minute,getdate(),audit_ts) as nvarchar) +'' minutes and apply lag is ''+cast(datediff(minute,getdate(),last_update_ts) as nvarchar)+ '' minutes !!!!'' 
from dbo.CHECKPOINTTABLE where audit_ts is not null and (last_update_ts < dateadd(mi,-5,getdate()) or cast(audit_ts as datetime2) < dateadd(mi,-10,getdate())) order by last_update_ts);   <---threshold is set to 5 min for both source lag and apply lag
IF  (select count(*) from dbo.CHECKPOINTTABLE where audit_ts is not null and (last_update_ts < dateadd(mi,-5,getdate()) or cast(audit_ts as datetime2) < dateadd(mi,-10,getdate()))) > 0  <---threshold is set to 5 min for both source lag and apply lag
begin

	exec msdb.dbo.sp_send_dbmail
	@profile_name = ''gmail'',               <---- The profile name we made to send email in the first step
	@recipients = ''info@dataplatform.gr'',  <----- And the recipients of the email
	@subject = @sub,
	@body = @bod;
end;

', 
              @database_name=N'gg_database',    <---- Set the database name contains GoldenGate's CHECKPOINTTABLE

After making the above modifications to the following script, all that remains is to run in a new query window on the instance:

USE [msdb]
GO

/****** Object:  Job [Alert for GoldenGate replication]    Script Date: 07/03/2023 02:05:43 μμ ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 07/03/2023 02:05:43 μμ ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert for GoldenGate replication', 
              @enabled=1, 
              @notify_level_eventlog=0, 
              @notify_level_email=0, 
              @notify_level_netsend=0, 
              @notify_level_page=0, 
              @delete_level=0, 
              @description=N'No description available.', 
              @category_name=N'[Uncategorized (Local)]', 
              @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step1]    Script Date: 07/03/2023 02:05:44 μμ ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
              @step_id=1, 
              @cmdexec_success_code=0, 
              @on_success_action=1, 
              @on_success_step_id=0, 
              @on_fail_action=2, 
              @on_fail_step_id=0, 
              @retry_attempts=0, 
              @retry_interval=0, 
              @os_run_priority=0, @subsystem=N'TSQL', 
              @command=N'set ansi_warnings on
go
set ansi_padding on
go
set quoted_identifier on
go
declare 
@sub nvarchar(max),
@bod nvarchar(max)
set @sub = (select ''GoldenGate SQL Server replication warning at: '' + @@servername);
set @bod = (select top 1 ''DESCRIPTION: At '' + cast(getdate() as nvarchar) +'' on SQL Server "'' + @@servername +''", GoldenGate replication warning for group: "'' +group_name + ''", lag from the source is ''+cast(datediff(minute,getdate(),cast(audit_ts as datetime2)) as nvarchar) +'' minutes and apply lag is ''+cast(datediff(minute,getdate(),last_update_ts) as nvarchar)+ '' minutes !!!!'' 
from dbo.CHECKPOINTTABLE where audit_ts is not null and (last_update_ts < dateadd(mi,-5,getdate()) or cast(audit_ts as datetime2) < dateadd(mi,-10,getdate())) order by last_update_ts);
IF  (select count(*) from dbo.CHECKPOINTTABLE where audit_ts is not null and (last_update_ts < dateadd(mi,-5,getdate()) or cast(audit_ts as datetime2) < dateadd(mi,-10,getdate()))) > 0
begin

	exec msdb.dbo.sp_send_dbmail
	@profile_name = ''gmail'',
	@recipients = ''info@dataplatform.gr'',
	@subject = @sub,
	@body = @bod;
end;

', 
              @database_name=N'gg_database', 
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily', 
              @enabled=1, 
              @freq_type=4, 
              @freq_interval=1, 
              @freq_subday_type=4, 
              @freq_subday_interval=15, 
              @freq_relative_interval=0, 
              @freq_recurrence_factor=0, 
              @active_start_date=20211029, 
              @active_end_date=99991231, 
              @active_start_time=0, 
              @active_end_time=235959, 
              @schedule_uid=N'df24cb48-55e9-436a-a0de-56d4dd5139a3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

When we run it we will see in the Object Explorer of SQL Server Management Studio that it has created a new Job in the SQL Server Agent:

How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem
01

After a while we will see that it sent itself without us doing anything an email informing us that the synchronization is a few minutes behind the source trail files:

How can we get emails whenever syncing to SQL Server via Oracle GoldenGate is having problem
02
Share it

Leave a reply