Best Practices from Oracle Development's A‑Team
-
Middleware / Data Integration
October 29, 2018
Oracle GoldenGate: Monitoring "Things" That Affect Replication Stability
Introduction
There have been a lot of escalations to A-Team where Oracle GoldenGate outages could have been avoided if System and Database Administrators had been properly monitoring their environments. In this article we're going to discuss various areas that should be monitored regularly to avoid degradations in performance and unscheduled outages.
The concepts, scripts, and information presented in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.
Main Article
The following discussion assumes the reader has a good understanding of system and database administration, Oracle GoldenGate Classic release 12.3 architecture, and the bash scripting language.
Monitoring Functionality Within Oracle GoldenGate
Oracle GoldenGate may be configured to monitor its environment and report error and performance degradation conditions.
Log Errors to SYSLOG
In Oracle GoldenGate 12.3, event logging to the system logs (syslog) is turned off by default. Logging functionality is controlled via the settings within the file $OGG_HOME/diretc/ogg-ggserr.xml (NOTE: In Oracle GoldenGate Microservices Architecure, this file is located in the $ORACLE_HOME/lib/utl/logging directory). To enable error logging to the syslog, edit the file and uncomment the section ggserr.Syslog as show below:
<!-- !- The ggserr.Syslog adapter will write all messages to the local !- system log. !- --> <appender name="ggserr.Syslog" class="SyslogAppender"> <layout class="PatternLayout"> <param name="Pattern" value="%-19.19d{%Y-%m-%d %H:%M:%S} %m%n"/> </layout> </appender> <!-- ! --> Then add the following before the line containing the text </configuration> to activate logging of warning and error messages to SYSLOG:
<logger name="ggserr.log" additivity="false">
<appender-ref name="ggserr.Syslog"/>
<level value="warn"/>
</logger>
Stop and start the Oracle GoldenGate Manager to activate error logging to the syslog:
[oracle@ora12nomt log]$ sudo tail -f messages
Oct 23 11:58:52 ora12nomt extract[20452]: 2018-10-23 11:58:52 ERROR OGG-01044 Oracle GoldenGate Capture for Oracle, etpc.prm: The trail './dirdat/ett' is not assigned to extract 'ETPC'. Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL ./dirdat/ett, EXTRACT ETPC".
Oct 23 11:58:52 ora12nomt extract[20452]: 2018-10-23 11:58:52 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, etpc.prm: PROCESS ABENDING.
Report Lag
If you monitor for lag, you must use the Oracle GoldenGate Integrated Heartbeat. There is an A-Team blog on this here.
Along with Integrated Heartbeat, the Oracle GoldenGate Manager may be configured to monitor and report lag conditions via three optional parameters LAGREPORT, LAGINFO, and LAGCRITICAL. Before setting these options, it is imperative that a realistic enterprise policy for replication lag be created that specifically states (1) acceptable replication lag and (2) the point at which replication lag becomes an error condition.
Notice we used the term "realistic" to describe the replication lag policy. This is the key as everyone wants their data replicated as quickly as possible; however, is it "realistic" to set a policy that lag exceeding 2 seconds for a replication stream between Tokyo and Manhattan constitutes a critical error? Not really.
Below is an example of enabling lag reporting within the Oracle GoldenGate Manager:
--
-- Listener on this port
port 19000
--
-- Cleanup consumed OGG Trails
purgeoldextracts ./dirdat/*, usecheckpoints
--
-- Allow connections to the OGG SERVER from the denoted
-- source system only
accessrule, prog server, ipaddr 192.168.120.44, allow, pri 1
--
-- Check for lag every 30 minutes
LAGREPORTMINUTES 30
--
-- The acceptable lag point
LAGINFOMINUTES 30
--
-- The point at which lag becomes an error condition
LAGCRITICALMINUTES 60
With the above settings, an error message will be written to SYSLOG for any Oracle GoldenGate process with 60, or more, minutes of backlog (lag):
Oct 23 12:53:19 ora12nomt mgr[24356]: 2018-10-23 12:53:19 WARNING OGG-00947 Oracle GoldenGate Manager for Oracle, mgr.prm: Lag for EXTRACT ETPC is 00:62:09 (checkpoint updated 00:00:03 ago).
Check for Down Groups
Set the Oracle GoldenGate Manager to check and report on Groups in the ABEND state that have not been restarted by setting the parameter DOWNREPORT:
--
-- Every 60 minutes, check for Groups that are not in the RUNNING state
DOWNREPORTMINUTES 60
Every 60 minutes Manager will check the Oracle GoldenGate instance and log an error to SYSLOG for any in the ABEND state (Groups in the STOPPED state will not be reported to SYSLOG):
Oct 23 13:18:33 ora12nomt mgr[25485]: 2018-10-23 13:18:33 WARNING OGG-00946 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT P12KVM abended.
Server Resources
Server resources can adversely affect Oracle GoldenGate; so we should be monitoring disk space, free memory, and network latency between the source and target Oracle GoldenGate servers. The following bash script, which I named OGGEnvCheck.sh, will write a customized error message to SYSLOG whenever any disk exceeds 75% capacity, free memory is less than 1GB, and average round trip time for ping exceeds 10ms. (Note: ping will not work between servers behind a firewall.)
#!/bin/bash ## Configurable by user # Target OGG hosts, ipaddress or DNS name to be used by ping command. readonly TARGET_HOSTS=("ora12kvm" "ora11kvm" "c3p0") ## DO NOT CHANGE THESE VALUES readonly SCRIPT_NAME=$(basename $0) readonly MAX_DISK_USED="75" ## % disk used reported from df -h command readonly MAX_RTT="10" ## average rtt time from ping readonly MIN_FREE_MEM="1000" ## available memory from free -m command function err() { logger -p user.error -t $SCRIPT_NAME "$@" } function check_disk_space { df -h | tail -n+2 | while read fs size used avail usedpct mountp ; do upct=${usedpct::-1} if [[ "$upct" -gt "$MAX_DISK_USED" ]] && [[ "$mountp" != "/boot" ]]; then err "OGG-99999: Mount point \"$mountp\" is $usedpct full." fi done } function check_free_mem { free -m | tail -n-2 | while read mem total used mfree shared cache avail ; do mem=${mem::-1} if [[ "$mem" =~ "Mem" ]]; then if [[ "$avail" -lt "$MIN_FREE_MEM" ]]; then err "OGG-99999: Low available memory: total mem: $total Mb, available memory: $avail Mb." fi fi if [[ "$mem" =~ "Swap" ]]; then if [[ "$mfree" -lt "$MIN_FREE_MEM" ]]; then err "OGG-99999: Low available swap: total swap: $total Mb, used: $used Mb, free: $mfree Mb." fi fi done } function ping_targets { for i in "${TARGET_HOSTS[@]}"; do ping $i -c 10 | tail -n-2 | while read line ; do if [[ "$line" =~ "100% packet loss" ]]; then err "OGG-99999: OGG target destination, $i, is unreachable." fi if [[ "$line" =~ "rtt min/avg/max/mdev" ]]; then # strip eveyrthing before and including the "=" character l=${line#*=} # strip the last 3 characters from the line l=${l::-3} # split the line into an array of values IFS='/' read -ra RTT <<< "$l" # array value "1" is the average rtt. # cannot use floating point numbers in bash math, so strip # everything after the decimal point. v=${RTT[1]} v="${v%%.*}" if [[ "$v" -gt "$MAX_RTT" ]]; then err "OGG-99999: Average network latency to OGG target destination, $i, is ${RTT[1]} ms." fi fi done done } ## MAIN check_disk_space check_free_mem ping_targets An example of the output written by this script to SYSLOG is below:
Oct 23 13:52:42 ora12nomt OGGEnvCheck.sh: OGG-99999: Mount point "/" is 78% full.
Oct 23 13:53:19 ora12nomt OGGEnvCheck.sh: OGG-99999: OGG target destination, c3p0, is unreachable.
Database Resources
For the Oracle Database, we should regularly check archive log space consumption as fully consumed archive space will not only have a negative impact upon the replication stream; it will bring the database to a grinding halt. The Oracle Database query below, which I named OGGEnvCheck.sql, will log an error when consumed archive log space exceeds 75% of capacity.
-- Create a location for the output (this directory must exist) CREATE OR REPLACE DIRECTORY MYERRLOC AS '/tmp'; DECLARE v_ArchiveUsed NUMBER(3); v_ReadSCN1 NUMBER(10); v_ReadSCN2 NUMBER(10); v_GapSeq VARCHAR(15); v_OutputString VARCHAR(150); v_OutLoc VARCHAR2(100) := 'MYERRLOC'; fHandle UTL_FILE.FILE_TYPE; BEGIN -- Open the "log file" fHandle := UTL_FILE.FOPEN(v_OutLoc, 'OGGEnvCheckOut', 'w'); -- Check archivelog space. Report error if used space is more than 75%.. SELECT percent_space_used into v_ArchiveUsed FROM V$RECOVERY_AREA_USAGE where file_type = 'ARCHIVED LOG'; IF v_ArchiveUsed > 75 THEN v_OutputString := 'OGG-99999: Archive Log space used is '; v_OutputString := CONCAT (v_OutputString, v_ArchiveUsed); v_OutputString := CONCAT (v_OutputString, '%.'); UTL_FILE.PUT(fHandle, v_OutputString); END IF; -- Close the "log file" and exit UTL_FILE.FCLOSE(fHandle); END; /
The file created by this script, /tmp/OGGEnvCheckOut, will be processed by a bash script we'll discuss in a bit.
If Downstream Capture is used in the replication stream, we also need to check the database to ensure Redo Transport is working properly and for gaps in the archive logs at the downstream database. The queries below, from the file OGGDBTransportCheck.sql, query the gv$logmnr_session Database View to ensure Integrated Extract has Redo data to process and obtain Archive Log information to determine if gaps exist (the Downstream Mining Extract will hang if gaps exist in the archive logs).
-- Check Redo Transport and archive log gaps -- -- Redo transport -- Spool the output to files we'll parse later SPOOL /tmp/readscn_a.out REPLACE SELECT session_name||' '||read_scn FROM gv$logmnr_session ORDER BY session_name ASC; SPOOL OFF -- Sleep 5 seconds host sleep 5 -- SPOOL /tmp/readscn_b.out REPLACE SELECT session_name||' '||read_scn FROM gv$logmnr_session ORDER BY session_name ASC; SPOOL OFF -- -- Check for archive log gap SPOOL /tmp/gapchk.out REPLACE SELECT consumer_name||' '||thread#||' '||name PREVIOUS_ARCHIVELOG FROM (SELECT consumer_name,thread#,name,sequence# ids, LEAD (sequence#, 1, NULL) OVER (ORDER BY consumer_name,thread#,sequence# ASC) lead_no FROM DBA_REGISTERED_ARCHIVED_LOG where purgeable='NO'); SPOOL OFF -- -- END of Downstream Capture checks
When this script is run, two files will be created in the /tmp directory that contain the read_scn value of all Mining Integrated Extracts:
[oracle@r2d2 tmp]$ cat readscn_a.out
SESSION_NAME||''||READ_SCN
-------------------------------------------------------------------------
OGG$CAP_ETPC 2769449
OGG$CAP_ETST 2769449
[oracle@r2d2 tmp]$ cat readscn_b.out
SESSION_NAME||''||READ_SCN
-------------------------------------------------------------------------
OGG$CAP_ETPC 2769459
OGG$CAP_ETST 2769459
This script also creates a file in the /tmp directory that contains information about the Archive Logs required for each Mining Integrated Extract.
[oracle@r2d2 tmp]$ cat gapchk.out
PREVIOUS_ARCHIVELOG
--------------------------------------------------------------------------------
OGG$CAP_ETST 1 /u01/oracle/app/c3p0_standby/1_71_911991262.dbf
OGG$CAP_ETST 1 /u01/oracle/app/c3p0_standby/1_72_911991262.dbf
OGG$CAP_ETST 1 /u01/oracle/app/c3p0_standby/1_73_911991262.dbf
OGG$CAP_ETST 1 /u01/oracle/app/c3p0_standby/1_74_911991262.dbf
OGG$CAP_ETPC 1 /u01/oracle/app/c3p0_standby/1_71_911991262.dbf
OGG$CAP_ETPC 1 /u01/oracle/app/c3p0_standby/1_72_911991262.dbf
OGG$CAP_ETPC 1 /u01/oracle/app/c3p0_standby/1_73_911991262.dbf
OGG$CAP_ETPC 1 /u01/oracle/app/c3p0_standby/1_74_911991262.dbf
We then use a bash script, OGGEnvReportDB.sh, to process the data retrieved from the Oracle Database and log any error conditions to SYSLOG:
#!/bin/bash ## Report any issues found during the database checks readonly ERRFILE="/tmp/OGGEnvCheckOut" readonly ARCFILE="/tmp/gapchk.out" readonly SCNFILEA="/tmp/readscn_a.out" readonly SCNFILEB="/tmp/readscn_b.out" readonly SCRIPT_NAME=$(basename $0) function err() { logger -p user.error -t $SCRIPT_NAME "$@" } function readErrFile() { ## Check the error file exists if [[ -f $ERRFILE ]]; then ## Read the file while read LINE; do if [[ "$LINE" =~ "OGG" ]]; then err $LINE fi done < $ERRFILE ## Clear out the file echo " " > $ERRFILE fi } function chkReadSCN() { mynamea='' mynameb='' myscna='' myscnb='' ## Check the output files exist if [[ -f $SCNFILEA ]] && [[ -f $SCNFILEB ]]; then ## Read the file data into arrays while read LINEA; do if [[ "$LINEA" =~ "OGG" ]]; then l=${LINEA% *} mynamea+=("$l") l=${LINEA#* } myscna+=("$l") fi done < $SCNFILEA while read LINEB; do if [[ "$LINEB" =~ "OGG" ]]; then l=${LINEB% *} mynameb+=("$l") l=${LINEB#* } myscnb+=("$l") fi done < $SCNFILEB #Process the array data and check if the read_scn is incrementing for ((i=1; i< ${#mynamea[@]};i++)); do namea=${mynamea[i]} scna=${myscna[i]} nameb=${mynameb[i]} scnb=${myscnb[i]} if [[ "$namea" = "$nameb" ]]; then if [[ "$scna" -ge "$scnb" ]]; then err "OGG-99999: Extract $namea is waiting for Redo. Check the upstream Redo Transport." fi fi done ## Clear out the files echo " " > $SCNFILEA echo " " > $SCNFILEB fi } function chkArcGap() { lastproc='' lastthread='' lastarch='0' curproc='' curthread='' curarch='' LINEARRAY='' ## Check the archive gap results file exists if [[ -f $ARCFILE ]]; then ## Read the file while read LINE; do if [[ "$LINE" =~ "OGG" ]]; then ## Split the line into its fields curproc=$(echo $LINE | cut -d' ' -f1) curthread=$(echo $LINE | cut -d' ' -f2) curarch=$(echo $LINE | cut -d' ' -f3) # Strip the last underscore from the archive curarch=${curarch%_*} # Strip the leading underscores from the archive # This gives the archive sequence umber curarch=${curarch#*_} curarch=${curarch#*_} # If the same reader and thread, check for an archive gap if [[ "$lastproc" = "$curproc" ]] && [[ "$lastthread" = "$curthread" ]]; then if [[ "$lastarch" -gt "0" ]]; then archdiff=$(echo $(( curarch - lastarch ))) if [[ "$archdiff" -gt "1" ]]; then err "OGG-99999: There is an archive log gap. Please check redo transport." fi fi lastarch=$(echo $curarch) else lastproc=$(echo $curproc) lastthread=$(echo $curthread) lastarch=$(echo $curarch) fi fi done < $ARCFILE ## Clear out the file echo " " > $ARCFILE fi } ## MAIN chkReadSCN chkArcGap readErrFile When this script is executed, error conditions will be written to SYSLOG.
Oct 24 09:20:03 r2d2 OGGEnvReportDB.sh: OGG-99999: There is an archive log gap. Please check redo transport.
Automating Script Execution
To automate the execution of these checks, I chose to use the Oracle Database DBMS_SCHEDULER.CREATE_JOB procedure to run the checks hourly. The pl/sql script SetupOGGEnvCheck.sql is used to create the jobs:
-- Must be run as SYSDBA -- sqlplus / as sysdba -- Set the necessary privileges GRANT CREATE JOB TO ggadmin; GRANT CREATE EXTERNAL JOB TO ggadmin; GRANT CREATE CREDENTIAL TO ggadmin; -- Create a credential so script is run as the -- correct OS user on the database server. CONN ggadmin/Oracle1 BEGIN DBMS_CREDENTIAL.create_credential( credential_name => 'oracle_ogg', username => 'oracle', password => 'Oracle1' ); END; / -- Delete jobs if they already exist DECLARE l_job_name VARCHAR2(30); l_job_exists number; BEGIN l_job_name := 'OGG_ENV_CHECK'; select count(*) into l_job_exists from user_scheduler_jobs where job_name = l_job_name ; if l_job_exists = 1 then DBMS_SCHEDULER.drop_job(job_name => l_job_name); end if; l_job_name := 'OGG_DB_CHECK'; select count(*) into l_job_exists from user_scheduler_jobs where job_name = l_job_name ; if l_job_exists = 1 then DBMS_SCHEDULER.drop_job(job_name => l_job_name); end if; l_job_name := 'OGG_DB_TRANSPORT_CHECK'; select count(*) into l_job_exists from user_scheduler_jobs where job_name = l_job_name ; if l_job_exists = 1 then DBMS_SCHEDULER.drop_job(job_name => l_job_name); end if; l_job_name := 'OGG_DB_CHECK_REPORT'; select count(*) into l_job_exists from user_scheduler_jobs where job_name = l_job_name ; if l_job_exists = 1 then DBMS_SCHEDULER.drop_job(job_name => l_job_name); end if; END; / -- Create a job with in-line scripts. DECLARE l_job_name VARCHAR2(30); l_script VARCHAR2(32767); BEGIN -- Check disk space and memory l_job_name := 'ogg_env_check'; l_script := '/u01/oracle/OGGEnvCheck/OGGEnvCheck.sh'; DBMS_SCHEDULER.create_job( job_name => l_job_name, job_type => 'EXTERNAL_SCRIPT', job_action => l_script, credential_name => 'oracle_ogg', start_date => systimestamp + interval '10' second, repeat_interval => 'FREQ=HOURLY;INTERVAL=1', enabled => TRUE ); END; / -- -- Check archive log space DECLARE l_job_name VARCHAR2(30); l_script VARCHAR2(32767); BEGIN -- Check database features l_job_name := 'ogg_db_check'; l_script := '/u01/oracle/OGGEnvCheck/OGGEnvCheck.sql'; DBMS_SCHEDULER.create_job( job_name => l_job_name, job_type => 'SQL_SCRIPT', job_action => l_script, credential_name => 'oracle_ogg', start_date => systimestamp + interval '10' second, repeat_interval => 'FREQ=HOURLY;INTERVAL=1', enabled => TRUE ); END; / -- -- Check Redo Transport (Downstream Capture) -- -- Uncomment the following if using downstream capture --DECLARE -- l_job_name VARCHAR2(30); -- l_script VARCHAR2(32767); --BEGIN -- Check database features -- l_job_name := 'ogg_db_check'; -- l_script := '/u01/oracle/OGGEnvCheck/OGGDBTransportCheck.sql'; -- DBMS_SCHEDULER.create_job( -- job_name => l_job_name, -- job_type => 'SQL_SCRIPT', -- job_action => l_script, -- credential_name => 'oracle_ogg', -- start_date => systimestamp + interval '30' second, -- repeat_interval => 'FREQ=HOURLY;INTERVAL=1', -- enabled => TRUE -- ); --END; --/ DECLARE l_job_name VARCHAR2(30); l_script VARCHAR2(32767); BEGIN -- Report any errors from database check l_job_name := 'ogg_db_check_report'; l_script := '/u01/oracle/OGGEnvCheck/OGGEnvReportDB.sh'; DBMS_SCHEDULER.create_job( job_name => l_job_name, job_type => 'EXTERNAL_SCRIPT', job_action => l_script, credential_name => 'oracle_ogg', start_date => systimestamp + interval '300' second, repeat_interval => 'FREQ=HOURLY;INTERVAL=1', enabled => TRUE ); END; /
With the hourly execution of the scripts and the GoldenGate Manager settings, we now have a good representation of our server and replication stream health. If the enterprise has processes in place that scrape SYSLOG, this information may be added for automated notifications.
Summary
In this article we presented information on how to setup Oracle GoldenGate release 12.3 and above to record warning and error conditions to SYSLOG, presented Oracle GoldenGate Manager settings to log excessive replication lag errors, presented Oracle GoldenGate Manager settings to report on Groups in the ABEND state, and provided scripting examples to log server and database resource issues that affect replication stability.
For more information on what other articles are available for Oracle GoldenGate please view our index page.