Re: How Do I Create Oracle Databases

From: Donna Parker <dsparker_at_worldnet.att.net>
Date: 1996/07/16
Message-ID: <31EB8160.5212_at_worldnet.att.net>#1/1


Urias McCullough wrote:
>
> My company is a software developer - We make an ODBC compliant Visual
> Basic application that currently handles several database platforms
> including Oracle.
>
> Unfortunately, we set up and tune our own servers for internal knowledge
> of the basic process. For oracle, however, we had a DBA do the initial
> install of Oracle 7 for Windows NT (because we couldn't get it working
> ourselves) - After about 3 days, it seemed that the DBA finally managed to
> figure it out himself with help from one of our employees (the DBA wasn't
> extremely knowledgable - like he was in training or something)
>
> They created one database successfully that we can log into, but since
> then we have not been able to create another database, we have run into
> all sorts of problems, mostly lack of permissions (even though we're
> logging in as an Admin)
>
> I assume it is required that we use SQL Plus to create this database, but
> the documentation doesn't seem to help us much, and my normal CREATE
> DATABASE knowledge doesn't seem to apply very accurately.
>
> I have worked extensively with Microsoft SQLServer, Sybase SQLServer,
> dBASE, IBM DB2/2, and Watcom 4.0, but Oracle is not like any of these.
>
> What is the normal procedure for creating a database under Oracle - short
> of hiring a DBA to do it for us?

You can do this with the instance manager or... To do this manually...

Copy an existing initora.ora file to initSID.ora where SID is the new database SID. Edit the new file and change the db_name to the new database name and the control_files to the new control file name (e.g. c:\orant\database\ctl1SID.ora where SID is the new SID).

To create the instance, from a command prompt type SET ORACLE_SID=NEWSID <CR>
oradim72 -NEW -SID NEWSID -INTPWD NEWPASSWORD -PFILE c:\ORANT\DATABASE\initSID.ora <CR>

Create an SQL script containing database preferences and run from SQL*DBA (below is a sample where SID is the new sid):

spool C:\ORANT\DATABASE\build.log
SET TERMOUT OFF
SET ECHO OFF
connect internal_at_2:SID
startup nomount pfile=C:\ORANT\DATABASE\initSID.ora create database SID

    controlfile reuse
    logfile 'C:\ORANT\DATABASE\log1SID.ora' size 200K reuse,

            'C:\ORANT\DATABASE\log2SID.ora' size 200K reuse     datafile 'C:\ORANT\DATABASE\sys1SID.ora' size 10M reuse autoextend on       next 10M maxsize 200M
    character set WE8ISO8859P1;
create rollback segment rb_temp;
create tablespace user_data

    datafile 'C:\ORANT\DATABASE\usr1SID.ora' size 25M reuse autoextend on       next 5M maxsize 150M;
create tablespace user_index

    datafile 'C:\ORANT\DATABASE\ndx1SID.ora' size 5M reuse autoextend on       next 5M maxsize 150M;
create tablespace temporary_data

    datafile 'C:\ORANT\DATABASE\tmp1SID.ora' size 2M reuse autoextend on       next 5M maxsize 150M;
create tablespace rollback

    datafile 'C:\ORANT\DATABASE\rbs1SID.ora' size 2M reuse autoextend on       next 5M maxsize 150M;
alter rollback segment rb_temp online;
alter user system temporary tablespace temporary_data; alter user system default tablespace user_data; create public rollback segment rbs1 storage(initial 100K next 100K)   tablespace rollback;
create public rollback segment rbs2 storage(initial 100K next 100K)   tablespace rollback;

_at_C:\ORANT\RDBMS72\admin\catalog.sql
_at_C:\ORANT\RDBMS72\admin\catalog6.sql
_at_C:\ORANT\RDBMS72\admin\catproc.sql

connect system/manager_at_2:SID
_at_C:\ORANT\RDBMS72\admin\catdbsyn.sql
_at_C:\ORANT\dbs\pupbld.sql
connect internal_at_2:SID
alter rollback segment rb_temp offline;
shutdown;
spool off

If you are going to be using network connections, don't forget to add the new instance to SQL*Net listener and tnsnames files. Received on Tue Jul 16 1996 - 00:00:00 CEST

Original text of this message