Basisdatenbank mit Script erstellen

Dieses Script von Martin Furter erstellt eine Basisdatenbank, aber mit sehr kleinem SYSTEM Tablespace, denn Applikations- und Benutzerdaten gehören immer in eigene Tablespaces.

#!/bin/sh
#====================================================================
#
# Copyright (C) 2003-2004 Martin Furter <mf@borg.ch>
#
# This file is part of DebOra9Howto
# (Oracle9i Installation auf Debian GNU/Linux 3.0)
#
# DebOra9Howto is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2, or (at your option)
# any later version.
#
# DebOra9Howto is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with DebOra9Howto; see the file COPYING.  If not, write to
# the Free Software Foundation, 675 Mass Ave, Cambridge, MA 02139, USA.
#
#===============================================================================
#
# createOra9DB.sh
#
# A script for creating an oracle instance.
# Use 'createOra9DB.sh -h' for help.
#
#===============================================================================

ORACLE_VERSION=`sqlplus '-?' | awk '/Release/{print $3}'`

case "$ORACLE_VERSION" in
    9.*)
        ORACLE_VERSION=9
        ;;
    10.*)
        ORACLE_VERSION=10
        ;;
    *)
        echo "unsupported oracle version $ORACLE_VERSION"
        exit 1
        ;;
esac

help() {
    echo ""
    echo "Creates a new oracle database"
    echo ""
    echo "  usage: $0 [options]"
    echo ""
    echo "  options are:"
    echo "  BASE=/path     oracle base directory  (env: ORACLE_BASE)"
    echo "  DATA=/path     oracle data directory  (env: ORACLE_DATA)"
    echo "  HOME=/path     oracle home directory  (env: ORACLE_HOME)"
    echo "  SID=name       oracle SID              (env: ORACLE_SID)"
    echo ""
    echo "  current setings:"
    if [ -n "$ORACLE_BASE" ]; then
        echo "  BASE='$ORACLE_BASE'"
    fi
    if [ -n "$ORACLE_DATA" ]; then
        echo "  DATA='$ORACLE_DATA'"
    fi
    if [ -n "$ORACLE_HOME" ]; then
        echo "  HOME='$ORACLE_HOME'"
    fi
    if [ -n "$ORACLE_SID" ]; then
        echo "  SID='$ORACLE_SID'"
    fi
    echo ""
}

while [ -n "$1" ]; do
    case "$1" in
        BASE=*)
            ORACLE_BASE=`echo "$1"|sed 's/^BASE=//'`
            export ORACLE_BASE
            ;;
        DATA=*)
            ORACLE_DATA=`echo "$1"|sed 's/^DATA=//'`
            export ORACLE_DATA
            ;;
        HOME=*)
            ORACLE_HOME=`echo "$1"|sed 's/^HOME=//'`
            export ORACLE_HOME
            ;;
        SID=*)
            ORACLE_SID=`echo "$1"|sed 's/^SID=//'`
            export ORACLE_SID
            ;;
        -h*)
            help
            exit 0
            ;;
        --h*)
            help
            exit 0
            ;;
        *)
            echo "unknown parameter '$1'"
            exit 1
            ;;
    esac
    shift
done

# try to guess some variables
if [ -n "$ORACLE_BASE" ]; then
    ORACLE_ADMIN="$ORACLE_BASE/admin"
fi
if [ -n "$ORACLE_HOME" ]; then
    ORACLE_DBS="$ORACLE_HOME/dbs"
fi

# check needed variables
OK=Y
for VAR in ORACLE_SID ORACLE_HOME ORACLE_ADMIN ORACLE_DATA; do
    eval VAL="\$$VAR"
    if [ -n "$VAL" ]; then
        export $VAR
    else
        echo "$VAR is not set!"
        OK=N
    fi
done
# check needed directories
if [ ! -d "$ORACLE_ADMIN" ]; then
    echo "admin dir '$ORACLE_ADMIN' doesn't exist"
    OK=N
fi
if [ ! -d "$ORACLE_HOME" ]; then
    echo "orahome dir '$ORACLE_HOME' doesn't exist"
    OK=N
fi
if [ ! -d "$ORACLE_DBS" ]; then
    echo "ora dbs dir '$ORACLE_DBS' doesn't exist"
    OK=N
fi
if [ ! -d "$ORACLE_DATA" ]; then
    echo "oradata dir '$ORACLE_DATA' doesn't exist"
    OK=N
fi
# OK ?
if [ "$OK" != "Y" ]; then
    echo "precondition failed, exiting."
    exit 1
fi

# setup variables with SID
SID_ADMIN="$ORACLE_ADMIN/$ORACLE_SID"
SID_DATA="$ORACLE_DATA/$ORACLE_SID"
SID_INITORA="$ORACLE_DBS/init$ORACLE_SID.ora"
#SID_ORAPW="$ORACLE_DBS/orapw$ORACLE_SID"

# check SID variables
if [ -d "$SID_ADMIN" ]; then
    echo "$SID_ADMIN allready exists!"
    OK=N
fi
if [ -d "$SID_DATA" ]; then
    echo "$SID_DATA allready exists!"
    OK=N
fi
if [ -f "$SID_INITORA" ]; then
    echo "$SID_INITORA allready exists!"
    OK=N
fi
# OK ?
if [ "$OK" != "Y" ]; then
    echo "SID $ORACLE_SID allready exists, exiting."
    exit 1
fi

echo "ORACLE_VERSION = '$ORACLE_VERSION'"
echo "ORACLE_SID     = '$ORACLE_SID'"
echo "ORACLE_HOME    = '$ORACLE_HOME'"
echo "ORACLE_DBS     = '$ORACLE_DBS'"
echo "ORACLE_ADMIN   = '$ORACLE_ADMIN'"
echo "ORACLE_DATA    = '$ORACLE_DATA'"
echo "SID_ADMIN      = '$SID_ADMIN'"
echo "SID_DATA       = '$SID_DATA'"
echo "SID_INITORA    = '$SID_INITORA'"
#echo "SID_ORAPW      = '$SID_ORAPW'"

# ask user if he's happy with these parameters
echo ""
OK=""
while [ "$OK" != "yes" ]; do
    echo 'create the database ? (yes|no)'
    read OK
    if [ "$OK" = "no" ]; then
        echo "aborting."
        exit 0
    fi
done

# create directories
mkdir $SID_ADMIN
mkdir $SID_ADMIN/create
mkdir $SID_ADMIN/cdump
mkdir $SID_ADMIN/adhoc
mkdir $SID_ADMIN/bdump
mkdir $SID_ADMIN/udump
mkdir $SID_DATA
mkdir $SID_DATA/archive

# set some variables depending on the oracle version
case "$ORACLE_VERSION" in
    9)
        ORA10SYSAUX=""
        O9=""
        ;;
    10)
        ORA10SYSAUX="
          SYSAUX DATAFILE '$SID_DATA/sysaux01.dbf'
            SIZE 60M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 400M"
        O9="# (ora9) "
        ;;
    *)
        ORA10SYSAUX=""
        O9=""
        exit 1
        ;;
esac

# create init.ora
cat > $SID_INITORA << EOF
#########################################################################
#                                Instance $ORACLE_SID                   #
db_name                          = $ORACLE_SID
instance_name                    = $ORACLE_SID
service_names                    = $ORACLE_SID
db_cache_size                    = 150M
pga_aggregate_target             = 50M
shared_pool_size                 = 50M
aq_tm_processes                  = 0
${O9}log_archive_start           = FALSE
log_archive_dest                 = $SID_DATA/archive
log_archive_format               = "T%TS%S.arc"
background_dump_dest             = $SID_ADMIN/bdump
user_dump_dest                   = $SID_ADMIN/udump
core_dump_dest                   = $SID_ADMIN/cdump
control_files                    = ( "$SID_DATA/control1.ctl",
                                     "$SID_DATA/control2.ctl" )
${O9}oracle_trace_enable         = FALSE

#########################################################################
#                                Resources                              #
db_block_checksum                = FALSE
db_block_size                    = 8192
db_file_multiblock_read_count    = 8
db_files                         = 100
dml_locks                        = 1000
fast_start_mttr_target           = 900
java_pool_size                   = 0
${O9}max_enabled_roles           = 30
open_cursors                     = 500
${O9}parallel_automatic_tuning   = FALSE
parallel_max_servers             = 8
processes                        = 100
sga_max_size                     = 150M
undo_management                  = auto
undo_tablespace                  = undo
undo_retention                   = 5400
workarea_size_policy             = auto

