daily pastebin goal
68%
SHARE
TWEET

Schema Refresh Script / Tyler van Vierzen

a guest Mar 30th, 2010 883 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/ksh
  2. # *****************************************************************************
  3. # Filename      : schemarefresh.ksh
  4. # Author        : Tyler van Vierzen
  5. # Description   : automate a schema refresh
  6. # Date          : 2/24/10
  7. # *****************************************************************************
  8.  
  9. if [ $# -lt 1 ];then
  10.  echo "Error: no arguments."
  11.  echo "Help: refreshschema.ksh -h"
  12.  exit 1
  13. fi
  14.  
  15. dbg="0"
  16.  
  17. # check for production host - refuse to run on prod
  18. hn=`hostname`
  19. hn2=$(echo $hn|cut -c1-2)
  20. if [[ $hn2 = "pr" ]];then
  21.  echo "***"
  22.  echo "You are on a PRODUCTION host: "$hn2
  23.  echo "This script is not safe on a production host.  Exiting."
  24.  echo "***"
  25.  exit 1
  26. fi
  27.  
  28. help () {
  29.  print ""
  30.  print "schemarefresh.ksh [[-hisdpflbgpomx] [value]]"
  31.  print ""
  32.  print "Examples: "
  33.  print "schemarefresh.ksh -i MYDB -s MYUSER -d MYUSER -f myDPexportfile%U.dmp -l mylogfile.log "
  34.  print "schemarefresh.ksh -i MYDB -s MYUSER -d MYNEWUSER -f myDPexportfile%U.dmp -l mylogfile.log -b -p "
  35.  print "schemarefresh.ksh -i MYDB -s MYUSER -d MYNEWUSER -f myDPexportfile%U.dmp -l mylogfile.log -b -g -m /mydir/mydir -x 4 "
  36.  print ""
  37.  print " h = This help info."
  38.  print " i = The database you are importing into."
  39.  print " s = Source - the schema you exported from."
  40.  print " d = Destination - the schema you are importing into - this may be the same as -s. "
  41.  print "     If -s and -d are not the same, will add a remap_schema to the impdp command."
  42.  print " f = Filename of the export file.  Include %U if you exported in parallel. "
  43.  print " l = Log filename for your import. Will be placed in the export directory. "
  44.  print " b = [optional] Include this if you do NOT want to back up the destination schema first. "
  45.  print " g = [optional] Include this to export grants and roles for the schema user beforehand, "
  46.  print "     and reapply them afterward. "
  47.  print " p = [optional] Do not print the encrypted password to the screen. "
  48.  print " o = [optional] Include the full syntax for any other data pump options here (such as remap_tablespace). "
  49.  print "     Exactly what you type will be added to the impdp command line. "
  50.  print " m = [optional] Specify a directory to import from that is different from the destination "
  51.  print "     database's export directory. You must still specify filename (using -f).  "
  52.  print " x = [optional] Specify parallel degree. "
  53.  print ""
  54. }
  55.  
  56. INST=""
  57. SRC=""
  58. DEST=""
  59. FILE=""
  60. LOGFILE=""
  61. NOBKUP=""
  62. OTHEROPTS=""
  63. IMPDIRLOC="DEFAULT"
  64. PARA="1"
  65. while getopts hi:s:d:f:l:bgpo:m:x: arg
  66. do
  67.    case $arg in
  68.         h) help
  69.            exit 0;;
  70.         i) INST=$OPTARG
  71.            export INST;;
  72.         s) SRC=$OPTARG
  73.            export SRC;;
  74.         d) DEST=$OPTARG
  75.            export DEST;;
  76.         f) FILE=$OPTARG
  77.            export FILE;;
  78.         l) LOGFILE=$OPTARG
  79.            export LOGFILE;;
  80.         b) NOBKUP="1"
  81.            export NOBKUP;;
  82.         g) EXPGRNT="1"
  83.            export EXPGRNT;;
  84.         p) PRTPW="1"
  85.            export PRTPW;;
  86.         o) OTHEROPTS=$OPTARG
  87.            export OTHEROPTS;;
  88.         m) IMPDIRLOC=$OPTARG
  89.            export IMPDIRLOC;;
  90.         x) PARA=$OPTARG
  91.            export PARA;;
  92.        \?)  echo "Unrecognized option ${arg} "
  93.             exit 1;;
  94.    esac
  95. done
  96.  
  97. handleError() {
  98.  if [ $1 != 0 ];then
  99.    echo "***"
  100.    echo "ERROR: The SQL Command Failed. ErrorCode: $1"
  101.    echo $2
  102.    echo "***"
  103.    exit;
  104.  fi
  105. }
  106.  
  107. printheader () {
  108.  print "************************************************************"
  109.  print "*   Refreshing schema ...                                  *"
  110.  print "************************************************************"
  111.  print "Parameters given: "
  112.  print "INSTANCE:    "${INST}
  113.  print "SOURCE:      "${SRC}
  114.  print "DESTINATION: "${DEST}
  115.  print "FILE:        "${FILE}
  116.  print "LOGFILE:     "${LOGFILE}
  117.  print "NO BACKUP:   "${NOBKUP}
  118.  print "EXP GRANTS:  "${EXPGRNT}
  119.  print "PRINT PW:    "${PRTPW}
  120.  print "OTHER OPTS:  "${OTHEROPTS}
  121.  print "IMPORT DIR:  "${IMPDIRLOC}
  122.  print "PARALLEL:    "${PARA}
  123. }
  124.  
  125. printheader
  126.  
  127. # check required input
  128. if [[ ! -n $INST || ! -n $SRC || ! -n $DEST || ! -n $FILE || ! -n $LOGFILE ]];then
  129.  echo
  130.  echo "***"
  131.  echo "ERROR: Required input missing, schemarefresh.ksh -h for help. "
  132.  echo "***"
  133.  echo
  134.  exit
  135. fi
  136.  
  137. # set env
  138. ORACLE_SID=$INST
  139. . oradbenv $INST
  140.  
  141. fromto=""
  142. if [[ $DEST != $SRC ]];then
  143.  fromto="remap_schema=${SRC}:${DEST}"
  144. fi
  145.  
  146. fd=`date '+%m%d%Y%H%M'`     # date and time for filenames
  147. lf=$fd"_"$ORACLE_SID"_refresh_"${destschema}".log"
  148. dbawork=/dba_work
  149. expdir=expdp"_"$ORACLE_SID
  150. impdir=impdp"_refresh_"$ORACLE_SID
  151. bkloc1=/dba_work/tmp/oracle/$ORACLE_SID/export/
  152. bkloc2=/u93/$hn/$ORACLE_SID/exp/
  153. bkupprog=/oracle/scripts/bin/schemadp.ksh
  154. grantscript="/u01/app/oracle/admin/${ORACLE_SID}/scripts/bin/gen_schema_grants_${ORACLE_SID}.sh"
  155. permsql=/tmp/${ORACLE_SID}_${DEST}_perms.sql
  156.  
  157. if [ ! -e ${bkupprog} ]; then
  158.  echo "***"
  159.  echo ${bkupprog}" does not exist, exiting."
  160.  echo "***"
  161.  exit 1
  162. fi
  163.  
  164. # determine the export directory; /dba_work preferred
  165. echo "***"
  166. echo "Choosing Oracle external directory location ..."
  167. if [ -d $dbawork ]; then
  168.  if [ -d $bkloc1 ];then
  169.   echo "${bkloc1} exists, using that."
  170.  else
  171.   echo "Creating ${bkloc1}."
  172.   mkdir $bkloc1
  173.  fi
  174.  bl=$bkloc1
  175. elif [ -d $bkloc2 ];then
  176.   bl=$bkloc2
  177.   echo "${dbawork} does not exist, using existing ${bkloc2}."
  178. else
  179.   echo "${dbawork} does not exist, creating ${bkloc2}."
  180.   mkdir $bkloc2
  181.   bl=$bkloc2
  182. fi
  183.  
  184. # determine the import directory
  185. if [[ $IMPDIRLOC != "DEFAULT" ]];then
  186.  impdirsql="create or replace directory ${impdir} as '${IMPDIRLOC}';"
  187.  echo "Import directory will be: "${IMPDIRLOC}
  188. else
  189.  impdir=${expdir}
  190.  impdirsql=""
  191.  echo "Import directory will be the same as export directory."
  192. fi
  193.  
  194. ERRORCODE=""
  195. ERRMSG=""
  196.  
  197. # create the external directories as needed in the database
  198. # lock the schema user
  199. # attempt to lock the app user
  200.  
  201. $ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  202. connect / as sysdba;
  203. whenever sqlerror exit sql.sqlcode;
  204. set head off
  205. set pagesize 0
  206. set feedback off
  207. set echo off
  208. set verify off
  209. set heading off
  210. set serveroutput off
  211. set termout off
  212. create or replace directory ${expdir} as '${bl}';
  213. ${impdirsql}
  214. alter user ${DEST} account lock;
  215. whenever sqlerror continue;
  216. alter user ${DEST}_app account lock;
  217. exit;
  218. EOF
  219.  
  220. ERRORCODE=$?
  221. ERRMSG="Create external directory and lock users step failed."
  222. handleError $ERRORCODE "$ERRMSG"
  223. echo "***"
  224. echo "Oracle external directory created and users locked."
  225.  
  226. if [[ ! ${NOBKUP} = "1" ]];then
  227.  echo "***"
  228.  echo "Backing up destination schema: ${DEST}"
  229.  ${bkupprog} ${DEST} ${PARA}
  230. fi
  231.  
  232. # save the default tablespace
  233. TBLSPC=`$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  234. connect / as sysdba;
  235. whenever sqlerror exit sql.sqlcode;
  236. set head off
  237. set pagesize 0
  238. set feedback off
  239. set echo off
  240. set verify off
  241. set heading off
  242. set serveroutput off
  243. set termout off
  244. select DEFAULT_TABLESPACE from dba_users where username='${DEST}';
  245. exit;
  246. EOF`
  247.  
  248. ERRORCODE=$?
  249. ERRMSG="Could not obtain default tablespace."
  250. handleError $ERRORCODE "$ERRMSG"
  251. echo "***"
  252. echo "Default tablespace saved: "${TBLSPC}
  253.  
  254. # save the password
  255. PSWD=`$ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  256. connect / as sysdba;
  257. whenever sqlerror exit sql.sqlcode;
  258. set head off
  259. set pagesize 0
  260. set feedback off
  261. set echo off
  262. set verify off
  263. set heading off
  264. set serveroutput off
  265. set termout off
  266. select PASSWORD from dba_users where username='${DEST}';
  267. exit;
  268. EOF`
  269.  
  270. ERRORCODE=$?
  271. ERRMSG="Could not obtain encrypted password."
  272. handleError $ERRORCODE "$ERRMSG"
  273. echo "***"
  274. if [[ $PRTPW = "1" ]];then
  275.  echo "Encrypted password saved."
  276. else
  277.  echo "Encrypted password saved: "${PSWD}
  278. fi
  279.  
  280. # export permissions if requested
  281.  
  282. if [[ $EXPGRNT = "1" ]];then
  283.  $ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
  284. connect / as sysdba;
  285. whenever sqlerror exit sql.sqlerror;
  286. set head off
  287. set pagesize 0
  288. set feedback off
  289. set echo off
  290. set verify off
  291. set heading off
  292. set serveroutput off
  293. set termout off
  294. spool ${permsql}
  295. select 'grant '||privilege||' to '||grantee||';'
  296. from dba_sys_privs
  297. where grantee = '${DEST}';
  298. select 'grant '||granted_role||' to '||grantee||';'
  299. from dba_role_privs
  300. where grantee='${DEST}';
  301. spool off
  302. exit;
  303. EOF
  304.  
  305.  ERRORCODE=$?
  306.  ERRMSG="Could not export permissions script."
  307.  handleError $ERRORCODE "$ERRMSG"
  308.  echo "***"
  309.  echo "Permissions script exported to: "${permsql}
  310.  
  311. fi
  312.  
  313. # clear any sessions using it
  314. # select 'alter system kill session '''||sid||','||serial#||''';' from V$SESSION where username = 'CELUSER';
  315.  
  316. $ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  317. connect / as sysdba;
  318. set head off
  319. set pagesize 0
  320. set feedback off
  321. set echo off
  322. set verify off
  323. set heading off
  324. set serveroutput off
  325. set termout off
  326. whenever sqlerror continue;
  327. spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
  328. select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
  329. spool off
  330. @/tmp/${ORACLE_SID}_${DEST}_killfile.sql
  331. spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
  332. select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
  333. spool off
  334. @/tmp/${ORACLE_SID}_${DEST}_killfile.sql
  335. spool /tmp/${ORACLE_SID}_${DEST}_killfile.sql
  336. select 'alter system kill session '''||sid||','||serial#||''';' from V\$SESSION where username = '${DEST}';
  337. spool off
  338. @/tmp/${ORACLE_SID}_${DEST}_killfile.sql
  339. exit;
  340. EOF
  341.  
  342. # drop it
  343. $ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
  344. connect / as sysdba;
  345. whenever sqlerror exit sql.sqlerror;
  346. set head off
  347. set pagesize 0
  348. set feedback off
  349. set echo off
  350. set verify off
  351. set heading off
  352. set serveroutput off
  353. set termout off
  354. drop user ${DEST} cascade;
  355. exit;
  356. EOF
  357.  
  358. ERRORCODE=$?
  359. ERRMSG="Could not kill sessions and drop user."
  360. handleError $ERRORCODE "$ERRMSG"
  361. echo "***"
  362. echo "Schema/user dropped."
  363.  
  364. # recreate the user
  365. $ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  366. connect / as sysdba;
  367. whenever sqlerror exit sql.sqlerror;
  368. set head off
  369. set pagesize 0
  370. set feedback off
  371. set echo off
  372. set verify off
  373. set heading off
  374. set serveroutput off
  375. set termout off
  376. create user ${DEST} identified by values '${PSWD}'
  377. default tablespace ${TBLSPC}
  378. temporary tablespace TEMP
  379. profile SERVICEACCT
  380. account lock;
  381. grant connect, resource to "${DEST}";
  382. grant create session to "${DEST}";
  383. grant create view to "${DEST}";
  384. grant unlimited tablespace to "${DEST}";
  385. exit;
  386. EOF
  387.  
  388. ERRORCODE=$?
  389. ERRMSG="Could not create user."
  390. handleError $ERRORCODE "$ERRMSG"
  391. echo "***"
  392. echo "Schema/user created."
  393.  
  394. # import the data
  395. echo "***"
  396. if [[ ${IMPDIRLOC} = "DEFAULT" ]];then
  397.  echo "Importing using external directory ${impdir} which points to: ${bl}"
  398. else
  399.  echo "Importing using external directory ${impdir} which points to: ${IMPDIRLOC}"
  400. fi
  401. echo "***"
  402.  
  403. impdp 'userid="/ as sysdba"' ${fromto} ${OTHEROPTS} \
  404. DIRECTORY=${impdir} \
  405. DUMPFILE=${FILE} \
  406. LOGFILE=${LOGFILE} \
  407. PARALLEL=${PARA}
  408.  
  409. echo "***"
  410. echo "Import complete.  Should say 1 error, due to user already created."
  411.  
  412. # run the grant script if it exists
  413. echo "***"
  414. if [ -e $grantscript ];then
  415.  echo "Running scheduled grant script."
  416.  echo "Script location: "$grantscript
  417.  $grantscript > /dev/null
  418. else
  419.  echo "No grant script found."
  420. fi
  421.  
  422. # reapply the permissions
  423.  
  424. if [[ $EXPGRNT = "1" ]];then
  425.  $ORACLE_HOME/bin/sqlplus -s  /nolog  << EOF
  426. connect / as sysdba;
  427. whenever sqlerror exit sql.sqlerror;
  428. set head off
  429. set pagesize 0
  430. set feedback off
  431. set echo off
  432. set verify off
  433. set heading off
  434. set serveroutput off
  435. set termout off
  436. @${permsql}
  437. exit;
  438. EOF
  439.  
  440.  ERRORCODE=$?
  441.  ERRMSG="Could not apply exported permissions script."
  442.  handleError $ERRORCODE "$ERRMSG"
  443.  echo "***"
  444.  echo "Exported permissions script applied: "${permsql}
  445.  
  446. fi
  447.  
  448. # unlock the schema user
  449. $ORACLE_HOME/bin/sqlplus -s /nolog  << EOF
  450. connect / as sysdba;
  451. whenever sqlerror continue;
  452. set head off
  453. set pagesize 0
  454. set feedback off
  455. set echo off
  456. set verify off
  457. set heading off
  458. set serveroutput off
  459. set termout off
  460. alter user ${DEST} account unlock;
  461. alter user ${DEST}_app account unlock;
  462. exit;
  463. EOF
  464.  
  465. echo "***"
  466. echo "Users unlocked."
  467.  
  468. echo "***"
  469. echo "REFRESH COMPLETE"
  470. echo "***"
  471. echo
  472. echo
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top