#!/bin/bash
#================================================================================
# File:		ora_vdb_prerefresh.sh
# Type:	 	bash-shell script
# Date:	 	17-Apr 2018
# Author:	Delphix Field Services
# Ownership:	This script is owned and maintained by the user, not by Delphix
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#       http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# Copyright (c) 2018 by Delphix. All rights reserved.
# 
# Description:
#
#	Shell script intended to be called from the "Pre-Refresh" hook of a
#	Delphix Oracle VDB in order to use Oracle Datapump Export to save the
#	definition of all database links, and then use SQL*Plus to use
#	SQL-generating-SQL to generate SQL*Plus scripts to re-apply the
#	passwords of database accounts.
#
#	The Datapump dump file from the Export and the generated SQL*Plus
#	scripts will then be employed by the "ora_vdb_postrefresh.sh"
#	script during a Post-Refresh hook to restore database links in
#	the refreshed VDB.
#
# Note:
#
#	There is a companion shell-script named "ora_vdb_postrefresh.sh" which
#	uses Oracle Datapump Import to import the dump file and then executes
#	the Oracle SQL*Plus script generated as output by this script.
#       
# Calling syntax:
#
#       ora_vdb_prerefresh.sh $ORACLE_SID $ORACLE_HOME <dump-dir>
#
# Calling parameters:
#
#	$ORACLE_SID	the ORACLE_SID environment variable expanded value
#	$ORACLE_HOME	the ORACLE_HOME environment variable expanded value
#	<dump-dir>	full path to the directory in which the Datapump dump
#			file will be exported
#
#
# Modifications:
#	TGorman	17apr18	written
#================================================================================
#
#--------------------------------------------------------------------------------
# Set local variables for use in the script, including the temporary directory in
# which the script's diagnostic log will go, as well as the name of the database
# account used by DataPump Export...
#--------------------------------------------------------------------------------
_tmpDir=/tmp
_logFile=${_tmpDir}/ora_vdb_prerefresh_${ORACLE_SID}.lst
_dbUser=system
_dumpFile=reapply_${ORACLE_SID}_dblinks
#
#--------------------------------------------------------------------------------
# Validate command-line parameters...
#--------------------------------------------------------------------------------
if (( $# != 3 ))
then
	echo "`date` Usage: \"$0 \$ORACLE_SID \$ORACLE_HOME <dump-dir>\"; aborting..." | tee -a ${_logFile}
	exit 1
fi
export ORACLE_SID=$1
export ORACLE_HOME=$2
export _dumpDir=$3
#
#--------------------------------------------------------------------------------
# Verify that the ORACLE_SID value is valid and the instance is up and running...
#--------------------------------------------------------------------------------
_instanceUp=`ps -eaf | grep "ora_pmon_${ORACLE_SID}$" | grep -v grep | wc -l`
if (( ${_instanceUp} == 0 )) 
then
	echo "`date` ORACLE_SID=\"${ORACLE_SID}\" - no running instance; aborting..."
	exit 1
fi
#
#--------------------------------------------------------------------------------
# Retrieve the password for the database account "_dbUser" from the hidden
# password file...
#
# Entries in the file are in the format...
#
#	ORACLE_SID:username/password			(if ORACLE_SID is local)
#
# ...with no whitespace embedded.
#--------------------------------------------------------------------------------
_hiddenPwdFile=${HOME}/.unpwd
if [ ! -r ${_hiddenPwdFile} ]
then
	echo "`date` password file \"${_hiddenPwdFile}\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
_dbPwd=`grep "^${ORACLE_SID}:${_dbUser}/" ${_hiddenPwdFile} | awk -F/ '{print $2}'`
if [[ "~${_dbPwd}~" = "~~" ]]
then
	echo "`date` password for \"${_dbUser}\" in \"${ORACLE_SID}\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
#
#--------------------------------------------------------------------------------
# Verify that the provided ORACLE_HOME value exists as a readable directory and
# that there is an executable SQL*Plus within it...
#--------------------------------------------------------------------------------
if [ ! -d ${ORACLE_HOME} ]
then
	echo "`date` ORACLE_HOME=\"${ORACLE_HOME}\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
if [ ! -d ${ORACLE_HOME}/bin ]
then
	echo "`date` ORACLE_HOME subdirectory \"${ORACLE_HOME}/bin\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
if [ ! -d ${ORACLE_HOME}/lib ]
then
	echo "`date` ORACLE_HOME subdirectory \"${ORACLE_HOME}/lib\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/sqlplus ]
then
	echo "`date` executable \"${ORACLE_HOME}/bin/sqlplus\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/expdp ]
then
	echo "`date` executable \"${ORACLE_HOME}/bin/expdp\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
#
#--------------------------------------------------------------------------------
# Verify that the specified dump directory exists and is both readable and writable...
#--------------------------------------------------------------------------------
if [ ! -d ${_dumpDir} ]
then
	echo "`date` specified dump directory \"${_dumpDir}\" not found; aborting..." | tee -a ${_logFile}
	exit 1
fi
touch ${_dumpDir}/test_touch_$$ > /dev/null 2>&1
if (( $? != 0 ))
then
	echo "`date` specified dump directory \"${_dumpDir}\" not writable; aborting..." | tee -a ${_logFile}
	exit 1
fi
rm -f ${_dumpDir}/test_touch_$$ > /dev/null 2>&1
if (( $? != 0 ))
then
	echo "`date` specified dump directory \"${_dumpDir}\" not readable or writable; aborting..." | tee -a ${_logFile}
	exit 1
fi
#
#--------------------------------------------------------------------------------
# Set the LD_LIBRARY_PATH and PATH environment variables, as generally Oracle
# utilities need them set...
#--------------------------------------------------------------------------------
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export PATH=${ORACLE_HOME}/bin:${PATH}
#
#--------------------------------------------------------------------------------
# If the variable "_userScript" is left blank or NULL, then this script will not
# generate a SQL*Plus script to restore the passwords of database user accounts.
#
# If the variable "_userScript" is not left blank or NOT NULL, then this script
# will generate a SQL*Plus script to restore the passwords of database user
# accounts.
#--------------------------------------------------------------------------------
_userScript=""
####_userScript=${_dumpDir}/reapply_${ORACLE_SID}_users
#
#--------------------------------------------------------------------------------
# If the variable "_userScript" is not null, then generate the SQL*Plus script
# to restore user account passwords...
#--------------------------------------------------------------------------------
if [[ "~${_userScript}~" != "~~" ]]
then
	#
	${ORACLE_HOME}/bin/sqlplus -L -S / as sysdba << __EOF1__ > ${_logFile} 2>&1
whenever oserror exit failure
whenever sqlerror exit failure
set echo off feedback off timing off pagesize 0 linesize 500 trimout on trimspool on pause off
col txt format a450
spool ${_userScript}.sql
select 'REM Generated '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' by Delphix hook' txt from dual;
prompt set echo on feedback on timing on trimout on trimspool on
prompt whenever oserror exit failure
prompt whenever sqlerror exit failure
prompt spool ${_userScript}.lst
prompt 
select	'alter user "'||name||'" identified by values '''||password||''';' txt
from	user$
where	password is not null
and	name not in ('XS\$NULL','ANONYMOUS','GLOBAL_AQ_USER_ROLE','OWB\$CLIENT')
order by 1;
prompt 
prompt spool off
spool off
exit success
__EOF1__
	#
	#------------------------------------------------------------------------
	# Handle any errors returned from SQL*Plus...
	#------------------------------------------------------------------------
	if (( $? != 0 ))
	then
		echo "`date` SQL*Plus to dump user account passwords failed; aborting..." | tee -a ${_logFile}
		echo "Please check spooled output file \"${_logFile}\" for diagnostics..." | tee -a ${_logFile}
		exit 1
	fi
	#
	#------------------------------------------------------------------------
	# Verify that the expected SQL*Plus script was generated, and clean up
	# the spooled output file if no errors...
	#------------------------------------------------------------------------
	if [ ! -r ${_userScript}.sql ]
	then
		echo "`date` generated SQL*Plus script \"${_userScript}.sql\" not found; aborting..." | tee -a ${_logFile}
		echo "Please check spooled output file \"${_logFile}\" for diagnostics..." | tee -a ${_logFile}
		exit 1
	fi
	rm -f ${_logFile}
	#
fi
#
#--------------------------------------------------------------------------------
# Drop/recreate the DELPHIX_REFRESH_DIR object within the database...
#--------------------------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -L -S / as sysdba << __EOF2__ > ${_logFile} 2>&1
whenever oserror exit failure
whenever sqlerror exit failure
set echo on feedback on timing on trimout on trimspool on pause off
create or replace directory DELPHIX_REFRESH_DIR as '${_dumpDir}';
grant READ, WRITE on directory DELPHIX_REFRESH_DIR to PUBLIC;
exit success
__EOF2__
#
#--------------------------------------------------------------------------------
# Handle any errors returned from SQL*Plus...
#--------------------------------------------------------------------------------
if (( $? != 0 ))
then
	echo "`date` SQL*Plus failed to recreate DELPHIX_REFRESH_DIR; aborting..." | tee -a ${_logFile}
	echo "Please check spooled output file \"${_logFile}\" for diagnostics..." | tee -a ${_logFile}
	exit 1
fi
rm -f ${_logFile}
#
#--------------------------------------------------------------------------------
# If a DataPump Export dump file already exists, then re-name it with a timestamp...
#--------------------------------------------------------------------------------
if [ -r ${_dumpDir}/${_dumpFile}.dmp ]
then
	_timeStamp=`date '+%y%m%d.%H%M%S'`
	mv ${_dumpDir}/${_dumpFile}.dmp ${_dumpDir}/${_dumpFile}.dmp.${_timeStamp}
	if (( $? != 0 ))
	then
		echo "\"mv ${_dumpDir}/${_dumpFile}.dmp ${_dumpDir}/${_dumpFile}.dmp.${_timeStamp}\" failed; aborting..." | tee -a ${_logFile}
		exit 1
	fi
fi
#
#--------------------------------------------------------------------------------
# Generate a parameter file to hide the username/password from the command-line
# while running DataPump Export...
#--------------------------------------------------------------------------------
rm -f ${_dumpDir}/${_dumpFile}.par
echo "userid=${_dbUser}/${_dbPwd}"	>  ${_dumpDir}/${_dumpFile}.par
chmod 600 ${_dumpDir}/${_dumpFile}.par
echo "dumpfile=${_dumpFile}.dmp"	>> ${_dumpDir}/${_dumpFile}.par
echo "directory=DELPHIX_REFRESH_DIR"	>> ${_dumpDir}/${_dumpFile}.par
echo "include=DB_LINK"			>> ${_dumpDir}/${_dumpFile}.par
echo "nologfile=Y"			>> ${_dumpDir}/${_dumpFile}.par
echo "full=Y"				>> ${_dumpDir}/${_dumpFile}.par
#
#--------------------------------------------------------------------------------
# Export the database link definitions to the dump file using DataPump Export...
#--------------------------------------------------------------------------------
${ORACLE_HOME}/bin/expdp parfile=${_dumpDir}/${_dumpFile}.par > ${_dumpDir}/${_dumpFile}.log 2>&1
typeset -i _status=$?
#
#--------------------------------------------------------------------------------
# Get rid of the parameter file as quickly as possible...
#--------------------------------------------------------------------------------
rm -f ${_dumpDir}/${_dumpFile}.par
#
#--------------------------------------------------------------------------------
# Handle any errors (except ORA-31655 which indicates that no database links
# exist) returned from DataPump Export...
#--------------------------------------------------------------------------------
grep "^ORA-31655:" ${_dumpDir}/${_dumpFile}.log > /dev/null 2>&1
if (( $? != 0 ))
then
	#------------------------------------------------------------------------
	# If DataPump Export returned a non-zero exit status...
	#------------------------------------------------------------------------
	if (( ${_status} != 0 ))
	then
		echo "`date` EXPDP failed to dump dblinks; aborting..." | tee -a ${_logFile}
		echo "Please check log file \"${_dumpDir}/${_dumpFile}.log\" for diagnostics..." | tee -a ${_logFile}
		exit 1
	fi
	#
	#------------------------------------------------------------------------
	# Verify that the expected SQL*Plus script was generated, and clean up
	# the spooled output file if no errors...
	#------------------------------------------------------------------------
	if [ ! -r ${_dumpDir}/${_dumpFile}.dmp ]
	then
		echo "`date` DataPump Export dump file \"${_dumpDir}/${_dumpFile}.dmp\" not found; aborting..." | tee -a ${_logFile}
		echo "Please check spooled output file \"${_dumpDir}/${_dumpFile}.log\" for diagnostics..." | tee -a ${_logFile}
		exit 1
	fi
fi
rm -f ${_dumpDir}/${_dumpFile}.log
#
#--------------------------------------------------------------------------------
# Completed successfully...
#--------------------------------------------------------------------------------
exit 0