> -----Original Message-----
> From: Harvinder Singh [mailto:[EMAIL PROTECTED]]
>
> We will appreciate if someone can send a script that
> can be used to creata a oracle database on unix .
> We need to give these script as batch file to developer so
> that they can create database.
> Oracle version is 8.1.7
> OS sun solaris 2.8
Here's a copy of a script for a small test database. Divided into three parts:
a) crdb1.sql - create database
b) crdb2.sql - run catalog, create tablespaces
c) crdb3.ksh - run catproc, pupbld, install SQL*Plus help
run other scripts as necessary, e.g. $ORACLE_HOME/rdbms/admin/utlxplan.sql for a plan_table, $ORACLE_HOME/sqlplus/admin/plustrce.sql to create the plustrace role allowing users access to autotrace, etc...
--------------------------------------------------------
crdb1.sql
--------------------------------------------------------
REM run this from inside sqlplus
REM * Set terminal output and command echoing on; log output of this script.
REM *
set termout on
set echo on
spool /oracle/admin/spcmgr_test/create/crdb_spcmgr_test.log
REM * Start the <sid> instance (ORACLE_SID here must be set to <sid>).
REM *
connect sys/change_on_install as sysdba
startup nomount pfile=/oracle/admin/spcmgr_test/pfile/initspcmgr_test_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 smgrtest
logfile
group 1 ('/data1/oradata/spcmgr_test/redo/redo_spcmgr_test01a.log',
'/data1/oradata/spcmgr_test/redo/redo_spcmgr_test01b.log') size 5M,
group 2 ('/data1/oradata/spcmgr_test/redo/redo_spcmgr_test02a.log',
'/data1/oradata/spcmgr_test/redo/redo_spcmgr_test02b.log') size 5M
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE '/data1/oradata/spcmgr_test/data/system01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1 ;
disconnect
spool off
--------------------------------------------------------
crdb2.sql
--------------------------------------------------------
REM run this from inside sqlplus
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
connect sys/change_on_install as sysdba
spool catalog.log
REM * The database should already be started up at this point with:
REM * pfile=/oracle/admin/spcmgr_test/pfile/initspcmgr_test_0.ora
REM # install data dictionary views:
@$ORACLE_HOME/rdbms/admin/catalog.sql
spool tablespaces.log
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/oradata/spcmgr_test/data/rbs01.dbf' size 60M
default storage (
initial 1M
next 1M
pctincrease 0
minextents 2
) ;
create tablespace data datafile
'/data1/oradata/spcmgr_test/data/data01.dbf' size 200M
default storage (
initial 128K
next 128K
maxextents unlimited
pctincrease 0
) ;
create tablespace indx datafile
'/data1/oradata/spcmgr_test/index/indx01.dbf' size 100M
default storage (
initial 128K
next 128K
maxextents unlimited
pctincrease 0
) ;
create tablespace users datafile
'/data1/oradata/spcmgr_test/data/users01.dbf' size 50M
default storage (
initial 128K
next 128K
maxextents unlimited
pctincrease 0
) ;
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 *
create tablespace temp datafile
'/data1/oradata/spcmgr_test/data/temp01.dbf' size 30M
default storage (
initial 64K
next 64K
pctincrease 0
) temporary;
REM * Create a tablespace for database tools.
REM *
create tablespace tools datafile
'/data1/oradata/spcmgr_test/data/tools01.dbf' size 30M;
REM * Create rollback segments.
REM *
create rollback segment rbs01 tablespace rbs
storage (maxextents 521 optimal 10M);
create rollback segment rbs02 tablespace rbs
storage (maxextents 521 optimal 10M);
create rollback segment rbs03 tablespace rbs
storage (maxextents 521 optimal 10M);
create rollback segment rbs04 tablespace rbs
storage (maxextents 521 optimal 10M);
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 rbs01 online;
alter rollback segment rbs02 online;
alter rollback segment rbs03 online;
alter rollback segment rbs04 online;
REM * Alter SYS and SYSTEM users.
REM *
alter user sys temporary tablespace temp;
alter user system default tablespace system temporary tablespace temp;
spool off
--------------------------------------------------------
crdb3.ksh
--------------------------------------------------------
# RUN THIS FROM operating system level
# set environment variable system_password first
echo "connect sys/change_on_install as sysdba" > tmp.sql
echo "spool catproc.log" >> tmp.sql
echo "@$ORACLE_HOME/rdbms/admin/catproc.sql" >> tmp.sql
echo "exit" >> tmp.sql
sqlplus /nolog <tmp.sql
echo "connect system/$system_password" > tmp.sql
echo "start $ORACLE_HOME/sqlplus/admin/pupbld.sql" >> tmp.sql
echo "exit" >> tmp.sql
sqlplus /nolog <tmp.sql
rm -f tmp.sql
export LOG=help_install.log
$ORACLE_HOME/bin/helpins system $system_password us
unset system_password
unset LOG