Schema Refresh Script / Tyler van Vierzen

#!/bin/ksh
# *****************************************************************************
# Filename      : schemarefresh.ksh
# Author        : Tyler van Vierzen
# Description   : automate a schema refresh
# Date          : 2/24/10
# *****************************************************************************

if [ $# -lt 1 ];then
 echo "Error: no arguments."
 echo "Help: refreshschema.ksh -h"
 exit 1
fi

dbg="0"

# check for production host - refuse to run on prod
hn=`hostname`
hn2=$(echo $hn|cut -c1-2)
if [[ $hn2 = "pr" ]];then
 echo "***"
 echo "You are on a PRODUCTION host: "$hn2
 echo "This script is not safe on a production host.  Exiting."
 echo "***"
 exit 1
fi

help () {
 print ""
 print "schemarefresh.ksh [[-hisdpflbgpomx] [value]]"
 print ""
 print "Examples: "
 print "schemarefresh.ksh -i MYDB -s MYUSER -d MYUSER -f myDPexportfile%U.dmp -l mylogfile.log "
 print "schemarefresh.ksh -i MYDB -s MYUSER -d MYNEWUSER -f myDPexportfile%U.dmp -l mylogfile.log -b -p "
 print "schemarefresh.ksh -i MYDB -s MYUSER -d MYNEWUSER -f myDPexportfile%U.dmp -l mylogfile.log -b -g -m /mydir/mydir -x 4 "
 print ""
 print " h = This help info."
 print " i = The database you are importing into."
 print " s = Source - the schema you exported from."
 print " d = Destination - the schema you are importing into - this may be the same as -s. "
 print "     If -s and -d are not the same, will add a remap_schema to the impdp command."
 print " f = Filename of the export file.  Include %U if you exported in parallel. "
 print " l = Log filename for your import. Will be placed in the export directory. "
 print " b = [optional] Include this if you do NOT want to back up the destination schema first. "
 print " g = [optional] Include this to export grants and roles for the schema user beforehand, "
 print "     and reapply them afterward. "
 print " p = [optional] Do not print the encrypted password to the screen. "
 print " o = [optional] Include the full syntax for any other data pump options here (such as remap_tablespace). "
 print "     Exactly what you type will be added to the impdp command line. "
 print " m = [optional] Specify a directory to import from that is different from the destination "
 print "     database's export directory. You must still specify filename (using -f).  "
 print " x = [optional] Specify parallel degree. "
 print ""
}

INST=""
SRC=""
DEST=""
FILE=""
LOGFILE=""
NOBKUP=""
OTHEROPTS=""
IMPDIRLOC="DEFAULT"
PARA="1"
while getopts hi:s:d:f:l:bgpo:m:x: arg
do
   case $arg in
        h) help
           exit 0;;
        i) INST=$OPTARG
           export INST;;
        s) SRC=$OPTARG
           export SRC;;
        d) DEST=$OPTARG
           export DEST;;
        f) FILE=$OPTARG
           export FILE;;
        l) LOGFILE=$OPTARG
           export LOGFILE;;
	b) NOBKUP="1"
	   export NOBKUP;;
	g) EXPGRNT="1"
	   export EXPGRNT;;
	p) PRTPW="1"
	   export PRTPW;;
	o) OTHEROPTS=$OPTARG
	   export OTHEROPTS;;
        m) IMPDIRLOC=$OPTARG
           export IMPDIRLOC;;
        x) PARA=$OPTARG
           export PARA;;
       \?)  echo "Unrecognized option ${arg} "
            exit 1;;
   esac
done

handleError() {
 if [ $1 != 0 ];then
   echo "***"
   echo "ERROR: The SQL Command Failed. ErrorCode: $1"
   echo $2
   echo "***"
   exit;
 fi
}

