Pennsylvania BANNER Users Group 2006

Imperial Valley College Robin Ying robin.ying@imperial.edu 760-355-6377 Pennsylvania BANNER Users Group2006 Instance Neutral Cloning Script

General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Questions will be answered at the end of the session Thank you for your cooperation

Background • Our Environment: • Users want to have their exclusive cloned databases – AR, FA, FIN, HR, STU. • Adding the TEST, PPRD, CURR, TRNG, and IVC (production), there are 10 instances. • Using the opportunity of conversion from Version 6 to Version 7 to clean up the code. • Having failed history due to production database parameter changes.

Goals of Refactoring • Allow only one place to make code changes. • Simplifies maintenance when the parameter of the production database changes • Avoid any hard-coded path names. • Avoid any hard-coded instance names. • Not affecting the baseline, maintaining the directory structure and hierarchy. • Easy to carry forward • Refactoring Result –separating the old code into an incidence independent (generic) part and a calling part.

Walk through the code ... 3 # CLONE.shl -- generic clone script, no hard-coded pathnames. 5 # Usage: CLONE.shl $1 $2 $3 $4 $5 $6 6 # $1: oracle_sid of clone 7 # $2: backup dir of the source dataabase (e.g. /u04/backups/IVC) 8 # $3: dest dir of the clone database (e.g. /u02/oradata/$ORACLE_SID) 9 # $4: command dir of the clone database (e.g. /u01/app/oracle/admin/$ORACLE_SID/scripts) 10 # $5: oracle_sid of production instance (e.g. IVC) 11 # $6: archive directory (e.g. /logs/archive) 13 # Run this script as oracle (i.e. "su oracle") 19 set -e 20 [ $# != 6 ] && { echo "Usage: $0 oracle_sid source_dir dest_dir cmd_dir"; exit 1; } 21 [ $1 = $5 ] && { echo "$5 is the production instance!!"; exit 1; } 6 parameters Error checking

Walk through the code ... 23 ORACLE_SID=$1 # command line overrides env setting 24 BACKUPDIR=$2 25 DESTDIR=$3 26 CMDDIR=$4 27 PROD=$5 28 ARCHDIR=$6 29 PATH=/usr/local/oracle/bin:/bin 30 oraenv=/usr/local/bin/oraenv_local 31 export PATH ORACLE_SID 36 /bin/grep $ORACLE_SID $oraenv > /dev/null 37 [ $? = 0 ] || { echo "$ORACLE_SID is not a valid ORACLE_SID"; exit 1; } 39 # /bin/grep $PROD $oraenv > /dev/null 40# [ $? = 0 ] || { echo "$PROD is not a valid ORACLE_SID"; exit 1; } 42 [ -d $ARCHDIR ] || { echo "$ARCHDIR does not exits"; exit 1; } 43 [ -d $BACKUPDIR ] || { echo "$BACKUPDIR does not exits"; exit 1; } 44 [ -d $CMDDIR ] || { echo "$CMDDIR does not exits"; exit 1; } 45 [ -d $DESTDIR ] || { echo "$DESTDIR does not exits"; exit 1; } Error checking

Walk through the code ... 49 # Get a clean shutdown of the database to avoid temp file problems. 50 sqlplus /nolog << EOF 51 connect /as sysdba 52 shutdown abort 53 startup restrict 54 shutdown immediate 55 EOF 57 # cleanup old files to avoid write/overwrite errors 58 rm -f $DESTDIR/* 60 # copy all files from the BACKUPDIR 61 for f in $BACKUPDIR/* 62 do 63 echo "-- copying $f to $DESTDIR ..." 64 cp -rp $f $DESTDIR 65 done 67 # copy the archive files 68 echo "-- copying archive files to $DESTDIR ..." 69 find $ARCHDIR -name "*.arc" -mtime -3 -exec cp -rp {} $DESTDIR \;

Keep the parameters in one place only Walk through the code ... 71 # Rename files 72 echo "-- rename files in $DESTDIR ..." 73 for f in $DESTDIR/*${PROD}* 74 do 75 mv $f `echo $f | sed "s/$PROD/$ORACLE_SID/"` 76 done 78 # generate the sql script for recreating the control file 79 # modify here if the control files are changed. 80 tmpf=$DESTDIR/create_${ORACLE_SID}_ctlfile.sql 82 echo "STARTUP NOMOUNT 118 '$DESTDIR/${ORACLE_SID}_xdb_01.dbf' 119 CHARACTER SET WE8ISO8859P1;" > $tmpf

Walk through the code ... 121 # run the sql script 122 echo "-- recreating control files ..." 123 sqlplus /nolog << EOF 124 connect /as sysdba 125 @$tmpf 126 EOF 128 # apply all of the archive files in the BACKUPDIR to bring the database up to last good file 129 # and clean up the archive files 130 for f in $DESTDIR/*.arc 131 do 132 echo "-- applying archive file $f ..." 133 sqlplus /nolog << EOF 134 connect / as sysdba 135 recover database using backup controlfile until cancel; 136 $f 137 cancel 138 EOF 139 done 140 rm $DESTDIR/*.arc

Keep the parameters in one place only Walk through the code ... 142 # generate the sql script for opening up the database and configure for the 143 # clone instance. modify here if needed. 144 tmpf=/tmp/after_${ORACLE_SID}_clone.sql 145 pls=/pls/`echo $ORACLE_SID | dd conv=lcase` 2> /dev/null 147 echo "ALTER TABLESPACE TEMP ADD TEMPFILE '$DESTDIR/${ORACLE_SID}_temp_01.dbf' 148 SIZE 611319808 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 2000M; 155 -- Update ae_paths table for Xtender 159 -- UPDATE OTGMGR.AE_PATHS SET PATH='E:\IMAGES\$ORACLE_SID\FT_PATH' WHERE PATHID=4;" > $tmpf

Calling Script 6 SID=FA # set the oracle_sid 8 BACKUPDIR=/u04/backups/IVC # backup of the production database 9 ARCHIVEDIR=/logs/archives # archive directory 11 DESTDIR=/u02/oradata/$SID # directory of FA database 12 CMDDIR=/u01/app/oracle/admin/$SID/scripts # directory of FA clone commands 23 sh CLONE.shl $SID$BACKUPDIR$DESTDIR$CMDDIRIVC$ARCHIVEDIR >> ${SID}_clone_$$.log 2>&1 6 parameters

Questions? PABUG 2006

Imperial Valley College Robin Ying robin.ying@imperial.edu 760-355-6377 Pennsylvania BANNER Users Group2006 Have a great day!

Pennsylvania BANNER Users Group 2006