#!/bin/sh
#############################################################
# generate_db (c) R. H. Reepe 9th February 1995 Version 3.1 #
#############################################################
# Description: Generates a Database by asking questions and picking up various system defaults
# Syntax: generate_db
# Extern: s_running, s_stopping, s_find_string, s_get_string
#############################################################
# Process generates a create.sql script and then uses sqldba
# to run the script. Automatic disk stripe sequencing is
# carried out as is multiple files per tablespace. Database
# size is gauged from .profile_references DB_SIZES parameter
# for the host THISHOST. Globally Unique Database Names are
# guarenteed as long as DATABASE_REFERENCES is maintained
# with this in mind. Tablespace Sizes are relational and
# keep in step as database size is increased. Some tablespaces
# are further modified by database type constraints.
# ===========================================================
# 960515 RHR Genesis
# 960803 RHR Revised Generated Create Script into two parts and logged
# 960912 RHR Added GDU controled SGA & Buffer Cache Sizes
# 961016 RHR Updated TEMP Tablspace size from gdu1
# 961025 RHR Updated ROLLBACK Segment size from 200 k to gdu5 k
# 961025 RHR Updated All Segment Sizes to be 1% of Tablespace Size
# 961114 RHR Updated ROLLBACK Tablespace Size from gdu5 to gdu6 (100%)
# 961118 RHR Updated Definitions of Segment Sizes to source from tablespace sizes
debug="off" # Debugging Option - set to ( on | off )
. $HOME/.profile # Setup Environment
s_header # Generate a Program Header
s_running $0 # Detect a second run
tmp0=`s_tmp 0` # Setup Temporary Filenames
tmp1=`s_tmp 1`
tmp2=`s_tmp 2`
tmp3=`s_tmp 3`
log0=`s_plog` # Logfile for this process
disk_full=77 # Limit Space on Disk in Percent
#############################################################
if [ "$debug" = "on" ]
then
echo " Setting Up Variables"
s_stopping $0
fi
#############################################################
#====================================================
# Get Generic Database Unit Size (GDU) for this Host
#====================================================
position=`s_find_string $THISHOST $REFERENCE_DBSERVER` # Find position of host in list
gdbu_size=`s_get_string $position $REFERENCE_DB_SIZES` # Find Database Size from host position
echo "Generic Database Unit Size for server \
$THISHOST = $gdbu_size Mb" # Display Size
#==================================================
# Set up some useful GDU multipliers for later use
#================================================== # gduN = (Fraction of GDU)
gdu0=`echo "$gdbu_size / 3" | bc` # 0 = (1/3 gds)
gdu1=`expr $gdu0 + $gdu0` # 1 = (2/3 gds)
gdu2=`expr $gdu0 + $gdu1` # 2 = ( 1 gds)
gdu3=`expr $gdu2 + $gdu2` # 3 = ( 2 gds)
gdu4=`expr $gdu2 + $gdu3` # 4 = ( 3 gds)
gdu5=`expr $gdu2 + $gdu4` # 5 = ( 4 gds)
gdu6=`expr $gdu5 + $gdu5` # 6 = ( 8 gds)
gdu7=`expr $gdu5 + $gdu6` # 7 = ( 12 gds)
gdu8=`expr $gdu5 + $gdu7` # 8 = ( 16 gds)
gdu9=`expr $gdu5 + $gdu8` # 9 = ( 20 gds)
#============================================= ===================================
# Assign Sizes to Database Tablespace Objects Size of Tablespace Files in Mbytes
#============================================= ===================================
export SYST_F_SIZE=$gdu2 # Size of SYSTEM tablespace
export TOOL_F_SIZE=$gdu2 # Size of SYSTEM_TOOLS Tablespace
export TEMP_T_SIZE=$gdu2 # Size of TEMP Tablespace
export ADMN_F_SIZE=$gdu1 # Size of ADMIN Tablespace
export INDX_F_SIZE=$gdu2 # Size of INDEXES Tablespace
export APPL_F_SIZE=$gdu2 # Size of APPL_OBJECTS Tablespace
export ROLB_F_SIZE=$gdu4 # Size of ROLLBACK Tablespace
export REDO_F_SIZE=128 # Size of REDO_LOG Files (In K)
export REDO_MAX_HIST=`echo "$gdu2 * 2" | bc` # Max Number of REDO logs to Archive
#========================================== ===========================
# Assign Sizes to Database Segment Objects Size of Segments in KBytes
#========================================== ===========================
export TOOL_SEG_INIT=`echo "$gdu0 * 3" | bc` # Size of TOOLS Segment INIT Parameter
export ADMN_SEG_INIT=`echo "$gdu0 * 3" | bc` # Size of ADMIN Segment INIT Parameter
export SYST_SEG_INIT=`echo "$gdu0 * 3" | bc` # Size of SYSTEM Segment INIT Parameter
export TEMP_SEG_INIT=`echo "$gdu0 * 3" | bc` # Size of TEMP Segment INIT Parameter
export APPL_SEG_INIT=`echo "$gdu0 * 2" | bc` # Size of APPL_OBJECTS Segment INIT Parameter
export INDX_SEG_INIT=`echo "$gdu0 * 2" | bc` # Size of INDEXES Segment INIT Parameter
export TOOL_SEG_NEXT=`echo "$gdu2 * 8" | bc` # Size of TOOLS Segment NEXT Parameter
export ADMN_SEG_NEXT=`echo "$gdu1 * 8" | bc` # Size of ADMIN Segment NEXT Parameter
export SYST_SEG_NEXT=`echo "$gdu1 * 8" | bc` # Size of SYSTEM Segment NEXT Parameter
export TEMP_SEG_NEXT=`echo "$gdu2 * 8" | bc` # Size of TEMP Segment NEXT Parameter
export APPL_SEG_NEXT=`echo "$gdu2 * 8" | bc` # Size of APPL_OBJECTS Segment NEXT Parameter
export INDX_SEG_NEXT=`echo "$gdu1 * 8" | bc` # Size of INDEXES Segment NEXT Parameter
export ROLB_SEG_SIZE=`echo "$gdu8 * 2" | bc` # Size of ROLLBACK Segment INIT and NEXT Parameters
export ROLB_SEG_OPTM=`echo "$gdu8 * 4" | bc` # Size of ROLLBACK Segment OPTIMAL Parameter
#############################################################
if [ "$debug" = "on" ]
then
echo " Generic Database Unit Profile and SEGMENT Size Allocation"
echo ""
echo "====================================================================================================="
echo "GDU Numbers: 0 1 2 3 4\
5 6 7 8 9"
echo "GDU Values: $gdu0 $gdu1 $gdu2 $gdu3 $gdu4\
$gdu5 $gdu6 $gdu7 $gdu8 $gdu9"
echo "====================================================================================================="
echo ""
echo "Database File Sizes for Tablespaces"
echo ""
echo "SYST_F_SIZE = $SYST_F_SIZE"
echo "TOOL_F_SIZE = $TOOL_F_SIZE"
echo "TEMP_T_SIZE = $TEMP_T_SIZE"
echo "ADMN_F_SIZE = $ADMN_F_SIZE"
echo "INDX_F_SIZE = $INDX_F_SIZE"
echo "APPL_F_SIZE = $APPL_F_SIZE"
echo "ROLB_F_SIZE = $ROLB_F_SIZE"
echo ""
echo "Database Default Segment Size Paramaters for Tablespaces"
echo ""
echo "ADMN_SEG_INIT = $ADMN_SEG_INIT K"
echo "APPL_SEG_INIT = $APPL_SEG_INIT K"
echo "INDX_SEG_INIT = $INDX_SEG_INIT K"
echo "SYST_SEG_INIT = $SYST_SEG_INIT K"
echo "TEMP_SEG_INIT = $TEMP_SEG_INIT K"
echo "TOOL_SEG_INIT = $TOOL_SEG_INIT K"
echo ""
echo "ADMN_SEG_NEXT = $ADMN_SEG_NEXT K"
echo "APPL_SEG_NEXT = $APPL_SEG_NEXT K"
echo "INDX_SEG_NEXT = $INDX_SEG_NEXT K"
echo "SYST_SEG_NEXT = $SYST_SEG_NEXT K"
echo "TEMP_SEG_NEXT = $TEMP_SEG_NEXT K"
echo "TOOL_SEG_NEXT = $TOOL_SEG_NEXT K"
echo ""
echo "ROLB_SEG_SIZE = $ROLB_SEG_SIZE K"
echo "ROLB_SEG_OPTM = $ROLB_SEG_OPTM K"
echo ""
echo "REDO_F_SIZE = $REDO_F_SIZE K"
echo "REDO_MAX_HIST = $REDO_MAX_HIST"
echo ""
s_stopping $0
exit
fi
#############################################################
#=====================================
# Assign Sizes to Database Parameters
#=====================================
export SGA_SIZE=`echo "600000 * $gdbu_size" | bc` # Size of General SGA Memory
export DB_BUFFER_SIZE=`echo "100 * $gdbu_size" | bc` # Size of DB_BLOCK_BUFFER Cache (2K Blocks)
#============================
# Assign Remaining Variables
#============================
export OS=`/usr/bin/uname -r | cut -c1-1` # Unix Version (4=sunos,5=solaris)
export ORACLE_BASE=/$CO/$THISHOST/unix/cen/oracle_base # Oracle Base Install Directory
export ORACLE_GEN=/$CO/$THISHOST/unix/cen/oracle_base/GENERIC # Oracle GENERIC Files Directory
#############################################################
if [ "$debug" = "on" ]
then
echo " Selecting Database Software Product"
fi
#############################################################
. $ORACLE_BASE/scripts/product # Run product selector script
#############################################################
if [ "$debug" = "on" ]
then
echo " THISHOST = [ $THISHOST ]"
echo " OS = [ $OS ]"
echo " UNIX = [ $UNIX ]"
echo "ORACLE_BASE = [ $ORACLE_BASE ]"
echo "ORACLE_HOME = [ $ORACLE_HOME ]"
echo " Selecting Database Type"
fi
#############################################################
#=======================================
# Find the database host and loc string
#=======================================
list_position=`s_find_string $THISHOST $REFERENCE_DBSERVER` # Find position of host in list
db_loc=`s_get_string $list_position $REFERENCE_LOCATION` # Get db_location string
#================================
# Ask the user for database type
#================================
correct=0 # Set correct = wrong
pick_list="" # Initialise a pick list
for next in $REFERENCE_DATABASE # Get all valid types
do
pick=`s_lower_case $next | cut -c1-1` # Get the option letter
pick_list=$pick_list$pick # Generate the valid pick list
done
#--------------------
# Generate User Menu
#--------------------
while [ $correct = 0 ] # While user is wrong
do # Display Menu of Valid Picks
echo ""
echo "Which type of Database do you wish to create ?" # ... Instructions
echo ""
echo "Choices are:"
echo ""
for next in $REFERENCE_DATABASE
do
pick=`s_lower_case $next | cut -c1-1`
echo " $pick $next Database" # ... Display List
done
echo ""
/usr/5bin/echo "Your choice (p,m,s,etc) = \c" # ... Request user input
read answer
echo ""
correct=`echo "$pick_list" | grep -c $answer` # Validate user answer against list
if [ "$answer" = "" ] # Check for NULL answer and exit
then
echo "Invalid Answer - Aborting"
exit
fi
done
database_type=`s_lower_case $answer` # This is the Valid Answer
export GENERIC_PFILE=$ORACLE_GEN/init_generic.ora.$database_type # Get Generic init.ora filename
#############################################################
if [ "$debug" = "on" ]
then
echo " Adjust the Tablespace Sizes according to Type"
fi
#############################################################
#====================================
# Detect a Resource Database (Small)
#====================================
if [ "$database_type" = "m" ]
then # Look out for a Resource Database
export RESOURCE=1 # Noted.
APPL_F_SIZE=`echo $APPL_F_SIZE / 2 | bc` # And reduce size of APPL and INDEXES
INDX_F_SIZE=`echo $INDX_F_SIZE / 2 | bc` # Tablespaces to suit
else
export RESOURCE=0 # Default Condition
fi
#======================================
# Detect a Production Database (Large)
#======================================
if [ "$database_type" = "p" ] || [ "$database_type" = "e" ] # TEMP, APPL, INDEXES tablespaces are all
then # larger for Prod & Eval type databases
export TEMP_T_SIZE=`echo "$TEMP_T_SIZE * 3" | bc`
export APPL_F_SIZE=`echo "$APPL_F_SIZE * 3" | bc`
export INDX_F_SIZE=`echo "$INDX_F_SIZE * 3" | bc`
export APPL_SEG_NEXT=`echo "$APPL_SEG_NEXT * 2" | bc`
export TEMP_SEG_NEXT=`echo "$TEMP_SEG_NEXT * 2" | bc`
export INDX_SEG_NEXT=`echo "$INDX_SEG_NEXT * 2" | bc`
fi
#############################################################
if [ "$debug" = "on" ]
then
echo " Create the Empty Directories"
fi
#############################################################
/usr/5bin/echo "Checking Disks and Directories...\c"
oracle_data_dirs="/$CO/$THISHOST/u/oracle_data*"
oracle_sub_dirs="ADMIN ARCH TOOLS TEMP CONTROL_FILES REDO_LOGS SYSTEM DUMP ROLLBACK USER RESOURCE INDEXES BACKUPS"
/usr/5bin/echo ".\c"
for directory in $oracle_data_dirs
do
/usr/5bin/echo ".\c"
cd $directory
/usr/5bin/echo ".\c"
mkdir $oracle_sub_dirs > /dev/null 2>&1
/usr/5bin/echo ".\c"
chgrp dba $oracle_sub_dirs > /dev/null 2>&1
/usr/5bin/echo ".\c"
chmod -R 775 $oracle_data_dirs > /dev/null 2>&1
done
cd
/usr/5bin/echo ".\c"
if [ $OS = 4 ] # Unix Dependant variables
then # SunOS Group
export UNIX="sunos"
export ORATAB=/etc/oratab
else # Solaris Group
export UNIX="solaris"
export ORATAB=/var/opt/oracle/oratab
fi # End of Unix Dependant variables
echo "."
initialise_db=$ORACLE_BASE/scripts/initialise_db # DB Initialisation script
#====================================
# Find all the globally similar sids
#====================================
for host in $REFERENCE_DBSERVER # For each host in list...
do
sid_list=`s_sid_list $host` # create list of DB SIDS
for sid in $sid_list # For each sid in sid list
do
echo "$sid" >> $tmp1 # Save the sid in a temp file
done
done
database_type="_"$database_type
grep $db_loc$database_type $tmp1 | sort > $tmp2 # Find the DB sids that match input
current_db_seq=`tail -1 $tmp2 | cut -c8-8` # Get the highest sequence number
#===============================================
# Test sequence number and increment for new DB
#===============================================
if [ "$current_db_seq" = "" ] # Test it...
then
db_seq=1 # If none - Create It
else
db_seq=`expr $current_db_seq + 1` # Else - Increment it
fi
seq_lng=`/usr/5bin/echo "$db_seq\c"| wc -c | cut -c1-8` # Count the digits
if [ $seq_lng = 1 ] # If its a single digit
then
db_seq="0$db_seq" # Prepend a ZERO
fi
export ORACLE_SID=$db_loc$database_type$db_seq # The New Database Name
echo "This Database Name = $ORACLE_SID"
#############################################################
if [ "$debug" = "on" ]
then
echo "Database Location String is [ $db_loc ]"
echo "Answer is [ $database_type ]"
echo "Length is [ $seq_lng ]"
echo "Database Sequence Number is [ $db_seq ]"
echo "Database Instance is [ $ORACLE_SID ]"
echo ""
echo " Selecting Database Slot (A,B,C, or D)"
fi
#############################################################
echo "Checking Database Slots In Use"
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++#
# NOTE: When SQL-Net 2.1 conversion ready - this reference #
# to ORATAB must be replaced with a dual call to ORATAB and #
# TNSNAMES to check for version 1 & 2 SQL-Net setups #
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++#
for instance in `grep -v \# $ORATAB | cut -f1-1 -d:` # List of DB_Names
do
slot=`du -a /$CO/$THISHOST/u/oracle_data*/SYSTEM | /bin/nawk '{print($2)}' | grep $instance"_system_01.dbf"`
if [ ! "$slot" = "" ] # If there are database files for this instance
then
slot=`dirname $slot` # Strip off a dir_level
slot=`dirname $slot` # Strip off another dir_level
slot=`basename $slot` # Take the last directory name
echo "using slot [ $slot ]" # Find the database slot
echo "slot = [ $slot ]" >> $tmp3 # And add it to the list file
else
echo "Not on oracle_data disks" # This Database is probably a DBA database
fi
done
slot1=`grep 1 $tmp3 | wc -l | cut -c8-8` # Check each slot for use
slot2=`grep 2 $tmp3 | wc -l | cut -c8-8` # Check each slot for use
slot3=`grep 3 $tmp3 | wc -l | cut -c8-8` # Check each slot for use
slot4=`grep 4 $tmp3 | wc -l | cut -c8-8` # Check each slot for use
#===============
# Choose a slot # Find the first empty slot
#===============
slot=1
if [ "$slot2" -lt "$slot1" ]
then
slot=2
elif [ "$slot3" -lt "$slot2" ]
then
slot=3
elif [ "$slot4" -lt "$slot3" ]
then
slot=4
fi
#############################################################
if [ "$debug" = "on" ]
then
echo " Set up the Dynamic Resources for Creation"
fi
#############################################################
if [ "$slot" = "1" ] # Name the Slot Directories
then
export O_D_2 ; O_D_2=/$CO/$THISHOST/u/oracle_data_1 # Oracle Data 1 root path
export O_D_3 ; O_D_3=/$CO/$THISHOST/u/oracle_data_2 # Oracle Data 2 root path
export O_D_4 ; O_D_4=/$CO/$THISHOST/u/oracle_data_3 # Oracle Data 3 root path
export O_D_1 ; O_D_1=/$CO/$THISHOST/u/oracle_data_4 # Oracle Data 4 root path
elif [ "$slot" = "2" ]
then
export O_D_3 ; O_D_3=/$CO/$THISHOST/u/oracle_data_1 # Oracle Data 1 root path
export O_D_4 ; O_D_4=/$CO/$THISHOST/u/oracle_data_2 # Oracle Data 2 root path
export O_D_1 ; O_D_1=/$CO/$THISHOST/u/oracle_data_3 # Oracle Data 3 root path
export O_D_2 ; O_D_2=/$CO/$THISHOST/u/oracle_data_4 # Oracle Data 4 root path
elif [ "$slot" = "3" ]
then
export O_D_4 ; O_D_4=/$CO/$THISHOST/u/oracle_data_1 # Oracle Data 1 root path
export O_D_1 ; O_D_1=/$CO/$THISHOST/u/oracle_data_2 # Oracle Data 2 root path
export O_D_2 ; O_D_2=/$CO/$THISHOST/u/oracle_data_3 # Oracle Data 3 root path
export O_D_3 ; O_D_3=/$CO/$THISHOST/u/oracle_data_4 # Oracle Data 4 root path
elif [ "$slot" = "4" ]
then
export O_D_1 ; O_D_1=/$CO/$THISHOST/u/oracle_data_1 # Oracle Data 1 root path
export O_D_2 ; O_D_2=/$CO/$THISHOST/u/oracle_data_2 # Oracle Data 2 root path
export O_D_3 ; O_D_3=/$CO/$THISHOST/u/oracle_data_3 # Oracle Data 3 root path
export O_D_4 ; O_D_4=/$CO/$THISHOST/u/oracle_data_4 # Oracle Data 4 root path
else
echo "No Slots Available - Program Screw-up" # This should never happen (!)
exit
fi
echo "Naming Database Files"
export DB_SYST_1=$O_D_1/SYSTEM/$ORACLE_SID"_system_01.dbf" # Set the DB File full paths
export DB_SYST_2=$O_D_1/SYSTEM/$ORACLE_SID"_system_02.dbf"
export DB_ROLB_1=$O_D_1/ROLLBACK/$ORACLE_SID"_rollback_1.dbf"
export DB_ROLB_2=$O_D_3/ROLLBACK/$ORACLE_SID"_rollback_2.dbf"
export DB_ROLB_3=$O_D_4/ROLLBACK/$ORACLE_SID"_rollback_3.dbf"
export DB_CONT_1=$O_D_2/CONTROL_FILES/$ORACLE_SID"_cntl1"
export DB_CONT_2=$O_D_4/CONTROL_FILES/$ORACLE_SID"_cntl2"
export DB_TOOLS=$O_D_3/TOOLS/$ORACLE_SID"_tools_01.dbf"
export DB_TEMP=$O_D_1/TEMP/$ORACLE_SID"_temp_01.dbf"
export DB_ADMIN=$O_D_2/ADMIN/$ORACLE_SID"_admin_01.dbf"
export DB_REDO_1A=$O_D_2/REDO_LOGS/$ORACLE_SID"_redo1a.log"
export DB_REDO_2A=$O_D_2/REDO_LOGS/$ORACLE_SID"_redo2a.log"
export DB_REDO_3A=$O_D_2/REDO_LOGS/$ORACLE_SID"_redo3a.log"
export DB_REDO_4A=$O_D_2/REDO_LOGS/$ORACLE_SID"_redo4a.log"
export DB_REDO_5A=$O_D_2/REDO_LOGS/$ORACLE_SID"_redo5a.log"
export DB_REDO_1B=$O_D_4/REDO_LOGS/$ORACLE_SID"_redo1b.log"
export DB_REDO_2B=$O_D_4/REDO_LOGS/$ORACLE_SID"_redo2b.log"
export DB_REDO_3B=$O_D_4/REDO_LOGS/$ORACLE_SID"_redo3b.log"
export DB_REDO_4B=$O_D_4/REDO_LOGS/$ORACLE_SID"_redo4b.log"
export DB_REDO_5B=$O_D_4/REDO_LOGS/$ORACLE_SID"_redo5b.log"
export DB_USER_1=$O_D_3/USER/$ORACLE_SID"_appl_objects_01.dbf"
export DB_USER_2=$O_D_3/USER/$ORACLE_SID"_appl_objects_02.dbf"
export DB_USER_3=$O_D_3/USER/$ORACLE_SID"_appl_objects_03.dbf"
export DB_USER_4=$O_D_3/USER/$ORACLE_SID"_appl_objects_04.dbf"
export DB_INDX_1=$O_D_1/INDEXES/$ORACLE_SID"_indexes_01.dbf"
export DB_INDX_2=$O_D_1/INDEXES/$ORACLE_SID"_indexes_02.dbf"
export DB_INDX_3=$O_D_1/INDEXES/$ORACLE_SID"_indexes_03.dbf"
export DB_INDX_4=$O_D_1/INDEXES/$ORACLE_SID"_indexes_04.dbf"
#############################################################
if [ "$debug" = "on" ]
then
echo " Set up the Static Resources for Creation"
fi
#############################################################
export DB_ARCH=$O_D_4/ARCH
export DB_DUMP=$O_D_4/DUMP
#############################################################
if [ "$debug" = "on" ]
then
echo " Database Constructed Parameter File"
fi
#############################################################
echo "Creating Parameter File"
#======================
# Parameter File Names
#======================
export DB_PFILE=$ORACLE_DBS/init$ORACLE_SID".ora"
export DB_PFI_BU=$ORACLE_BASE/SQL/DB_CREATE/init$ORACLE_SID".ora"
export DB_CREATE_1=$ORACLE_DBS/create_1-$ORACLE_SID".sql"
export DB_CREATE_2=$ORACLE_DBS/create_2-$ORACLE_SID".sql"
export DB_CRE_BU_1=$ORACLE_BASE/SQL/DB_CREATE/create_1-$ORACLE_SID".sql"
export DB_CRE_BU_2=$ORACLE_BASE/SQL/DB_CREATE/create_2-$ORACLE_SID".sql"
#=====================================================
# Parameter File and Create File Clean-up and Prepare
#=====================================================
rm -f $DB_PFILE DB_PFI_BU
touch $DB_PFILE
rm -f $DB_CREATE_1 $DB_CRE_BU_1
touch $DB_CREATE_1
rm -f $DB_CREATE_2 $DB_CRE_BU_2
touch $DB_CREATE_2
#===============================
# Start Building Parameter File
#===============================
cat $ORACLE_GEN/init_header.ora >> $DB_PFILE
echo "# Specific init.ora file for database $ORACLE_SID" >> $DB_PFILE
echo "##################################################" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Read Generic Parameter File" >> $DB_PFILE
echo "# ---------------------------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " ifile = $GENERIC_PFILE" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Instance Name" >> $DB_PFILE
echo "# -------------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " db_name = $ORACLE_SID" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Memory Limits" >> $DB_PFILE
echo "# -------------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " db_block_buffers = $DB_BUFFER_SIZE" >> $DB_PFILE
echo " open_cursors = $DB_BUFFER_SIZE" >> $DB_PFILE
echo " shared_pool_size = $SGA_SIZE" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Dumpfiles" >> $DB_PFILE
echo "# ---------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " background_dump_dest = $DB_DUMP" >> $DB_PFILE
echo " user_dump_dest = $DB_DUMP" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Control Files" >> $DB_PFILE
echo "# -------------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " control_files = $DB_CONT_1" >> $DB_PFILE
echo " control_files = $DB_CONT_2" >> $DB_PFILE
echo "" >> $DB_PFILE
echo "# Archiver" >> $DB_PFILE
echo "# --------" >> $DB_PFILE
echo "" >> $DB_PFILE
echo " log_archive_start = true" >> $DB_PFILE
echo " log_archive_dest = $DB_ARCH/$ORACLE_SID" >> $DB_PFILE
echo " log_archive_format = LOG%S_%T.ARC" >> $DB_PFILE
echo "" >> $DB_PFILE
#############################################################
if [ "$debug" = "on" ]
then
echo " Database Constructed Create File"
fi
#############################################################
echo "Creating Database Create Script"
#========================
# Create Script Number 1
#========================
echo "REM # Create Database (1) $ORACLE_SID on $UNIX system $THISHOST" >> $DB_CREATE_1
echo "REM # -----------------------------------------------------" >> $DB_CREATE_1
echo "REM # Script created by generate_db (c) R. H. Reepe" >> $DB_CREATE_1
echo "REM # Version 2.0 date `date`" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "connect internal" >> $DB_CREATE_1
echo "startup nomount" >> $DB_CREATE_1
echo "create database $ORACLE_SID" >> $DB_CREATE_1
echo " maxinstances 1" >> $DB_CREATE_1
echo " maxlogfiles 16" >> $DB_CREATE_1
echo " maxloghistory $REDO_MAX_HIST" >> $DB_CREATE_1
echo " character set \"EE8ISO8859P2\"" >> $DB_CREATE_1
echo " datafile" >> $DB_CREATE_1
echo " '$DB_SYST_1' size $SYST_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_SYST_2' size $SYST_F_SIZE M" >> $DB_CREATE_1
echo " logfile" >> $DB_CREATE_1
echo " group 1 ('$DB_REDO_1A'," >> $DB_CREATE_1
echo " '$DB_REDO_1B')" >> $DB_CREATE_1
echo " size $REDO_F_SIZE K," >> $DB_CREATE_1
echo " group 2 ('$DB_REDO_2A'," >> $DB_CREATE_1
echo " '$DB_REDO_2B')" >> $DB_CREATE_1
echo " size $REDO_F_SIZE K," >> $DB_CREATE_1
echo " group 3 ('$DB_REDO_3A'," >> $DB_CREATE_1
echo " '$DB_REDO_3B')" >> $DB_CREATE_1
echo " size $REDO_F_SIZE K," >> $DB_CREATE_1
echo " group 4 ('$DB_REDO_4A'," >> $DB_CREATE_1
echo " '$DB_REDO_4B')" >> $DB_CREATE_1
echo " size $REDO_F_SIZE K," >> $DB_CREATE_1
echo " group 5 ('$DB_REDO_5A'," >> $DB_CREATE_1
echo " '$DB_REDO_5B')" >> $DB_CREATE_1
echo " size $REDO_F_SIZE K;" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create rollback segment temp1 storage (initial 10K next 10K);" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "alter rollback segment temp1 online;" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace rollback_segs datafile" >> $DB_CREATE_1
echo " '$DB_ROLB_1'" >> $DB_CREATE_1
echo " size $ROLB_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_ROLB_2'" >> $DB_CREATE_1
echo " size $ROLB_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_ROLB_3'" >> $DB_CREATE_1
echo " size $ROLB_F_SIZE M" >> $DB_CREATE_1
echo " default storage ( initial $ROLB_SEG_SIZE K" >> $DB_CREATE_1
echo " next $ROLB_SEG_SIZE K" >> $DB_CREATE_1
echo " minextents 2" >> $DB_CREATE_1
echo " pctincrease 0" >> $DB_CREATE_1
echo " optimal $ROLB_SEG_OPTM K );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create rollback segment rs1 tablespace rollback_segs;" >> $DB_CREATE_1
echo "create rollback segment rs2 tablespace rollback_segs;" >> $DB_CREATE_1
echo "create rollback segment rs3 tablespace rollback_segs;" >> $DB_CREATE_1
echo "create rollback segment rs4 tablespace rollback_segs;" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "alter rollback segment rs1 online;" >> $DB_CREATE_1
echo "alter rollback segment rs2 online;" >> $DB_CREATE_1
echo "alter rollback segment rs3 online;" >> $DB_CREATE_1
echo "alter rollback segment rs4 online;" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace tools datafile" >> $DB_CREATE_1
echo " '$DB_TOOLS' size $TOOL_F_SIZE M" >> $DB_CREATE_1
echo " default storage (initial $TOOL_SEG_INIT K" >> $DB_CREATE_1
echo " next $TOOL_SEG_NEXT K" >> $DB_CREATE_1
echo " pctincrease 0 );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace temp datafile" >> $DB_CREATE_1
echo " '$DB_TEMP' size $TEMP_T_SIZE M" >> $DB_CREATE_1
echo " default storage (initial $TEMP_SEG_INIT K" >> $DB_CREATE_1
echo " next $TEMP_SEG_NEXT K" >> $DB_CREATE_1
echo " pctincrease 0 );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace admin datafile" >> $DB_CREATE_1
echo " '$DB_ADMIN' size $ADMN_F_SIZE M" >> $DB_CREATE_1
echo " default storage (initial $ADMN_SEG_INIT K" >> $DB_CREATE_1
echo " next $ADMN_SEG_NEXT K" >> $DB_CREATE_1
echo " pctincrease 0 );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace appl_objects datafile" >> $DB_CREATE_1
echo " '$DB_USER_1' size $APPL_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_USER_2' size $APPL_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_USER_3' size $APPL_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_USER_4' size $APPL_F_SIZE M" >> $DB_CREATE_1
echo " default storage (initial $APPL_SEG_INIT K" >> $DB_CREATE_1
echo " next $APPL_SEG_NEXT K" >> $DB_CREATE_1
echo " pctincrease 20 );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "create tablespace indexes datafile" >> $DB_CREATE_1
echo " '$DB_INDX_1' size $INDX_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_INDX_2' size $INDX_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_INDX_3' size $INDX_F_SIZE M," >> $DB_CREATE_1
echo " '$DB_INDX_4' size $INDX_F_SIZE M" >> $DB_CREATE_1
echo " default storage (initial $INDX_SEG_INIT K" >> $DB_CREATE_1
echo " next $INDX_SEG_NEXT K" >> $DB_CREATE_1
echo " pctincrease 20 );" >> $DB_CREATE_1
echo "" >> $DB_CREATE_1
echo "shutdown immediate" >> $DB_CREATE_1
echo "disconnect" >> $DB_CREATE_1
echo "exit" >> $DB_CREATE_1
#========================
# Create Script Number 2
#========================
echo "REM # Create Database (2) $ORACLE_SID on $UNIX system $THISHOST" >> $DB_CREATE_2
echo "REM # -----------------------------------------------------" >> $DB_CREATE_2
echo "REM # Script created by generate_db (c) R. H. Reepe" >> $DB_CREATE_2
echo "REM # Version 2.0 date `date`" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "connect internal" >> $DB_CREATE_2
echo "startup open" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "alter user system default tablespace tools temporary tablespace temp;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
if [ $RESOURCE = 0 ]
then
echo "create user applprod identified by applprod" >> $DB_CREATE_2
echo " default tablespace appl_objects" >> $DB_CREATE_2
echo " temporary tablespace temp" >> $DB_CREATE_2
echo " quota unlimited on appl_objects" >> $DB_CREATE_2
echo " quota unlimited on indexes;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "grant connect to applprod;" >> $DB_CREATE_2
elif [ $RESOURCE = 1 ]
then
echo "create user applprod identified by applprod" >> $DB_CREATE_2
echo " default tablespace appl_objects" >> $DB_CREATE_2
echo " temporary tablespace temp" >> $DB_CREATE_2
echo " quota unlimited on appl_objects" >> $DB_CREATE_2
echo " quota unlimited on indexes;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "grant connect to applprod;" >> $DB_CREATE_2
fi
echo "" >> $DB_CREATE_2
echo "alter tablespace system" >> $DB_CREATE_2
echo " default storage (initial $SYST_SEG_INIT K" >> $DB_CREATE_2
echo " next $SYST_SEG_NEXT K" >> $DB_CREATE_2
echo " pctincrease 5 );" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "alter rollback segment temp1 offline;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
if [ $RESOURCE = 0 ]
then
echo "create role estimator;" >> $DB_CREATE_2
echo "grant select any table," >> $DB_CREATE_2
echo " delete any table," >> $DB_CREATE_2
echo " update any table," >> $DB_CREATE_2
echo " create session," >> $DB_CREATE_2
echo " select any sequence," >> $DB_CREATE_2
echo " insert any table to estimator;" >> $DB_CREATE_2
echo "grant estimator to public;" >> $DB_CREATE_2
else
echo "create role loader;" >> $DB_CREATE_2
echo "grant select any table," >> $DB_CREATE_2
echo " delete any table," >> $DB_CREATE_2
echo " update any table," >> $DB_CREATE_2
echo " create session," >> $DB_CREATE_2
echo " select any sequence," >> $DB_CREATE_2
echo " insert any table to loader;" >> $DB_CREATE_2
echo "grant loader to applprod;" >> $DB_CREATE_2
echo "grant dba to applprod;" >> $DB_CREATE_2
fi
echo "@$ORACLE_HOME/rdbms/admin/catalog" >> $DB_CREATE_2
echo "@$ORACLE_HOME/rdbms/admin/catproc" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "create role dd_access;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "@$ORACLE_BASE/SQL/DBA/data_dictionary_access" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "grant dd_access to system;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
$account_name $system_coded $0 $$
. $account_string$$
acc_uid=`/usr/5bin/echo "$account" | cut -c1-6`
acc_pwd=`/usr/5bin/echo "$account" | cut -c8-`
echo "alter user $acc_uid identified by $acc_pwd;" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "connect $account" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "@$ORACLE_HOME/sqlplus/admin/pupbld" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
if [ $RESOURCE = 1 ]
then
echo "connect $userid/$passwd" >> $DB_CREATE_2
fi
echo "" >> $DB_CREATE_2
echo "@$ORACLE_HOME/rdbms/admin/catdbsyn" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "connect internal" >> $DB_CREATE_2
echo "shutdown normal" >> $DB_CREATE_2
echo "startup mount" >> $DB_CREATE_2
echo "" >> $DB_CREATE_2
echo "alter database archivelog;" >> $DB_CREATE_2
echo "archive log start;" >> $DB_CREATE_2
echo "alter database open;" >> $DB_CREATE_2
echo "exit" >> $DB_CREATE_2
#############################################################
if [ "$debug" = "on" ]
then
echo " Database Files"
fi
#############################################################
echo ""
echo "Creating a slot $slot database using the following files"
echo ""
echo "$DB_SYST_1 $SYST_F_SIZE Mbytes"
echo "$DB_SYST_2 $SYST_F_SIZE Mbytes"
echo "$DB_TOOLS $TOOL_F_SIZE Mbytes"
echo "$DB_TEMP $TEMP_T_SIZE Mbytes"
echo "$DB_ADMIN $ADMN_F_SIZE Mbytes"
echo "$DB_CONT_1 1 Kbytes"
echo "$DB_CONT_2 1 Kbytes"
echo "$DB_REDO_1A $REDO_F_SIZE Kbytes"
echo "$DB_REDO_2A $REDO_F_SIZE Kbytes"
echo "$DB_REDO_3A $REDO_F_SIZE Kbytes"
echo "$DB_REDO_4A $REDO_F_SIZE Kbytes"
echo "$DB_REDO_5A $REDO_F_SIZE Kbytes"
echo "$DB_REDO_1B $REDO_F_SIZE Kbytes"
echo "$DB_REDO_2B $REDO_F_SIZE Kbytes"
echo "$DB_REDO_3B $REDO_F_SIZE Kbytes"
echo "$DB_REDO_4B $REDO_F_SIZE Kbytes"
echo "$DB_REDO_5B $REDO_F_SIZE Kbytes"
echo "$DB_ROLB_1 $ROLB_F_SIZE Mbytes"
echo "$DB_ROLB_2 $ROLB_F_SIZE Mbytes"
echo "$DB_ROLB_3 $ROLB_F_SIZE Mbytes"
echo "$DB_USER_1 $APPL_F_SIZE Mbytes"
echo "$DB_USER_2 $APPL_F_SIZE Mbytes"
echo "$DB_USER_3 $APPL_F_SIZE Mbytes"
echo "$DB_USER_4 $APPL_F_SIZE Mbytes"
echo "$DB_INDX_1 $INDX_F_SIZE Mbytes"
echo "$DB_INDX_2 $INDX_F_SIZE Mbytes"
echo "$DB_INDX_3 $INDX_F_SIZE Mbytes"
echo "$DB_INDX_4 $INDX_F_SIZE Mbytes"
echo ""
echo "$DB_ARCH [directory]"
echo "$DB_DUMP [directory]"
echo ""
echo "$DB_PFILE"
echo "$DB_CREATE_1"
echo "$DB_CREATE_2"
echo ""
echo "Database SID $ORACLE_SID"
echo "SGA Size $SGA_SIZE M"
echo "DB Block Buffers $DB_BUFFER_SIZE (2K Blocks)"
echo "Open Cursors $DB_BUFFER_SIZE"
echo ""
#############################################################
if [ "$debug" = "on" ]
then
echo " Lets Just Doit"
lpr $DB_PFILE
lpr $DB_CREATE_1
lpr $DB_CREATE_2
exit
fi
#############################################################
echo "Checking For Available Disk Space"
od1_size=`df -k $O_D_1 | tail -1 | /bin/nawk '{print($5)}' | cut -f1-1 -d%` # Used Space on Oracle Disk 1
od2_size=`df -k $O_D_2 | tail -1 | /bin/nawk '{print($5)}' | cut -f1-1 -d%` # Used Space on Oracle Disk 2
od3_size=`df -k $O_D_3 | tail -1 | /bin/nawk '{print($5)}' | cut -f1-1 -d%` # Used Space on Oracle Disk 3
od4_size=`df -k $O_D_4 | tail -1 | /bin/nawk '{print($5)}' | cut -f1-1 -d%` # Used Space on Oracle Disk 4
if [ $od1_size -gt $disk_full ] # If any disk is at limit size...
then
echo "WARNING - Disk [ $O_D_1 ] is $od1_size % full" # Then I cannot fit a database in.
db_create=no
elif [ $od2_size -gt $disk_full ]
then
echo "WARNING - Disk [ $O_D_2 ] is $od2_size % full"
db_create=no
elif [ $od3_size -gt $disk_full ]
then
echo "WARNING - Disk [ $O_D_3 ] is $od3_size % full"
db_create=no
elif [ $od4_size -gt $disk_full ]
then
echo "WARNING - Disk [ $O_D_4 ] is $od4_size % full"
db_create=no
else
echo "There is space to create this database"
db_create=yes
fi
if [ "$db_create" = "no" ]
then
echo ""
echo " ####################################"
echo " ## Database will not be created ##"
echo " ####################################"
echo ""
echo "But init$ORACLE_SID.ora and create-$ORACLE_SID.sql scripts have"
echo "been made available for your inspection in the dbs directory"
echo ""
echo "Database instance name is $ORACLE_SID"
echo ""
s_stopping $0
exit
fi
db_exists=`grep $ORACLE_SID $ORATAB | wc -l | cut -c7-8` # Get Database Existance
if [ $db_exists = 1 ] # Does it already exist ?
then
echo ""
echo "Database Instance [ $ORACLE_SID ] found in [ $ORATAB ]"
/usr/5bin/echo "Is this okay ? (y/n) \c"
read answer
ecgo ""
answer=s_upper_case $answer
if [ ! "$answer" = "Y" ]
then
echo "Aborting"
exit
fi
else
echo "Creating entry for [ $ORACLE_SID ] in [ $ORATAB ]"
echo "$ORACLE_SID:$ORACLE_HOME:"Y >> $ORATAB # Register Database in ORATAB file
fi
cp $DB_PFILE $DB_PFI_BU # Save backup of DB Parameter File
cp $DB_CREATE_1 $DB_CRE_BU_1 # Save backup of DB Create File
cp $DB_CREATE_2 $DB_CRE_BU_2 # Save backup of DB Create File
echo ""
echo "Saved Oracle Create File and Parameter File in:"
echo " -> $ORACLE_BASE/SQL/DB_CREATE"
echo ""
cd $ORACLE_HOME/dbs # Go to Startup Directory
sqldba command=\"@$DB_CREATE_1\" # Run create script 1 in sqldba
echo ""
echo "Generate_DB Script 1 complete"
echo "Generate_DB Script 2 will now start in Silent Mode"
echo "Output will be spooled to the [generate] logfile"
echo "Use [logs generate] to monitor results"
echo "This terminal window will remain locked until Script 2 completes"
echo ""
sqldba command=\"@$DB_CREATE_2\" > $log0 2>&1 # Run create script 2 in sqldba & log
echo ""
echo "Updating SQL-Net 2.1 Files"
echo ""
#
########################################################################
# Sql Net 2 Listener Stuff #
########################################################################
#
$ORACLE_BASE/scripts/NET2FILES/scan_tns_listener
$ORACLE_BASE/scripts/NET2FILES/sync_listener
$ORACLE_BASE/scripts/NET2FILES/sync_tnsname
#
########################################################################
echo ""
echo "Generate_DB Script 2 complete"
echo "Generate_DB Terminated"
echo ""
s_stopping $0
|
Page 2gdb