# Akadia AG, Zieglerstrasse 34, CH-3007 Bern                    initSOL1.sql
# --------------------------------------------------------------------------
# File:        initSOL1.sql
#
# Autor:       Martin Zahn Akadia AG 11.08.99 (Tag der Sonnenfinsternis)
# 
# Purpose:     Erstellen der produktiven Datenbank SOL1 
#
# Certified:   Oracle 7.3.4.4 on SUN Solaris 2.6
# --------------------------------------------------------------------------

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

CONNECT INTERNAL;

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

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

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

CREATE DATABASE SOL1
        CONTROLFILE     REUSE
        MAXDATAFILES    256
        MAXINSTANCES    4
        MAXLOGFILES     62
        MAXLOGMEMBERS   5
        MAXLOGHISTORY   1600
        CHARACTER SET   "WE8ISO8859P1"
        DATAFILE  '/u01/db/SOL1/sys/SOL1_sys1.dbf' SIZE 100M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/u01/db/SOL1/rdo/SOL1_log1A.rdo',
                 '/oracle/redo/SOL1/SOL1_log1B.rdo') SIZE 10M REUSE,
        GROUP 2 ('/u01/db/SOL1/rdo/SOL1_log2A.rdo',
                 '/oracle/redo/SOL1/SOL1_log2B.rdo') SIZE 10M REUSE,
        GROUP 3 ('/u01/db/SOL1/rdo/SOL1_log3A.rdo',
                 '/oracle/redo/SOL1/SOL1_log3B.rdo') SIZE 10M REUSE,
        GROUP 4 ('/u01/db/SOL1/rdo/SOL1_log4A.rdo',
                 '/oracle/redo/SOL1/SOL1_log4B.rdo') SIZE 10M REUSE,
        GROUP 5 ('/u01/db/SOL1/rdo/SOL1_log5A.rdo',
                 '/oracle/redo/SOL1/SOL1_log5B.rdo') SIZE 10M REUSE,
        GROUP 6 ('/u01/db/SOL1/rdo/SOL1_log6A.rdo',
                 '/oracle/redo/SOL1/SOL1_log6B.rdo') SIZE 10M REUSE;

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

ALTER ROLLBACK SEGMENT sys_rb2 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/SOL1/rbs/SOL1_rbs1.dbf' SIZE 220M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     2M 
                         NEXT        2M
                         MINEXTENTS  2
                         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_rb2 OFFLINE;

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

CREATE TABLESPACE temp
       DATAFILE '/u01/db/SOL1/tmp/SOL1_temp1.dbf' SIZE 150M 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/SOL1/usr/SOL1_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/SOL1/tab/SOL1_tab1.dbf' SIZE 200M 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/SOL1/cdr/SOL1_cdr1.dbf' SIZE 100M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     50M 
                         NEXT        50M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

### Create tablespace for CREDIT table
### ----------------------------------

CREATE TABLESPACE cre
        DATAFILE '/u01/db/SOL1/cre/SOL1_cre1.dbf' SIZE 50M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     50M 
                         NEXT        50M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        PERMANENT
        ONLINE;

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

CREATE TABLESPACE idx
        DATAFILE '/u01/db/SOL1/idx/SOL1_idx1.dbf' SIZE 150M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     64K 
                         NEXT        64K
                         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;

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catparr.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catrep.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/utllockt.sql
@$ORACLE_HOME/rdbms/admin/utlmontr.sql
@$ORACLE_HOME/rdbms/admin/dbmspipe.sql

### Load scripts for Oracle Enterprise Manager 
### ------------------------------------------
@smptsi73.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 ppb IDENTIFIED BY ppb
    DEFAULT TABLESPACE tab
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON cdr
    QUOTA UNLIMITED ON cre
    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 ppb;
    GRANT ALL PRIVILEGES TO ppb;

ALTER USER dbsnmp
TEMPORARY TABLESPACE temp;

CREATE ROLE PPB_PROCESS;
CREATE ROLE PPB_CLIENT;

GRANT CONNECT TO PPB_CLIENT;
GRANT CONNECT,RESOURCE TO PPB_PROCESS;
GRANT PPB_PROCESS TO ppb;

DISCONNECT;
CONNECT INTERNAL;

GRANT EXECUTE ON dbms_pipe TO ppb;
GRANT EXECUTE ON dbms_alert TO ppb;
GRANT SELECT ON V_$PARAMETER TO PPB_PROCESS;
GRANT SELECT ON V_$PARAMETER TO PPB_CLIENT;

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

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

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

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

DISCONNECT;
CONNECT system/manager;

### sql*plus product user profile
### -----------------------------

@$ORACLE_HOME/sqlplus/admin/pupbld.sql
SPOOL OFF;
EXIT;