控制文件中MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS等参数的说明

说到这个MAXDATAFIEL我们不得不说另一个参数db_files,那么这个maxdatafile和这个db_files之间有什么数量上的关系呢？

db_files:

  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

# 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 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的值。