Oracle db_files and maxdatafiles parameters
The db_files parameter is a "soft limit "
parameter that controls the maximum
number of physical OS files that can map to an Oracle instance.
Historically (before Oracle8i) you need to be careful not to set
db_files too high, else you would have DBWR (database writer)
The maxdatafiles parameter is a different
"hard limit" parameter.
When you issue a "create database" command, the value you
specify for maxdatafiles is stored in your Oracle control files.
The default value of 32 is usually sufficient, but after Oracle8i
there is no downside to using a larger value.
In practice, many Oracle DBA with large databases
will segregate important tables and indexes into isolated tablespaces
and datafiles to give them more control and detailed statistrics.
Fixing a maxdatafiles limit
In practice, the ORA-1118 occurs when your
database has hit the MAXDATAFILES limit, usually during database
maintenance. Here are instructions from "Rhubarb" Stewart
McGlaughlin, one of the best Oracle DBA's in North Carolina:
1. Shutdown database; Backup database
2. Start up database
3. From sqlplus as sysdba, type: alter database backup controlfile
4. Type: shutdown immediate:
5. Go to the operating system and go to the USER_DUMP_DEST directory
6. Find the newest trace file
7. Edit the trace file and change MAXDATAFILES to the new value. You
will also need to delete all of the lines prior to the line that
begins: STARTUP NOMOUNT. See sample text below (the example is for a
database using ARCHIVELOG):
MAXLOGFILES 32 MAXLOGMEMBERS 2
CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG
GROUP 1 'D:\ORAWIN95\DATABASE\LOG2ORCL.ORA' SIZE 200K,
GROUP 2 'D:\ORAWIN95\DATABASE\LOG1ORCL.ORA' SIZE 200K DATAFILE
# Database can now be opened normally. ALTER DATABASE OPEN
8. From sqlplus as sysdba, run the edited trace
file from step 7.
9. Shutdown database and backup database