Scripts   Home

Create a Database Instance in Oracle

Before 8i there was no easy way to get the scripts to create databases in Oracle. Like it was a state secret or something. Larry, you are a moron.

Oracle seems to have a lack of basic utilities, like schema extraction, etc. Some of this DDL can be seen in a "full" database export, but gee whiz, Oracle seems to revel in making it difficult to use their expensively licensed product.


#################### cut here ##########################
REM * Database instance create script used with Oracle 8.0.5
REM * Start the  instance (ORACLE_SID here must be set to ).
REM * Use svrmgrl so admin procedures get run correctly.
-- Shutdown instance, remove all datafiles, control files, lockfiles,
-- logfiles to rerun and recreate the same instance

set termout on
set echo on
spool create.log

connect internal
startup nomount pfile=/apps/oracle/product/8.0.5/dbs/initumnp.ora

create database "umnp"
    maxinstances 8
    maxlogfiles  32
    maxdatafiles 400
    character set "US7ASCII"
    datafile
	'/vol01/oradata/umnp/system01.dbf'	size  100M
    logfile 
	('/vol05/oradata/umnp/redo01.01',
	'/vol06/oradata/umnp/redo01.02') size 20M,
        group 2
        ('/vol05/oradata/umnp/redo02.01',
        '/vol06/oradata/umnp/redo02.02') size 20M,
        group 3
        ('/vol05/oradata/umnp/redo03.01',
        '/vol06/oradata/umnp/redo03.02') size 20M,
        group 4
        ('/vol05/oradata/umnp/redo04.01',
        '/vol06/oradata/umnp/redo04.02') size 20M,
        group 5
        ('/vol05/oradata/umnp/redo05.01',
        '/vol06/oradata/umnp/redo05.02') size 20M ;


@/apps/oracle/product/8.0.5/rdbms/admin/catalog.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catproc.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catparr.sql


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

alter rollback segment temprbs online;

drop tablespace rollback01;
create tablespace rollback01 
 datafile '/vol07/oradata/umnp/rollback01_01.dbf' size 128M reuse
  default storage (initial 1m next 1m maxextents unlimited pctincrease 0);

drop tablespace temp01 ;
create tablespace temp01 
 datafile '/vol01/oradata/umnp/temp01_01.dbf' size 128M reuse
  default storage (initial 2m next 2m maxextents unlimited pctincrease 0) 
  temporary; 

drop tablespace user01 ;
create tablespace user01 
 datafile '/vol02/oradata/umnp/user01_01.dbf' size 100M reuse
  default storage (initial 100k next 100k maxextents unlimited pctincrease 0); 

create rollback segment R01
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R02
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R03
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R04
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R05
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);


alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r05 online;

alter rollback segment temprbs offline;

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.
REM *
alter user sys temporary tablespace temp01;
alter user system default tablespace user01 temporary tablespace temp01;

REM * Now run the Oracle-supplied scripts we need for this DB
REM *
@/apps/oracle/product/8.0.5/rdbms/admin/catblock.sql 
@/apps/oracle/product/8.0.5/rdbms/admin/catio.sql 
@/apps/oracle/product/8.0.5/rdbms/admin/dbmspool.sql

REM * All done, so close the log file and exit.
REM *
spool off
exit

################## cut here ###################################