All posts by mrculp

GoldenGate Setup For The Hub

GoldenGate Setup for the Hub

Steps to setup a new environment on a node.

Assumptions.

  • The required disk has been make available to the Hub database system.
  • XAG has been installed, 9.1 is the current version.
  • All required software has been installed (fuse, GoldenGate, CRS, XAG  etc.)
  • All required setting and logging is configured at the DB level.
  • All installs are in the same location on all servers.

 

These are all the manual steps required to implement resiliency for the GoldenGate hub.   Mike Culp has written scripts to do this process. In general, using scripting for a process that covers many different servers is a best practice approach, as it insures the installs are done in an identical fashion.  This reduces complexity of all maintenance.

 

Setup Overview

Each cluster on the Hub has its’ own database.  This database is used to supply a file system(s) for the trail files and other OGG files required for recovery.   The DBFS filesystem for the OGG files can only be mounted on one node at a time as a best practice. Otherwise duplicate processes could be started, which could cause corruption in the replication hub.  The data disk can be mounted on all systems if desired. If there is only one data group in a replication instance, it is possible to put all files on that one mount, and mount it to only one node at a time. But it is very difficult to undo that decision.

 

  1. Create a directories off the root filesystem system.
  2. Grant the oracle user full control of the dir.
  3. Create the tablespace for DBFS – standard create tablespace command.  The following example uses a bigfile tablespace but that is NOT required.
    1. create bigfile tablespace dbfs_tblsp datafile ‘+DBFS_DG’ size 32g autoextend on next 8g maxsize 300g NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO ;
  4. Create a user for that tablespace.
    1. At minimum, database users must have the following privileges to create a file system: GRANT CONNECT, CREATE SESSION, RESOURCE, CREATE TABLE, and CREATE PROCEDURE, and DBFS_ROLE
      1. create user dbfs_user identified by dbfs_passwd default tablespace dbfs_tblsp quota unlimited on dbfs_tblsp;
      2. grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;
  5. Create the file system, logging in as the user created above.
    1. start dbfs_create_filesystem dbfs_tblsp dbfs_mnt
      1. This script takes two arguments:
        1. dbfs_tblsp: tablespace for the DBFS database objects
        2. dbfs_mnt : filesystem name, this can be any string and will appear as a directory under the mount point
    2. Validate the system is configured with nocache, logging
      1. SQL>  SELECT logging, cache FROM dba_lobs WHERE tablespace_name=’DBFS_TBLSP’;

LOGGING CACHE

——- ———-

YES     NO

  1. Modify/rename the mount-dbfs.conf and mount-dbfs.sh scripts for this instantiation.  Each OGG instance will have its own “name” used to identify it within crs. As such a different set of conf and sh script will be needed for each OGG instance.
  2. Test the mount-dbfs.sh script.  (start, stop , status). This validates the setup.
    1. Start should mount the file system.
    2. Stop should unmount.
    3. Status should return the status.
  3. Create a service for the instantation.
    1. srvctl add service -database db_name -service ggname_svc -preferred node1 -available node2
  4. Create the clusterware setup for the dbfs file system.  Use cluster resource to only allow it to be mounted one one node.  The data dbfs system can use local_resource.
    1. $GRID_HOME/bin/crsctl add resource dbfs_mnt \

-type cluster_resource \

-attr “ACTION_SCRIPT=/ora01/scripts/ggcommon/mount-dbfs.sh CHECK_INTERVAL=30 RESTART_ATTEMPTS=10 , \  START_DEPENDENCIES=’hard(ora.raca_domain.db)pullup(ora.raca_domain.db)’,STOP_DEPENDENCIES=’hard(ora.raca_domain.db)’,SCRIPT_TIMEOUT=300”

 

  1. Test crsctl to start,stop and relocate to validate it works.

