Subject: Maxdatafiles and DB_FILES Parameters
Last Revision Date: 11 September 1994
_______________________________________________________________________________
MAXDATAFILES and DB_FILES Parameters
This article discusses the two parameters that determine the maximum number of
database files your database can have, Maxdatafiles and Db_files. This paper
discusses the differences between these parameters, and will give you the
tools to solve the ora-1118 error ("cannot add any more database files: limit
of XXX exceeded").
DEFINITIONS:
------------
In the syntax for CREATE DATABASE, you will find a parameter called
"MAXDATAFILES". The value that is entered for this parameter (or
some default if the parameter is left out) is stored in the CONTROL
FILE upon database creation. This is the "hard" limit on the number of
datafiles which can be associated with this particular database.
In the init.ora (init<SID>.ora on UNIX, <node>_<ora_sid>_init.ora on VMS),
there is a parameter "DB_FILES". This is the limit on the total number of
files associated with a particular INSTANCE of a database. Since this
parameter can be changed simply by editing the init.ora and shutting down
and restarting the database, it is considered a "soft" limit.
COMMON QUESTIONS:
-----------------
1 - Why is there a limit on MAXDATAFILES?
Each platform uses a port-specific number of bits to store the ORACLE
file numbers. Thus, MAXDATAFILES is limited by this number.
Typical values are: V6 V7
-------- --------
UNIX 62 1022
VMS 254 1022
DOS 254 NA
Notice that each value is 2^n - 2 for some n.
NOTE: Previous to V7.0.16 some unix ports may have a maxdatafile
limit lower than 1022.
2 - Why would one set MAXDATAFILES to anything less than the port-specific
maximum?
Increasing the value of MAXDATAFILES increases the size of the
CONTROL FILE.
3 - Why would one set DB_FILES to anything less than MAXDATAFILES?
Increasing the value of DB_FILES increases the size of the PGA, or
Program Global Area, which is allocated for every user process
connected to ORACLE.
4 - How can I determine my machine's maximum limit on MAXDATAFILES?
V6 - connect internal to SQLDBA.
select count(*) from x$kcffi;
V7 - Check your ORACLE Installation and User's Guide.
The index should point to a port-specific limit.
5 - How can I determine where my CONTROL FILE(s) are?
In SQLDBA, type: show parameter control_files;
If you have multiple control files, you may find that some of them may
be cut off in the output from show parameter. In this case,
(prior to 7.0.16), you must look in your init.ora for the parameter
"control_files" to be set. If you have an "ifile" in your init.ora,
then control_files may be set there.
On 7.0.16 and higher, you can query from V$CONTROLFILE;
SOLVING ORA-1118:
-----------------
If an ora-1118 error arises, this means that your database has hit the
MAXDATAFILES limit, it is not a problem with the DB_FILES parameter,
because if it was, an ORA-59: 'maximum number of DB_FILES exceeded'
would arise.
This error occurs because you have hit the hard limit for the number of data
files. The following are possible options to get around this problem:
1. Reduce the number of datafiles associated with each individual
tablespace. For example, if you have a tablespace with three 100 Meg
datafiles, recreate the tablespace with just one 300 Meg datafiles.
The easiest way to do this would be to export all of the objects out
of that tablespace. Drop the tablespace. Recreate it with just
one datafile, and then import the objects back in.
2. Recreate the database. Since the MAXDATAFILES parameter is specified
upon database creation, recreating the database allows you to increase
this parameter. NOTE: If the database is running 6.0.33.0 or earlier,
this is your only option to increase the MAXDATAFILES limit.
In addition to your operating system documentation, you may want to
reference the following bulletins:
V6 - Recreating a database on VMS - #100389.518
V6 - Recreating an existing database on Unix - #102543.611
O7 - Creating Database under Unix - #104538.187
O7 - Creating Database under VMS and Oracle7 - #104652.565
3. Increase the MAXDATAFILES parameter. If you are running v6.0.33.1 or
higher, it is possible for you to increase the MAXDATAFILES limit without
recreating the entire database. This is done by recreating the control
file only.
NOTE: Before V6.0.33.1 the create control file syntax is NOT SUPPORTED.
CREATING THE CONTROL FILE:
--------------------------
RDBMS V6:
If you are running v6.0.33.1 or higher, you can create the control file
following these steps. The details of the "CREATE CONTROLFILE" syntax can
be found at the end of this bulletin.
1. Get a full listing of all your database files, including path/filename.
Log into sqlplus as a dba and query the following:
a. For database files, select file_name from sys.dba_data_files;
b. For redo log files, select name from v$logfile;
2. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).
3. Take a full database backup.
4. Remove the current control files. It is essential to remove all control
files, otherwise, you will receive an error. In addition, you CANNOT
REUSE the control file, since the size of the control file will increase
when you increase MAXDATAFILES.
5. Create the controlfile within SQLDBA:
SQLDBA> startup nomount;
SQLDBA> connect internal
SQLDBA> <issue create controlfile command>
SQLDBA> alter database open noresetlogs;
If you receive a "Statement processed" message, then your database is
now back up and running with a higher datafile limit. It is recommended
to shutdown at this time and take a full backup.
RDBMS ORACLE7:
In all Oracle7 versions, you can create the control file. In addition, you
can get Oracle to create the script for you. To do this, perform the
following steps:
1. With the database mounted or open, issue the following commands:
SQLDBA> alter database backup controlfile to trace;
SQLDBA> exit
2. A trace file will have been generated in your 'user_dump_dest'.
User_dump_dest is an init.ora parameter, and can be found by
issuing:
SQLDBA> show parameter user_dump_dest
The easiest way to locate the correct trace is to look at its date. A
file will exist with the current date and time. The naming convention
for these files is operating system specific.
3. Once the file is located, search through the file for the word "CONTROL"
You should find:
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
ETC.ETC.
4. Copy this trace file to some location and rename it to end it ".sql",
for this example, it is called "recr_con.sql".
5. Edit the "recr_con.sql" deleting the trace header information. Then
increase the value that you find next to the word "MAXDATAFILES".
6. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).
7. Take a full database backup at this time.
8. Remove the current control files. It is essential to remove all control
files, otherwise, you will receive an error. In addition, you CANNOT
REUSE the control file, since the size of the control file will increase
when you increase MAXDATAFILES.
9. Create the controlfile within SQLDBA:
SQLDBA> connect internal
SQLDBA> @recr_con.sql
SQLDBA> alter database open noresetlogs;
If you receive a "Statement processed" message, then your database is
now back up and running with a higher datafile limit. It is recommended
to shutdown at this time and take a full backup.
CREATE CONTROLFILE SYNTAX:
--------------------------
The following is information on the create control file syntax, this
information is fully documented in the Oracle7 Sql Language Reference Manual.
NOTE: This command is ONLY SUPPORTED with Oracle RDBMS V6.0.33.1 and HIGHER.
The syntax of this command is similar to CREATE DATABASE.
The defaults for any missing clauses are the same as the
DATABASE defaults for CREATE DATABASE.
CREATE CONTROLFILE [REUSE]
DATABASE name
[LOGFILE filespec [, filespec] ...]
RESETLOGS | NORESETLOGS
[MAXLOGFILES integer]
[DATAFILE filespec [, filespec] ...]
[MAXDATAFILES integer]
[MAXINSTANCES integer]
[ARCHIVELOG | NOARCHIVELOG]
[SHARED | EXCLUSIVE]
Parameters:
REUSE: If present the control files may already exist. The
new control files will overwrite the existing files. If
this option is missing, the new control files must not
yet exist. As in CREATE DATABASE, the names of the
control files are determined by the init.ora parameter
control_files.
DATABASE: Must match the database names in the data and log
files.
LOGFILE: This clause lists all the online logs that will be
used for this database. If not specified the port
dependant defaults will be assumed. The interpretation
of the filespecs depends on the next parameter.
RESETLOGS: If this flag is present the current contents of
the online logs are ignored. The new control files will
contain flags requiring ALTER DATABASE OPEN RESETLOGS,
which initializes the logs. Media recovery may be
applied as needed before the open. Note that either
RESETLOGS or NORESETLOGS must be specified. It is safest
to choose RESETLOGS and follow it with normal media
recovery.
NORESETLOGS: If specified, the log files must be the current
online logs. They must not be restored backups, and all
log files must be listed. Their headers are read to
construct the control file entries. They are used for
recovery. If archiving is enabled all the online logs
must be archived, even if they were already archived.
The SIZE option in the filespecs, if present, will be
used to validate the size of the file named.
MAXLOGFILES: Same as for CREATE DATABASE. May be different
than the value in the original control file, but it may
not be smaller the maximum number of log files the
database ever contained - including ones that have been
dropped. Set it greater than or equal to the value used
at CREATE DATABASE time.
DATAFILE: To ensure proper behavior, all datafiles for the
database must be listed. It is possible to omit a non
system tablespace file only if media recovery is enabled
and you will not be doing an open reset logs on the first
open after the create controlfile. If the omitted
file(s) contain(s) active rollback segments, the open
will most likely fail, in which case the missing
datafile(s) must be found, and the controlfile recreated.
All datafiles listed must be accessible since they are
assumed to be online. They MAY be backup copies needing
recovery. Their headers are read to construct the
control file records. The SIZE option in the filespecs,
if present, is used to validate the size of the file
named. The reuse option is ignored. The next database
open validates that all the files are specified and that
the sizes match.
MAXDATAFILES: Same as for CREATE DATABASE. May be different
than the value in the original control file, but it may
not be smaller the maximum number of data files the
database ever contained - including ones that have been
dropped.
MAXINSTANCES: Same as for CREATE DATABASE. May be different
than the value in the original control file.
[NO]ARCHIVELOG: Same as for CREATE DATABASE. May be
different than the value in the original control file.
If you wish to archive logs, it is recommended that the
ARCHIVELOG option be used with CREATE CONTROLFILE even
though the option can later be enabled with an ALTER
DATABASE command. NOARCHIVELOG is the default.
SHARED: Same as for CREATE DATABASE.
EXCLUSIVE: Same as for CREATE DATABASE.
EXAMPLE:
--------
CREATE CONTROLFILE
DATABASE ORACLE
LOGFILE '/releases1/6036p/dbs/log2ORACLE.dbf',
'/releases1/6036p/dbs/log3ORACLE.dbf'
DATAFILE '/releases1/oracle/dbs/data_space.dbf',
'/releases1/6036p/dbs/usrORACLE.dbf'
MAXDATAFILES 121
NORESETLOGS;