I am attempting to create a database manually with oracle 9i. I have
started the instance through the command prompt and opened it. I did a
startup nomount and the database mounted. I am at the point now where I
have to create the actual database I am using windows xp pro and these
commands

CREATE DATABASE
MAXLOGFILES 255
MAXINSTANCES 10
MAXDATAFILES 256
MAXLOGHISTORY 256
DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;

It attempts to create but I get an error which says " ORA-01092: ORACLE
instance terminated. Disconnection forced. "
I can not figure out what's going on. I checked my all my parameter
files and paths and everything seems to be all right so could someone
please give advice here

Thanks in advanced

D.j.

First off, you seem to be missing the database name in your create
statement. Please check the log file for any other errors (specified by the
background_dump_dest).

Alex Ivascu

"d.j." <dec2g@hotmail.com> wrote in message
news:uaSoc.87$H7.3026@eagle.america.net...
> I am attempting to create a database manually with oracle 9i. I have
> started the instance through the command prompt and opened it. I did a
> startup nomount and the database mounted. I am at the point now where I
> have to create the actual database I am using windows xp pro and these
> commands
>
> CREATE DATABASE
> MAXLOGFILES 255
> MAXINSTANCES 10
> MAXDATAFILES 256
> MAXLOGHISTORY 256
> DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
> UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
> SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
> CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
> LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
> GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
> GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>
> It attempts to create but I get an error which says " ORA-01092: ORACLE
> instance terminated. Disconnection forced. "
> I can not figure out what's going on. I checked my all my parameter
> files and paths and everything seems to be all right so could someone
> please give advice here
>
> Thanks in advanced
>
> D.j.
>

database name is dec2g

C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>>UNDO TABLESPACE "UNDOTBS" DATAFILE
'c:\oracle\oradata\dec2g\undotbs01.dbf'

Thanks

D.j.
Alex Ivascu wrote:

> First off, you seem to be missing the database name in your create
> statement. Please check the log file for any other errors (specified by the
> background_dump_dest).
>
> Alex Ivascu
>
> "d.j." <dec2g@hotmail.com> wrote in message
> news:uaSoc.87$H7.3026@eagle.america.net...
>
>>I am attempting to create a database manually with oracle 9i. I have
>>started the instance through the command prompt and opened it. I did a
>>startup nomount and the database mounted. I am at the point now where I
>>have to create the actual database I am using windows xp pro and these
>>commands
>>
>>CREATE DATABASE
>>MAXLOGFILES 255
>>MAXINSTANCES 10
>>MAXDATAFILES 256
>>MAXLOGHISTORY 256
>>DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>>UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>>SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
>>CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
>>LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
>>GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
>>GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>>
>>It attempts to create but I get an error which says " ORA-01092: ORACLE
>>instance terminated. Disconnection forced. "
>>I can not figure out what's going on. I checked my all my parameter
>>files and paths and everything seems to be all right so could someone
>>please give advice here
>>
>>Thanks in advanced
>>
>>D.j.
>>
>
>
>

BACKGROUND_DUMP_DEST=C:\Oracle\admin\dec2g\bdump
CORE_DUMP_DEST=C:\Oracle\admin\dec2g\cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=C:\Oracle\admin\dec2g\udump


d.j.



d.j. wrote:

> database name is dec2g
>
> C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
> >>UNDO TABLESPACE "UNDOTBS" DATAFILE
> 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>
> Thanks
>
> D.j.
> Alex Ivascu wrote:
>
>> First off, you seem to be missing the database name in your create
>> statement. Please check the log file for any other errors (specified
>> by the
>> background_dump_dest).
>>
>> Alex Ivascu
>>
>> "d.j." <dec2g@hotmail.com> wrote in message
>> news:uaSoc.87$H7.3026@eagle.america.net...
>>
>>> I am attempting to create a database manually with oracle 9i. I have
>>> started the instance through the command prompt and opened it. I did a
>>> startup nomount and the database mounted. I am at the point now where I
>>> have to create the actual database I am using windows xp pro and these
>>> commands
>>>
>>> CREATE DATABASE
>>> MAXLOGFILES 255
>>> MAXINSTANCES 10
>>> MAXDATAFILES 256
>>> MAXLOGHISTORY 256
>>> DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>>> UNDO TABLESPACE "UNDOTBS" DATAFILE
>>> 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>>> SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
>>> CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
>>> LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M
>>> REUSE,
>>> GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
>>> GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>>>
>>> It attempts to create but I get an error which says " ORA-01092: ORACLE
>>> instance terminated. Disconnection forced. "
>>> I can not figure out what's going on. I checked my all my parameter
>>> files and paths and everything seems to be all right so could someone
>>> please give advice here
>>>
>>> Thanks in advanced
>>>
>>> D.j.
>>>
>>
>>
>>
>

d.j. wrote:

> I am attempting to create a database manually with oracle 9i. I have
> started the instance through the command prompt and opened it. I did a
> startup nomount and the database mounted. I am at the point now where I
> have to create the actual database I am using windows xp pro and these
> commands
>
> CREATE DATABASE
> MAXLOGFILES 255
> MAXINSTANCES 10
> MAXDATAFILES 256
> MAXLOGHISTORY 256
> DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
> UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
> SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
> CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
> LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
> GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
> GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>
> It attempts to create but I get an error which says " ORA-01092: ORACLE
> instance terminated. Disconnection forced. "


It means your init.ora doesn't reference the correct UNDO tablespace.
Set UNDO_TABLESPACE=UNDOTBS.

You know how I ever learnt that the ORA-01092 meant that my undo
tablespace was not named/referenced correctly? Because when you get an
error like this, you should always check your alert log. And in the
alert log, there will be a message along the lines of 'can't find the
undo tablespace referenced. Terminating instance'.

The alert log is in the BACKGROUND_DUMP_DEST directory.

Regards
HJR

On Thu, 13 May 2004 17:55:41 -0400, "d.j." <dec2g@hotmail.com> wrote:

>I am attempting to create a database manually with oracle 9i. I have
>started the instance through the command prompt and opened it. I did a
>startup nomount and the database mounted. I am at the point now where I
>have to create the actual database I am using windows xp pro and these
>commands
>
>CREATE DATABASE
>MAXLOGFILES 255
>MAXINSTANCES 10
>MAXDATAFILES 256
>MAXLOGHISTORY 256
>DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
>CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
>LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
>GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
>GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>
>It attempts to create but I get an error which says " ORA-01092: ORACLE
>instance terminated. Disconnection forced. "
>I can not figure out what's going on. I checked my all my parameter
>files and paths and everything seems to be all right so could someone
>please give advice here
>
>Thanks in advanced
>
>D.j.

You did create a service using oradim prior to your startup nomount,
did you ?

Sounds like you didn't

oradim -? in a dos box will show you all the options there are

If you did create a service it should show as 'OracleService<SID>' as
'Started' in the administrative tools section services applet of
Control Panel.

Character set US7ASCII is a bad idea. It should be MSWIN1252 on
Winblows.


--
Sybrand Bakker, Senior Oracle DBA

Ok
1)I tried to put the db name in the create statement and it did not work
would not even open the create file.
2)When I changed the character set to mswin1252 Error " CREATE DATABASE
character set is not know" I changed both character sets US7ASCII &
AL16UTF16 to mswin1252 was that correct?
3)Bdump has not been created do I need to go in and create one manually.

D.j.


Sybrand Bakker wrote:

> On Thu, 13 May 2004 17:55:41 -0400, "d.j." <dec2g@hotmail.com> wrote:
>
>
>>I am attempting to create a database manually with oracle 9i. I have
>>started the instance through the command prompt and opened it. I did a
>>startup nomount and the database mounted. I am at the point now where I
>>have to create the actual database I am using windows xp pro and these
>>commands
>>
>>CREATE DATABASE
>>MAXLOGFILES 255
>>MAXINSTANCES 10
>>MAXDATAFILES 256
>>MAXLOGHISTORY 256
>>DATAFILE 'C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>>UNDO TABLESPACE "UNDOTBS" DATAFILE 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>>SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
>>CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16
>>LOGFILE GROUP 1 ('c:\oracle\oradata\dec2g\redo1_01.dbf') SIZE 100M REUSE,
>>GROUP 2 ('c:\oracle\oradata\dec2g\redo1_02.dbf') SIZE 100M REUSE,
>>GROUP 3 ('c:\oracle\oradata\dec2g\redo1_03.dbf') SIZE 100M REUSE;
>>
>>It attempts to create but I get an error which says " ORA-01092: ORACLE
>>instance terminated. Disconnection forced. "
>>I can not figure out what's going on. I checked my all my parameter
>>files and paths and everything seems to be all right so could someone
>>please give advice here
>>
>>Thanks in advanced
>>
>>D.j.
>
>
> You did create a service using oradim prior to your startup nomount,
> did you ?
>
> Sounds like you didn't
>
> oradim -? in a dos box will show you all the options there are
>
> If you did create a service it should show as 'OracleService<SID>' as
> 'Started' in the administrative tools section services applet of
> Control Panel.
>
> Character set US7ASCII is a bad idea. It should be MSWIN1252 on
> Winblows.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

d.j. wrote:

> database name is dec2g
>
> C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
> >>UNDO TABLESPACE "UNDOTBS" DATAFILE
> 'c:\oracle\oradata\dec2g\undotbs01.dbf'

Just how in blazes is the SQL parser supposed to "know"
the actual name of the instance when you do NOT provide it?

The SQL parser to not know OFA from a hole on the ground!

d.j. wrote:

> Ok
> 1)I tried to put the db name in the create statement and it did not work
> would not even open the create file.


> 2)When I changed the character set to mswin1252 Error " CREATE DATABASE
> character set is not know" I changed both character sets US7ASCII &
> AL16UTF16 to mswin1252 was that correct?

> 3)Bdump has not been created do I need to go in and create one manually.

Hang on. Before you can create a database manually, you must first
create an init.ora. When you are creating your init.ora, you must set
control_files, and things like UDUMP and BDUMP. It only makes sense to
reference directories which actually exist, otherwise you can expect
fireworks.

