CONTENTS
--------
1. What are the maximun number of datafiles ? MAXDATAFILES
2. What are the maximun number of redolog groups ? MAXLOGFILES
3. What are the maximun number of members for a redolog group ? MAXLOGMEMBERS
4. What are the maximun number of instances that can access a database concurrently? MAXINSTANCES
5. What are the maximun number of redo log files that can be recorded in the log history of the control file? MAXLOGHISTORY
1. MAXDATAFILE
Maxdatafiles can be defined when the database is created or when the controlfile is recreated. The default valueis 32, the maximun value is 4096 per tablespace
In 8.0 and 8i, the limit on datafiles per database is around 40,000. This limit is imposed by the limit on control file size (20,000 blocks) with a 4K block size.
In 9i and 10g, larger block sizes are supported, thus with DB_BLOCK_SIZE=8192 there can be up to 65,534 datafiles (same limit as other platforms).
However, there is an issue with the maximum number of files the server can have open simultaneously. In both the OSDI and MPM implementations the server is limited to approximately 3000 distinct simultaneously open files across all sessions. This limit is removed
in Oracle9i 9.2.
说到这个MAXDATAFIEL我们不得不说另一个参数db_files,那么这个maxdatafile和这个db_files之间有什么数量上的关系呢？
db_files:
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
200 |
|
Modifiable |
No |
|
Range of values |
Minimum: the largest among the absolute file numbers of the datafiles in the database Maximum: operating system-dependent |
|
Basic |
No |
|
Real Application Clusters |
Multiple instances must have the same value. |
DB_FILES specifies the maximum number of database files that can be opened for this database. The maximum valid value is the maximum number of files, subject to operating system constraint(受制于操作系统的约束), that will ever be specified
for the database, including files to be added by ADD DATAFILE statements.
If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database, then they should have
the same value for this parameter.
maxdatafiles：
这个参数是保存在控制文件里的，在DBCA创建实例的时候可以指定该值的大小。 官网对这个参数的说明如下：
MAXDATAFILES The MAXDATAFILES option of CREATE DATABASE determines the number of data files a database can have. With Oracle Real Application Clusters, databases tend to have more data files and log files than an exclusive mounted database. 一般db_files <= maxdatafiles值。 当select count(*) from dba_data_files; 的值达到db_files时，就需要修改db_files，把这个值调大。 对这个参数的默认值，从我dump 出的trace 文件看，是30. 这个值明显过小。 稍大一点的系统也不止30个datafile。 不过dbca来看，该值是100. SQL>alter database backup controlfile to trace CREATE CONTROLFILE REUSE DATABASE "DAVE" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 1 MAXLOGHISTORY 292 在DBCA创建Instance的时候，我们可以指定该参数。 如下图：
如果说是已经建好了，就只能重建控制文件来修改该参数值。先将控制文件dump 出来，然后修改该值，在重建控制文件。 在重建之前，记得备份控制文件和DB。 具体操作步骤参考：
Oracle 控制文件
http://blog.csdn.net/tianlesoftware/archive/2009/12/09/4974440.aspx
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) issues.
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 problem
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 to trace;
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):
STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1600
LOGFILE
GROUP 1 'D:\ORAWIN95\DATABASE\LOG2ORCL.ORA' SIZE 200K,
GROUP 2 'D:\ORAWIN95\DATABASE\LOG1ORCL.ORA' SIZE 200K DATAFILE 'D:\ORAWIN95\DATABASE\SYS1ORCL.ORA', 'D:\ORAWIN95\DATABASE\USR1ORCL.ORA', 'D:\ORAWIN95\DATABASE\RBS1ORCL.ORA', 'D:\ORAWIN95\DATABASE\TMP1ORCL.ORA' ;
# Database can now be opened normally. ALTER DATABASE OPEN RESETLOGS;
8. From sqlplus as sysdba, run the edited trace file from step 7.
9. Shutdown database and backup database
关于maxdatafiles、db_files和数据库最大datafiles数的关系：
DB_FILES 参数指定数据库能打开的最大的datafiles 的数量，这个最大值也会受操作系统的限制。
--如果我们增加DB_FILES 的值，那么必须重启instance，才能让修改生效。 如果是DG 环境，也需要保证主备库参数一致。
在Oracle 11gR2 中，这个参数的默认值是200，比控制文件的默认值大。
--从Oracle 8 开始，控制文件是自动扩展的，当新添加的datafile时，datafile的个数超过了控制空maxdatafiles 参数的限制时，maxdatafiles参数值会自动的增加。
--这个验证可以通过dump 控制文件来查看
This alsohappens if new records are added to the "log_history" section of thecontrol files and there are no entries that could be replaced.
--控制文件的自动扩展也发生在的section 不够时。
The routine thatperforms the expansion writes this message to the alert log. The messagesspecifies the section that was expanded and the amount of the expansion. Pleasenote that this message cannot be turned off.
--执行扩展操作时会往alert log 里写一些log 信息，就是上面部分的内容，这部分内容列出了扩展的section和扩展的数量，这个显示信息不能被关闭。
The automatic expansion only occurs up to the limit of the "init.ora" parameter "DB_FILES".
--注意，控制文件中的自控扩展的datafile 的最大值就是我们DB_FILES 参数的值。 所以这也是我们之前看的，DB_FILES 参数值比控制文件中默认值大的原因。
总结一下：
数据库中真正的最大的datafiles 的个数由DB_FILES 参数决定，虽然控制文件中也有限制，但是这个控制文件中的限制参数会自动的增加，直到到达DB_FILES的值。
2. MAXLOGFILES
MaxLogFiles can be defined when the database is created or when the controlfile is recreated.
The default value, if not especified, is 32, and the maximun value is 256.
3. MAXLOGMEMBERS
MaxLogMembers can be defined when the database is created or when the controlfile is recreated.
The default value, if not especified, is 2, and the maximun value is 5.
In Oracle10g, now you can also specify the following parameters:
4. MAXINSTANCES
The MAXINSTANCES option of CREATE DATABASE limits the number of instances that can access a database concurrently.
The default value for this option under z/OS is 15. Set MAXINSTANCES to a value greater than the
maximum number of instances you expect to run concurrently.
5. MAXLOGHISTORY
The MAXLOGHISTORY option of CREATE DATABASE specifies the maximum number of redo log files that can be recorded in the log
history of the control file. The log history is used for automatic media recovery of
Oracle Real Application Clusters.
For Oracle Real Application Clusters, set MAXLOGHISTORY to a large value, such
as 100. The control file can then store information about this number of redo log
files. When the log history exceeds this limit, the Oracle server overwrites the oldest
entries in the log history.
Setting the MAXLOGHISTORY to 0 (zero), disables log history.