REM Akadia AG, Arvenweg 4, CH-3604 Thun                           initTYP2.ora
REM --------------------------------------------------------------------------
REM File:        initTYP2.ora
REM
REM Autor:       Martin Zahn Akadia AG 09.05.2000
REM
REM Purpose:     Create database TYP2
REM
REM Location:    $ORACLE_HOME/dbs
REM
REM Certified:   Oracle 9.2.0 on SUN Solaris 8
REM
REM Notes:     - If you want to store LOBs in a tablespace, then DO NOT CREATE
REM              it using "SEGMENT SPACE MANAGEMENT AUTO" (non supported)
REM            - On Production Systems you should not set "AUTOEXTEND ON",
REM              this may fill up your file system until 100%.
REM --------------------------------------------------------------------------

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

CONNECT / AS SYSDBA;

REM Startup database
REM ----------------

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

REM Create database
REM ---------------

CREATE DATABASE TYP2
        CONTROLFILE     REUSE
        MAXDATAFILES    256
        MAXINSTANCES    4
        MAXLOGFILES     62
        MAXLOGMEMBERS   5
        MAXLOGHISTORY   1600
        CHARACTER SET   "WE8ISO8859P1"
        NATIONAL CHARACTER SET   "AL16UTF16"
        DATAFILE  '/u01/db/TYP2/sys/TYP2_sys1.dbf' SIZE 500M REUSE
          EXTENT MANAGEMENT LOCAL
        UNDO TABLESPACE undo DATAFILE '/u01/db/TYP2/sys/TYP2_undo1.dbf'
          SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
        DEFAULT TEMPORARY TABLESPACE tmp
          TEMPFILE '/u01/db/TYP2/tmp/TYP2_tmp1.dbf' SIZE 500M REUSE
          EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
LOGFILE GROUP 1 ('/u01/db/TYP2/rdo/TYP2_log1A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log1B.rdo') SIZE 150M REUSE,
        GROUP 2 ('/u01/db/TYP2/rdo/TYP2_log2A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log2B.rdo') SIZE 150M REUSE,
        GROUP 3 ('/u01/db/TYP2/rdo/TYP2_log3A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log3B.rdo') SIZE 150M REUSE,
        GROUP 4 ('/u01/db/TYP2/rdo/TYP2_log4A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log4B.rdo') SIZE 150M REUSE,
        GROUP 5 ('/u01/db/TYP2/rdo/TYP2_log5A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log5B.rdo') SIZE 150M REUSE,
        GROUP 6 ('/u01/db/TYP2/rdo/TYP2_log6A.rdo',
                 '/u02/db/TYP2/rdo/TYP2_log6B.rdo') SIZE 150M REUSE;

REM Create temporary tablespace
REM ---------------------------

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

REM Create users tablespace
REM -----------------------

CREATE TABLESPACE users
        LOGGING
        DATAFILE '/u01/db/TYP2/usr/TYP2_users1.dbf' SIZE 10M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        BLOCKSIZE 2K
        PERMANENT
        ONLINE;

REM Create tablespace for small objects
REM -----------------------------------

CREATE TABLESPACE tab
        LOGGING
        DATAFILE '/u01/db/TYP2/tab/TYP2_tab1.dbf' SIZE 800M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;

REM Create tablespace for small Indexes
REM -----------------------------------

CREATE TABLESPACE idx
        LOGGING
        DATAFILE '/u01/db/TYP2/idx/TYP2_idx1.dbf' SIZE 800M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;

REM Create tablespace for Oracle Options
REM ------------------------------------
REM
CREATE TABLESPACE drsys 
        LOGGING 
        DATAFILE '/u01/db/TYP2/sys/TYP2_drsys1.dbf' SIZE 20M REUSE
        AUTOEXTEND ON NEXT 640K
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;        
        
CREATE TABLESPACE odm 
        LOGGING 
        DATAFILE '/u01/db/TYP2/sys/TYP2_odm1.dbf' SIZE 20M REUSE
        AUTOEXTEND ON NEXT 640K
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;

CREATE TABLESPACE xdb 
        LOGGING 
        DATAFILE '/u01/db/TYP2/sys/TYP2_xdb1.dbf' SIZE 20M REUSE
        AUTOEXTEND ON NEXT 640K
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;

CREATE TABLESPACE tools 
        LOGGING 
        DATAFILE '/u01/db/TYP2/sys/TYP2_tools1.dbf' SIZE 10M REUSE
        AUTOEXTEND ON NEXT 640K
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE; 

CREATE TABLESPACE example 
        LOGGING 
        DATAFILE '/u01/db/TYP2/sys/TYP2_example1.dbf' SIZE 20M REUSE
        AUTOEXTEND ON NEXT 640K
        EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
        PERMANENT
        ONLINE;

REM Set security for the administrative users
REM -----------------------------------------

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

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

REM Load the system scripts
REM -----------------------

DISCONNECT;
CONNECT / AS SYSDBA;
SET ECHO OFF;
SET TERMOUT ON;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

REM Create Oracle Cryptographic ToolKit
@$ORACLE_HOME/rdbms/admin/catoctk.sql;

REM Create Oracle OWM
@$ORACLE_HOME/rdbms/admin/owminst.plb;

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

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

REM Create roles and users
REM ----------------------
REM GRANT EXECUTE ON dbms_pipe, dbms_alert must be granted
REM 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 0 ON system
    PROFILE default;
    GRANT CONNECT,RESOURCE TO scott;
    GRANT ALL PRIVILEGES TO scott;

ALTER USER dbsnmp
TEMPORARY TABLESPACE temp;

DISCONNECT;
CONNECT / as SYSDBA;

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

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

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;

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

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

DISCONNECT;
CONNECT system/manager;

REM SQL*PLUS product user profile
REM -----------------------------

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

REM Create Help Tables for SQL*Plus
REM -------------------------------

@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;

EXIT;
