# Akadia AG, Arvenweg 4, CH-3604 Thun                           initRAB1.ora
# --------------------------------------------------------------------------
# File:        initRAB1.ora
#
# Autor:       Martin Zahn Akadia AG 09.05.2000
#
# Purpose:     Create database RAB1
#
# Location:    $ORACLE_HOME/dbs
#
# Certified:   Oracle 8.1.7 on RedHat 6.2
# --------------------------------------------------------------------------

SPOOL $HOME/config/8.1.7/init$ORACLE_SID.log;

CONNECT sys/manager AS SYSDBA;

### Startup database
### ----------------

STARTUP NOMOUNT pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora

### Create database
### ---------------

CREATE DATABASE RAB1
        CONTROLFILE     REUSE
        MAXDATAFILES    256
        MAXINSTANCES    4
        MAXLOGFILES     62
        MAXLOGMEMBERS   5
        MAXLOGHISTORY   1600
        CHARACTER SET   "UTF8"
        NATIONAL CHARACTER SET   "WE8ISO8859P1"
        DATAFILE  '/u01/oracle/db/RAB1/sys/RAB1_sys1.dbf' SIZE 300M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/u01/oracle/db/RAB1/rdo/RAB1_log1A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log1B.rdo') SIZE 15M REUSE,
        GROUP 2 ('/u01/oracle/db/RAB1/rdo/RAB1_log2A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log2B.rdo') SIZE 15M REUSE,
        GROUP 3 ('/u01/oracle/db/RAB1/rdo/RAB1_log3A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log3B.rdo') SIZE 15M REUSE,
        GROUP 4 ('/u01/oracle/db/RAB1/rdo/RAB1_log4A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log4B.rdo') SIZE 15M REUSE,
        GROUP 5 ('/u01/oracle/db/RAB1/rdo/RAB1_log5A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log5B.rdo') SIZE 15M REUSE,
        GROUP 6 ('/u01/oracle/db/RAB1/rdo/RAB1_log6A.rdo',
                 '/opt/oracle/db/RAB1/rdo/RAB1_log6B.rdo') SIZE 15M REUSE;

CREATE ROLLBACK SEGMENT sys_rbs
        TABLESPACE SYSTEM
        STORAGE
        (
          INITIAL     200K
          NEXT        200K
          MINEXTENTS    2
          MAXEXTENTS  100
        );

ALTER ROLLBACK SEGMENT sys_rbs ONLINE;

### Create tablespace for rollback segments
###
### Do not set PCTINCREASE for rollback segment tablespace
### nor for any rollback segment. Rollback segments always
### have a PCTINCREASE value of 0
### -------------------------------------------------------

### Dictionary managed

# CREATE TABLESPACE rbs
#         DATAFILE '/u01/oracle/db/RAB1/rbs/RAB1_rbs1.dbf' SIZE 500M REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         MINIMUM EXTENT 64K
#         DEFAULT STORAGE (INITIAL     1024K
#                          NEXT        1024K
#                          MINEXTENTS  20
#                          MAXEXTENTS  UNLIMITED)
# PERMANENT
# ONLINE;

### Locally managed (SIZE + 64K for Header Bitmap)
### e.g. for 100MB: (1024*100+64)=102464K

CREATE TABLESPACE rbs
        DATAFILE '/u01/oracle/db/RAB1/rbs/RAB1_rbs1.dbf' SIZE 512064K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
        PERMANENT
        ONLINE;

CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs02 ONLINE;

CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs03 ONLINE;

CREATE ROLLBACK SEGMENT rbs04 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs04 ONLINE;

CREATE ROLLBACK SEGMENT rbs05 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs05 ONLINE;

CREATE ROLLBACK SEGMENT rbs06 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs06 ONLINE;

CREATE ROLLBACK SEGMENT rbs07 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs07 ONLINE;

CREATE ROLLBACK SEGMENT rbs08 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs08 ONLINE;

CREATE ROLLBACK SEGMENT rbs09 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs09 ONLINE;

CREATE ROLLBACK SEGMENT rbs10 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs10 ONLINE;

CREATE ROLLBACK SEGMENT rbs11 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs11 ONLINE;

CREATE ROLLBACK SEGMENT rbs12 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs12 ONLINE;

CREATE ROLLBACK SEGMENT rbs13 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs13 ONLINE;

CREATE ROLLBACK SEGMENT rbs14 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs14 ONLINE;

CREATE ROLLBACK SEGMENT rbs15 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs15 ONLINE;

CREATE ROLLBACK SEGMENT rbs16 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs16 ONLINE;

CREATE ROLLBACK SEGMENT rbs17 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs17 ONLINE;

CREATE ROLLBACK SEGMENT rbs18 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs18 ONLINE;

CREATE ROLLBACK SEGMENT rbs19 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs19 ONLINE;

CREATE ROLLBACK SEGMENT rbs20 TABLESPACE rbs
  STORAGE
  (INITIAL     1024K
   NEXT        1024K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED);
ALTER ROLLBACK SEGMENT rbs20 ONLINE;

### The rollback segments are to be set in the configuration file
### rollback_segments = (rbs01,rbs02,rbs03,rbs04,rbs05, ........
### The second rollback segment in system can now be set offline
### -------------------------------------------------------------

ALTER ROLLBACK SEGMENT sys_rbs OFFLINE;

### Create temporary tablespace
### ---------------------------

### Create Dictionary managed TEMP tablespace for initial DB creation

# CREATE TABLESPACE temp
#         DATAFILE '/u01/oracle/db/RAB1/tmp/RAB1_temp1.dbf' SIZE 200M REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         MINIMUM EXTENT 64K
#         DEFAULT STORAGE (INITIAL     2M
#                          NEXT        2M
#                          MINEXTENTS  2
#                          MAXEXTENTS  UNLIMITED
#                          PCTINCREASE 0)
#         TEMPORARY
#         ONLINE;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TEMPORARY TABLESPACE temp
        TEMPFILE '/u01/oracle/db/RAB1/tmp/RAB1_temp1.dbf' SIZE 512064K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;


### Create users tablespace
### -----------------------

### Dictionary managed

# CREATE TABLESPACE users
#         DATAFILE '/u01/oracle/db/RAB1/usr/RAB1_users1.dbf' SIZE 10M REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         MINIMUM EXTENT 64K
#         DEFAULT STORAGE (INITIAL     100K
#                          NEXT        100K
#                          MINEXTENTS  1
#                          MAXEXTENTS  UNLIMITED
#                          PCTINCREASE 0)
#         PERMANENT
#         ONLINE;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE users
        DATAFILE '/u01/oracle/db/RAB1/usr/RAB1_users1.dbf' SIZE 10304K REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;


### Create tablespace for small objects
### -----------------------------------

### Dictionary managed

# CREATE TABLESPACE tab
#         DATAFILE '/u01/oracle/db/RAB1/tab/RAB1_tab1.dbf' SIZE 2000M REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         MINIMUM EXTENT 64K
#         DEFAULT STORAGE (INITIAL     64K
#                          NEXT        64K
#                          MINEXTENTS  1
#                          MAXEXTENTS  UNLIMITED
#                          PCTINCREASE 0)
#         PERMANENT
#         ONLINE;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE tab
        DATAFILE '/u01/oracle/db/RAB1/tab/RAB1_tab1.dbf' SIZE 819264K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;

### Create tablespace for small Indexes
### -----------------------------------

### Dictionary managed

# CREATE TABLESPACE idx
#         DATAFILE '/u01/oracle/db/RAB1/idx/RAB1_idx1.dbf' SIZE 2000M REUSE
#         AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
#         MINIMUM EXTENT 64K
#         DEFAULT STORAGE (INITIAL     200K
#                          NEXT        200K
#                          MINEXTENTS  1
#                          MAXEXTENTS  UNLIMITED
#                          PCTINCREASE 0)
#         PERMANENT
#         ONLINE;

### Locally managed (SIZE + 64K for Header Bitmap)

CREATE TABLESPACE idx
        DATAFILE '/u01/oracle/db/RAB1/idx/RAB1_idx1.dbf' SIZE 819264K REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
        PERMANENT
        ONLINE;

### Set security for the administrative users
### -----------------------------------------

ALTER USER sys
TEMPORARY TABLESPACE temp;
ALTER USER sys IDENTIFIED BY manager;

ALTER USER system
TEMPORARY TABLESPACE temp;
ALTER USER system IDENTIFIED BY manager;

### Load the system scripts
### -----------------------

SET STOPONERROR OFF;
SET TERMOUT OFF;

# Creates data dictionary views. This is the very first
# Script which must be executed during Database Building
@$ORACLE_HOME/rdbms/admin/catalog.sql

# Parallel-Server specific views for performance queries
@$ORACLE_HOME/rdbms/admin/catparr.sql

# Scripts for the PL/SQL procedural option
@$ORACLE_HOME/rdbms/admin/catproc.sql

# Run all sql scripts for replication option
@$ORACLE_HOME/rdbms/admin/catrep.sql

# Collect I/O per table (actually object) statistics by
# statistical  sampling
@$ORACLE_HOME/rdbms/admin/catio.sql

# This package creates a table into which references to
# the chained rows for an IOT (Index-Only-Table) can be
# placed using the ANALYZE command.
@$ORACLE_HOME/rdbms/admin/dbmsiotc.sql

