Saturday, November 18, 2017

Shell script to cleanup Goldengate PROD extracts in NON-PROD


PRODUCTION DB: Oracle on linux with Goldengate extracts ( ex: ETESTDBQ2 ) running
LINUX: RHEL6

The below script used for cleanup non-prod databases where goldengate is running when it is refreshed (FULL refresh like SNAP copy, rman clone, any physical clone) from PROD databases where goldengate also running, which means when you refresh non-prod with prod database then you will see goldengate prod extract name in non-prod which will repeat errors in alert log and restricts archivelog from deleting.

You’ll see something like below in non-prod with prod extract group names which needs cleanup

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME
--------------------------------------------------------------------------------
STATUS   CAPTURED_SCN  APPLIED_SCN   CAPTURE_TY   STATUS_CHAN
------------- -----------------  -------------------    -------------------  --------------
OGG$CAP_ETESTDBQ2
ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017


#!/bin/ksh
####################################################################################
# This script will do the POST refresh activities for Databases having Goldengate
# It will uses the directory defined in the EXPDPDR directory
#
# Syntax:  Script.sh <database_name>
#
# PARAMETERS:
#     Source dbname    : (required) - database name
#
# EX: GG_POST.sh mydev
#
# AUTHOR: Chandra
####################################################################################
echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$1" = "" ]; then
  echo " "
  echo "Syntax: $0 <DB name>"
  exit 1
fi

DBTGT=`echo $1 | tr "[:upper:]" "[:lower:]"`
DBTAIL=`echo $DBTGT |awk '{print substr($0,3)}'`
SOURCE_SERVER=`hostname`
#echo "DBTAIL :" $DBTAIL
#echo "DBTGT : " $DBTGT

if [[ "$DBTAIL" = "prod"]]; then
echo -e "\nThis script CANNOT be run on $DBTGT PRODUCTION databases \n"
exit 1
fi

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT
#. dbaenv $ORACLE_SID
if [[ "${SOURCE_SERVER:0:3}" = "rac" ]] || [[ "${SOURCE_SERVER:0:3}" = "RAC" ]]; then
. /opt/oracle/RAC/oraenv
else
. /usr/local/bin/oraenv
fi

echo "ORACLE_HOME=" $ORACLE_HOME

CAP_NAME=`sqlplus -s /nolog << EOF
connect / as sysdba
set verify off trimspool on feed off echo off termout off head off
col capture_name for a15
col STATE for a33
select capture_name from dba_capture where capture_name like '%PROD%';
exit
EOF
`

CAP_NEW_NAME=$CAP_NAME
echo -e "\n CAP_NEW_NAME = " $CAP_NEW_NAME
C_ENV=`echo $CAP_NAME |awk '{print substr($0,9,14)}'`
echo " CAP_NAME= $CAP_NAME"
echo " C_ENV" =$C_ENV

stop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

drop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG\$CAP_${C_ENV}');
EOF
}

CHECK_CAP () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
set line 280 pages 999
col CAPTURE_NAME for a22
col STATE for a25
select capture_name, status, captured_scn, applied_scn, capture_type from dba_capture;
select extract_name,CAPTURE_NAME, sid, serial#, STATE from  V\$GOLDENGATE_CAPTURE;
EOF
}


if [[ "$C_ENV" == "ESTPRODP" ]] || [[ "$C_ENV" == "EDVPRODP" ]] ; then
echo -e "\n*****************************************************"
echo -e "Database ${1} has PROD "${CAP_NEW_NAME}" capture name "
echo -e "*************************************************** \n"
echo -e " BEFORE Dropping :"
CHECK_CAP ;
echo -e " \nStopping "${CAP_NEW_NAME}" Capture process "
stop_cap;
echo -e "\nDROPPING "${CAP_NEW_NAME}" Capture process "
drop_cap;
echo -e " AFTER Dropping :"
CHECK_CAP ;
else
echo -e "\n*********************************************************"
echo -e "NOT found PROD extract process name in $1 database "
echo -e "*********************************************************\n"
CHECK_CAP ;
fi

echo -e "\nDATE = `date`"


Another option is to login to ggsci and do unregister, check below link to unregister manually

0 comments:

Post a Comment

Auto Scroll Stop Scroll