From akaplan@interaccess.com Wed Apr  7 10:51:06 1999
Date: Wed, 7 Apr 1999 10:49:34 -0500 (CDT)
From: Ari D Kaplan 
To: Vanessa 
Cc: akaplan@interaccess.com
Subject: Re: rollback segments

On Tue, 6 Apr 1999, Vanessa wrote:

> Sir
> I was browsing for information on rollback segments and I got to your website.
> 
> I am confused on on the necessary things to create for a new dbase eg.
> rollback segs. In short
> I don't know what to do to create a database now. 
> 
> Thanks
> Admirer 

Hi Vanessa,

Thanks for thinking of me for your Oracle questions.
You can create a database in two ways:
1) Use the Oracle installer to generate a default database
   Steps: create the Oracle user, directories, and run "orainst" install
   program.
2) Use a script to manually create the database for better control
   Steps: create the Oracle user, directories, create database scripts,
   and initialization parameter files.

   For the create database script, use the CREATE DATABASE command and
   then issue CREATE TABLESPACE, CREATE ROLLBACK, CREATE USER, and so on.

   A sample script is below:

-Ari Kaplan
Independent Oracle DBA Consultant
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
connect internal
startup nomount pfile=/home/oracle/admin/PROD/pfile/initPROD_0.ora
create database "PROD"
    maxinstances 8
    maxlogfiles  32
    character set "WE8ISO8859P9"
    datafile
        '/export/home6/oradata/PROD/system01.dbf'  size   40M
    logfile
        '/export/home6/oradata/PROD/redoPROD01.log' size 8M,
        '/export/home6/oradata/PROD/redoPROD02.log' size 8M,
        '/export/home6/oradata/PROD/redoPROD03.log' size 8M;

REM # install data dictionary views:
@/home/oracle/product/7.3.4/rdbms/admin/catalog.sql

REM * Create additional rollback segment in SYSTEM before creating tablespace.
REM *
create rollback segment r0 tablespace system
storage (initial 16k next 16k minextents 2 maxextents 20);

REM * Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online without shutting
REM * down and restarting the database.
REM *
alter rollback segment r0 online;
REM *   1 rollback segments for every 4 concurrent xactions.
REM *   No more than 50 rollback segments.
REM *   All rollback segments the same size.
REM *   Between 2 and 4 homogeneously-sized extents per rollback segment.
REM * Attempt to keep rollback segments to 4 extents.
REM *
create tablespace rbs datafile
        '/export/home6/oradata/PROD/rbs01.dbf'  size   100M
default storage (
        initial          2M
        next             2M
        pctincrease        0
        minextents         2
);

REM * Create a tablespace for temporary segments.
REM * Temporary tablespace configuration guidelines:
REM *   Initial and next extent sizes = k * SORT_AREA_SIZE, k in {1,2,3,...}.
REM *
create tablespace temp datafile
        '/export/home6/oradata/PROD/temp01.dbf'    size   50M

default storage (
        initial      256k
        next         256k
        pctincrease  0
);

REM * Create a tablespace for database tools.
REM *
create tablespace tools datafile
        '/export/home6/oradata/PROD/tools01.dbf'   size   15M;

REM * Create a tablespace for miscellaneous database user activity.
REM *
create tablespace users datafile
        '/export/home6/oradata/PROD/users01.dbf'   size   10M;


REM * Create rollback segments.
REM *
create rollback segment r01 tablespace rbs;
create rollback segment r02 tablespace rbs;
create rollback segment r03 tablespace rbs;
create rollback segment r04 tablespace rbs;

REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online
REM * without shutting down and restarting the database.  Only put one
REM * of the rollback segments online at this time so that it will always
REM * be the one used.  When the user shuts down the database and starts
REM * it up with initSID.ora, all four will be brought online.
REM *
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;


REM * Since we've created and brought online 4 more rollback segments,
REM * we no longer need the second rollback segment in the SYSTEM tablespace.
alter rollback segment r0 offline;
drop rollback segment r0;

REM * Alter SYS and SYSTEM users.
REM *
alter user sys temporary tablespace temp;
alter user system default tablespace tools temporary tablespace temp;

REM * Run catproc as internal...
@$ORACLE_HOME/rdbms/admin/catproc.sql

REM * Run dbmsutil as internal...
@$ORACLE_HOME/rdbms/admin/dbmsutil.sql

REM * For each DBA user, run DBA synonyms SQL script.  Don't forget that EACH
REM * DBA USER created in the future needs dba_syn.sql run from its account.
REM *
connect system/manager
@$ORACLE_HOME/rdbms/admin/catdbsyn.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql

> 

Back to Ari Kaplan's Home Page pupbld.sql >

Back to Ari Kaplan's Home Page