# Akadia AG, Zieglerstrasse 34, CH-3007 Bern                    initSOL3.sql
# --------------------------------------------------------------------------
# File:        initSOL3.sql
#
# Autor:       Martin Zahn Akadia AG 11.08.99 (Tag der Sonnenfinsternis)
#
# Purpose:     Erstellen der produktiven Datenbank SOL3
#
# Location:    $ORACLE_HOME/dbs
#
# Certified:   Oracle 8.1.5 on SUN Solaris 2.6
# --------------------------------------------------------------------------

SPOOL $HOME/config/8.1.5/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 SOL3
        CONTROLFILE     REUSE
        MAXDATAFILES    256
        MAXINSTANCES    4
        MAXLOGFILES     62
        MAXLOGMEMBERS   5
        MAXLOGHISTORY   1600
        CHARACTER SET   "WE8ISO8859P1"
        NATIONAL CHARACTER SET   "WE8ISO8859P1"
        DATAFILE  '/u01/db/SOL3/sys/SOL3_sys1.dbf' SIZE 175M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/u01/db/SOL3/rdo/SOL3_log1A.rdo',
                 '/oracle/redo/SOL3/SOL3_log1B.rdo') SIZE 15M REUSE,
        GROUP 2 ('/u01/db/SOL3/rdo/SOL3_log2A.rdo',
                 '/oracle/redo/SOL3/SOL3_log2B.rdo') SIZE 15M REUSE,
        GROUP 3 ('/u01/db/SOL3/rdo/SOL3_log3A.rdo',
                 '/oracle/redo/SOL3/SOL3_log3B.rdo') SIZE 15M REUSE,
        GROUP 4 ('/u01/db/SOL3/rdo/SOL3_log4A.rdo',
                 '/oracle/redo/SOL3/SOL3_log4B.rdo') SIZE 15M REUSE,
        GROUP 5 ('/u01/db/SOL3/rdo/SOL3_log5A.rdo',
                 '/oracle/redo/SOL3/SOL3_log5B.rdo') SIZE 15M REUSE,
        GROUP 6 ('/u01/db/SOL3/rdo/SOL3_log6A.rdo',
                 '/oracle/redo/SOL3/SOL3_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
### -------------------------------------------------------

CREATE TABLESPACE rbs
        DATAFILE '/u01/db/SOL3/rbs/SOL3_rbs1.dbf' SIZE 500M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     1M
                         NEXT        1M
                         MINEXTENTS  20
                         MAXEXTENTS  UNLIMITED)
