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

#. oraenv $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

http://chandu208.blogspot.com/2017/11/rman-08137-warning-archived-log-not.html