UNIX Shell Script Create Database Template

Updated : 18-Apr-2002
Version : 2.0

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 "
   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

sqlplus << EOF

spool create_${ORACLE_SID}.log

connect / as sysdba
startup nomount
create database "${ORACLE_SID}" controlfile reuse
       datafile '' size 250 M reuse
       autoextend on next 10M
       logfile  '' size 500k reuse,
                '' size 500k reuse
       maxdatafiles   254
       maxlogfiles    32
       maxinstances   16
       maxlogmembers  2
       maxloghistory  1600
       character set  UTF8;          # Unicode  Character Set
-- character set WE8ISO8859P15       # if you want Western European

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;

/*--------------------------------------------------------
-- Creating the Data Dictionary
---------------------------------------------------------*/
@${ORACLE_HOME}/rdbms/admin/catalog.sql
@${ORACLE_HOME}/rdbms/admin/catproc.sql
@${ORACLE_HOME}/rdbms/admin/caths.sql
@${ORACLE_HOME}/rdbms/admin/otrcsvr.sql

/*--------------------------------------------------------
--  Uncomment this for Scott user and Demo Tables
--
--  @$ORACLE_HOME/rdbms/admin/utlsampl.sql
---------------------------------------------------------*/

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


/*--------------------------------------------------------
-- Creating DBA Synonyms and Product Profiles Tables 
---------------------------------------------------------*/
connect system/manager
@${ORACLE_HOME}/rdbms/admin/catdbsyn.sql
@${ORACLE_HOME}/sqlplus/admin/pupbld.sql

/*--------------------------------------------------------
-- Uncomment this to Loading the help
--
-- @$ORACLE_HOME/sqlplus/admin/help/helpbld.sql helpus.sql
---------------------------------------------------------*/

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

Previous Oracle Version Links

Create Database Template

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal