Get the SQL TEXT/SQLID of the Transaction Which Goldengate Extract is Lagging At

get_SQLText_for_goldengate_XID.sh

# #############################################################################
# Script to get the SQLID for a goldengate transaction id XID
# Ver 1.0
#					#   #     #
# Author:	Mahmmoud ADEL	      # # # #   ###
# Created:	23-09-21	    #   #   # #   # 
#
#
#
# #############################################################################

# ############
# Description:
# ############
echo
echo "=============================================================="
echo "This script fetch the SQLText for a goldengate transaction XID"
echo "=============================================================="
echo
sleep 1

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB="\-MGMTDB|ASM|APX"                           #Excluded INSTANCES [Will not get reported offline].

# ############################
# Listing Available Databases:
# ############################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
 then
   echo No Database Running !
   exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
 then
   export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
 then
    echo
    echo "Select the ORACLE_SID:[Enter the number]"
    echo ---------------------
    select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
     do
                integ='^[1-9]+$'
                if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ]
                        then
                        echo
                        echo "Error: Not a valid number!"
                        echo
                        echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
                        echo "-----------------------------------------------"
                else
                        export ORACLE_SID=$DB_ID
                        echo 
                        printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
                        echo
                        break
                fi
     done

fi
# Exit if the user selected a Non Listed Number:
        if [ -z "${ORACLE_SID}" ]
         then
          echo "You've Entered An INVALID ORACLE_SID"
          exit
        fi

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
  then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