PERMANENT
ONLINE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE ROLLBACK SEGMENT rbs20 TABLESPACE rbs
  STORAGE
  (INITIAL     1M
   NEXT        1M
   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 TABLESPACE temp
        DATAFILE '/u01/db/SOL3/tmp/SOL3_temp1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     2M
                         NEXT        2M
                         MINEXTENTS  2
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        TEMPORARY
        ONLINE;

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

CREATE TABLESPACE users
        DATAFILE '/u01/db/SOL3/usr/SOL3_users1.dbf' SIZE 10M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     100K
                         NEXT        100K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

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

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

### Create tablespace for CDR table
### -------------------------------

CREATE TABLESPACE cdr
        DATAFILE '/u01/db/SOL3/cdr/SOL3_cdr1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     5M
                         NEXT        5M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

### Create tablespace for BOOKING table
### -----------------------------------

CREATE TABLESPACE bkg
        DATAFILE '/u01/db/SOL3/bkg/SOL3_bkg1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     5M
                         NEXT        5M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

### Create tablespace for REQUEST table
### -----------------------------------

CREATE TABLESPACE req
        DATAFILE '/u01/db/SOL3/req/SOL3_req1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     5M
                         NEXT        5M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

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

CREATE TABLESPACE idx
        DATAFILE '/u01/db/SOL3/idx/SOL3_idx1.dbf' SIZE 2000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     200K
                         NEXT        200K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

### Create tablespace for CDR indexes
### ---------------------------------

CREATE TABLESPACE idx_cdr
        DATAFILE '/u01/db/SOL3/idx/SOL3_idx_cdr1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     200K
                         NEXT        200K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
PERMANENT
ONLINE;

### Create tablespace for BOOKING indexes
### -------------------------------------

CREATE TABLESPACE idx_bkg
        DATAFILE '/u01/db/SOL3/idx/SOL3_idx_bkg1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     200K
                         NEXT        200K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
PERMANENT
ONLINE;

### Create tablespace for REQUEST indexes
### -------------------------------------

CREATE TABLESPACE idx_req
        DATAFILE '/u01/db/SOL3/idx/SOL3_idx_req1.dbf' SIZE 1000M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     200K
                         NEXT        200K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
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/utlchain1.sql

# Creates the EXCEPTION table
@$ORACLE_HOME/rdbms/admin/utlexcpt1.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

### 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 ppb users, not through a role !

DISCONNECT;
CONNECT system/manager;

CREATE USER smb IDENTIFIED BY smb
    DEFAULT TABLESPACE tab
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON cdr
    QUOTA UNLIMITED ON bkg
    QUOTA UNLIMITED ON req
    QUOTA UNLIMITED ON idx
    QUOTA UNLIMITED ON idx_bkg
    QUOTA UNLIMITED ON idx_req
    QUOTA UNLIMITED ON idx_cdr
    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 smb;
    GRANT ALL PRIVILEGES TO smb;

DROP USER clientadmin;
CREATE USER clientadmin IDENTIFIED BY clientadmin
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON cdr
    QUOTA UNLIMITED ON bkg
    QUOTA UNLIMITED ON req
    QUOTA UNLIMITED ON idx
    QUOTA UNLIMITED ON idx_bkg
    QUOTA UNLIMITED ON idx_req
    QUOTA UNLIMITED ON idx_cdr
    QUOTA UNLIMITED ON rbs
    QUOTA 0 ON system
    QUOTA UNLIMITED ON tab
    QUOTA UNLIMITED ON temp
    QUOTA UNLIMITED ON users
    PROFILE default;

DROP USER ops$smb;
CREATE USER ops$smb IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON cdr
    QUOTA UNLIMITED ON bkg
    QUOTA UNLIMITED ON req
    QUOTA UNLIMITED ON idx
    QUOTA UNLIMITED ON idx_bkg
    QUOTA UNLIMITED ON idx_req
    QUOTA UNLIMITED ON idx_cdr
    QUOTA UNLIMITED ON rbs
    QUOTA 0 ON system
    QUOTA UNLIMITED ON tab
    QUOTA UNLIMITED ON temp
    QUOTA UNLIMITED ON users
    PROFILE default;

ALTER USER dbsnmp
TEMPORARY TABLESPACE temp;

### Create Application Roles
### ------------------------

CREATE ROLE SMB_PROCESS;
CREATE ROLE SMB_CLIENT;
GRANT CONNECT,RESOURCE TO SMB_PROCESS;
GRANT CONNECT TO SMB_CLIENT;
GRANT SMB_PROCESS TO smb;
GRANT SMB_PROCESS TO ops$smb;
GRANT SMB_CLIENT TO clientadmin;

DISCONNECT;
CONNECT sys/manager AS SYSDBA;

GRANT EXECUTE ON dbms_pipe TO clientadmin;
GRANT EXECUTE ON dbms_pipe TO ops$smb;
GRANT EXECUTE ON dbms_pipe TO smb;
GRANT EXECUTE ON dbms_alert TO clientadmin;
GRANT EXECUTE ON dbms_alert TO ops$smb;
GRANT EXECUTE ON dbms_alert TO smb;

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

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

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

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

DISCONNECT;
CONNECT system/manager;

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

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

### SQL*PLUS Help Tables
### --------------------
### Start Shell Script: ./create_help_tables.sh

EXIT;