$GRID_HOME/bin/start resource dbfs_mnt

  1. Create the goldengate soft links to the dbfs mounts.
    1. cd $GG_HOME
    2. mv dirprm dirprm.old
    3. mv dirchk dirchk.old
    4. mv dircrd dircrd.old
    5. mv dirrpt dirrpt.old
    6. ln –s /mnt/dbfs_mnt/dirprm dirprm
    7. ln –s /mnt/dbfs_mnt/dirchk dirchk
    8. ln –s /mnt/dbfs_mnt/dircrd dircrd
    9. ln –s /mnt/dbfs_mnt/dirrpt dirrpt
  2. Create the crs definition for goldengate using the XAG interface
    1. $XAG_HOME/bin/agctl add goldengate GG_SOURCE \ –gg_home /u01/oracle/goldengate \ –oracle_home /u01/app/oracle/product/12.2.0/dbhome_1 \  –db_services ggname_svc –use_local_services –filesystems dbfs_mnt
  3. Create the manager parameter file
  4. Start and stop the goldengate instance using agctl.   Validate the mgr process started.
  5. Create the required data dbfs file systems for the data.
  6. Create the crsctl definitions, if desired.  The only difference is -cluster_resource is not used, as the data can be mounted on all systems with no issues.  The only reason for the crsctl definitions is to startup the mount system automatically, there is no failover required.

The system is ready for replication, however the same setup, with the exception of the XAG and CRS configuration is required for all nodes/clusters that may run this instantiation of GoldenGate.  The XAG and the crs setup is only done once per cluster.

 

Upgrade Hub GoldenGate Version

Steps

  1. Stop all GG extract and replicat from the directory you are upgrading from
  2. set_ggenv   ggtxd002 for example
  3. Configure the new instance ggtxd006 for dbfs_mrct and service ora.dbfsxd01.dbfs_mrct.svc
  4. set_ggenv ggtxd006, and check the target environment to see if its running, if not start the instance in order to check do this: hubhelp xag_stat_gginst ggtxd006

TAR Common

 


tar_help() 
##############################################################
# This will display the help for the tar command
##############################################################
{ 
tar --help
} 


tar_create()
##############################################################
# This will create a tar file
##############################################################
{
tarnm=$1
tardir=$2

tar -cvf $tarnm $tardir

}

tar_view()
##############################################################
# This will allow you to view a tar 
############################################################## 
{
flnm=$1
tar -tvf $flnm
}

tar_all()
{
echo " "
}

tar_mv()

{

cd /oracle/admin/scripts

ls -lt asm*tar

}

 

appgold dir

appgold.ggini – init parameters

cr_dbfs_exeprimdb.ksh – primary
cr_dbfs_exestdy.ksh – standby

dbfs_cr_objs_parm_appgold_fn.ksh
dbfs_cr_tnsname_appgold_fn.ksh

dbfs_cr_mnt_pnt_parm_appgold_fn.ksh
dbfs_svc_parm_appgold_fn.ksh

dbfs_cr_tblspusr_parm_appgold_fn.ksh
crs_add_res_dbfs_parm_appgold_fn.ksh

dbfs_mount_passwd_appgold.ksh

xag_relo_ggtxd003_appgold_parm_fn.ksh
xag_start_ggtxd003_appgold_fn.ksh
xag_stop_ggtxd003_appgold_fn.ksh

mount_dbfs_appgold_.bsh
mount-dbfs_appgold.conf

xag_add_proj_fs_parm_appgold_fn.ksh

monitor_fgl.ksh

monitor_fgl.ksh

#!/bin/ksh

###########################################################################################################                                         #############
# DBA TEAM   - initial version
# 03/18/2019 - Made it dynamic to pick the FGL home
###########################################################################################################                                         #############
##set -x
#
# This job will monitor foglight and attempt to restart it if it
# is not running. It will send email notification.
# REL_2
#  Envir.
. $HOME/.profile > /dev/null
if [ -a .kshrc ]; then
   . ./.kshrc
fi

export OS=$(uname)
BASE=$(dirname $0)
## LOGDIR="/oracle/dba/output/generic"
dte=`date +%Y%m%d`
HOST=`hostname -s`
## logf=${LOGDIR}/${HOST}_Foglight_Stat_${dte}.log
logf=/tmp/${HOST}_Foglight_Stat_${dte}_$$.log

# SENDER_EMAIL_ID="dg.Adminemail_baci_oracle_dba@test.com"
## FGL_STATE=/oracle/dba/foglight/fgl_5/state/default

