# #########################################################################################################################################
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
# THE AUTHOR WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT.
#
# Ver: 1.3
# This script help with importing data using impdp.
#
#                                       #   #     #
# Author:       Mahmmoud ADEL         # # # #   ###
#                                   #   #   # #   #
# Created:      22-12-2020          
# Modified:	31-12-2020 Allow turning OFF the FLASHBACK mode during the import.
#		31-12-2020 Delay the drop of the importer user DBA_BUNDLE7 for 5 sec as some slow workers may not yet cleaned up.
#		27-05-2022 Add the RESUMABLE option, for impdp it can be only set system wide, unlike imp which have the option built-in.
#		01-09-2022 Forcefully drop the exporter user DBA_BUNDLEEXP7
#
#
# #########################################################################################################################################

# ###########
# Description:
# ###########
export SRV_NAME="`uname -n`"

echo
echo "=================================================================="
echo "THIS SCRIPT HELP WITH IMPORTING DATA ON DATABASE | SCHEMA | TABLE."
echo "=================================================================="
echo
sleep 1

echo ""
echo "DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED \"AS IS\"."
echo "THE AUTHOR WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT."
echo ""
echo "Do you agree to continue? [YES | NO]"
echo "========================="
while read DISCLAIMER
do
	case ${DISCLAIMER} in
	Y|y|yes|YES|Yes) echo; break;;
	N|n|no|NO|No|NE) echo; echo "Nothing wrong with being on the safe side :-)";echo "Please test this script on a test environment first to be more confident with it.";echo "SCRIPT TERMINATED! "; echo; exit;;
		      *) echo "Please Enter a valid answer: [YES|NO]";;
	esac
done

echo "Provide the DIRECTORY FULL LOCATION PATH where the export file located: [e.g. /backup/export]" 
echo "======================================================================="
while read DUMPDIRPATH
do
	case ${DUMPDIRPATH} in
         '') export DUMPDIRPATH=`pwd`;   echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
	'.') export DUMPDIRPATH=`pwd`;   echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
	'~') export DUMPDIRPATH=${HOME}; echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
	esac
	if [[ -d "${DUMPDIRPATH}" ]] && [[ -r "${DUMPDIRPATH}" ]] && [[ -w "${DUMPDIRPATH}" ]]
	then
	export DUMPDIR="${DUMPDIRPATH}"; break
	else
	echo; printf "`echo "Please make sure that oracle user has"` `echo -e "\033[33;5mREAD/WRITE\033[0m"` `echo "permissions on the provided directory."`\n"; echo; echo "Enter the complete PATH where the dump file is located: [e.g. /backup/export]"
	fi
done

echo ""
echo "Provide the EXPORT FILE name: [The dump file name to import from]"
echo "============================="
echo "For multiple dump files separate them by comma: e.g. dump01.dmp,dump02.dmp"
echo "For multiple dump files in sequence, replace the sequence number with *: e.g. dump*.dmp"
echo
while read DUMPFILE
do
	case ${DUMPFILE} in
	"")  echo "You MISSED to Enter the export file name!"; echo; echo "Provide the EXPORT FILE name: [e.g. dump01.dmp]";;
	*/*) echo; echo "Enter the FILE NAME only NOT the full path!"; echo; echo "Provide the EXPORT FILE name: [e.g. dump01.dmp]";;
	*)   export DUMPFILENAME="$(sed s/*/%U/g <<<${DUMPFILE})"; echo; echo "File Name is: ${DUMPFILE}"; break;;
	esac
done
        #if [ -f ${DUMPFILE} ]; then
		#export VALID_DUMP=`strings {${DUMPFILEPATH}} | head -1 | grep 'EXPORT' | wc -l`
		#export LEGACY_MODE=`strings {${DUMPFILEPATH}} | head -1 | grep 'EXPORT:' | wc -l`
		#export TABLE_EXPORT_MODE=`strings {${DUMPFILEPATH}} | head -1 | grep TABLE | wc -l`
		#export DUMPDIR="${DUMPFILEPATH%/*}"
		#export DUMPFILENAME="${DUMPFILEPATH##*/}"
	#	break;;
        #else
        #        echo "File not found or not accessible!"
	#	echo
	#	echo "Please Enter a valid path, or make sure it's readable by Oracle user:"
	#	echo "====================================================================="
	#	;;
        #fi

# Warning if the file is not a valid Import file:
#	if [ ${VALID_DUMP} -ne 1 ]
#	then
#	echo
#	echo "WARNING: The provided file doesn't sound like a valid EXPORT file! "
#	echo
#	fi

# #######################################
# 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 is Running !"
   echo
   return
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='^[0-9]+$'
                if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ] || [ ${REPLY} -eq 0 ]
                        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} 2>/dev/null|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

# ########################################
# 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


# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user's profile if not set:

if [ -z "${ORACLE_BASE}" ]
 then
  ORACLE_BASE=`grep -h 'ORACLE_BASE=\/' ${USR_ORA_HOME}/.bash* ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
   export ORACLE_BASE
fi

# ###########################
# INSTANCE OPEN MODE CHECKING:
# ###########################

INSTANCE_STATUS_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off;
SELECT STATUS FROM V\$INSTANCE;
EOF
)
INSTANCE_STATUS=`echo ${INSTANCE_STATUS_RAW}| awk '{print $NF}'`
                case ${INSTANCE_STATUS} in
                "OPEN") echo ;;
                *) echo;echo "ERROR: INSTANCE [${ORACLE_SID}] IS IN STATUS: ${INSTANCE_STATUS} !"
                   echo;echo "PLEASE FIRST OPEN INSTANCE [${ORACLE_SID}] THEN RE-RUN THIS SCRIPT.";echo; exit ;;
                esac


# ###################
# Checking DB Version:
# ###################

DB_VER_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select version from v\$instance;
exit;
EOF
)
DB_VER=`echo ${DB_VER_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
DB_RELEASE=`echo ${DB_VER_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f2 -d '.'`

# ###########################
# Checking FORCE LOGGING mode:
# ###########################
FORCE_LOGGING_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
select force_logging from v\$database;
EOF
)
FORCE_LOGGING=`echo ${FORCE_LOGGING_RAW}| awk '{print $NF}'`

	case ${FORCE_LOGGING} in
        YES) export FORCE_LOGGING=1; echo; echo -e "\033[33;5mFORCE LOGGING MODE IS ENABLED!\033[0m"; echo;;
        *)   export FORCE_LOGGING=0;;
        esac

# ############################
# Checking PARALLELISM Feature:
# ############################
CHK_PARALLELISM_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from v\$option where parameter='Parallel execution' and value='TRUE';
exit;
EOF
)
CHK_PARALLELISM_OPTION=`echo ${CHK_PARALLELISM_OPTION_RAW} | awk '{print $NF}'`

	if [ "${CHK_PARALLELISM_OPTION}" -eq 1 ]
	then
	export PARALLELISM=1
	else
	export PARALLELISM=0
	fi