# Wrap Package which creates IOTs (Index-Only-Table)
@$ORACLE_HOME/rdbms/admin/prvtiotc.plb

# This package allows you to display the sizes of objects in the
# shared pool, and mark them for keeping or unkeeping in order to
# reduce memory fragmentation.
@$ORACLE_HOME/rdbms/admin/dbmspool.sql

# Creates the default table for storing the output
# of the ANALYZE LIST CHAINED ROWS command
@$ORACLE_HOME/rdbms/admin/utlchain.sql

# Creates the EXCEPTION table
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

# Grant public access to all views used by TKPROF
# with verbose=y option
@$ORACLE_HOME/rdbms/admin/utltkprf.sql

# Create table PLAN_TABLE that is used by the EXPLAIN PLAN
# statement. The explain statement requires the presence of this
# table in order to store the descriptions of the row sources.
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

# Create performance tuning views
@$ORACLE_HOME/rdbms/admin/catperf.sql

# Create v7 style export/import views against the v8 RDBMS
# so that EXP/IMP v7 can be used to read out data in a v8 RDBMS.
# These views are necessary if you want to export from Oracle8
# and import in an Oracle7 database.
@$ORACLE_HOME/rdbms/admin/catexp7.sql

# Create views of oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql

# Print out the lock wait-for graph in a tree structured fashion
@$ORACLE_HOME/rdbms/admin/utllockt.sql

# Creates the default table for storing the output of the
# analyze validate command on a partitioned table
@$ORACLE_HOME/rdbms/admin/utlvalid.sql

# PL/SQL Package of utility routines for raw datatypes
@$ORACLE_HOME/rdbms/admin/utlraw.sql
@$ORACLE_HOME/rdbms/admin/prvtrawb.plb

# Contains the PL/SQL interface to the cryptographic toolkit
@$ORACLE_HOME/rdbms/admin/dbmsoctk.sql
@$ORACLE_HOME/rdbms/admin/prvtoctk.plb

# This package provides a built-in random number generator. It is
# faster than generators written in PL/SQL because it calls Oracle's
#internal random number generator.
@$ORACLE_HOME/rdbms/admin/dbmsrand.sql

# DBMS package specification for Oracle8 Large Object
# This package provides routines for operations on BLOB
# and CLOB datatypes.
@$ORACLE_HOME/rdbms/admin/dbmslob.sql

# Procedures for instrumenting database applications
# DBMS_APPLICATION_INFO package spec.
@$ORACLE_HOME/rdbms/admin/dbmsapin.sql

# Run obfuscation toolkit script.
@$ORACLE_HOME/rdbms/admin/catobtk.sql

# Create Heterogeneous Services data dictionary objects.
@$ORACLE_HOME/rdbms/admin/caths.sql

# Stored procedures for Oracle Trace server
@$ORACLE_HOME/rdbms/admin/otrcsvr.sql

### Recompile all INVALID objects
### -----------------------------
@$ORACLE_HOME/rdbms/admin/utlrp.sql

### Password Verify Function
### ------------------------
### @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

### Load scripts for Oracle Enterprise Manager
### ------------------------------------------

@smptsi80.sql
@xpview.sql

### Create roles and users
### ----------------------
### GRANT EXECUTE ON dbms_pipe, dbms_alert must be granted
### to the real users, not through a role !

DISCONNECT;
CONNECT system/manager;

CREATE USER scott IDENTIFIED BY tiger
    DEFAULT TABLESPACE tab
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON idx
    QUOTA UNLIMITED ON rbs
    QUOTA 0 ON system
    QUOTA UNLIMITED ON tab
    QUOTA UNLIMITED ON temp
    QUOTA UNLIMITED ON users
    PROFILE default;
    GRANT CONNECT,RESOURCE TO scott;
    GRANT ALL PRIVILEGES TO scott;

ALTER USER dbsnmp
TEMPORARY TABLESPACE temp;

DISCONNECT;
CONNECT INTERNAL;

GRANT EXECUTE ON dbms_pipe TO PUBLIC;
GRANT EXECUTE ON dbms_alert TO PUBLIC;
GRANT EXECUTE ON dbms_obfuscation_toolkit TO PUBLIC;

### Setup PLAN_TABLE for TKPROF, SQL-Tuning
### ---------------------------------------

GRANT DELETE,INSERT,UPDATE,SELECT ON sys.plan_table TO PUBLIC;
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT SELECT ON V_$PARAMETER TO PUBLIC;

### Setup AUTO TRACE for SQL*PLUS
### -----------------------------

@$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT plustrace to scott;

DISCONNECT;
CONNECT system/manager;

### SQL*PLUS product user profile
### -----------------------------

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

EXIT;