RE: DATABASE CREATION SCRIPT

From: Glenn Travis <Glenn.Travis_at_sas.com>
Date: Wed, 31 Oct 2001 11:21:14 -0800
Message-ID: <F001.003B98B9.20011031112131@fatcity.com>

Here's mine. The sid is obviously EST. The will need to create the init.ora file first and you may want to add redo log mirrors.

#!/bin/sh

# Be sure $ORACLE_HOME is defined.

# Define Instance
ORACLE_SID=EST
export ORACLE_SID

# Use sqlplus (svrmgrl being phased out in future releases).

$ORACLE_HOME/bin/sqlplus << EOF
internal
spool $0.$ORACLE_SID.log

REM * Start the instance (ORACLE_SID must be set). REM * Init file must be defined.

startup nomount pfile=$ORACLE_HOME/dbs/initEST.ora

REM * Create the database.
REM * Enter database name.

create database "EST"

    maxinstances 8
    maxlogfiles 32
    maxdatafiles 1000
    character set "US7ASCII"

datafile '/EST/oradata01/oradata/EST/system01.dbf' size 300M
logfile  '/EST/oradata01/oradata/EST/redo01a.log' size 1M,
         '/EST/oradata01/oradata/EST/redo02a.log' size 1M,
         '/EST/oradata01/oradata/EST/redo03a.log' size 1M;

REM * install data dictionary:
@$ORACLE_HOME/rdbms/admin/catalog.sql

REM * install procedural components:
@$ORACLE_HOME/rdbms/admin/catproc.sql

REM * Modify the system tablespace storage params.

alter tablespace system
default storage (

  initial      64K
  next         64K

  pctincrease 50
  minextents 1
  maxextents unlimited
);

alter tablespace system
minimum extent 64K;

REM * Create additional rollback segment in SYSTEM since
REM * at least one non-system rollback segment is required
REM * before creating a tablespace.

create rollback segment SYSROLL tablespace system storage (initial 32K next 32K minextents 2 maxextents 99);

REM * Put SYSROLL online without shutting
REM * down and restarting the database.
REM *

alter rollback segment SYSROLL online;

REM * Create a tablespace for rollback segments.

create tablespace RBS
datafile '/EST/oradata01/oradata/EST/rbs01.dbf' size 200M autoextend off
minimum extent 512K
default storage (

  initial      512K
  next         512K

pctincrease 0
minextents 4
);

REM * Create the rollback segments.

create rollback segment RBS01 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS02 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS03 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS04 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS05 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS06 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS07 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS08 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS09 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M); create rollback segment RBS10 tablespace RBS storage (initial 512K next 512K minextents 4 optimal 2M);

REM * Use ALTER RBS SEGMENT ONLINE to put rollback segments online REM * without shutting down and restarting the database.

alter rollback segment RBS01 online;
alter rollback segment RBS02 online;
alter rollback segment RBS03 online;
alter rollback segment RBS04 online;
alter rollback segment RBS05 online;
alter rollback segment RBS06 online;
alter rollback segment RBS07 online;
alter rollback segment RBS08 online;
alter rollback segment RBS09 online;
alter rollback segment RBS10 online;

REM * Since we've created and brought online more rollback segments,
REM * we no longer need the rollback segment in the SYSTEM tablespace.

REM * We could delete it, but we will leave it here in case we need it REM * in the future.

alter rollback segment SYSROLL offline;

REM * Create a tablespace for temporary segments. create tablespace TEMP
datafile '/EST/oradata01/oradata/EST/temp01.dbf' size 300M autoextend off
minimum extent 128K
default storage (

 initial     128K
 next        128K

minextents 1
maxextents UNLIMITED
pctincrease 0
)
temporary;

REM * Create a tablespace for database tools.

create tablespace TOOLS
datafile '/EST/oradata01/oradata/EST/tools01.dbf' size 10M autoextend off
minimum extent 32K
default storage (

 initial     32K
 next        32K

minextents 1
pctincrease 0
);

REM * Create tablespaces for user activity.

create tablespace EST_D01
datafile '/EST/oradata01/oradata/EST/esd_data01.dbf' size 2048M default storage (

   initial     1M
   next        1M

maxextents UNLIMITED
pctincrease 0
);

REM * Create tablespaces for indexes.

create tablespace EST_I01
datafile '/EST/oradata01/oradata/EST/esd_index01.dbf' size 1024M default storage (

   initial     512K
   next        512K

maxextents UNLIMITED
pctincrease 0
);

REM * Create tablespaces for users.
REM * Not necessary in dev.

REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don't
REM * want that.

alter user sys default tablespace TOOLS temporary tablespace TEMP; alter user system default tablespace TOOLS temporary tablespace TEMP;

REM * Now run the Oracle-supplied scripts we need for this DB

@$ORACLE_HOME/rdbms/admin/catexp.sql
@$ORACLE_HOME/rdbms/admin/caths.sql
@$ORACLE_HOME/rdbms/admin/otrcsvr.sql
@$ORACLE_HOME/rdbms/admin/catrep.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catio.sql
@$ORACLE_HOME/rdbms/admin/dbmsutil.sql
@$ORACLE_HOME/rdbms/admin/dbmspool.sql
@$ORACLE_HOME/rdbms/admin/dbmslock.sql
@$ORACLE_HOME/rdbms/admin/prvtpool.plb

connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql
@$ORACLE_HOME/rdbms/admin/utlvalid.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/rdbms/admin/catdbsyn.sql

disconnect
spool off
exit

EOF

> -----Original Message-----
> From: Harvinder Singh [mailto:Harvinder.Singh_at_MetraTech.com]
> Sent: Wednesday, October 31, 2001 1:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: DATABASE CREATION SCRIPT
> 
> 
> Hi,
> 
> We will appreciate if someone can send a script that
> can be used to creata a  oracle database on unix .
> We need to give these script as batch file to developer so
> that they can create database.
> Oracle version is 8.1.7
> OS sun solaris 2.8
> 
> 
> Thanks
> -Harvinder
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Harvinder Singh
>   INET: Harvinder.Singh_at_MetraTech.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_sas.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Oct 31 2001 - 13:21:14 CST