UNIX Shell Script

Create Database Template

Updated : 26-October-1998
Version : 1.1

Description

Creates a database. You will need to edit this and enter you system tablespace directory and size and the redo log members redo log directory and size. The second member has been added as a seperate line to make it easier to edit out if there is only a requirement for one member.

Parameters

$1 (MANDATORY) - ORACLE_SID of the database to create

Shell Source

#!/bin/ksh

DATABASE=$1

# Check that we have a database
if [ -z "${DATABASE}" ]
then
   echo
   echo "No Database Specified !"
   echo
   echo "Usage : create_db.ksh <ORACLE_SID>"
   echo
   echo "where ORACLE_SID (Mandatory) - SID of database to start"
   echo
   echo " e.g.  $ create_db.ksh PROD" 
   echo
   exit 1
fi

# Get the ORACLE_HOME from the oratab file
ORACLE_HOME=`cat /etc/oratab | grep $DATABASE | awk -F: '{print $2}'`
if [ ! -d "${ORACLE_HOME}" ]
then
  echo
  echo "$ORACLE_HOME does not exist !"
  echo
  exit 1
fi

# Check we have created an init.ora file
if [ ! -f "${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora" ]  # could be a link
then
  echo
  echo "${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora does not exist !"
  echo
  echo "The database init.ora has not been created."
  echo
  exit 1
fi

ORACLE_SID=$DATABASE; export ORACLE_SID
PATH=.:$ORACLE_HOME/bin:$PATH; export PATH

# See if we can find svrmgrl, if not use sqldba instead
if [ -x "${ORACLE_HOME}/bin/svrmgrl" ]
then
    SVR="svrmgrl"
else
    SVR="sqldba lmode=y"
fi

$SVR << EOF

spool /tmp/create_${ORACLE_SID}.log

connect internal
startup nomount
create database "${ORACLE_SID}" controlfile reuse
       datafile '<SYSTEM DATAFILE>' size 25M reuse
       logfile  '<REDO LOG GROUP 1>' size 500k reuse,
                '<REDO LOG GROUP 2>' size 500k reuse
       maxdatafiles   128
       maxlogfiles    32
       maxinstances   16
       maxlogmembers  2
       maxloghistory  1600
       character set  US7ASCII;

select name
       , to_char(to_date(created,'mm/dd/yy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') 
       , log_mode
       , CHECKPOINT_CHANGE#
       , ARCHIVE_CHANGE#
from v$database;

@${ORACLE_HOME}/rdbms/admin/catalog.sql
@${ORACLE_HOME}/rdbms/admin/catproc.sql
@${ORACLE_HOME}/rdbms/admin/dbmspool.sql
rem 
rem Some Versions of Oracle previous to 7.3 use prvtpool.sql, uncomment this line if needed
rem
rem @${ORACLE_HOME}/rdbms/admin/prvtpool.sql
@${ORACLE_HOME}/rdbms/admin/prvtpool.plb

<ADD ANY ADDITONAL SCRIPTS HERE e.g.dbmspipe.sql>

create rollback segment system_admin tablespace system 
storage (initial 1M next 1M optimal 2M);
alter rollback segment system_admin online;

connect system/manager
@${ORACLE_HOME}/rdbms/admin/catdbsyn.sql
@${ORACLE_HOME}/sqlplus/admin/pupbld.sql

rem
rem Check /tmp/create_${ORACLE_SID}.log for errors
rem
rem You are now ready to create the ROLLBACK,TEMPORARY,DATA and INDEX tablespaces.
rem
rem CREATE DATABASE COMPLETED.
rem
spool off
exit
EOF

exit 0