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_
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 "*****************************
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`
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(
EOF
}
drop_cap () {
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect / as sysdba
exec DBMS_CAPTURE_ADM.DROP_CAPTURE(
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
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 "*****************************
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 "\n*********************************************************"
echo -e "NOT found PROD extract process name in $1 database "
echo -e "*********************************************************\n"
CHECK_CAP ;
echo -e "*********************************************************\n"
fi
echo -e "\nDATE = `date`"
echo -e "\nDATE = `date`"
Another option is to login to ggsci and do unregister, check below link to unregister manually