# ####################
# Checking RAC Feature:
# ####################
CHK_RAC_ACTIVE_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select count(*) from gv\$instance;
EOF
)
CHK_RAC_ACTIVE=`echo ${CHK_RAC_ACTIVE_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

        if [ "${CHK_RAC_ACTIVE}" -gt 1 ]
        then
	export RAC_ACTIVE=1
	else
        export RAC_ACTIVE=0
        fi


# ###########################
# Checking FLASHBACK Feature:
# ###########################
CHK_FLASHBACK_ACTIVE_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select count(*) from v\$database where FLASHBACK_ON='YES';
EOF
)
CHK_FLASHBACK_ACTIVE=`echo ${CHK_FLASHBACK_ACTIVE_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

        if [ "${CHK_FLASHBACK_ACTIVE}" -eq 1 ]
	then
	export FLASHBACK_ACTIVE=1
        else
        export FLASHBACK_ACTIVE=0
	fi


# ##########################################
# db_block_checking parameter value:
# ##########################################
DB_BLOCK_CHECKING_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='db_block_checking';
EOF
)
DB_BLOCK_CHECKING_VAL=`echo ${DB_BLOCK_CHECKING_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

# ##########################################
# db_block_checksum parameter value:
# ##########################################
DB_BLOCK_CHECKSUM_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='db_block_checksum';
EOF
)
DB_BLOCK_CHECKSUM_VAL=`echo ${DB_BLOCK_CHECKSUM_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

# ##########################################
# resumable_timeout parameter value:
# ##########################################
RESUMABLE_TIMEOUT_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='resumable_timeout';
EOF
)
RESUMABLE_TIMEOUT_VAL=`echo ${RESUMABLE_TIMEOUT_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

# #########################################################
# Check the existance of the export DB user DBA_BUNDLEEXP7:
# #########################################################
USER_OBJECTS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER='DBA_BUNDLEEXP7';
EOF
)
USER_OBJECTS_COUNT=`echo ${USER_OBJECTS_COUNT_RAW}| awk '{print $NF}'`
                if [ ${USER_OBJECTS_COUNT} -gt 0 ]
                then
                echo
                printf "`echo "The Exporter User [DBA_BUNDLEEXP7] is already EXIST in the database and has [${USER_OBJECTS_COUNT}] objects and "` `echo -e "\033[33;5mwill be DROPPED\033[0m"` `echo " by this script."`\n"
                echo
                fi

# ##########################################
# _dlm_stats_collect parameter value:
# ##########################################
#STATS_COLLECT_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
#set heading off echo off feedback off termout off
#select y.ksppstvl from x\$ksppi x, x\$ksppcv y where x.indx = y.indx and x.ksppinm like '\_%' escape '\' and x.ksppinm='_dlm_stats_collect';
#EOF
#)
#STATS_COLLECT_VAL=`echo ${STATS_COLLECT_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`


# #########################
# IMPORT Section:
# #########################
# VARIABLES:
# #########
# Date Stamp:
DUMPDATE=`date +%d-%b-%Y`
DUMPDATEFULL=`date +'%d-%b-%Y %T'`
#PASSHALF=`echo $((RANDOM % 999+7000))`
PASSHALF=`date '+%s'`


# ##############################
# Prompt for EMAIL Confirmation:
# ##############################

echo "Enter your EMAIL to receive a notification upon the completion of the Import job: [Leave it BLANK or Enter N to Skip the notification]"
echo "================================================================================"
while read EMAILANS
 do
 case ${EMAILANS} in
 ""|"N"|"n"|"NO"|"No"|"no")export EMAILANS=""; export SENDEMAIL=""; echo; break;;
 	*@*.*) export SENDEMAIL="mail -s \"\${JOBSTATUS} on Server ${SRV_NAME}\" \${EMAILID} < \${LOGFILE}"; echo; break;;
 	*)echo ""
   	echo -e "\033[32;5mThis doesn't sound like a valid Email? ${EMAILANS}\033[0m"
   	echo ""
   	echo "Please Enter your Email: [Leave it BLANK or Enter N to Skip this!]"
   	echo "------------------------"
   	echo "i.e. john.smith@xyzcompany.com"
   	echo "";;
 esac
 done


# ######################
# IMPORT MODES:
# ######################

#if [ "${LEGACY_MODE}" -eq 1 ]
#then

# ######################
# LEGACY IMPORT MODE:
# ######################




#else
# ######################
# DATA PUMP IMPORT MODE:
# ######################

# Specify the IMPORT MODE:
echo "What do you want to IMPORT?"
echo "==========================="
echo "1. FULL_DATABASE"
echo "2. SCHEMAS"
echo "3. TABLES"
while read IMPORTMODEVAR
do
	case ${IMPORTMODEVAR} in
	1|FULL|full|FULL_DATABASE|DB|db) echo; echo "Entering FULL import mode ..."; echo; export TABLE_IMPORT_MODE=0; export IMPORT_MODE="FULL"; break;;
	2|SCHEMAS|SCHEMA|schema|schemas) echo; echo "Entering SCHEMA import mode ..."; echo; export TABLE_IMPORT_MODE=0; export IMPORT_MODE="SCHEMA"; break;;
	3)   echo; echo "Entering TABLE import mode ..."; echo; export TABLE_IMPORT_MODE=1; export IMPORT_MODE="TABLE"; break;;
	*)   echo "Please Enter a number [1, 2, 3]:";;
	esac
done

# PARFILE Setup:
                if [ -w ${DUMPDIR} ]
                then
                export STGDIR=${DUMPDIR}
                else
                export STGDIR=~
                fi
PARFILE_NAME=import_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.par
export PARFILE=${STGDIR}/${PARFILE_NAME}
echo "# This PAR file is for ${IMPORT_MODE} IMPORT task that ran against [${ORACLE_SID}] On [${DUMPDATEFULL}]:" > ${PARFILE}
echo "directory=EXPORT_FILES_DBA_BUNDLE"        			>> ${PARFILE}
echo "dumpfile=${DUMPFILENAME}"                 			>> ${PARFILE}
echo "logfile=import_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.log"   >> ${PARFILE}



	if [ "${TABLE_IMPORT_MODE}" -eq 1 ]
	then
# ENTER TABLE IMPORT MODE:
	echo "Enter the TABLE NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import ALL TABLES in the Export file]"
	echo "========================================"
	echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
	echo ""
		while read LISTOFTABLESVAR
 		do
                 case ${LISTOFTABLESVAR} in
                 "") echo; export LISTOFTABLES=""; break ;;
                  *) echo; export LISTOFTABLES="TABLES=${LISTOFTABLESVAR}"; echo ${LISTOFTABLES} >> ${PARFILE}; break ;;
                 esac
 		done
	
			if [ -z ${LISTOFTABLESVAR} ]
			then
			echo "Enter the TABLE NAMES you want to EXCLUDE from this import job: [Leave it BLANK and hit ENTER to import ALL TABLES]"
			echo "=============================================================="
			echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
                        echo ""
			while read EXCLUDETABLEVAR
 			do
                 	case ${EXCLUDETABLEVAR} in
                 	"") echo; export EXCLUDETABLE=""; break ;;
                  	*)  echo; export EXCLUDETABLE="EXCLUDE=TABLE:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDETABLEVAR}| tr '[:lower:]' '[:upper:]')')\""
                            echo ${EXCLUDETABLE} >> ${PARFILE}; break ;;
                 	esac
 			done
			fi
	else
	echo "Enter the SCHEMA NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import FULL DATABASE or ALL SCHEMAS in the Export file]"
	echo "========================================="
	echo "i.e. SCOTT,HR"
        echo ""
                while read LISTOFSCHEMASVAR
                do
                 case ${LISTOFSCHEMASVAR} in
                 "") echo; export LISTOFSCHEMAS=""; break ;;
                  *) echo; export LISTOFSCHEMAS="SCHEMAS=${LISTOFSCHEMASVAR}"; echo ${LISTOFSCHEMAS} >> ${PARFILE}; break ;;
                 esac
                done

                        if [ -z ${LISTOFSCHEMASVAR} ]
                        then
                        echo "Enter the SCHEMA NAMES you want to EXCLUDE from this import job: [Leave it BLANK and hit ENTER to import ALL SCHEMAS]"
		        echo "==============================================================="
echo "i.e. ANONYMOUS,APPQOSSYS,AUDSYS,BI,CTXSYS,DBSNMP,DIP,DMSYS,DVF,DVSYS,EXDSYS,EXFSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMUSER,LBACSYS,MDSYS,MGMT_VIEW,MDDATA,MTSSYS,ODM,ODM_MTR,OJVMSYS,OLAPSYS,ORACLE_OCM,ORDDATA,ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SPATIAL_CSW_ADMIN,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN,SPATIAL_WFS_ADMIN_USR,SYS,SYSBACKUP,SYSDG,SYSKM,SYSMAN,SYSTEM,TSMSYS,WKPROXY,WKSYS,WK_TEST,WMSYS,XDB,XTISYS,DSSYS,PERFSTAT,REPADMIN,OEM_ADVISOR,OEM_MONITOR,OLAP_DBA,OLAP_USER,OWBSYS,OWBSYS_AUDIT,APEX_030200"

        		echo ""
			while read EXCLUDESCHEMAVAR
                        do
                        case ${EXCLUDESCHEMAVAR} in
                        "") echo; export EXCLUDESCHEMA=""; break ;;
                        *)  echo; export EXCLUDESCHEMA="EXCLUDE=SCHEMA:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDESCHEMAVAR}| tr '[:lower:]' '[:upper:]')')\""
                            echo ${EXCLUDESCHEMA} >> ${PARFILE}; break ;;
                        esac
                        done
                        fi

# Enter TABLE MODE if the user specifies no SCHEMA:	
	if [ -z ${LISTOFSCHEMASVAR} ] && [ -z ${EXCLUDESCHEMAVAR} ]
	then
        echo "Enter the TABLE NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import ALL TABLES in the Export file]"
        echo "========================================"
        echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
        echo ""
                while read LISTOFTABLESVAR
                do
                 case ${LISTOFTABLESVAR} in
                 "") echo; export LISTOFTABLES=""; break ;;
                  *) echo; export LISTOFTABLES="TABLES=${LISTOFTABLESVAR}"; echo ${LISTOFTABLES} >> ${PARFILE}; break ;;
                 esac
                done

                        if [ -z ${LISTOFTABLESVAR} ]
                        then
                        echo "Enter the TABLE NAMES you want to EXCLUDE from this import: [Leave it BLANK and hit ENTER to import ALL TABLES]"
                        echo "=========================================================="
                        echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
                        echo ""
                        while read EXCLUDETABLEVAR
                        do
                        case ${EXCLUDETABLEVAR} in
                        "") echo; export EXCLUDETABLE=""; break ;;
                        *)  echo; export EXCLUDETABLE="EXCLUDE=TABLE:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDETABLEVAR}| tr '[:lower:]' '[:upper:]')')\""
                            echo ${EXCLUDETABLE} >> ${PARFILE}; break ;;
                        esac
                        done
                        fi
	fi
	fi

# REMAP SCHEMA section:
        echo "REMAP SCHEMAS or TABLES OWNER: [Leave it BLANK to keep SCHEMA NAMES | TABLES OWNER same as source]"
        echo "============================="
        echo "SOURCE_SCHEMA:TARGET_SCHEMA i.e. If you want to import objects owned by HR to BLAKE schema:"
        echo "HR:BLAKE"
        echo ""
                while read REMAPSCHEMAVAR
                do
                 case ${REMAPSCHEMAVAR} in
                 "") echo; export REMAPSCHEMA=""; break ;;
                *:*) echo; export REMAPSCHEMA="REMAP_SCHEMA=${REMAPSCHEMAVAR}";echo ${REMAPSCHEMA} >> ${PARFILE}; break ;;
		  *) echo; echo "Please REMAP the SCHEMA in a right format: i.e. SOURCE_SCHEMA_NAME:TARGET_SCHEMA_NAME";;
                 esac
                done

# REMAP TABLE section:
        echo "REMAP TABLE NAMES: [Leave it BLANK to keep TABLE NAMES same as source]"
        echo "================="
        echo "SOURCE_SCHEMA.SOURCE_TABLE:TARGET_TABLE i.e. If you want to import table HR.EMPLOYEES as EMPX then enter it as follows:"
        echo "HR.EMPLOYEES:EMPX"
        echo ""
                while read REMAPTABLEVAR
                do
                 case ${REMAPTABLEVAR} in
                 "") echo; export REMAPTABLE=""; break ;;
                *:*) echo; export REMAPTABLE="REMAP_TABLE=${REMAPTABLEVAR}"; echo ${REMAPTABLE} >> ${PARFILE}; break ;;
		  *) echo; echo "Please REMAP the TABLE in a right format: i.e. SOURCE_TABLE:TARGET_TABLE";;
                 esac
                done

# REMAP TABLESPACE section:
        echo "REMAP TABLESPACE: [Leave it BLANK to import the objects on same TABLESPACE NAME as source]"
        echo "================="
        echo "SOURCE_TABLESPACE:TARGET_TABLESPACE"
	echo "i.e. If you want the imported objects to be created on EXAMPLE tablespace instead of USERS tablespace, then enter it as follows:"
        echo "USERS:EXAMPLE"
        echo ""
                while read REMAPTABLESPACEVAR
                do
                 case ${REMAPTABLESPACEVAR} in
                 "") echo; export REMAPTABLESPACE=""; break ;;
                *:*) echo; export REMAPTABLESPACE="REMAP_TABLESPACE=${REMAPTABLESPACEVAR}"; echo ${REMAPTABLESPACE} >> ${PARFILE}; break ;;
                  *) echo; echo "Please REMAP the TABLESPACE in a right format: i.e. SOURCE_TABLESPACE:TARGET_TABLESPACE";;
                 esac
                done

# TABLE_EXISTS_ACTION SECTION:
	echo "TABLE_EXISTS_ACTION: Specify an action to be taken if the table is ALREADY EXIST: [[SKIP] | APPEND | REPLACE | TRUNCATE]"
        echo "================================================================================"
	echo "1. SKIP     [IF TABLE IS ALREADY EXIST, SKIP ITS IMPORT AND LEAVE IT INTACT [DEFAULT]]"
	echo "2. APPEND   [IF TABLE IS ALREADY EXIST, APPEND THE IMPORTED DATA TO THE TABLE AND LEAVE THE CURRENT DATA INTACT]"
        echo "3. REPLACE  [IF TABLE IS ALREADY EXIST, DROP, RE-CREATE IT AND IMPORT NEW DATA, WHICH WILL DESTROY ITS CURRENT EXISTING DATA]"
	echo "4. TRUNCATE [IF TABLE IS ALREADY EXIST, TRUNCATE IT AND IMPORT NEW DATA, WHICH WILL DESTROY ITS CURRENT EXISTING DATA]"
	echo ""
		while read TABLEEXISTSACTIONVAR
                do
                 case ${TABLEEXISTSACTIONVAR} in
                 ""|1|SKIP|skip|Skip) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=SKIP"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
				      echo "SKIP MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE [PRESERVED]"; echo; break;;
	      2|APPEND|append|Append) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=APPEND"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
	  			      echo "APPEND MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE [PRESERVED]"; echo; break;;
           3|REPLACE|replace|Replace) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=REPLACE"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
	   			      printf "`echo "REPLACE  MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE ["` `echo -e "\033[33;5mDESTROYED\033[0m"` `echo "]"`\n"; echo; break;;
        4|TRUNCATE|truncate|Truncate) echo; export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=TRUNCATE"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
				      printf "`echo "TRUNCATE MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE ["` `echo -e "\033[33;5mDESTROYED\033[0m"` `echo "]"`\n"; echo; break;;
 		 		   *) echo; echo "Please Enter a valid NUMBER between 1 to 4";;
                 esac
                done

# CONTENT SECTION:
	echo "Specify if you want to import DATA or METADATA(DDLs) or both: [[DATA+METADATA] | METADATA_ONLY | DATA_ONLY]"
        echo "============================================================"
        echo "1. DATA+METADATA (ALL) [DEFAULT]"
        echo "2. METADATA_ONLY"
        echo "3. DATA_ONLY"
        echo ""
		while read CONTENTVAR
                do
                 case ${CONTENTVAR} in
                 ""|1|DATA+METADATA|data+metadata|DATAMETADATA|all|ALL) export CONTENT="CONTENT=ALL"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
		 2|METADATA|METADATA_ONLY|metadata|metadata_only) export CONTENT="CONTENT=METADATA_ONLY"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
		 3|DATA|data|DATA_ONLY|data_only|dataonly|DATAONLY) export CONTENT="CONTENT=DATA_ONLY"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
                 *) echo; echo "Please Enter a valid NUMBER between 1 to 3";;
                 esac
                done

# INCLUDE ONLY SPECIFIC OBJECTS AND DENY THE OTHERS:
        echo "Specify if you want to import ONLY specific objects and IGNORE the rest in the dump file: [Leave it BLANK to include all objects in the dump file]"
	echo "========================================================================================"
	echo "Note: TABLE option is valid ONLY in FULL & SCHEMA import modes."
        echo "e.g. To import ONLY specific object types, separate them by comma: TABLE,INDEX,CONSTRAINT,REF_CONSTRAINT,TRIGGER,PROCEDURE,PACKAGE,FUNCTION,..."
        echo "e.g. You can IMPORT ONLY the INDEXES starting with name PROD by typing: INDEX:\"LIKE 'PROD%'\""
        echo ""
                while read INCLUDEOBJECTSVAR
                do
                 case ${INCLUDEOBJECTSVAR} in
                 "") echo; export INCLUDEOBJECTS=""; break;;
                  *) echo; export INCLUDEOBJECTS="INCLUDE=${INCLUDEOBJECTSVAR}"; echo ${INCLUDEOBJECTS} >> ${PARFILE}; echo; echo "EXCLUDE option will not be shown because INCLUDE option is used, whereas both options are mutually exclusive.";echo;break;;
                 esac
                done

# Hide the following EXCLUDE options if INCLUDE is used, because INCLUDE & EXCLUDE are mutually exclusive:

		if [ -z "${INCLUDEOBJECTSVAR}" ]
		then

# EXCLUDE OTHER OBJECT TYPES:
	echo "Specify if you want to EXCLUDE specific object types from this import: [Leave it BLANK to include all objects in the dump file]"
        echo "====================================================================="
        echo "Note: TABLE option is valid ONLY in FULL & SCHEMA import modes."
	echo "e.g. you can exclude one or more object type separating with comma: TABLE,INDEX,CONSTRAINT,REF_CONSTRAINT,TRIGGER,PROCEDURE,PACKAGE,FUNCTION,..."
	echo "e.g. you can exclude all tables start with name TEMP: TABLE:\"LIKE 'TEMP%'\""
        echo ""
                while read EXCLUDEOTHEROBJECTSVAR
                do
                 case ${EXCLUDEOTHEROBJECTSVAR} in
                 "") echo; export EXCLUDEOTHEROBJECTS=""; break;;
		  *) echo; export EXCLUDEOTHEROBJECTS="EXCLUDE=${EXCLUDEOTHEROBJECTSVAR}"; echo ${EXCLUDEOTHEROBJECTS} >> ${PARFILE}; break;;
                 esac
                done


# EXCLUDE GRANTS SECTION:
        echo "Do you want to import the OBJECT PRIVILEGES? [[YES] | NO]"
        echo "============================================"
        echo ""
                while read GRANTSVAR
                do
                 case ${GRANTSVAR} in
                 ""|YES|Yes|yes|y|Y) echo; export GRANTS=""; break;;
                       NO|No|no|n|N) echo; export GRANTS="EXCLUDE=OBJECT_GRANT"; echo ${GRANTS} >> ${PARFILE}; break;;
                                  *) echo; echo "Please Enter a valid ANSWER YES or NO:";;
                 esac
                done


# EXCLUDE STATISTICS SECTION:
        echo "Do you want to import the STATISTICS? [[YES] | NO]"
        echo "====================================="
        echo ""
                while read STATISTICSVAR
                do
                 case ${STATISTICSVAR} in
                 ""|YES|Yes|yes|Y|y) echo; export STATISTICS=""; break;;
		       NO|No|no|N|n) echo; export STATISTICS="EXCLUDE=STATISTICS"; echo ${STATISTICS} >> ${PARFILE}; break;;
                                  *) echo; echo "Please Enter a valid ANSWER YES or NO:";;
                 esac
                done

		fi

echo -e "\033[33;5m[IMPDP PERFORMANCE SETTINGS]\033[0m";echo

# PARALLEL SECTION:
	# If database edition support the PARALLEL option:
        if [ "${CHK_PARALLELISM_OPTION}" -eq 1 ]
        then
	# Get the CPU Count on the machine:
        	case `uname` in
        	Linux ) export CPUCOUNT=`cat /proc/cpuinfo| grep processor|wc -l`;;
        	AIX )   export CPUCOUNT=`lsdev -C|grep Process|wc -l`;;
        	SunOS ) export CPUCOUNT=`kstat cpu_info|grep core_id|sort -u|wc -l`;;
        	HP-UX)  export CPUCOUNT=`lsdev -C|grep Process|wc -l`;;
        	esac

        echo "Enter the PARALLELISM DEGREE if you want to run the import in PARALLEL: [Leave it BLANK for NO PARALLEL | CPU COUNT is: ${CPUCOUNT}]"
	echo "======================================================================"
        echo ""
                while read PARALLELDEGREEVAR
                do
                 case ${PARALLELDEGREEVAR} in
                 "") echo; export PARALLELDEGREE=""; break;;
	   *[!0-9]*) echo "Please enter a valid NUMBER:";;
           	  *) echo; export PARALLELDEGREE="PARALLEL=${PARALLELDEGREEVAR}"; echo ${PARALLELDEGREE} >> ${PARFILE}; break;;
                 esac
                done

        fi

# CLUSTER SECTION:
	# If cluster detected and other instances are open:
	if [ "${RAC_ACTIVE}" -eq 1 ]
        then
        echo "Do you want to BALANCE the import load between RAC nodes? [YES | [NO]] [Note: The dumpfile should be located on a SHARED location]"
        echo "========================================================="
        echo ""
                while read CLUSTERVAR
                do
                 case ${CLUSTERVAR} in
                 ""|N|n|NO|no) echo; export CLUSTER=""; break;;
		 Y|y|YES|yes)  echo; export CLUSTER="CLUSTER=YES"; echo ${CLUSTER} >> ${PARFILE}; break;;
		 	   *)  echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

        fi

	# If DB Version is 12c+:
	if [ ${DB_VER} -gt 11 ]
	then

# Disable ARCHIVE LOGGING for the import:
        echo "Perform the import in NOLOGGING mode? [[YES] | NO] [NOT RECOMMENDED to use NOLOGGING if this DB is DataGuarded by a STANDBY DB]"
        echo "====================================="
	echo "Note: Only the import operation will run in NOLOGGING mode by setting TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y"
        echo ""
                while read ARCHIVELOGGINGVAR
                do
                 case ${ARCHIVELOGGINGVAR} in
                 N|n|NO|no) echo; export ARCHIVELOGGING=""; break;;
            ""|Y|y|YES|yes) export ARCHIVELOGGING="TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y"; echo ${ARCHIVELOGGING} >> ${PARFILE};echo "TRANSFORM=DISABLE_ARCHIVE_LOGGING mode activated.";echo; break;;
                         *) echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

# Enable ADDITIONAL PARAMETERS for 12c+:

# Enable LOGTIME: [Timed Log Records]
echo "LOGTIME=all" >> ${PARFILE}

# Enable METRICS: [Detailed log]
echo "METRICS=YES" >> ${PARFILE}

# Enable STATUS update every 30sec:
#echo "STATUS=30"   >> ${PARFILE}

	fi
                
echo -e "\033[33;5m[DATABASE PERFORMANCE SETTINGS]\033[0m";echo

# Disable FORCE LOGGING mode:
	# If FORCE LOGGING is ENABLED:
	if [ "${FORCE_LOGGING}" -eq 1 ]
	then
        echo "FORCE LOGGING mode is ENABLED | Do you want to DISABLE it during the import? [YES | [NO]] [NOT RECOMMENDED to disable if this DB is DataGuarded by a STANDBY DB]"
        echo "============================================================================"
        echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
        echo ""
                while read FORCELOGGINGVAR
                do
                 case ${FORCELOGGINGVAR} in
                 ""|N|n|NO|no) echo; export FORCELOGGING=""; break;;
                  Y|y|YES|yes) export FORCELOGGING="ALTER DATABASE NO FORCE LOGGING;";PRESERVEFORCELOGGING="ALTER DATABASE FORCE LOGGING;"; echo "FORCE LOGGING mode will be disabled during the import and will be enabled back after the import."; echo; break;;
                            *) echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

        fi


# Disable FLASHBACK mode:
        # If FLASHBACK mode is ENABLED:
        if [ "${FLASHBACK_ACTIVE}" -eq 1 ]
        then
        echo "FLASHBACK mode is ENABLED | Do you want to DISABLE it during the import? [[YES] | NO]"
        echo "========================================================================"
        echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
        echo ""
                while read FLASHBACKVAR
                do
                 case ${FLASHBACKVAR} in
                      N|n|NO|no) echo; export FLASHBACK=""; break;;
                 ""|Y|y|YES|yes) export FLASHBACK="ALTER DATABASE FLASHBACK OFF;";PRESERVEFLASHBACK="ALTER DATABASE FLASHBACK ON;"; echo "FLASHBACK mode will be disabled during the import and will be enabled back after the import."; echo; break;;
                            *) echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

        fi


# Disable of DB_BLOCK_CHECKING:
	# IF DB_BLOCK_CHECKING_VAL=TRUE
        if [ "${DB_BLOCK_CHECKING_VAL}" = "TRUE" ]
	then
        echo "DB_BLOCK_CHECKING is set to TRUE, Do you want to DISABLE it to speed up the import? [[YES] | NO]"
        echo "==================================================================================="
        echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
        echo ""
                while read DBBLOCKCHECKINGVAR
                do
                 case ${DBBLOCKCHECKINGVAR} in
                      N|n|NO|no) echo; export DBBLOCKCHECKING=""; break;;
                 ""|Y|y|YES|yes) export DBBLOCKCHECKING="ALTER SYSTEM SET db_block_checking=FALSE SCOPE=MEMORY;"; export PRESERVEDBBLOCKCHECKING="ALTER SYSTEM SET db_block_checking=TRUE;";echo "DB_BLOCK_CHECKING will be disabled during the import and will be enabled back after the import."; echo; break;;
                            *) echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

        fi

# Disable of DB_BLOCK_CHECKSUM:
        # IF DB_BLOCK_CHECKSUM_VAL=TRUE
        if [ "${DB_BLOCK_CHECKSUM_VAL}" = "TYPICAL" ] || [ "${DB_BLOCK_CHECKSUM_VAL}" = "FULL" ]
        then    
        echo "DB_BLOCK_CHECKSUM is set to TRUE, Do you want to DISABLE it to speed up the import? [[YES] | NO]"
        echo "==================================================================================="
        echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
        echo ""
                while read DBBLOCKCHECKSUMVAR
                do
                 case ${DBBLOCKCHECKSUMVAR} in
                      N|n|NO|no) echo; export DBBLOCKCHECKSUM=""; break;;
                 ""|Y|y|YES|yes) export DBBLOCKCHECKSUM="ALTER SYSTEM SET db_block_checksum=OFF SCOPE=MEMORY;"
				 export PRESERVEDBBLOCKCHECKSUM="ALTER SYSTEM SET db_block_checksum=${DB_BLOCK_CHECKSUM_VAL};"
				 echo "DB_BLOCK_CHECKSUM will be disabled during the import and will be enabled back after the import."; echo; break;;
                            *) echo "Please enter a valid answer: [YES|NO]";;
                 esac
                done

        fi

# Enable RESUMABLE TIMEOUT:
        echo "Enter the number of HOURs this import can RESUME within if it gets hung due to SPACE limitation: [Leave it Blank to Skip RESUMABLE_TIMEOUT]"
        echo "================================================================================================"
	echo "Note: This will set the parameter resumable_timeout system wide and will set it back to its original value after the import."
	echo ""
		while read RESUMABLEVAR
	 	do
        	 case ${RESUMABLEVAR} in
          		"") export RESUMABLESTMT=""; break;;
    			*[!0-9]*) echo "Please enter a valid NUMBER:"
				  echo "----------------------------";;
           		*) RESUMABLEVARSEC=$((${RESUMABLEVAR} * 3600))
			   export RESUMABLESTMT="ALTER SYSTEM SET resumable_timeout=${RESUMABLEVARSEC} SCOPE=MEMORY;"
			   export PRESERVERESUMABLESTMT="ALTER SYSTEM SET resumable_timeout=${RESUMABLE_TIMEOUT_VAL} SCOPE=BOTH;"
			   echo ""; echo "RESUMABLE_TIMEOUT will be set to: ${RESUMABLEVAR} Hours"; echo ""; break;;
        	 esac
 		done



echo
echo "You are almost done!"; echo
echo "Hint: Make sure that TEMPORARY & UNDO Tablespaces along with UNDO_RETENTION Parameter are big enough to support this import operation."
echo ""
sleep 1

echo "Please verify the import settings summary:"
echo "------------------------------------------"
cat ${PARFILE}
echo
sleep 2
echo "Shall we start the IMPORT now? [[YES] | NO]"
echo "=============================="
while read STARTNOW
do
 case ${STARTNOW} in
      N|n|NO|no) echo; echo "SCRIPT TERMINATED! "; echo; exit;;
 ""|Y|y|YES|yes) echo; echo "STARTING THE IMPORT ..."; echo; break;;
            *) echo "Please enter a valid answer: [YES|NO]";;
 esac
done




VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SPOOL ${STGDIR}/user_creation.log
PROMPT CREATE USER DBA_BUNDLEEXP7 [IMPORTER USER] (WILL BE DROPPED AFTER THE IMPORT) ...
CREATE USER DBA_BUNDLEEXP7 IDENTIFIED BY "BUNdle_#-^${PASSHALF}" ACCOUNT UNLOCK;
ALTER USER DBA_BUNDLEEXP7 IDENTIFIED BY "BUNdle_#-^${PASSHALF}" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO DBA_BUNDLEEXP7;
GRANT DBA TO DBA_BUNDLEEXP7;
-- The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_BUNDLEEXP7;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_BUNDLEEXP7;
PROMPT
PROMPT CREATING DIRECTORY EXPORT_FILES_DBA_BUNDLE POINTING TO ${DUMPDIR} ...
CREATE OR REPLACE DIRECTORY EXPORT_FILES_DBA_BUNDLE AS '${DUMPDIR}';
PROMPT
PROMPT DISABLING DB FEATURES TO SPEED UP THE IMPORT: [will be enabled back after the import]
${FORCELOGGING}
${FLASHBACK}
${DBBLOCKCHECKING}
${DBBLOCKCHECKSUM}
${RESUMABLESTMT}
PROMPT

SPOOL OFF
EOF
)

echo
# Creation of the Import Script:
export LOGFILE="${STGDIR}/IMPORT_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.log"
export IMPORTSCRIPT=${STGDIR}/IMPORTSCRIPT.sh
export IMPORTSCRIPTRUNNER=${STGDIR}/IMPORTSCRIPTRUNNER.sh

echo "# IMPORT Script: [Created By DBA_BUNDLE]"							> ${IMPORTSCRIPT}
echo "echo ''"                                                                                  >>${IMPORTSCRIPT}
echo "echo \"While the Import job is running, you can check the STATUS using:\""		>>${IMPORTSCRIPT}
echo "echo \"--------------------------------------------------------------- \""                >>${IMPORTSCRIPT}
echo "echo \"SELECT job_name, operation, job_mode, DEGREE, state FROM dba_datapump_jobs where OPERATION='IMPORT' and state='EXECUTING' and owner_name='DBA_BUNDLEEXP7';\""								                >>${IMPORTSCRIPT}
echo "echo ''"                                                                                  >>${IMPORTSCRIPT}
echo "echo \"Then you can ATTACH to the import job and control it using:\""  			>>${IMPORTSCRIPT}
echo "echo \"---------------------------------------------------------- \""                     >>${IMPORTSCRIPT}
echo "echo \"impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" ATTACH=<JOB_NAME_FROM_ABOVE_COMMAND>\""   	>>${IMPORTSCRIPT}
echo "echo \"i.e.\""										>>${IMPORTSCRIPT}
echo "echo \"impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" ATTACH=SYS_IMPORT_${IMPORT_MODE}_01\""	>>${IMPORTSCRIPT}
echo "echo \"To Show the STATUS:....... STATUS\""						>>${IMPORTSCRIPT}
echo "echo \"To KILL the import:....... KILL_JOB\""                                             >>${IMPORTSCRIPT}
echo "echo \"To PAUSE the import:...... STOP_JOB\""                                             >>${IMPORTSCRIPT}
echo "echo \"To RESUME a paused import: START_JOB\""                                            >>${IMPORTSCRIPT}
echo "export ORACLE_SID=${ORACLE_SID}"                                                          >>${IMPORTSCRIPT}
echo "echo ''"                                                                                  >>${IMPORTSCRIPT}
echo "echo 'Running The IMPORT Job Now ...'"                                                    >>${IMPORTSCRIPT}
echo "${ORACLE_HOME}/bin/impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" PARFILE=${PARFILE}"     >>${IMPORTSCRIPT}
echo "echo ''"                                                                                  >>${IMPORTSCRIPT}
echo "echo 'Running Post IMPORT Steps ...'"							>>${IMPORTSCRIPT}
echo "echo ''"											>>${IMPORTSCRIPT}
echo "sleep 5"                                                                                  >>${IMPORTSCRIPT}
echo "${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF"					>>${IMPORTSCRIPT}
echo "PROMPT"											>>${IMPORTSCRIPT}
echo "PROMPT DROPPING THE IMPORTER USER DBA_BUNDLEEXP7  ..."					>>${IMPORTSCRIPT}
echo "DROP USER DBA_BUNDLEEXP7 CASCADE;"							>>${IMPORTSCRIPT}
echo "PROMPT PRESERVE ORIGINAL DB SETTINGS ..."                                                 >>${IMPORTSCRIPT}
echo "${PRESERVEFORCELOGGING}"                                                                  >>${IMPORTSCRIPT}
echo "${PRESERVEFLASHBACK}"                                                                     >>${IMPORTSCRIPT}
echo "${PRESERVEDBBLOCKCHECKING}"                                                               >>${IMPORTSCRIPT}
echo "${PRESERVEDBBLOCKCHECKSUM}"                                                               >>${IMPORTSCRIPT}   
echo "${PRESERVERESUMABLESTMT}"                                                                 >>${IMPORTSCRIPT}   
echo "EOF"											>>${IMPORTSCRIPT}
echo "export JOBSTATUS=\`grep \"successfully\\|stopped\\|completed\" ${LOGFILE}|tail -1\`"      >>${IMPORTSCRIPT}
echo "export LOGFILE=${LOGFILE}"                                                                >>${IMPORTSCRIPT}
echo "export EMAILID=\"${EMAILANS}\""                                                           >>${IMPORTSCRIPT}
echo "${SENDEMAIL}"                                                                             >>${IMPORTSCRIPT}
echo "echo; echo -e '\033[32;5mIMPORT IS DONE.\033[0m'"                                         >>${IMPORTSCRIPT}
echo "echo"                                                                                     >>${IMPORTSCRIPT}

chmod 740 ${IMPORTSCRIPT}

echo
echo "#!/bin/bash"   										> ${IMPORTSCRIPTRUNNER}
echo "nohup sh ${IMPORTSCRIPT}| tee ${LOGFILE} 2>&1 &"  					>>${IMPORTSCRIPTRUNNER}
chmod 740 ${IMPORTSCRIPTRUNNER}
echo -e "\033[32;5mFeel free to EXIT from this session as the IMPORT SCRIPT is running in the BACKGROUND.\033[0m";echo
source ${IMPORTSCRIPTRUNNER}

# #############
# END OF SCRIPT
# #############
# Your feedback is most welcome, e-mail me at: mahmmoudadel@hotmail.com
# Every month a new release of DBA BUNDLE will get published, make sure to download it from:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html