printheader () {
 print "************************************************************"
 print "*   Refreshing schema ...                                  *"
 print "************************************************************"
 print "Parameters given: "
 print "INSTANCE:    "${INST}
 print "SOURCE:      "${SRC}
 print "DESTINATION: "${DEST}
 print "FILE:        "${FILE}
 print "LOGFILE:     "${LOGFILE}
 print "NO BACKUP:   "${NOBKUP}
 print "EXP GRANTS:  "${EXPGRNT}
 print "PRINT PW:    "${PRTPW}
 print "OTHER OPTS:  "${OTHEROPTS}
 print "IMPORT DIR:  "${IMPDIRLOC}
 print "PARALLEL:    "${PARA}
}

printheader

# check required input
if [[ ! -n $INST || ! -n $SRC || ! -n $DEST || ! -n $FILE || ! -n $LOGFILE ]];then
 echo
 echo "***"
 echo "ERROR: Required input missing, schemarefresh.ksh -h for help. "
 echo "***"
 echo
 exit
fi

# set env
ORACLE_SID=$INST
. oradbenv $INST

fromto=""
if [[ $DEST != $SRC ]];then
 fromto="remap_schema=${SRC}:${DEST}"
fi

fd=`date '+%m%d%Y%H%M'`     # date and time for filenames
lf=$fd"_"$ORACLE_SID"_refresh_"${destschema}".log"
dbawork=/dba_work
expdir=expdp"_"$ORACLE_SID
impdir=impdp"_refresh_"$ORACLE_SID
bkloc1=/dba_work/tmp/oracle/$ORACLE_SID/export/
bkloc2=/u93/$hn/$ORACLE_SID/exp/
bkupprog=/oracle/scripts/bin/schemadp.ksh
grantscript="/u01/app/oracle/admin/${ORACLE_SID}/scripts/bin/gen_schema_grants_${ORACLE_SID}.sh"
permsql=/tmp/${ORACLE_SID}_${DEST}_perms.sql

if [ ! -e ${bkupprog} ]; then
 echo "***"
 echo ${bkupprog}" does not exist, exiting."
 echo "***"
 exit 1
fi

# determine the export directory; /dba_work preferred
echo "***"
echo "Choosing Oracle external directory location ..."
if [ -d $dbawork ]; then
 if [ -d $bkloc1 ];then
  echo "${bkloc1} exists, using that."
 else
  echo "Creating ${bkloc1}."
  mkdir $bkloc1
 fi
 bl=$bkloc1
elif [ -d $bkloc2 ];then
  bl=$bkloc2
  echo "${dbawork} does not exist, using existing ${bkloc2}."
else
  echo "${dbawork} does not exist, creating ${bkloc2}."
  mkdir $bkloc2
  bl=$bkloc2
fi

# determine the import directory
if [[ $IMPDIRLOC != "DEFAULT" ]];then
 impdirsql="create or replace directory ${impdir} as '${IMPDIRLOC}';"
 echo "Import directory will be: "${IMPDIRLOC}
else
 impdir=${expdir}
 impdirsql=""
 echo "Import directory will be the same as export directory."
fi

ERRORCODE=""
ERRMSG=""

# create the external directories as needed in the database
# lock the schema user
# attempt to lock the app user

$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlcode;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
create or replace directory ${expdir} as '${bl}';
${impdirsql}
alter user ${DEST} account lock;
whenever sqlerror continue;
alter user ${DEST}_app account lock;
exit;
EOF

ERRORCODE=$?
ERRMSG="Create external directory and lock users step failed."
handleError $ERRORCODE "$ERRMSG"
echo "***"
echo "Oracle external directory created and users locked."

if [[ ! ${NOBKUP} = "1" ]];then
 echo "***"
 echo "Backing up destination schema: ${DEST}"
 ${bkupprog} ${DEST} ${PARA}
fi

# save the default tablespace
TBLSPC=`$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlcode;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
select DEFAULT_TABLESPACE from dba_users where username='${DEST}';
exit;
EOF`

ERRORCODE=$?
ERRMSG="Could not obtain default tablespace."
handleError $ERRORCODE "$ERRMSG"
echo "***"
echo "Default tablespace saved: "${TBLSPC}

