Pennsylvania BANNER Users Group 2006

1
Pennsylvania BANNER Users Group2006

• Instance Neutral Cloning Script

2
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

3
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.

4
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.

5
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
6
Walk through the code ...
23 ORACLE_SID1 command line overrides
env setting 24 BACKUPDIR2 25
DESTDIR3 26 CMDDIR4 27 PROD5 28
ARCHDIR6 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 gt /dev/null 37 ? 0
echo "ORACLE_SID is not a valid
ORACLE_SID" exit 1 39 /bin/grep PROD
oraenv gt /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
7
Walk through the code ...
49 Get a clean shutdown of the database to
avoid temp file problems. 50 sqlplus /nolog
ltlt 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 \
8
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 tmpfDESTDIR/create_O
RACLE_SID_ctlfile.sql 82 echo "STARTUP
NOMOUNT 118 'DESTDIR/ORACLE_SID_xdb_01.dbf
' 119 CHARACTER SET WE8ISO8859P1" gt tmpf
9
Walk through the code ...
121 run the sql script 122 echo "--
recreating control files ..." 123 sqlplus
/nolog ltlt EOF 124 connect /as sysdba 125
_at_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 ltlt 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
10
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 convlcase 2gt
/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 PATHID4" gt tmpf
11
Calling Script
6 SIDFA set the oracle_sid 8
BACKUPDIR/u04/backups/IVC backup of the
production database 9 ARCHIVEDIR/logs/archi
ves 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
CMDDIR IVC ARCHIVEDIR gtgt SID_clone_.lo
g 2gt1
6 parameters
12
PABUG 2006
13
Pennsylvania BANNER Users Group2006

• Have a great day!