REM Akadia AG, Zieglerstrasse 34, CH-3007 Bern 
REM ----------------------------------------------------------------------
REM
REM File:       initPAR1.ora
REM
REM Autor:      Martin Zahn / 10.07.1998
REM 
REM Purpose:    Create Database Script 
REM
REM Location:   $ORACLE_HOME\Database	
REM
REM Certified:  Oracle V.7.3.3 on Windows-NT
REM ---------------------------------------------------------------------

SPOOL D:\Orant\Database\initPARA.log

CONNECT INTERNAL/manager;

REM *************************************************************
REM *                                                           *
REM * Startup database                                          *
REM *                                                           *
REM *************************************************************

STARTUP NOMOUNT pfile=D:\Orant\Database\initPARA_init.ora

REM *************************************************************
REM *                                                           *
REM * Create database.                                          *
REM *                                                           *
REM *************************************************************

CREATE DATABASE        PARA
       CONTROLFILE     REUSE
       MAXDATAFILES    256
       MAXINSTANCES    4
       MAXLOGFILES     62
       MAXLOGMEMBERS   3
       MAXLOGHISTORY   1600
       CHARACTER SET   "WE8ISO8859P1"
       DATAFILE 'E:\Oradata\PARA\PARA_sys1.dbf' SIZE 100M REUSE
       AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
 LOGFILE GROUP 1 ('E:\Oradata\PARA\PARA_log1A.rdo',
                  'E:\Oradata\PARA\PARA_log1B.rdo') SIZE 1M REUSE,
         GROUP 2 ('E:\Oradata\PARA\PARA_log2A.rdo',
                  'E:\Oradata\PARA\PARA_log2B.rdo') SIZE 1M REUSE;

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

ALTER ROLLBACK SEGMENT sys_rb2 ONLINE;

REM *************************************************************
REM *                                                           *
REM * Create tablespace for rollback segments                   *
REM * (Do not set PCTINCREASE for rollback segment tablespace   *
REM *  nor for any rollback segment. Rollback segments always   *
REM *  have a PCTINCREASE value of 0.)
REM *                                                           *
REM *************************************************************

CREATE TABLESPACE rb
        DATAFILE 'E:\Oradata\PARA\PARA_rb1.dbf' SIZE 100M REUSE 
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     100K 
                         NEXT        200K
                         MINEXTENTS  5
                         MAXEXTENTS  100)
	    ONLINE;

