| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DATABASE CREATION SCRIPT
> -----Original Message-----
> From: Harvinder Singh [mailto:Harvinder.Singh_at_MetraTech.com]
>
> 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...
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 *
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
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 *
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 *
initial 64K next 64K pctincrease 0
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;
REM * Alter SYS and SYSTEM users.
REM *
alter user sys temporary tablespace temp;
alter user system default tablespace system temporary tablespace temp;
spool off
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
echo "connect system/$system_password" > tmp.sql echo "start $ORACLE_HOME/sqlplus/admin/pupbld.sql" >> tmp.sql echo "exit" >> tmp.sql
 |
 |