Hi,
This is my full script
#!/usr/bin/ksh
#--------------------------------------------------------------------------------------------------------------------
#Script
ataPumpExport.ksh
#Author:
#Date : 31-Jul-2013
#Purpose: This script exports a Schema export using expdp and refreshes the Schema using the Latest Backup from Production
#--------------------------------------------------------------------------------------------------------------------
host_name=`hostname`
#MAIL_TO=
vschemas=$1
MAIL_TO=deepak.punjabi@sungard.com
BIN_DIR=/export/home/oracle/admin/KEOMSDB/scripts;export BIN_DIR
. /export/home/oracle/varenvKEOMSDB.sh
SID=`echo $SID|tr 'a-z' 'A-Z'`
drpusersql=/tmp/dropuser_`date "+Date=%d-%m-%y;Time=%T"`.sql;export drpusersql;
drpuserslog=/tmp/dropuser_`date "+Date=%d-%m-%y;Time=%T"`.log;export drpuserslog;
createusersql=/export/home/oracle/admin/KEOMSDB/scripts/createuser.sql;export createusersql
createuserlog=/tmp/dropuser_`date "+Date=%d-%m-%y;Time=%T"`.log;export createuserlog;
get_export_dir_path()
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 0 echo off feed off verify off lines 150
select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='WEEKLY_EXPORT';
EOFF
}
do_export()
{
edisplay=`date "+Date=%d-%m-%y;Time=%T"`
echo "Exported Started at: $edisplay"
echo "Database Name : $ORACLE_SID"
epost_fix=`date "+%Y%m%d%H%M%S"`;export epost_fix;
edir_path=`get_export_dir_path`
echo "Dumpfile directory: $edir_path"
edumpfile=expdp_SAFE_${ORACLE_SID}_${epost_fix}_%U.dmp
ejobname=expdp_SAFE_${ORACLE_SID}_${epost_fix}
elogfile=expdp_SAFE_${ORACLE_SID}_${epost_fix}.log
expdp \"/ as sysdba\" schemas=$vschemas job_name=${ejobname} directory=WEEKLY_EXPORT DUMPFILE=${edumpfile} FILESIZE=20G logfile=${elogfile}
cd $dir_path
error_count=`egrep "ORA-|EXP-" $elogfile|grep -v grep |wc -l`
if [ ${error_count} -gt 0 ]; then
rm -f "*${epost_fix}*.dmp"
df -k >>${elogfile}
# ${BIN_DIR}/SendMail.ksh -s "${NE_REGION}:${SID}: Full datapump export (FAILURE)" -b ${logfile} -e "${MAIL_TO}"
exit
fi
set -x
for filex in *${epost_fix}*.dmp
do
if [ -f $filex ]; then
gzip -c $filex > $filex.gz
ret_code=$?
if [ $ret_code -ne 0 ]; then ##Delete Zip files if Zip is not Successful
rm -f *${epost_fix}*.gz
df -h >>${elogfile}
echo " Zip of DumpFiles is Not Successful " >>${elogfile}
exit
fi
echo "Safe Export and Gip of Dumpfiles for OMS Schema Completed Successfully" >>${elogfile}
exit
fi
done
}
do_export
get_import_dir_path()
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 0 echo off feed off verify off lines 150
select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='WEEKLY_IMPORT';
EOFF
}
lock_user() ## Function To Create a Dynamic Script in /tmp to lock the users to be refreshed
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 0 echo off feed off verify off lines 150
spool /tmp/lock_user.sql
select 'alter user '||username||' account lock ;' from dba_users where username in ('$vschemas');
set pages 1000 lines 120 feed on echo on verify on
spool /tmp/lock_user.log
@/tmp/lock_user.sql
spool off
EOFF
}
kill_session()
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 0 echo off feed off verify off lines 150
spool /tmp/kill_session.sql
select '!kill -9 '||p.spid||chr(10)||'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'from v\$session s,v\$process p where s.USERNAME in ('$vschemas') and s.username is not null and s.paddr=p.addr and s.status in ('INACTIVE','ACTIVE');
spool off
set pages 1000 lines 120 feed on echo on verify on
spool /tmp/kill_session.log
@/tmp/kill_session.sql
spool off
EOFF
}
do_delete_before_import()
{
idir_path=`get_import_dir_path`
idisplay=`date "+Date=%d-%m-%y;Time=%T"`
echo "Dumpfiles and Logfiles Deleted on : $idisplay">>${idir_path}/deleted_dumpfile.log
echo "***************************************************************">>${idir_path}/deleted_dumpfile.log
find ${idir_path} -type f -name "*.dmp" -print >>${idir_path}/deleted_dumpfile.log
find ${idir_path} -type f -name "*.dmp" -exec rm {} \;
#exit
}
do_delete_before_import
do_unzip_before_import()
{
idir_path=`get_import_dir_path`
idisplay=`date "+Date=%d-%m-%y;Time=%T"`
ipost_fix_search=`date "+%Y%m%d"`;
echo " Zip Dumpfiles Used for Refresh : $post_fix_search">>$dir_path/dumpfile_used_forrefresh.log
find $idir_path -type f \( -name "*$ipost_fix_search*.gz" \) -print >>$idir_path/dumpfile_used_forrefresh.log
find $idir_path -type f \( -name "*$ipost_fix_search*.gz" \) -exec gunzip {} \;
idumpfiles=`ls *${ipost_fix_search}.dmp|paste -sd ","`
export idumpfiles
}
generate_drop_user()
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 0 echo off feed off verify off lines 150
spool ${drpusersql}
select 'drop user '||username||' cascade;' from dba_users where USERNAME in ('TEST');
spool off
set pages 1000 lines 120 feed on echo on verify on
spool ${drpuserslog}
@${drpusersql}
spool off
EOFF
}
create_blankuser()
{
sqlplus -s "/ as sysdba"<<EOFF
set pages 1000 lines 120 feed on echo on verify on;
spool ${createuserlog}
@${createusersql}
spool off
EOFF
}
do_precheck()
{
lock_user;
lockuser=$?;export lockuser; echo "Lockuser Return Code is :- $lockuser " > /tmp/ReturnCode.log
if [ "$lockuser" -eq 0 ]; then
kill_session;
killsession=$?;export killsession ;echo " Kill Session Return Code is :- $killsession" >> /tmp/ReturnCode.log
if [ "$killsession" -eq 0 ]; then
do_delete_before_import;
deletebeforeimport=$?;export deletebeforeimport ;echo " Delete Before Import return Code is :-$deletebeforeimport" >> /tmp/ReturnCode.log
if [ "$deletebeforeimport" -eq 0 ]; then
do_unzip_before_import;
unzip=$?;export unzip;echo " Unzip Operation Return Code is :- $unzip" >> /tmp/ReturnCode.log
if [ "$unzip" -eq 0 ]; then
generate_drop_user;
dropuser=$?; export dropuser;echo " Drop User Operation Return Code is :- $dropuser " >> /tmp/ReturnCode.log
if [ "$dropuser" -eq 0 ]; then
create_blankuser;
blankuser=$?;export blankuser; echo " Create Blank User User Operation Return Code is :- $blankuser " >> /tmp/ReturnCode.log
if [ "$blankuser" -eq 0 ]; then
echo "Blank User Creation Successful "
fi
else
echo " Create Blank User Operation Failed " > /tmp/status.log
exit
fi
else
echo " Drop user Failed " > /tmp/status.log
exit
fi
else
echo "Unzip Operation Failed " > /tmp/status.log
exit
fi
else
echo "deletebeforeimport Steps Failed " > /tmp/status.log
exit
fi
else
echo "Killing the Session of User Failed " > /tmp/status.log
exit
fi
else
echo "Locking the USER Failed " > /tmp/status.log
exit
}
do_import()
{
ipost_fix=`date "+%Y%m%d%H%M%S"`;
idir_path=`get_import_dir_path`
ijobname=impdp_${ORACLE_SID}_${ipost_fix}
ilogfile=impdp_${ORACLE_SID}_${ipost_fix}.log
## lock_user; -- Lock the User
## kill_session; -- Kill the Session
## do_delete_before_import; -- Delete the Earlier Unwanted Dumpfiles .
## do_unzip_before_import; -- Unzip the Dumpfile for Refresh
## generate_drop_user; -- Generates Drop User sql file in /tmp and Drops the Required User.
## create_blankuser; -- Creates Blank User.Hardcoded with /export/home/oracle/admin/KEOMSDB/scripts/createuser.sql
do_precheck();
precheck=$?
if [precheck -eq 0 ]; then
{
impdp \"/ as sysdba\" schemas=$vschemas job_name=${impjobname} directory=$impdir DUMPFILE=${dmpfilesforimport} FILESIZE=20G logfile=${implogfile}
cd $dir_path
error_count=`egrep "ORA-|EXP-" $implogfile|grep -v grep |wc -l`
if [ ${error_count} -gt 0 ]; then
{
df -k >>${implogfile}
mail -s " Import of ${vschemas} has Errors.Please check the attached Logfile " < ${implogfile}
exit
}
else
{
df -k >>${implogfile}
mail -s " Import of ${vschemas} has Completed Successful. Please check the attached Logfile " < ${implogfile}
exit
}
fi
}
else
{
mail -s " Import of ${vschemas} has Not been Started.Some of the Prechecks Failed." < /tmp/status.log
exit
}
fi
error :-
DataPumpExport_test.ksh: line 185: syntax error near unexpected token `else'
DataPumpExport_test.ksh: line 185: `else'
Thanks for all your help.