CREATE ROLLBACK SEGMENT rb01 TABLESPACE rb
  STORAGE
       (INITIAL     1M
        NEXT        1M
        MINEXTENTS  2
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb01 ONLINE;

CREATE ROLLBACK SEGMENT rb02 TABLESPACE rb
  STORAGE
       (INITIAL     1M
        NEXT        1M
        MINEXTENTS  2
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb02 ONLINE;

CREATE ROLLBACK SEGMENT rb03 TABLESPACE rb
  STORAGE
       (INITIAL     1M
        NEXT        1M
        MINEXTENTS  2
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb03 ONLINE;

CREATE ROLLBACK SEGMENT rb04 TABLESPACE rb
  STORAGE
       (INITIAL     100K
        NEXT        200K
        MINEXTENTS  5
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb04 ONLINE;

CREATE ROLLBACK SEGMENT rb05 TABLESPACE rb
  STORAGE
       (INITIAL     1M
        NEXT        1M
        MINEXTENTS  2
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb05 ONLINE;

CREATE ROLLBACK SEGMENT rb06 TABLESPACE rb
  STORAGE
       (INITIAL     1M
        NEXT        1M
        MINEXTENTS  2
        MAXEXTENTS  100
        OPTIMAL     2M);

ALTER ROLLBACK SEGMENT rb06 ONLINE;

CREATE ROLLBACK SEGMENT rb_big TABLESPACE rb
  STORAGE
       (INITIAL     10M
        NEXT        10M
        MINEXTENTS  2
        MAXEXTENTS  20
        OPTIMAL     40M);

ALTER ROLLBACK SEGMENT rb_big ONLINE;
ALTER ROLLBACK SEGMENT rb_big OFFLINE;

REM ********************************************************************
REM * The rollback segments are to be set in the configuration file:   *
REM * rollback_segments     = (r01,r02,r03,r04,r05,r06)                *
REM * The second rollback segment in system can now be set offline.    *
REM ********************************************************************
 
ALTER ROLLBACK SEGMENT sys_rb2 OFFLINE;

REM ************************************************************
REM *                                                          *
REM * Create temporary tablespace                              *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE temp
        DATAFILE 'E:\Oradata\PARA\PARA_temp1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     2M 
                         NEXT        2M
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create users tablespace                                  *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE users
        DATAFILE 'E:\Oradata\PARA\PARA_users1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create Index tablespace                                  *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE indx
        DATAFILE 'E:\Oradata\PARA\PARA_indx1.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        ONLINE;

REM ************************************************************
REM *                                                          *
REM * Create Oracle Enterprise Manager tablespace              *
REM *                                                          *
REM ************************************************************

CREATE TABLESPACE oem
        DATAFILE 'E:\Oradata\PARA\PARA_oem1.dbf' SIZE 150M REUSE
        AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
        DEFAULT STORAGE (INITIAL     300K 
                         NEXT        500K
                         MINEXTENTS  1
                         MAXEXTENTS  UNLIMITED
                         PCTINCREASE 0)
        ONLINE;

REM *************************************************************
REM *                                                           *
REM * Set security for the administrative users.                *
REM *                                                           *
REM *************************************************************

ALTER USER sys
TEMPORARY TABLESPACE temp;

ALTER USER system
TEMPORARY TABLESPACE temp;

ALTER USER system
IDENTIFIED BY ...;

ALTER USER sys
IDENTIFIED BY ...;

REM *************************************************************
REM *                                                           *
REM * Load the system scripts.                                  *
REM * Load the scripts for procedural options (PL/SQL)          *
REM *                                                           *
REM *************************************************************

SET STOPONERROR OFF;

### scripts for data dictionary & server options 
###   - standard data dictionary views (CATALOG.SQL)
###   - parallel server option         (CATPARR.SQL)
###   - procedural option              (CATPROC.SQL)
###   - replication option             (CATREP.SQL)
###   - snapshots
###   - export views
### --------------------------------------------

@D:\ORANT\RDBMS73\ADMIN\CATALOG.SQL
@D:\ORANT\RDBMS73\ADMIN\CATPARR.SQL
@D:\ORANT\RDBMS73\ADMIN\CATPROC.SQL
@D:\ORANT\RDBMS73\ADMIN\CATREP.SQL

### Some additional scripts 
### -----------------------
### - create views of oracle locks          (CATBLOCK.SQL)
### - show locking sessions in tree fashion (UTLLOCKT.SQL)
### - Grant public access to all views used 
###   by the char-mode SQLDBA.              (UTLMONTR.SQL)
### - Allow sessions to pass information 
###   between them through pipes            (DBMSPIPE.SQL)

@D:\ORANT\RDBMS73\ADMIN\CATBLOCK.SQL
@D:\ORANT\RDBMS73\ADMIN\UTLLOCKT.SQL
@D:\ORANT\RDBMS73\ADMIN\UTLMONTR.SQL
@D:\ORANT\RDBMS73\ADMIN\DBMSPIPE.SQL

REM *************************************************************
REM *                                                           *
REM * Create ELAN roles and users                               *
REM *                                                           *
REM * Note: GRANT EXECUTE ON dbms_pipe must be granted to the   *
REM *       real sipp users, not through a role !               *
REM *                                                           *
REM *************************************************************

SET TERMOUT ON;
CONNECT system/manager;

CREATE USER elan IDENTIFIED BY elan DEFAULT TABLESPACE users
	TEMPORARY TABLESPACE TEMP;

CREATE USER sp_elan IDENTIFIED BY sp_elan DEFAULT TABLESPACE USERS
	TEMPORARY TABLESPACE TEMP;

REM ****************************************************************
REM * Oracle Enterprise Manager Repository Owner, must be the same *
REM * Username in Windows NT with "Log on as a batch job" access   *
REM ****************************************************************

CREATE USER mz IDENTIFIED BY mz DEFAULT TABLESPACE oem
	TEMPORARY TABLESPACE TEMP;

GRANT DBA,EXP_FULL_DATABASE,IMP_FULL_DATABASE,SNMPAGENT TO mz;

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

CONNECT system/manager;
@D:\ORANT\DBS\pupbld.sql

### Grant EXECUTE on dbms_pipe on real user, not through roles
### ----------------------------------------------------------

CONNECT internal/manager;
GRANT EXECUTE ON dbms_pipe TO mz;
GRANT EXECUTE ON dbms_pipe TO elan;

### Create Application Roles Projekt Elan
### -------------------------------------

CONNECT system/manager;

CREATE ROLE elan_clerk;
CREATE ROLE elan_admin;

GRANT CREATE SESSION TO elan_clerk;
GRANT CREATE SESSION TO elan_admin;

GRANT elan_admin TO elan;
GRANT elan_admin TO mz;
GRANT elan_admin TO sp_elan;

### Grant Privileges for SET AUTOTRACE in SQL*PLUS
### ----------------------------------------------

CONNECT INTERNAL/manager;
GRANT ALL ON sys.plan_table TO elan_admin;
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT SELECT ON V_$PARAMETER TO elan_admin;

@D:\ORANT\PLUS33\plustrce.sql
GRANT plustrace to elan_admin;

SPOOL OFF;
EXIT;