Re: manual creation

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 14 May 2004 10:26:03 +1000
Message-ID: <40a4120e$0$23830$afc38c87@news.optusnet.com.au>

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

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

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

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 Received on Thu May 13 2004 - 19:26:03 CDT