find / -type d \( -name proc -o -name banktools -o -name efs -o -name dbacl -o -name run -o -name ".efs" -o                                          -name home -o -name vol \) -prune -o -name "fgl_5" -print 2>&1 | grep -v "Permission denied" > /tmp/fog_te                                         st_$$.lst

for i in `cat /tmp/fog_test_$$.lst`
do
FGL_OWNER=`ls -ld $i |cut -d " " -f 3`
if [ "$FGL_OWNER" = "$USER" ]
then
export FGL_HOME=$i
fi
done

if [ $? -ne 0 ]; then
echo "Not able to identify the FGL home - Please Investigate"
exit 1
fi

if [ -z $FGL_HOME ]; then
echo "Not able to identify the FGL home - Please Investigate"
exit 1
fi

FGL_STATE=$FGL_HOME/../../foglight/fgl_5/state/default

MAILDATE=$(date '+%m%d%Y_%H%M')
TOA="test.com"
EMAIL="dg.oracle_hosting_services_-_non_exa@${TOA}"
#MESSAGE="Foglight Not Running - Attempt to Restart Failed"
#MESSAGE="Foglight Was Not Running - Restarted"
MSG_SUBJECT="Test email with file"

#send_mail () {
#   if [ "$OS" == "Linux" ]; then
#    if [ `uname -r | cut -d. -f3 | cut -d- -f1` -le 18 ]; then
#     (echo $MESSAGE ) | mail -s "$HOST - $MSG_SUBJECT" $EMAIL -- -f $SENDER_EMAIL_ID
#    else
#     (echo $MESSAGE) |  mailx -s "$HOST - $MSG_SUBJECT" -r $SENDER_EMAIL_ID $EMAIL
#    fi
#   else
#    (echo $MESSAGE ) | mailx -s "$HOST - $MSGTEXT" -r $SENDER_EMAIL_ID $EMAIL
#   fi
#}

if [ -r $FGL_STATE/.*.pid ]; then
cat `ls $FGL_STATE/.*.pid|tail -1` > /tmp/fgl_pcheck_$$.lst
FGL_PID=`cat /tmp/fgl_pcheck_$$.lst`
## FGL_PID=`cat $FGL_STATE/.*.pid`
## ps -ef | grep $FGL_PID | grep -v grep | grep -i fog > /dev/null
if [ $? -ne 0 ]; then
printf "Foglight not running PID = $FGL_PID \n" >>${logf} 2>> ${logf}
printf "Restarting ........................ \n" >>${logf} 2>> ${logf}
$FGL_HOME/bin/fglam -Xint -d >>${logf} 2>> ${logf}
sleep 60
FGL_PID=`cat $FGL_STATE/.*.pid`
ps -ef | grep $FGL_PID | grep -v grep > /dev/null
if [ $? -ne 0 ]; then
#    MESSAGE="Foglight Not Running - Attempt to Restart Failed - Please Investigate"
#    MSG_SUBJECT="Foglight Restart Failed"
#send_mail
echo "Foglight Not Running - Attempt to Restart Failed - Please Investigate"
exit 1
else
echo "Foglight Was Not Running - Restarted Successfully"
#    MSG_SUBJECT="Foglight Restarted"
#send_mail
fi
else
printf "Foglight is running PID = $FGL_PID \n" >>${logf} 2>> ${logf}
fi
else
printf "Foglight not running .............. \n"  >>${logf} 2>> ${logf}
printf "Restarting ........................ \n"  >>${logf} 2>> ${logf}
$FGL_HOME/bin/fglam -Xint -d  >>${logf} 2>> ${logf}
sleep 60
FGL_PID=`cat $FGL_STATE/.*.pid`
ps -ef | grep $FGL_PID | grep -v grep > /dev/null
if [ $? -ne 0 ]; then
#  MESSAGE="Foglight Not Running - Attempt to Restart Failed - Please Investigate"
#  MSG_SUBJECT="Foglight Restart Failed"
echo "Foglight Not Running - Attempt to Restart Failed - Please Investigate"
exit 1
#send_mail
else
#  MESSAGE="Foglight Was Not Running - Restarted Successfully"
echo "Foglight Was Not Running - Restarted Successfully"
MSG_SUBJECT="Foglight Restarted"
#send_mail
fi
fi
#
exit