export PGREP=`which pgrep`
export PWDX=`which pwdx`
if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
then
PMON_PID=`pgrep  -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
fi

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
## If OS is Linux:
if [ -f /etc/oratab ]
  then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
fi

# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
ORACLE_HOME=`dbhome "${ORACLE_SID}"`
export ORACLE_HOME
fi

# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
fi

# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
fi

# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
	if [ -x /usr/bin/locate ]
 	 then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
	fi
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
 then
  echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
  echo "e.g."
  echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
exit
fi

export LD_LIBRARY_PATH=${ORACLE_HOME}/lib

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

        if [ -f ./login.sql ]
         then
mv ./login.sql   ./login.sql_NeutralizedBy${SCRIPT_NAME}
        fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
#CURR_USER=`whoami`
#        if [ ${ORA_USER} != ${CURR_USER} ]; then
#          echo ""
#          echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
#          echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
#          echo "Script Terminated!"
#          exit
#        fi

echo "From GoldenGate GGSCI console get the transaction XID by executing below command:"
printf "`echo -e "\033[33;5msend extract XXX showtrans\033[0m"` `echo "-- replace XXX with the EXTRACT group name"`\n"
echo
echo "Then, copy the complete number in front of XID: and provide it when prompted."
echo
        echo "Enter the complete goldengate Transaction XID: [e.g. 139.12.7079729]"
	echo "============================================="
                while read XID
                do
                 case ${XID} in
		 *[0-9]*.*[0-9]*.*[0-9]*)  	echo; export XID; echo; break;;
		 *)  				echo;echo "Please enter a valid XID number: [e.g. 139.12.7079729]"
						     echo "-------------------------------";;
                 esac
                done

echo "Searching XID [${XID}] in the REDO LOG Buffer ..."
echo

SQLID_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
alter system checkpoint;
prompt
select  decode(s.sql_id,null,s.prev_sql_id) sqlid from gv\$transaction t, gv\$session s
	where s.saddr = t.ses_addr
	and t.xidusn=REGEXP_SUBSTR  ('${XID}', '[^.]+', 1,1)
	and t.xidslot=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,2)
	and t.xidsqn=REGEXP_SUBSTR  ('${XID}', '[^.]+', 1,3);
exit;
EOF
)
SQLID=`echo ${SQLID_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`

	if [ -z ${SQLID} ]
	then
	echo "Transaction with XID [${XID}] CANNOT be found in the REDO Buffer. This is Expected!"
	echo
# ################################
# LogMiner Feature:
# ################################
        echo "Do you want to search deeper using LogMiner [License Free feature in EE]: [Highly Recommended] [Y|n]"
        echo "==========================================="
                while read ANS
                do
 		 case ${ANS} in
      		 N|n|NO|no) 	 echo; echo "SCRIPT TERMINATED! "; echo; exit;;
 		 ""|Y|y|YES|yes) echo; echo "Checking the availability of LogMiner Feature in this Database Edition..."; echo; 

# Checking if Data Mining feature is enabled:

DATAMINING_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from v\$option where parameter='Data Mining' and value='TRUE';
exit;
EOF
)
DATAMINING_OPTION=`echo ${DATAMINING_OPTION_RAW} | awk '{print $NF}'`

        if [ ${DATAMINING_OPTION} -eq 0 ]
         then
	 echo "DATA MINING Feature is DISABLED in this Database Edition!"
	 echo ""
	 echo "SCRIPT TERMINATED! "
	 echo ""
	 exit
	 else
	 echo "[DATA MINING Feature is Available]"
	 echo
	fi

# Checking supplemental_log_data_min is enabled:

SUPPL_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from v\$database where supplemental_log_data_min='YES';
exit;
EOF
)
SUPPL_OPTION=`echo ${SUPPL_OPTION_RAW} | awk '{print $NF}'`

        if [ ${DATAMINING_OPTION} -eq 0 ]
         then
         echo "SUPPLEMENTAL LOG Feature is DISABLED, LogMiner may fail to find the required data, but let's try!"
         echo ""
        fi


			# Setting the number of Hours for LogMiner to search within:
			echo "How many HOURS back you want to mine for transactions?: [Default 1 Hour]"
			echo "------------------------------------------------------"
				while read HOURS
				do
				 case ${HOURS} in
				 "") export NUM_HOURS=1; echo "LogMiner will search the last 1 hours transactions";echo; break;;
			   *[!0-9]*) echo "Please enter a valid NUMBER:"
				     echo "---------------------------";;
				  *) echo; export NUM_HOURS=${HOURS}; break;;
				 esac
				done

export SPOOL_DIR=/tmp
export SPOOL_FILE='Build_LogMiner_Catalog.sql'
# Building the LogMiner Catalog of Archive Logs:
${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 lines 300 feedback off echo off termout off serveroutput off
prompt Archiving the current REDOLOG file ...
alter system archive log current;
prompt
prompt Adding Archive Logs to LogMiner Catalog:
spool ${SPOOL_DIR}/${SPOOL_FILE}
select distinct 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '''||NAME||''',OPTIONS => DBMS_LOGMNR.ADDFILE);' from gv\$archived_log where completion_time > sysdate-${NUM_HOURS}/24 and DEST_ID=1 group by name order by 1;
spool off
EOF


# Executing LogMiner:
# Removing un-needed lines from the generated spool file:
sed -i 's/completion_time//g' 	${SPOOL_DIR}/${SPOOL_FILE}
sed -i 's/spool//g' 		${SPOOL_DIR}/${SPOOL_FILE}
# The first REDOLOG should be add with NEW parameter and the rest to keep with ADDFILE parameter:
sed -i '1s/.ADDFILE/.NEW/' 	${SPOOL_DIR}/${SPOOL_FILE}

${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set feedback off
prompt
prompt Building the LogMiner catalog using ${SPOOL_FILE} ...
start ${SPOOL_DIR}/${SPOOL_FILE}
prompt
prompt Activating the LogMiner catalog ...
EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
prompt
prompt Searching for Transaction with XID [${XID}]: 
prompt
set pages 1000 lines 156 long 2000000000 timing on feedback on
col xid	      for a18
col TIMESTAMP for a20
col EXECUTER  for a15
col OBJ_OWNER for a15
col sql_redo  for a95
spool Transaction_XID_${XID}.log
select xidusn||'.'||xidslt||'.'||xidsqn xid,to_char(timestamp,'DD-MON-YY HH24:MI:SS') TIMESTAMP,username EXECUTER,sql_redo  from v\$logmnr_contents
where xidusn=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,1)
  and xidslt=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,2)
  and xidsqn=REGEXP_SUBSTR ('${XID}', '[^.]+', 1,3)
  and sql_redo <> 'set transaction read write;';

spool off
set feedback off timing off
-- Cleanup the LogMiner Session:
execute dbms_logmnr.end_logmnr();

EOF

break;;
            	 *) 		 echo "Please enter a valid answer ya Lemed: [YES|NO]"
				 echo "------------------------------------";;
		 esac
                done


	else
${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 1000 lines 156 long 2000000000 feedback off;
col SQL_ID	 for a15
col SQL_FULLTEXT for a100
prompt Voilà! Transaction found:
prompt
select  SQL_ID, SQL_FULLTEXT from gv\$sql where SQL_ID='${SQLID}';
prompt
exit;
EOF
	fi


# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
        if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
         then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME}  ./login.sql
        fi

# #############
# END OF SCRIPT
# #############
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
# Do not live under a rock :-) Every month a new version of DBA_BUNDLE get released, download it by visiting:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
# REPORT BUGs to: [email protected]