Oracle Database Create Script, In Ruby

###############################################################################################
$oracle_version="11.1.0"
 
$sid="orcl"
 
$oracle_base="/oracle/apps/oracle/oracle"
$oracle_home="#{$oracle_base}/product/11.1.0/db_1"
 
# datafile location
$data_home="#{$oracle_base}/oradata/#{$sid}"
 
# pfile location
$pfile_home="#{$oracle_base}/admin/#{$sid}/pfile"
 
# change default for production
$ctl1="#{$data_home}/control1.ctl"
$ctl2="/opt/oracle/oradata/#{$sid}/control2.ctl"
$ctl3="/backup/oracle/oradata/#{$sid}/control3.ctl"
 
# change default for production
$redo1="#{$data_home}/redo01.log"
$redo2="#{$data_home}/redo02.log"
$redo3="#{$data_home}/redo03.log"
 
$password="xxxxxxx"
###############################################################################################

#!/usr/bin/ruby
 
=begin
 
create database script.
 
todo: autocreate oratab entry
 
=end
 
#
# invoke sqlplus as sysdba, run sql, return output.
#
def sqlplus(sql)
  puts sql
  return if $debug
  connect_string = %Q("/as sysdba")
  sqlplus = "#{$oracle_home}/bin/sqlplus -s #{connect_string}"
  output = ""
 
  headers= <<txt
set pages 0
set lines 300
set head off
set echo off
set verify off
set feedback off
txt
 
  IO.popen(sqlplus, "w+") do |pipe|
    pipe.puts sql
    pipe.close_write
    output = pipe.read
  end
  puts output
  return output
end
 
#
# create the init.ora pfile
#
def create_init_ora_file
 
  if $oracle_version == "10.2.0"
    init_ora = <<txt
      control_files = (#{$ctl1},#{$ctl2},#{$ctl3})
      undo_management = AUTO
      undo_tablespace = undotbs01
      db_name = #{$sid}
      db_block_size = 8192
      sga_max_size = 1073741824   # 1GB
      sga_target = 1073741824     # 1GB
txt
 
  elsif $oracle_version == "11.1.0"
 
    init_ora = <<txt
      # log_archive_dest_1='LOCATION=/home/oracle/apps/oracle/oracle/product/11.1.0/db_1/dbs/arch'
      # log_archive_format=%t_%s_%r.dbf
      db_block_size=8192
      open_cursors=300
      db_domain=""
      db_name=#{$sid}
      control_files = (#{$ctl1},#{$ctl2},#{$ctl3})
      compatible=11.1.0.0.0
      diagnostic_dest=#{$oracle_base}
      # memory_target=4294967296 # 4GB
      memory_target=1073741824 # 1GB
      processes=200
      sessions=225
      audit_file_dest=#{$oracle_base}/admin/#{$sid}/adump
      audit_trail=db
      remote_login_passwordfile=EXCLUSIVE
      dispatchers="(PROTOCOL=TCP) (SERVICE=#{$sid}XDB)"
      undo_tablespace=undotbs01
txt
 
  end
 
  puts init_ora
  f = "#{$oracle_home}/dbs/init#{$sid}.ora"
  File.new(f, "w")
  open(f, 'w') { |f| f.puts init_ora }
end
 
def create_password_file
  cmd="orapwd file=#{$oracle_home}/dbs/orapw#{$sid} password=#{$password}"
  puts cmd
  %x[#{cmd}]
end
 
def create_db_dirs
  if $oracle_version == "10.2.0"
    # datafiles dir
    %x[mkdir -p #{$oracle_home}/oradata/#{$sid}]
    # archive files dir
    %x[mkdir -p #{$oracle_home}/dbs/arch/#{$sid}]
    # dump files dirs
    %x[mkdir -p #{$oracle_home}/admin/#{$sid}/adump]
    %x[mkdir -p #{$oracle_home}/admin/#{$sid}/bdump]
    %x[mkdir -p #{$oracle_home}/admin/#{$sid}/cdump]
    %x[mkdir -p #{$oracle_home}/admin/#{$sid}/udump]
  elsif $oracle_version == "11.1.0"
    # note: oracle 11 stores database related files off of oracle_base, not off of oracle_home.
    # adump directory
    %x[mkdir -p #{$oracle_base}/admin/#{$sid}/adump]
    %x[mkdir -p #{$oracle_base}/admin/#{$sid}/dpdump]
    # pfile location
    %x[mkdir -p #{$oracle_base}/admin/#{$sid}/pfile]
    # oradata
    %x[mkdir -p #{$oracle_base}/oradata/#{$sid}]
    # dbs
    %x[mkdir -p #{$oracle_home}/dbs]
    # dbs arch
    %x[mkdir -p #{$oracle_home}/dbs/arch]
  end
end
 
def prompt(s)
  puts "#{s} Continue? [Y/N]"
  choice = STDIN.gets
  choice.chomp!
  exit if choice != "Y"
end
 
################################################################################################
 
##############################################################################
$sid=ARGV[0]
if $sid == nil 
  puts "SID must be passed as a parameter. Exiting."
  exit
end
 
sidconfig = "#{$sid}.conf"
"Loading sidconfig for #{$sid}..."
load sidconfig
 
$debug=false
##############################################################################
 
prompt "About to create database for SID: #{$sid}"
 
prompt "Create dbdirs"
create_db_dirs
 
prompt "Create init.ora"
create_init_ora_file
 
prompt "Create password file"
create_password_file
 
prompt "Create spfile"
sqlplus "create spfile from pfile;"
 
prompt "Startup nomount"
sqlplus "startup nomount"
 
prompt "Create database"
sql = <<txt
CREATE DATABASE "#{$sid}"
   USER SYS IDENTIFIED BY #{$password}
   USER SYSTEM IDENTIFIED BY #{$password}
   LOGFILE GROUP 1 ('#{$redo1}') SIZE 100M,
           GROUP 2 ('#{$redo2}') SIZE 100M,
           GROUP 3 ('#{$redo3}') SIZE 100M
   MAXLOGFILES 16
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 200
   MAXINSTANCES 8
   CHARACTER SET UTF8
   NATIONAL CHARACTER SET AL16UTF16
     DATAFILE '#{$data_home}/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX 
     DATAFILE '#{$data_home}/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE temp
     TEMPFILE '#{$data_home}/temp01.dbf' SIZE 100M REUSE
   UNDO TABLESPACE undotbs01 
     DATAFILE '#{$data_home}/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
txt
 
#CHARACTER SET WE8ISO8859P1
#NATIONAL CHARACTER SET UTF8
 
sqlplus sql
 
 
sql = <<txt
 
CREATE TABLESPACE users LOGGING
     DATAFILE '#{$data_home}/users01.dbf' 
     SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;
txt
 
prompt "Create users tbs"
sqlplus sql
 
prompt "Run catalog.sql"
sqlplus "@?/rdbms/admin/catalog.sql"
 
prompt "Run catproc.sql"
sqlplus "@?/rdbms/admin/catproc.sql"