Manually Create a Database On Personal 9i Edition

[salamina]

This is my first encounter with Oracle, so I read some documentations and installed Oracle9i Personal Edition for Win98.
The installer created a database with SID=first.
Everything is ok (excepting that i have to start lsnrctl manually and I hat to make an PO8 account/password to use the built in PO8 user in Oracle Navigator - took me some time to figure this out).
But afterwards, I wanted more control and insisted to create manually another database with SID=second (I want to have both of them installed and after some practice move to a Linux installation).

I followed this steps :
In the directory C:\oracle\ora90\database where is initfirst.ora (init$ORACLE_SID.ora), I created initsecond.ora with the same structure but slightly modified to suit the "second" SID
It looks like this :

###########################################
# Cache and I/O
###########################################
db_block_size=4096
db_cache_size=12M
disk_asynch_io=FALSE

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=c:\oracle\admin\second\bdump
core_dump_dest=c:\oracle\admin\second\cdump
timed_statistics=TRUE
user_dump_dest=c:\oracle\admin\second\udump

###########################################
# Distributed, Replication and Snapshot
###########################################
db_domain=""
remote_login_passwordfile=EXCLUSIVE

###########################################
# File Configuration
###########################################
control_files=("c:\oracle\oradata\second\CONTROL01.CTL", "c:\oracle\oradata\second\CONTROL02.CTL", "c:\oracle\oradata\second\CONTROL03.CTL")

###########################################
# Miscellaneous
###########################################
compatible=9.0.0
db_name=second

###########################################
# Network Registration
###########################################
instance_name=second

###########################################
# Pools
###########################################
java_pool_size=33554432
shared_pool_size=50331648

###########################################
# Processes and Sessions
###########################################
processes=100

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=0

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
sort_area_size=524288

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS
#########################

I maked the appropiate directories C:\oracle\admin\second and C:\oracle\oradata\second (at my first try, the installing process breaked because I didn't make these ones )
then I created the script c:\create.sql :

CREATE DATABASE second
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE 'c:\oracle\oradata\second\system01.dbf' SIZE 100M REUSE
UNDO TABLESPACE undotbs DATAFILE 'c:\oracle\oradata\second\undotbs01.dbf'
SIZE 60M REUSE AUTOEXTEND ON NEXT 2420K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('c:\oracle\oradata\second\redo01.log') SIZE 40M,
GROUP 2 ('c:\oracle\oradata\second\redo02.log') SIZE 40M,
GROUP 3 ('c:\oracle\oradata\second\redo03.log') SIZE 40M

Then followed this steps:

1.C:\ sqlplus /nolog
2 SQL>connect sys/change_on_install as sysdba
#At this point the instance database start and mount automatically "first" SID, so I issue shutdown immediate to have oracle 9i instance started without a mounted database
3 SQL> shutdown immediate
4 SQL>startup nomount pfile='C:\oracle\ora90\database\initsecond.ora'
#(if I don't use pfile option an error arise because of "first")
5 SQL>get C:\create.sql
6 SQL>run
I get the following message :
Error at line 1:
ORA-01092 : the Oracle instance finished; forced disconnect

Oops not so good ! I started browsing the documentation and one idea come into my mind.
Maybe something related to ORACLE_SID environment variable.
So I try
1. C:\set ORACLE_SID=second
2. C:\ sqlplus /nolog
3. SQL>connect sys/change_on_install as sysdba
#The Oracle 9i Startup window appear and ask me for password ??? What password ?

I'm stucked at this point as I can't go further and I want both databases "first" and "second" created and I'd like to the "second" one manually.

Any help would be much appreciated.

Best regards

[julian]

The problem is here, in the init.ora you have:

undo_management=AUTO
undo_tablespace=UNDOTBS

But the tablespace does not exist yet.

Now, set UNDO_MANAGEMENT to MANUAL.

Startup the database and create a new UNDO tablespace UNDOTBS. If you want the instance to work under AUM set:

undo_tablespace=UNDOTBS and bounce.

Remark: 9i is not a database meant for using wizards and Oracle generated scripts, they don't always work. Get to know 9i, read about it first, then try out things.