Re: Simple Database creation question

From: <sunk_at_focushope.edu>
Date: Tue, 10 Aug 1999 15:17:39 GMT
Message-ID: <7opfq6$epd$1@nnrp1.deja.com>

In article <5tEr3.19$q82.2358_at_newsr2.u-net.net>,   "Paul Dewey" <pauld_at_altyfc.u-net.com> wrote:
> Hi,
>
> Can anybody tell me which scripts and in which order I need to run
them to
> create an empty database.
>
> Thanks
>
> Paul
>
>

They are all right. But after you read your admin book, you might want to check out a tool called "Oracle installer/orainst" which will install the file system and with option to create a default db(if you take the default, it only takes about 15 mins to create a db and you will find 2 files "crdbsid.sql and crdb2sid.sql" under $ORACLE_ADMIN/create directory which lists all the steps the installer did). Once again it depends on your platform and you need read something about it before you can use it.

The following is an example of Oracle 7.3.3 on Sun SPARC Solaris 2.5.1 SID=sunk.

1.filename=crdbsunk.sql

---

REM * Set terminal output and command echoing on; log output of this script.
REM *
#set termout on
#set echo on
spool /u02/app/oracle/admin/sunk/create/crdbsunk.lst

REM * Start the <sid> instance (ORACLE_SID here must be set to <sid>). REM *
connect internal
startup nomount pfile=/u02/app/oracle/admin/sunk/pfile/initsunk_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 "sunk"

    maxinstances 8
    maxlogfiles 32
    character set "WE8ISO8859P1"
    datafile

        '/u02/app/oradata/oradata/sunk/system01.dbf' size 40M     logfile

	'/u02/app/oradata/oradata/sunk/redosunk01.log'	size 500k,
	'/u02/app/oradata/oradata/sunk/redosunk02.log'	size 500k,
	'/u02/app/oradata/oradata/sunk/redosunk03.log'	size 500k;

disconnect
spool off

---

2.filename=crdb2sunk.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=/u02/app/oracle/admin/sunk/pfile/initsunk_0.ora

connect internal

REM # install data dictionary views:
@/u02/app/oracle/product/7.3.3/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

        '/u02/app/oradata/oradata/sunk/rbs01.dbf' size 25M 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 *
create tablespace temp datafile

        '/u02/app/oradata/oradata/sunk/temp01.dbf' size 550k default storage (

	initial      256k
	next         256k
        pctincrease  0

);

REM * Create a tablespace for database tools. REM *
create tablespace tools datafile

        '/u02/app/oradata/oradata/sunk/tools01.dbf' size 15M;

REM * Create a tablespace for miscellaneous database user activity. REM *
create tablespace users datafile

        '/u02/app/oradata/oradata/sunk/users01.dbf' size 1M;

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 *
connect system/manager
@/u02/app/oracle/product/7.3.3/rdbms/admin/catdbsyn.sql

spool off

---

And next time you can modify those scripts to create your db on the fly.

Ps. You can find oraisnt on you oracle CD.

Good luck, Kurt.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 10 1999 - 10:17:39 CDT