Just for you, here's a quick and dirty manual database creation on 9i
Release 2, Windows 2000 (but it's exactly the same as on XP). And I get
the same error as you:

C:\>mkdir newdb
C:\>cd newdb
C:\newdb>mkdir udump
C:\newdb>mkdir bdump
C:\newdb>mkdir admin
C:\newdb>cd admin

C:\newdb\admin>copy con initnewdb.ora
control_files=c:\newdb\control01.dbf
db_block_size=8192
db_cache_size=16000000
shared_pool_size=48000000
log_buffer=1000000
db_name=newdb
remote_login_passwordfile=none
user_dump_dest=c:\newdb\udump
background_dump_dest=c:\newdb\bdump
undo_tablespace=undotbs
undo_management=auto
^Z
1 file(s) copied.

C:\newdb\admin>oradim -NEW -SID newdb -PFILE
c:\newdb\admin\initnewdb.ora -STARTMODE auto

C:\newdb\admin>set ORACLE_SID=newdb

C:\newdb\admin>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 14 10:08:55 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=c:\newdb\admin\initnewdb.ora
ORACLE instance started.

Total System Global Area 106388200 bytes
Fixed Size 453352 bytes
Variable Size 88080384 bytes
Database Buffers 16777216 bytes
Redo Buffers 1077248 bytes

SQL> create database newdb
2 maxdatafiles 256
3 maxloghistory 1024
4 datafile 'c:\newdb\system01.dbf' size 150m autoextend on next 50m
5 undo tablespace undotbs datafile 'c:\newdb\undotbs01.dbf' size 50m
6 logfile 'c:\newdb\log1a.rdo' size 10m,'c:\newdb\log2a.rdo' size 10m;
create database newdb
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


But at this point I do the thing which you didn't appear to do, which is
check the alert log:

Fri May 14 10:14:38 2004
CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'c:\newdb\undotbs01.dbf' size 50m

Fri May 14 10:14:44 2004
ORA-406 signalled during: CREATE UNDO TABLESPACE UNDOTBS DATAFILE
'c:\newdb...
Fri May 14 10:14:44 2004
Errors in file c:\newdb\udump\newdb_ora_1620.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00406: COMPATIBLE parameter needs to be 9.0.0.0.0 or greater


So, I was wrong. I suggested it was because the undo tablespace name was
wrong or wasn't set, but it's worse than that: automatic undo management
is a uniquely 9i feature, and compatible is 8.1.0 by default, so it
needs setting explicitly in the init.ora. With that in mind, I edit my
init.ora so that it reads:

control_files=c:\newdb\control01.dbf
db_block_size=8192
db_cache_size=16000000
shared_pool_size=48000000
log_buffer=1000000
db_name=newdb
remote_login_passwordfile=none
user_dump_dest=c:\newdb\udump
background_dump_dest=c:\newdb\bdump
undo_tablespace=undotbs
undo_management=auto
compatible=9.2.0

And then:

SQL> startup nomount pfile=c:\newdb\admin\initnewdb.ora
ORACLE instance started.

Total System Global Area 106388200 bytes
Fixed Size 453352 bytes
Variable Size 88080384 bytes
Database Buffers 16777216 bytes
Redo Buffers 1077248 bytes
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\newdb\admin>cd ..

C:\newdb>del *.*
C:\newdb\*.*, Are you sure (Y/N)? y
C:\newdb> exit

SQL> create database newdb
2 maxdatafiles 256
3 maxloghistory 1024
4 datafile 'c:\newdb\system01.dbf' size 150m autoextend on next 50m
5 undo tablespace undotbs datafile 'c:\newdb\undotbs01.dbf' size 50m
6 logfile 'c:\newdb\log1a.rdo' size 10m,'c:\newdb\log2a.rdo' size 10m;

Database created.

I would then finish off like so:

SQL> create spfile from pfile='c:\newdb\admin\initnewdb.ora';
File created.

SQL> create temporary tablespace temp
2 tempfile 'c:\newdb\temp01.dbf' size 40m;

Tablespace created.

And then

SQL> @?\rdbms\admin\catalog
SQL> @?\rdbms\admin\catproc

And after all that, I have to say: why don't you just use DBCA??!

Just in case we have people who'd like to criticise the create database
statement I issued (it could have had the create temporary tablespace
business in it, for example, instead of doing it after the event): KISS.

Regards
HJR

Anna C. Dent wrote:
> d.j. wrote:
>
>> database name is dec2g
>>
>> C:\oracle\oradata\dec2g\system_01.dbf' SIZE 400M REUSE
>> >>UNDO TABLESPACE "UNDOTBS" DATAFILE
>> 'c:\oracle\oradata\dec2g\undotbs01.dbf'
>
>
> Just how in blazes is the SQL parser supposed to "know"
> the actual name of the instance when you do NOT provide it?

This I don't understand. It knows the name of the INSTANCE because the
ORACLE_SID environment variable tells it.

But what I think you meant, chiming in with Sybrand, is: how is 'create
database' supposed to work if you don't supply the database name?

To which the answer is: because the db_name parameter in the init.ora
tells it.

So that this, for example, works fine:


C:\newdb2\admin>set ORACLE_SID=newdb2

C:\newdb2\admin>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 14 10:35:37 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=c:\newdb2\admin\initnewdb2.ora
ORACLE instance started.

Total System Global Area 89610904 bytes
Fixed Size 453272 bytes
Variable Size 71303168 bytes
Database Buffers 16777216 bytes
Redo Buffers 1077248 bytes
SQL> create database
2 maxdatafiles 256
3 maxloghistory 1024
4 datafile 'c:\newdb2\system01.dbf' size 150m autoextend on next 50m
5 undo tablespace undotbs datafile 'c:\newdb2\undotbs01.dbf' size 50m
6 logfile 'c:\newdb2\log1a.rdo' size 10m,'c:\newdb2\log2a.rdo' size 10m;

Database created.

SQL> select name from v$database;

NAME
---------
NEWDB2

And you might note that my database has magically acquired a database
name despite the 'create database' statement not actually supplying one.

Regards
HJR