#########################################################################
#                                Optimizer                              #
compatible                       = 9.2.0
cursor_sharing                   = EXACT
cursor_space_for_time            = FALSE
optimizer_index_caching          = 80
optimizer_index_cost_adj         = 25
${O9}optimizer_max_permutations  = 1000
optimizer_mode                   = CHOOSE
timed_statistics                 = TRUE

#########################################################################
#                                Logs & Dumps                           #
log_checkpoint_timeout           = 10000
max_dump_file_size               = 10M

#########################################################################
#                                Misc                                   #
global_names                     = FALSE
os_authent_prefix                = ""
o7_dictionary_accessibility      = TRUE
remote_login_passwordfile        = NONE
EOF

# create orapw file
#rm -f $SID_ORAPW
#orapwd "file=$SID_ORAPW" password=oracle entries=10

# create create-script
cat > $SID_ADMIN/create/create.sh << CREATEEOF
#!/bin/sh

sqlplus /nolog << EOF
set echo on
set define off
whenever OSERROR exit 2
whenever SQLERROR exit 1
spool create1.log

prompt STEP: create database
set termout on
connect SYS/change_on_install as SYSDBA
startup nomount
set verify on
CREATE DATABASE $ORACLE_SID CONTROLFILE REUSE
MAXINSTANCES 1
MAXLOGHISTORY 0
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 1022
DATAFILE '$SID_DATA/system01.dbf'
  SIZE 150M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 250M
  EXTENT MANAGEMENT LOCAL $ORA10SYSAUX
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
  '$SID_DATA/temp01.dbf'
  SIZE 20M REUSE AUTOEXTEND ON next 10M maxsize 50M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
UNDO TABLESPACE "UNDO" DATAFILE '$SID_DATA/undo01'
  SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 50M
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 ('$SID_DATA/redolog1a.rdo',
           '$SID_DATA/redolog1b.rdo') SIZE 50M REUSE,
  GROUP 2 ('$SID_DATA/redolog2a.rdo',
           '$SID_DATA/redolog2b.rdo') SIZE 50M REUSE,
  GROUP 3 ('$SID_DATA/redolog3a.rdo',
           '$SID_DATA/redolog3b.rdo') SIZE 50M REUSE,
  GROUP 4 ('$SID_DATA/redolog4a.rdo',
           '$SID_DATA/redolog4b.rdo') SIZE 50M REUSE;
prompt STEP: done.
spool off

spool create2.log
connect SYS/change_on_install as SYSDBA
whenever SQLERROR continue

prompt STEP: creates data dictionary views 
@$ORACLE_HOME/rdbms/admin/catalog.sql;
prompt STEP: done.

prompt STEP: create catalog v7 style export/import views
@$ORACLE_HOME/rdbms/admin/catexp7.sql;
prompt STEP: done.

prompt STEP: create catalog oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql;
prompt STEP: done.

prompt STEP: create catalog procedural option
@$ORACLE_HOME/rdbms/admin/catproc.sql;
prompt STEP: done.

prompt STEP: create catalog oracle cryptographic toolkit
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
prompt STEP: done.

prompt STEP: create catalog PL/SQL obfuscation toolkit
@$ORACLE_HOME/rdbms/admin/catobtk.sql;
prompt STEP: done.

prompt STEP: create catalog heterogeneous hervices
@$ORACLE_HOME/rdbms/admin/caths.sql;
prompt STEP: done.

prompt STEP: create user ovm_sys for storing metadata
@$ORACLE_HOME/rdbms/admin/owminst.plb;
prompt STEP: done.
spool off

connect SYSTEM/manager
spool create3.log

prompt STEP: install sql*plus product_user_profile tables
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
prompt STEP: done.

prompt STEP: build sql*plus help taple and load English languege help test
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;
prompt STEP: done.
spool off

set echo on
whenever SQLERROR exit 1
spool create4.log
prompt STEP: shutdown
connect SYS/change_on_install as SYSDBA
shutdown immediate;
prompt STEP: done.
spool off
EOF

CREATEEOF
chmod 755 $SID_ADMIN/create/create.sh

# now create the db
cd $SID_ADMIN/create
./create.sh

# and some info...
echo "==============================================================================="
echo ""
echo "dont't forget to add the new SID to oratab, listener.ora and tnsnames.ora"



Urs Stotz 2005-09-01