Max DB Files

Max DB Files

Post by B C Zygmu » Tue, 06 Aug 1996 04:00:00



I'm a DBA who's also filling in for another DBA on extended leave. On this
other system, I've run into a problem when trying to add a database file. The
max value was set to 30 (db_files = 30) in the init.ora file. I increased it to
40, shutdown and restarted the database. When I tried to add the datafile, I
got Oracle error 1118:

01118, 00000, "cannot add any more database files: limit of 30 exceeded"
// *Cause:  Obvious
// *Action:

If I look at v$parameter, I see that db_files is now set to 40. I know that,
if worse comes to worse, I can "collapse" some of the index files by dropping
and then rebuilding the indexes. But if I could add an index file it would be
easier and, anyway, I want to know why this isn't working. Does anyone have
any ideas? The database is running on a Sun SPARC 2 (Sun OS). It's version
7.0.15.6.

Thanks.

Beverly Zygmunt
Oak Ridge National Lab

 
 
 

Max DB Files

Post by Robert W. Swisshel » Tue, 06 Aug 1996 04:00:00



> I'm a DBA who's also filling in for another DBA on extended leave. On this
> other system, I've run into a problem when trying to add a database file. The
> max value was set to 30 (db_files = 30) in the init.ora file. I increased it to
> 40, shutdown and restarted the database. When I tried to add the datafile, I
> got Oracle error 1118:

> 01118, 00000, "cannot add any more database files: limit of 30 exceeded"
> // *Cause:  Obvious
> // *Action:

> Beverly Zygmunt
> Oak Ridge National Lab

There is a parameter on the CREATE DATABASE command that defines the
hard limit for the number of database files. It is the MAXDATAFILES
option.

As you have found out, this is the real maximum value, not DB_FILES.

To change it, you must recreate your control file. The process for doing
this is documented in chapter 6 of the Version 7.2 Database
Administrator's Guide.

The process is pretty easy, although it is scary the first time you do
it.  

Good Luck!
--
Bob Swisshelm
Eli Lilly and Company


 
 
 

Max DB Files

Post by James B. Reynold » Tue, 06 Aug 1996 04:00:00


Beverly,

You must create a new control file for the database using the CREATE
CONTROLFILE command. You can then change the permanent database
settings of which MAXDATAFILES is one.

Increasing the db_files value alone won't do it for you. Based on my
experiences, MAXDATAFILES defaults on installation to a value that is
quickly outgrown for most databases of reasonable size.

See your Oracle Server Administrator's Guide, probable Chapter 6, for
info on how to create a new control file.

Hope this helps.

Regards,

Jim Reynolds


writes:

Quote:

>I'm a DBA who's also filling in for another DBA on extended leave. On
this
>other system, I've run into a problem when trying to add a database
file. The
>max value was set to 30 (db_files = 30) in the init.ora file. I
increased it to
>40, shutdown and restarted the database. When I tried to add the
datafile, I
>got Oracle error 1118:

>01118, 00000, "cannot add any more database files: limit of 30
exceeded"
>// *Cause:  Obvious
>// *Action:

>If I look at v$parameter, I see that db_files is now set to 40. I know
that,
>if worse comes to worse, I can "collapse" some of the index files by
dropping
>and then rebuilding the indexes. But if I could add an index file it
would be
>easier and, anyway, I want to know why this isn't working. Does anyone
have
>any ideas? The database is running on a Sun SPARC 2 (Sun OS). It's
version
>7.0.15.6.

>Thanks.

>Beverly Zygmunt
>Oak Ridge National Lab

 
 
 

Max DB Files

Post by Bill A » Wed, 07 Aug 1996 04:00:00


You must recreate the control file to increase the maximum number of data
files allowed. When the database was created, a parameter called maxdatafiles
was specified. You cannot exceed the number in this parameter unless you
create a new control file. The steps to do this are in the Oracle7 server
administrators guide, Chapter 6 managing contolfiles. But be careful. Creating
a new control file can be very tricky and blow away your database if you dont
do it right.
Bill Alt. WLR Foods, Inc.

 
 
 

Max DB Files

Post by Jim Blaze » Thu, 08 Aug 1996 04:00:00



> I'm a DBA who's also filling in for another DBA on extended leave. On this
> other system, I've run into a problem when trying to add a database file. The
> max value was set to 30 (db_files = 30) in the init.ora file. I increased it to
> 40, shutdown and restarted the database. When I tried to add the datafile, I
> got Oracle error 1118:

> 01118, 00000, "cannot add any more database files: limit of 30 exceeded"
> // *Cause:  Obvious
> // *Action:

> If I look at v$parameter, I see that db_files is now set to 40. I know that,
> if worse comes to worse, I can "collapse" some of the index files by dropping
> and then rebuilding the indexes. But if I could add an index file it would be
> easier and, anyway, I want to know why this isn't working. Does anyone have
> any ideas? The database is running on a Sun SPARC 2 (Sun OS). It's version
> 7.0.15.6.

> Thanks.

> Beverly Zygmunt
> Oak Ridge National Lab

Here is a procedure that I have used to increace the amount of data file
used for an database. You can do steps 1, 4, and 5 before shutting down
the database to minimize the down time.

1) Run this command "alter database backup controlfile to trace;"
2) Shut down database
3) Move all controlfiles
4) Edit the trace file
        a) Remove all the lines down to the 'STARTUP NOMOUNT' line
        b) Change the value for MAXDATAFILES
        c) DO NOT edit the data file locations or size
5) Alter the init.ora file and increase the db_file parameter
6) Run SQLDBA and login
7) Run the edited trace file

=-=-=-=-=-=-=-=-=-=
Jim Blazek

Telephone Express

http://www.telexp.com