Il s'agit d'un script en shell qui doit être lancé par l'usager de rôle "dba" une fois le SGBD Oracle installé sur la station. Ce script, qui inclut le script crbd7.sh (création d'une base en version 7, donné à la suite), crée les fichiers initTOGA.ora et config.ora qui contiennent l'ensemble des paramétres de la BD TOGA : la taille des blocs Oracle, les noms des fichiers de contrôle, les nombres de fichiers, de buffer, de process, de "rollback segments" utilisés dans la BD, ...
#! /bin/sh
########################################################
#
# CREATION DE LA BASE DE DONNEES TOGA SUR RECYF1
# C. Piriou, juillet 99
#
# ATTENTION: doit etre lance depuis le compte oracle!!!
#
########################################################
# creation des tablespaces de la base TOGA
# Variables a initialiser:
# ORACLE_SID : nom de travail de la base
# DATA0 : contiendra les TABLESPACES TEMP(20Mo) et USERS(10Mo)
# et le redolog 3 (500Ko)et le controlfile 3
# DATA1 : contiendra les TABLESPACES TOOL(15Mo) et RBS(8 Mo)
# et le redolog 1 (500Ko)et le controlfile 1
# DATA3 : contiendra le TABLESPACE SYST(40Mo)
# et le redolog 3 (500Ko)et le controlfile 2
# ADMIN : contiendra les fichiers d'administration de la base
#
########################################################
#
ORACLE_SID=TOGA ; export ORACLE_SID
DATA0=/users/oracle/oradata/$ORACLE_SID ; export DATA0
DATA1=/data1/oracle/oradata/$ORACLE_SID ; export DATA1
DATA3=/data3/oracle/oradata/$ORACLE_SID ; export DATA3
DATA6=/data6/oracle/oradata/$ORACLE_SID ; export DATA6
ADMIN=/data2/oracle/app/oracle/admin/$ORACLE_SID ; export ADMIN
#
#####################################################"
# lancement de la procedure de creation de la base
#
/data2/oracle/app/oracle/admin/crbd7.sh $ORACLE_SID $ORACLE_SID $DATA0 $DATA1 $DATA3 $ADMIN SMALL
#
# creation des repertoires supplementaires pour datafiles
mkdir $DATA6
##########################################
#
# creation du fichier de droits
#
cat <<EOF > $ADMIN/create/grant$ORACLE_SID.sql
connect system/manager
create role utilisateur identified by password;
grant connect to utilisateur;
rem
rem distribution des roles
rem
create user grafic
identified by grafic
default tablespace users
temporary tablespace temp
;
grant utilisateur to grafic
;
create user toga
identified by toga
default tablespace users
temporary tablespace temp
;
grant utilisateur to toga
;
create user piriouc
identified by piriouc
default tablespace users
temporary tablespace temp
;
grant utilisateur to piriouc
;
alter user toga quota unlimited on users;
alter user toga quota unlimited on temp;
alter user piriouc quota unlimited on users;
alter user piriouc quota unlimited on temp;
exit;
EOF
#####################################################"
# creation de la procedure sql crtablespace$ORACLE_SID.sql
# de creation des tablespaces et des tables de donnees
cat <<EOF > $ADMIN/create/crtablespace$ORACLE_SID.sql
connect system/manager
spool $ADMIN/logbook/3-rdbms.lst;
REM * The database should already be started up at this point with:
REM * pfile=$ADMIN/pfile/init${ORACLE_SID}.ora
REM *
rem
rem fichiers sur $DATA6
rem
create tablespace DATA_ALT
datafile '${DATA6}/${ORACLE_SID}alt_1.dbf' size 370M
default storage ( pctincrease 0);
create tablespace ind_alt
datafile '${DATA6}/${ORACLE_SID}ialt_1.dbf' size 120M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_ALT ;
alter user toga quota unlimited on ind_alt;
rem
create tablespace DATA_SURF
datafile '/data6/oracle/oradata/TOGA/TOGAsurf_1.dbf' size 150M
default storage ( pctincrease 0);
create tablespace ind_surf
datafile '/data6/oracle/oradata/TOGA/TOGAisurf_1.dbf' size 60M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_surf ;
alter user toga quota unlimited on ind_surf ;
rem
create tablespace DATA_FICH
datafile '/data6/oracle/oradata/TOGA/TOGAfich_1.dbf' size 20M
default storage ( pctincrease 0);
create tablespace IND_FICH
datafile '/data6/oracle/oradata/TOGA/TOGAifich_1.dbf' size 10M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_FICH ;
alter user toga quota unlimited on ind_fich ;
rem
create tablespace DATA_CEP
datafile '/data6/oracle/oradata/TOGA/TOGAcep_1.dbf' size 700M
default storage ( pctincrease 0);
create tablespace IND_CEP
datafile '/data6/oracle/oradata/TOGA/TOGAicep_1.dbf' size 150M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_CEP ;
alter user toga quota unlimited on IND_CEP ;
rem
create tablespace DATA_FLUX
datafile '/data6/oracle/oradata/TOGA/TOGAflux_1.dbf' size 160M
default storage ( pctincrease 0);
create tablespace IND_FLUX
datafile '/data6/oracle/oradata/TOGA/TOGAiflux_1.dbf' size 100M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_FLUX ;
alter user toga quota unlimited on IND_FLUX ;
rem
create tablespace DATA_RADAR
datafile '/data6/oracle/oradata/TOGA/TOGAradar_1.dbf' size 50M
default storage ( pctincrease 0);
create tablespace IND_RADAR
datafile '/data6/oracle/oradata/TOGA/TOGAiradar_1.dbf' size 10M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_RADAR ;
alter user toga quota unlimited on IND_RADAR ;
rem
create tablespace DATA_POI
datafile '/data6/oracle/oradata/TOGA/TOGApoi_1.dbf' size 1064M
default storage ( pctincrease 0);
create tablespace IND_POI
datafile '/data6/oracle/oradata/TOGA/TOGAipoi_1.dbf' size 500M
default storage ( pctincrease 0);
alter user toga quota unlimited on DATA_POI ;
alter user toga quota unlimited on IND_POI ;
rem
rem creation d'un tb temporaire, plus gros, pour les calculs a effectuer
rem create tablespace TEMP_TEMPO
rem datafile '/data6/oracle/oradata/TOGA/tempTOGA02.dbf' size 1000M
rem default storage (initial 10M next 10M pctincrease 0);
rem alter user toga quota unlimited on TEMP_TEMPO;
rem alter user toga temporary tablespace TEMP_TEMPO;
spool off;
exit;
EOF
##########################################
#
# lancement des script sql
svrmgrl < $ADMIN/create/crbd$ORACLE_SID.sql
echo base $ORACLE_SID creee
svrmgrl < $ADMIN/create/crbd2$ORACLE_SID.sql > $ADMIN/create/crbd2$ORACLE_SID.log
echo rollback segments et 1ers tablespaces crees
svrmgrl < $ADMIN/create/grant$ORACLE_SID.sql > $ADMIN/create/grant$ORACLE_SID.log
svrmgrl < $ADMIN/create/crtablespace$ORACLE_SID.sql > $ADMIN/create/crtablespace$ORACLE_SID.log
echo tablespaces crees
##########################################
#
# nettoyage des fichiers "trace" et "alert" par cron
if [ `grep -c "BD $ORACLE_SID" /data2/oracle/bin/nettoie.proc` -eq 0 ]
then
cat <<EOF >>/data2/oracle/bin/nettoie.proc
# nettoyage des fichiers "trace" et "alert" de la BD $ORACLE_SID
cd $ADMIN/bdump
for i in \`find . -mtime +30 -name '*.trc' \`
do
rm \$i
done
tail -n500 alert_$ORACLE_SID.log > tampon.log
mv tampon.log alert_$ORACLE_SID.log
EOF
fi
Le script crbd7.sh, appelé par le script précédent, est donné ici:
#! /bin/sh
########################################################
# creation d une base de donnees V7.3.2
# Variables a initialiser:
# ORACLE_SID : nom de travail de la base
# DB_NAME : nom interne de la base
# modif du 15 octobre 98: TEMP fait 5M au lieu de 550k
# modif du 20 juillet 99: TEMP fait 20M
# DATA0 : contiendra les TABLESPACES TEMP(550Ko) et USERS(10Mo)
# et le redolog 3 (500Ko)et le controlfile 3
# (pour FASTEX /users/oracle/oradata/$ORACLE_SID )
# DATA1 : contiendra les TABLESPACES TOOL(15Mo) et RBS(8 Mo)
# et le redolog 1 (500Ko)et le controlfile 1
# (pour FASTEX /data1/oracle/oradata/$ORACLE_SID )
# DATA2 : contiendra le TABLESPACE SYST(40Mo)
# et le redolog 2 (500Ko)et le controlfile 2
# (pour FASTEX /data2/oracle/oradata/$ORACLE_SID )
# ADMIN : contiendra les fichiers d'administration de la base
# (pour FASTEX /data2/oracle/app/oracle/admin/$ORACLE_SID
#
# DIM_BASE : dimension de la base (SMALL = 5500K)
# (MEDIUM = 8800K)
# (LARGE = 21000K)
#
########################################################
#
ORACLE_SID=$1 ; export ORACLE_SID
DB_NAME=$2 ; export DB_NAME
DATA0=$3 ; export DATA0
DATA1=$4 ; export DATA1
DATA2=$5 ; export DATA2
ADMIN=$6 ; export ADMIN
DIM_BASE=$7 ; export DIM_BASE
#
########################################################
#
# creation de l'arborescence
#
mkdir $ADMIN
mkdir $ADMIN/bdump
mkdir $ADMIN/cdump
mkdir $ADMIN/create
mkdir $ADMIN/logbook
mkdir $ADMIN/pfile
mkdir $ADMIN/udump
#
mkdir $DATA0
mkdir $DATA1
mkdir $DATA2
#
######################################
#
# creation du fichier config${ORACLE_SID}.ora
#
cat <<EOF >$ADMIN/pfile/config${ORACLE_SID}.ora
#
# $Header: cnfg.orc 1.1 95/02/27 12:14:25 wyim Osd<unix> $ Copyr (c) 1992 Oracle
#
# cnfg.ora - instance configuration parameters
control_files = (${DATA1}/control${ORACLE_SID}01.ctl,
${DATA2}/control${ORACLE_SID}02.ctl,
${DATA0}/control${ORACLE_SID}03.ctl)
# Below for possible future use...
#init_sql_files = (?/dbs/sql.bsq,
# ?/rdbms/admin/catalog.sql,
# ?/rdbms/admin/expvew.sql)
background_dump_dest = ${ADMIN}/bdump
core_dump_dest = ${ADMIN}/cdump
user_dump_dest = ${ADMIN}/udump
#log_archive_dest = ${ADMIN}/arch/arch.log
db_block_size = 4096
db_name = ${DB_NAME}
EOF
#
######################################
#
# creation du fichier init"$ORACLE_SID"_0.ora
#
case "$DIM_BASE" in
SMALL)
cat <<EOF >$ADMIN/pfile/init"$ORACLE_SID"_0.ora
#
# $Header: initx.orc 1.1 95/02/27 12:14:56 wyim Osd<unix> $ Copyr (c) 1992 Oracle
#
# include database configuration parameters
ifile = ${ADMIN}/pfile/config${ORACLE_SID}.ora
rollback_segments = ()
# tuning parameters
db_files = 20
db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGE
db_block_buffers = 200 # SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE
shared_pool_size = 3500000 # SMALL
# shared_pool_size = 6000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE
log_checkpoint_interval = 10000
processes = 50 # SMALL
# processes = 100 # MEDIUM
# processes = 200 # LARGE
dml_locks = 100 # SMALL
# dml_locks = 200 # MEDIUM
# dml_locks = 500 # LARGE
log_buffer = 8192 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE
sequence_cache_entries = 10 # SMALL
# sequence_cache_entries = 30 # MEDIUM
# sequence_cache_entries = 100 # LARGE
sequence_cache_hash_buckets = 10 # SMALL
# sequence_cache_hash_buckets = 23 # MEDIUM
# sequence_cache_hash_buckets = 89 # LARGE
# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
# log_archive_start = true # if you want automatic archiving
# compatible = 7.1.0.0
# global_names = TRUE
mts_dispatchers="ipc,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=${ORACLE_SID}
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=${ORACLE_SID}))"
nls_date_format=YYYYMMDDHH24MISS
EOF
;;
MEDIUM)
cat <<EOF >$ADMIN/pfile/init"$ORACLE_SID"_0.ora
#
# $Header: initx.orc 1.1 95/02/27 12:14:56 wyim Osd<unix> $ Copyr (c) 1992 Oracle
#
# include database configuration parameters
ifile = ${ADMIN}/pfile/config${ORACLE_SID}.ora
rollback_segments = ()
# tuning parameters
db_files = 20
# db_file_multiblock_read_count = 16 # MEDIUM
# db_block_buffers = 550 # MEDIUM
# shared_pool_size = 6000000 # MEDIUM
log_checkpoint_interval = 10000
processes = 100 # MEDIUM
dml_locks = 200 # MEDIUM
log_buffer = 32768 # MEDIUM
sequence_cache_entries = 30 # MEDIUM
sequence_cache_hash_buckets = 23 # MEDIUM
# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
# log_archive_start = true # if you want automatic archiving
# compatible = 7.1.0.0
# global_names = TRUE
mts_dispatchers="ipc,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=${ORACLE_SID}
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=${ORACLE_SID}))"
nls_date_format=YYYYMMDDHH24MISS
EOF
;;
LARGE)
cat <<EOF >$ADMIN/pfile/init"$ORACLE_SID"_0.ora
#
# $Header: initx.orc 1.1 95/02/27 12:14:56 wyim Osd<unix> $ Copyr (c) 1992 Oracle
#
# include database configuration parameters
ifile = ${ADMIN}/pfile/config${ORACLE_SID}.ora
rollback_segments = ()
# tuning parameters
db_files = 20
db_file_multiblock_read_count = 32 # LARGE
db_block_buffers = 3200 # LARGE
shared_pool_size = 9000000 # LARGE
log_checkpoint_interval = 10000
processes = 200 # LARGE
dml_locks = 500 # LARGE
log_buffer = 163840 # LARGE
sequence_cache_entries = 100 # LARGE
sequence_cache_hash_buckets = 89 # LARGE
# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
# log_archive_start = true # if you want automatic archiving
# compatible = 7.1.0.0
# global_names = TRUE
mts_dispatchers="ipc,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=${ORACLE_SID}
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=${ORACLE_SID}))"
nls_date_format=YYYYMMDDHH24MISS
EOF
;;
esac
#
######################################
#
# creation du fichier init${ORACLE_SID}.ora
sed -e's/= ()/= (r01,r02,r03,r04)/' $ADMIN/pfile/init"$ORACLE_SID"_0.ora \
>$ADMIN/pfile/init$ORACLE_SID.ora
#########################################
# creation d'un lien entre $ADMIN/pfile/init$ORACLE_SID.ora et
#$ORACLE_HOME/dbs/init$ORACLE_SID.ora pour utilisation de dbstart
# (montage des base au boot de la station)
ln $ADMIN/pfile/init$ORACLE_SID.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora
#
#######################################
#
# creation de la procedure sql crbd${ORACLE_SID}.sql
# de creation de la base
#
cat <<EOF > $ADMIN/create/crbd$ORACLE_SID.sql
REM * Set terminal output and command echoing on; log output of this script.
REM *
#set termout on
#set echo on
spool ${ADMIN}/crdb${ORACLE_SID}.lst
REM * Start the <sid> instance (ORACLE_SID here must be set to <sid>).
REM *
connect internal
startup nomount pfile=${ADMIN}/pfile/init${ORACLE_SID}_0.ora
REM * Create the <dbname> database.
REM * SYSTEM tablespace configuration guidelines:
REM * General-Purpose ORACLE RDBMS 5Mb
REM * Additional dictionary for applications 10-50Mb
REM * Redo Log File configuration guidelines:
REM * Use 3+ redo log files to relieve ``cannot allocate new log...'' waits.
REM * Use ~100Kb per redo log file per connection to reduce checkpoints.
REM *
create database "${ORACLE_SID}"
maxinstances 8
maxlogfiles 32
character set "US7ASCII"
datafile
'${DATA2}/sys${ORACLE_SID}01.dbf' size 40M
logfile
'${DATA1}/redo${ORACLE_SID}01.log' size 500k,
'${DATA2}/redo${ORACLE_SID}02.log' size 500k,
'${DATA0}/redo${ORACLE_SID}03.log' size 500k;
disconnect
spool off
EOF
#
#####################################################"
#
# creation de la procedure sql crbd2$ORACLE_SID.sql
# de creation des rollbacks segment et des tablespaces la base
cat <<EOF > $ADMIN/create/crbd2$ORACLE_SID.sql
REM * This script takes care off all commands necessary to create
REM * an OFA compliant database after the CREATE DATABASE command has
REM * succeeded.
REM * Set terminal output and command echoing on; log output of this script.
REM *
#set termout on
#set echo on
#spool 2-rdbms.lst
REM * The database should already be started up at this point with:
REM * pfile=${ADMIN}/pfile/init${ORACLE_SID}_0.ora
connect internal
REM # install data dictionary views:
@${ORACLE_HOME}/rdbms/admin/catalog.sql
REM * Create additional rollback segment in SYSTEM before creating tablespace.
REM *
connect internal
create rollback segment r0 tablespace system
storage (initial 16k next 16k minextents 2 maxextents 20);
REM * Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online without shutting
REM * down and restarting the database.
REM *
alter rollback segment r0 online;
REM * Create a tablespace for rollback segments.
REM * Rollback segment configuration guidelines:
REM * 1 rollback segments for every 4 concurrent xactions.
REM * No more than 50 rollback segments.
REM * All rollback segments the same size.
REM * Between 2 and 4 homogeneously-sized extents per rollback segment.
REM * Attempt to keep rollback segments to 4 extents.
REM *
create tablespace rbs datafile
'${DATA1}/rbs${ORACLE_SID}01.dbf' size 8M
default storage (
initial 128k
next 128k
pctincrease 0
minextents 2
);
REM * Create a tablespace for temporary segments.
REM * Temporary tablespace configuration guidelines:
REM * Initial and next extent sizes = k * SORT_AREA_SIZE, k in {1,2,3,...}.
REM *
REM ------------Modif pour la creation de la bd MAP: temp +grand ----------
REM create tablespace temp datafile
REM '${DATA0}/temp${ORACLE_SID}01.dbf' size 5M
REM default storage (
REM initial 256k
REM next 256k
REM pctincrease 0
REM );
create tablespace temp datafile
'${DATA0}/temp${ORACLE_SID}01.dbf' size 20M
default storage (
initial 256k
next 256k
pctincrease 0
);
REM * Create a tablespace for database tools.
REM *
create tablespace tools datafile
'${DATA1}/tools${ORACLE_SID}01.dbf' size 5M;
REM * Create a tablespace for miscellaneous database user activity.
REM *
create tablespace users datafile
'${DATA0}/users${ORACLE_SID}01.dbf' size 10M;
REM * Create rollback segments.
REM *
create rollback segment r01 tablespace rbs;
create rollback segment r02 tablespace rbs;
create rollback segment r03 tablespace rbs;
create rollback segment r04 tablespace rbs;
REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online
REM * without shutting down and restarting the database. Only put one
REM * of the rollback segments online at this time so that it will always
REM * be the one used. When the user shuts down the database and starts
REM * it up with initSID.ora, all four will be brought online.
REM *
alter rollback segment r01 online;
REM * alter rollback segment r02 online;
REM * alter rollback segment r03 online;
REM * alter rollback segment r04 online;
REM * Since we've created and brought online 2 more rollback segments,
REM * we no longer need the second rollback segment in the SYSTEM tablespace.
alter rollback segment r0 offline;
drop rollback segment r0;
REM * Alter SYS and SYSTEM users.
REM *
alter user sys temporary tablespace temp;
#revoke resource from system;
#revoke resource on system from system;
grant resource on tools to system;
alter user system default tablespace tools temporary tablespace temp;
REM * For each DBA user, run DBA synonyms SQL script. Don't forget that EACH
REM * DBA USER created in the future needs dba_syn.sql run from its account.
REM *
rem creation de la configuration procedurale
rem
@$ORACLE_HOME/rdbms/admin/catproc.sql
rem
connect system/manager
@${ORACLE_HOME}/rdbms/admin/catdbsyn.sql
rem
rem installation de la table product_user_profile
rem
@${ORACLE_HOME}/sqlplus/admin/pupbld.sql
spool off
EOF