Applies to:
Oracle GoldenGate – Version: 4.0.0 – Release: 4.0.0
Information in this document applies to any platform.
Solution
PROBLEM
Some user accounts in the database are setup with session timeout value for idle processes. If the GoldenGate process is connected to a database that exceeds the set timeout, the process will be terminated.
SOLUTION SUMMARY
Use SQLEXEC at the root level to execute a query based on an interval of time. This technique will prevent our process from becoming idle.
EXPLANATION OF SOLUTION
The method used for setting this up is a SQLEXEC statement in the parameter file. The SQLEXEC for the heartbeat can be put in the extract or replicat parameter file anywhere, but with the map statement.
Syntax for Oracle: SQLEXEC “select ‘x’ from dual” EVERY 30 SECONDS
Valid intervals are: SECONDS, MINUTES, HOURS, DAYS.
================================================================================
Sample Replicat: (SQL Server)
================================================================================
REPLICAT REPCUST
discardfile c:\ggs\dirout\repcust.dsc, append, megabytes 10
sourcedefs c:\ggs\dirdef\source.def
TARGETDB GGSDB, USERID ggsuser, PASSWORD *****
SQLEXEC “select ‘x’ ” EVERY 2 MINUTES
map source.customer, target target.customer;
=================================================================
Sample Extract: (ORACLE)
=================================================================
EXTRACT EXTCUST
USERID ggsuser, PASSWORD *****
SQLEXEC “select ‘x’ from dual” EVERY 2 MINUTES
RMTHOST houston, MGRPORT 7809
RMTTRAIL C:\ggs\dirdat\et
TABLE source.CUSTOMER;
Related PostsGGSCI (www.ads1st.com) 3> view param r11g
extract R11G
USERID ggs, PASSWORD ggs
dynamicresolution
NOPASSTHRU
sqlexec “select ‘x’ from dual” every 2 minutes
rmthost 192.168.0.221,mgrport 8409,compress
rmttrail d:\tools\ggs\dirdat\wi
numfiles 3000
TABLE ggs.test ;GGSCI (www.ads1st.com) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EINI_1 00:00:00 00:00:00
EXTRACT RUNNING PINI_1 00:00:00 00:00:01
EXTRACT RUNNING R11G 00:00:00 00:00:00
REPLICAT RUNNING RINI_1 00:00:00 00:00:06GGSCI (www.ads1st.com) 6> view report r11g
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:57:20Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-07-20 13:14:13
***********************************************************************Operating System Version:
Linux
Version #1 SMP Thu Sep 3 02:16:47 EDT 2009, Release 2.6.18-164.el5
Node: www.ads1st.com
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimitedProcess id: 3997
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
extract R11G
USERID ggs, PASSWORD ***
dynamicresolution
NOPASSTHRU
sqlexec “select ‘x’ from dual” every 2 minutes
rmthost 192.168.0.221,mgrport 8409,compress
rmttrail d:\tools\ggs\dirdat\wi
numfiles 3000
TABLE ggs.test ;CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 2G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 2.90G
CACHESIZEMAX (strict force to disk): 2.66GDatabase Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – ProductionDatabase Language and Character Set:
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “ZHS16GBK”Warning: NLS_LANG is not set. Please refer to user manual for more information.
2010-07-20 13:14:19 GGS INFO 406 Socket buffer size set to 27985 (flush size 27985).
2010-07-20 13:14:24 GGS INFO 112 Recovery initialization completed for target file d:\tools\ggs\dirdat\wi000003, at RBA 110
2.2010-07-20 13:14:24 GGS INFO Z0-05M Output file d:\tools\ggs\dirdat\wi is using format RELEASE 10.4.
2010-07-20 13:14:24 GGS INFO 224 Rolling over remote file d:\tools\ggs\dirdat\wi000004.
2010-07-20 13:14:24 GGS INFO 112 Recovery completed for target file d:\tools\ggs\dirdat\wi000004, at RBA 459.
2010-07-20 13:14:24 GGS INFO 112 Recovery completed for all targets.
***********************************************************************
** Run Time Messages **
***********************************************************************Opened trail file /oracle/ggs/dirdat/ds000019 at 2010-07-20 13:14:24
Switching to next trail file /oracle/ggs/dirdat/ds000020 at 2010-07-20 13:14:24 due to EOF, with current RBA 1523
Opened trail file /oracle/ggs/dirdat/ds000020 at 2010-07-20 13:14:24
2010-07-20 13:16:14 GGS INFO Z0-01O SQL statement executed successfully on interval.2010-07-20 13:18:15 GGS INFO Z0-01O SQL statement executed successfully on interval.