This script is useful to import monthly backup of specific schema from prod to uat after taking necessary schema backup in uat. In prod every month the schema is exported in local mount point. This script is created with user interface manner.
Before running this script add necessary variable details in expimp.txt file.
vi expimp.txt
ENV=env file name
EX= env location
USERNAME=username
PWD=passowrd
EXPDIR= uat env expimpdp directory name
SCHEMANAME=schema name
ftp_site=prod ip
user=prod login name
SDIR=schema dumpfile location in prod
script:
vi expimp.sh
#!/bin/bash
DIR=”$1″
cd $DIR
. $DIR/expimp.txt
. $EX/$ENV
export US_NAME=$USERNAME
export PW=$PWD
export DATEF=`date +”%m-%d-%y”`
export EXPLOG=`echo $SCHEMANAME`_`echo $DATEF`.log
export ELOG=exp_`echo $DATEF`.log
#export DIRL=`echo $EXPDIR`
export DIRLL=`echo $DIR`
export IMPLOG=`echo $DATEF`_IMP.log
echo “WOULD YOU LIKE TO COPY SCHEMA <schema_name > DUMPFILE FROM PROD TO UAT….YES/NO”
read n
case $n in
yes )
echo -e “ENTER SCHEMA DUMP FILE NAME TO UPLOAD\n”
read upschema
ftp -in $ftp_site <<EOF
user $user
cd “$SDIR”
get $upschema
EOF
echo -e “\e[1;32m \n************************SCHEMA DUMPFILE SUCCESSFULLY COPIED****************************\n\n \e[0m”
;;
no ) exit;;
* ) echo “\e[1;32m PLEASE ANS YES/NO \e[0m”;;
esac
echo “WOULD YOU LIKE TO TAKE A BACKUP OF SCHEMA YES/NO”
read e
case $e in
yes )
expdp $US_NAME/$PW directory=$EXPDIR schemas=$SCHEMANAME dumpfile=`echo $SCHEMANAME`_`echo $DATEF`_UATbkp.dmp logfile=$EXPLOG
if grep -i “successfully completed” “$DIRLL/$EXPLOG”
then
echo -e “\e[1;32m \n****************************EXPDP SUCCESSFULLY COMPLETED********************************\n\n \e[0m”
else
echo -e “\e[1;31m \n*************************EXPDP FAILED PLEASE CHECK LOGFILE******************************\n\n \e[0m”
exit
fi
;;
no ) exit;;
* ) echo “\e[1;32m PLEASE ANS YES/NO \e[0m”;;
esac
cd $DIRLL
echo “ARE U SURE WANT TO DROP THE USER yes/no”
read d
case $d in
yes )
`sqlplus / as sysdba<<!
spool myfile.txt
drop user <SCHEMA_NAME> cascade; ######schema name#########
spool off
exit
!`
if grep -i “user dropped” “$DIRLL/myfile.txt”
then
echo -e “\e[1;32m \n*********************************SCHEMA SUCCESSFULLY DROPPED**********************************\n\n \e[0m”
else
echo -e “\e[1;31m \n*************************FAILED TO DROP USER PLEASE CHECK USER DETAILS************************\n\n \e[0m”
exit
fi
;;
no ) exit;;
* ) echo “\e[1;32m PLEASE ANS YES/NO \e[0m”;;
esac
echo “TO IMPORT SCHEMA PLEASE ENTER YES/NO”
read i
case $i in
yes )
echo “GIVE IMPORT SCHEMA DUMP FILE NAME”
read impschema
impdp $US_NAME/$PW directory=$EXPDIR dumpfile=$impschema logfile=$IMPLOG
echo “$IMPLOG >>$DIRLL/$ELOG”
if grep -i “successfully completed” “$DIRLL/$IMPLOG”
then
echo -e “\e[1;32m \n*****************************IMPDP SUCCESSFULLY COMPLETED*******************************\n\n \e[0m”
exit
else
echo -e “\e[1;31m \n*********************FAILED TO IMPORT SCHEMA PLEASE CHECK LOGFILE***********************\n\n \e[0m”
exit
fi
;;
no ) exit;;
* ) echo “\e[1;32m PLEASE ANS YES/NO \e[0m”;;
esac
**************verify the code and Test this script in dev before use this in prod*****************
