scripts/LJCOPY/DP/DEV/weekly_expdp_v4.ksh

#!/bin/ksh
# ============================================================================================================
# Richard Wilson - 28.04.2017
# Backup the database using Data Pump.
# ------------------------------------------------------------------------------------------------------------
# History:
#       RW      02.06.2014      Minor modifications.
#       RW      24.06.2014      Parallelised - RW - Removed 28.04.2017.
#                               EXPDP to ASM only.
#       RW      26.06.2014      Added ASM dump file deletion.
#       RW      04.09.2014      Use dynamic date, changed ORACLE_HOME.
#                               Corrected errors in ASM purge.
#	RW	28.04.2017	Modified for tst-02
#	RW	28.04.2017	Added Database Sizing function.
#	RW	07.06.2017	Modified for Weekly backups.
#
# ============================================================================================================
# OWNER      DIRECTORY_NAME  DIRECTORY_PATH
# ---------- --------------  ---------------------------------------------------------------------------------
# SYS        DUMPSET_LOGS	/home/oracle/Scripts/DP/logs
# SYS	     EXP_DIR		+DATA/<${ORACLE_SID}>/DUMPSET
# ------------------------------------------------------------------------------------------------------------

function is_sid_valid
{
if [ `ps -ef | grep -v grep | grep "ora_pmon_${ORACLE_SID}$" | wc -l 2> /dev/null` -eq 0 ]
then
        return 1
else
        return 0
fi
}


function get_db_size
{
res=`sqlplus -s expman/expdp@${ORACLE_SID} <<-EOF
	SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
	column bytes format 9999
	select trim(round(sum(bytes)/1024/1024/1024)) from dba_extents;
	exit
EOF`
echo ${res}
}

# ------------------------------------------------------------------------------------------------------------
export DEBUG="N"  # "Y"||"N"
export ORACLE_SID=`echo ${1} | tr "[a-z]" "[A-Z]"`
export DB_SID=${ORACLE_SID}
export SIZE_FLAG=""
export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
export SCRIPTS_DIR="/home/oracle/Scripts/DP"
export TOLOG="${SCRIPTS_DIR}/${ORACLE_SID}_to_log.log"

if [ "$#" -eq 1 ]
then
	if is_sid_valid
	then
		if [[ `get_db_size` -gt 10 ]]  #10GB
		then
			#  SIZE_FLAG="exclude=SCHEMA:\"in \(\'MDMADM\',\'CCBADM\'\)\""
			SIZE_FLAG="exclude=SCHEMA:\"in \(\'MDMADM\'\,\'CCBADM\'\)\""
		fi 

		[ "${DEBUG}" == "Y" ] && cmd="echo" || cmd="eval"

		# Export the database.
		# ${cmd} ${ORACLE_HOME}/bin/expdp expman/expdp@${ORACLE_SID} dumpfile=EXP_DIR:${ORACLE_SID}_week_`date '+%V'`_expdp.dmp full=y ${SIZE_FLAG} logfile=DUMPSET_LOGS:${ORACLE_SID}_week_`date '+%V'`_expdp.log reuse_dumpfiles=y ${TOLOG}

		${cmd} ${ORACLE_HOME}/bin/expdp expman/expdp@${ORACLE_SID} dumpfile=EXP_DIR:${ORACLE_SID}_week_`date '+%V'`_expdp.dmp full=y ${SIZE_FLAG} logfile=DUMPSET_LOGS:${ORACLE_SID}_week_`date '+%V'`_expdp.log reuse_dumpfiles=y 
		

		#[ "${DEBUG}" == "Y" ] && echo ${ORACLE_HOME}/bin/expdp expman/expdp@${ORACLE_SID} dumpfile=EXP_DIR:${ORACLE_SID}_week_`date '+%V'`_expdp.dmp full=y ${SIZE_FLAG} logfile=DUMPSET_LOGS:${ORACLE_SID}_week_`date '+%V'`_expdp.log reuse_dumpfiles=y >> ${SCRIPTS_DIR}/DEBUG_${ORACLE_SID}.log ||
		#			 eval ${ORACLE_HOME}/bin/expdp expman/expdp@${ORACLE_SID} dumpfile=EXP_DIR:${ORACLE_SID}_week_`date '+%V'`_expdp.dmp full=y ${SIZE_FLAG} logfile=DUMPSET_LOGS:${ORACLE_SID}_week_`date '+%V'`_expdp.log reuse_dumpfiles=y 
		# Purge from ASM..............
		if [ $? -eq 0 ]
		then
	                [ -f ${SCRIPTS_DIR}/logs/${ORACLE_SID}_week_`date +%V --date='2 weeks ago'`_expdp.log ] && rm -f ${SCRIPTS_DIR}/logs/${ORACLE_SID}_week_`date +%V --date='2 weeks ago'`_expdp.log || :
			export ORACLE_SID="+ASM"
        		export ORACLE_HOME="/u01/app/oracle/product/11.2.0/grid"
			# ${ORACLE_HOME}/bin/asmcmd rm -f +DATA/${DB_SID}/dumpset/${DB_SID}_week_`date +%V --date='2 weeks ago'`_expdp.dmp
		fi
	else
		echo -e "ERROR: Parameter 1: Database '${ORACLE_SID}' does not exist."
	fi
else
	echo -e "ERROR: Usage weekly_expdp_v?.ksh <ORACLE_SID>." 
fi

# =============================================================================================================