# save the password
PSWD=`$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlcode;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
select PASSWORD from dba_users where username='${DEST}';
exit;
EOF`

ERRORCODE=$?
ERRMSG="Could not obtain encrypted password."
handleError $ERRORCODE "$ERRMSG"
echo "***"
if [[ $PRTPW = "1" ]];then
 echo "Encrypted password saved."
else
 echo "Encrypted password saved: "${PSWD}
fi

# export permissions if requested

if [[ $EXPGRNT = "1" ]];then
 $ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlerror;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
spool ${permsql}
select 'grant '||privilege||' to '||grantee||';'
from dba_sys_privs
where grantee = '${DEST}';
select 'grant '||granted_role||' to '||grantee||';'
from dba_role_privs
where grantee='${DEST}';
spool off
exit;
EOF

 ERRORCODE=$?
 ERRMSG="Could not export permissions script."
 handleError $ERRORCODE "$ERRMSG"
 echo "***"
 echo "Permissions script exported to: "${permsql}

fi

# clear any sessions using it
# select 'alter system kill session '''||sid||','||serial#||''';' from V$SESSION where username = 'CELUSER';

$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
whenever sqlerror continue;
spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
spool off
@/tmp/${ORACLE_SID}_${DEST}_killfile.sql
spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
spool off
@/tmp/${ORACLE_SID}_${DEST}_killfile.sql
spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
spool off
@/tmp/${ORACLE_SID}_${DEST}_killfile.sql
exit;
EOF

# drop it
$ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlerror;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
drop user ${DEST} cascade;
exit;
EOF

ERRORCODE=$?
ERRMSG="Could not kill sessions and drop user."
handleError $ERRORCODE "$ERRMSG"
echo "***"
echo "Schema/user dropped."

# recreate the user
$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlerror;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
create user ${DEST} identified by values '${PSWD}'
default tablespace ${TBLSPC}
temporary tablespace TEMP
profile SERVICEACCT
account lock;
grant connect, resource to "${DEST}";
grant create session to "${DEST}";
grant create view to "${DEST}";
grant unlimited tablespace to "${DEST}";
exit;
EOF

ERRORCODE=$?
ERRMSG="Could not create user."
handleError $ERRORCODE "$ERRMSG"
echo "***"
echo "Schema/user created."

# import the data
echo "***"
if [[ ${IMPDIRLOC} = "DEFAULT" ]];then
 echo "Importing using external directory ${impdir} which points to: ${bl}"
else
 echo "Importing using external directory ${impdir} which points to: ${IMPDIRLOC}"
fi
echo "***"

impdp 'userid="/ as sysdba"' ${fromto} ${OTHEROPTS} \
DIRECTORY=${impdir} \
DUMPFILE=${FILE} \
LOGFILE=${LOGFILE} \
PARALLEL=${PARA}

echo "***"
echo "Import complete.  Should say 1 error, due to user already created."

# run the grant script if it exists
echo "***"
if [ -e $grantscript ];then
 echo "Running scheduled grant script."
 echo "Script location: "$grantscript
 $grantscript > /dev/null
else
 echo "No grant script found."
fi

# reapply the permissions

if [[ $EXPGRNT = "1" ]];then
 $ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
connect / as sysdba;
whenever sqlerror exit sql.sqlerror;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
@${permsql}
exit;
EOF

 ERRORCODE=$?
 ERRMSG="Could not apply exported permissions script."
 handleError $ERRORCODE "$ERRMSG"
 echo "***"
 echo "Exported permissions script applied: "${permsql}

fi

# unlock the schema user
$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
connect / as sysdba;
whenever sqlerror continue;
set head off
set pagesize 0
set feedback off
set echo off
set verify off
set heading off
set serveroutput off
set termout off
alter user ${DEST} account unlock;
alter user ${DEST}_app account unlock;
exit;
EOF

echo "***"
echo "Users unlocked."

echo "***"
echo "REFRESH COMPLETE"
echo "***"
echo
echo