Re: Database creation fails

Comment at the end....
"Alex Ivascu" <alex_at_ivascu.comNOSPAM> wrote in message news:HmWJ9.323429$QZ.48039_at_sccrnsc02...
> Shravana Kumar wrote:
>
> > Hello All,
> >
> > I am a newbie to the database administartion.
> >
> > we are using oracle 9.0.1 on solaris 2.8. I am trying to create a
> > database and ended up with the following error message. Any help is
> > appreciated.
> >
> > SQL> @/export/home/oracle/1.sql
> > Connected to an idle instance.
> > ORACLE instance started.
> >
> > Total System Global Area 42533080 bytes
> > Fixed Size 434392 bytes
> > Variable Size 37748736 bytes
> > Database Buffers 4194304 bytes
> > Redo Buffers 155648 bytes
> > CREATE DATABASE "SAMPLE"
> > *
> > ERROR at line 1:
> > ORA-30014: operation only supported in Automatic Undo Management mode
> >
> >
> > My 1.sql file contains the following lines.
> >
> > connect SYS/change_on_install as sysdba
> > spool $ORACLE_BASE/sample.log
> > startup force
> > pfile='/export/home/oracle/products/9.0.1/dbs/initsample.ora' nomount;
> > CREATE DATABASE "SAMPLE"
> > CONTROLFILE REUSE
> > DATAFILE '/export/home/oracle/sample/SAMPLE.system' SIZE 10M REUSE
> > UNDO TABLESPACE tbs_rollback1
> > DATAFILE '/export/home/oracle/sample/SAMPLE.tbs_rollback1' SIZE 10M
> > REUSE
> > DEFAULT TEMPORARY TABLESPACE tbs_temp
> > TEMPFILE '/export/home/oracle/sample/SAMPLE.tbs_temp' SIZE 10M REUSE
> > LOGFILE
> > GROUP 1 ('/export/home/oracle/sample/SAMPLE.redo111') SIZE 10M REUSE
> > ,GROUP 2 ('/export/home/oracle/sample/SAMPLE.redo121') SIZE 10M REUSE
> > ,GROUP 3 ('/export/home/oracle/sample/SAMPLE.redo131') SIZE 10M REUSE
> > ,GROUP 4 ('/export/home/oracle/sample/SAMPLE.redo141') SIZE 10M REUSE
> > ,GROUP 5 ('/export/home/oracle/sample/SAMPLE.redo151') SIZE 10M REUSE
> > ,GROUP 6 ('/export/home/oracle/sample/SAMPLE.redo161') SIZE 10M REUSE
> > ,GROUP 7 ('/export/home/oracle/sample/SAMPLE.redo171') SIZE 10M REUSE
> > ,GROUP 8 ('/export/home/oracle/sample/SAMPLE.redo181') SIZE 10M REUSE
> > CHARACTER SET WE8ISO8859P1
> > MAXDATAFILES 255
> > MAXLOGFILES 32
> > NOARCHIVELOG
> > MAXINSTANCES 4;
> >
> >
> > Best Regards,
> > ShravanaKumar.
> >
> >
> You need to set UNDO_MANAGEMENT = TRUE, in your init.
>

The setting is UNDO_MANAGEMENT=AUTO, actually.

And I suggest you make the size of your datafile for the system tablespace about 10 times bigger than it currently is, because otherwise the creation is going to fail, big time, even if you do set undo_management=AUTO.

SYSTEM needs to be around 100M or so.

It's also strongly advised to switch autoextend on for SYSTEM, as things go haywire if it ever runs out of space. Much, much bigger if you intend installing Java or creating lots of stored procedures etc.

Obviously, 10M for temp and undo are feeble, and I assume this is just a training database of some sort as a result. But if that's the case, you still need the 100M for SYSTEM, otherwise the thing just won't create in the first place.

Along the same lines: why on earth do you think you'll need 8 redo log groups?????! And why such weird names for them? Stick to numbers for the groups, letters for the member designator. It's easier to manage that way.

Unless you are proposing to knock up a RAC, MAXINSTANCES can safely be left at its default value of 1. Likewise, maxloghistory is only of relevance to a RAC. You don't need to specify NOARCHIVELOG, since that is the default anyway.

And you don't need any of your REUSE keywords, unless you are proposing to over-write existing files (ie, you are re-creating the database).

And since you have to run catalog.sql and catproc.sql immediately after creating the database if it's to be of any use to anyone, why don't you throw those two lines in now, while you're at it.

All you truly need is:

create database sample
maxdatafiles 500
maxlogmembers 5
datafile '/export/home/oracle/sample/SAMPLE.system' size 100M autoextend on next 10m
undo tablespace tbs_rollback1
datafile '/export/home/oracle/sample/SAMPLE.tbs_rollback1' size 50M default temporary tablespace tbs_temp
tempfile '/export/home/oracle/sample/SAMPLE.tbs_temp' size 10M logfile group 1 ('/export/home/oracle/sample/SAMPLE.redo1a') size 10M,

          group 2 ('/export/home/oracle/sample/SAMPLE.redo2a'') size 10M; @?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

Regards
HJR Received on Thu Dec 12 2002 - 01:56:41 CST