maxdatafiles 与db_files

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.

该参数存在于控制文件中，maxdatafiles主要控制的是controlfile中能够记录的datafile数量

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.

该参数保存着spfile文件中，db_files实际控制 da tafile数量

下面做个实验：

1）SQL> show parameter db_files


NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_files                             integer
200

默认db_files为200

 2）SQL> alter database backup controlfile to trace as '/home/oracle/ctl.ctl';  导出控制文件

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/export/home/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/export/home/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/export/home/oracle/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/export/home/oracle/oradata/orcl/system01.dbf',
  '/export/home/oracle/oradata/orcl/undotbs01.dbf',
  '/export/home/oracle/oradata/orcl/sysaux01.dbf',
  '/export/home/oracle/oradata/orcl/users01.dbf',
  '/export/home/oracle/oradata/orcl/example01.dbf',
  '/export/home/oracle/oradata/orcl/ORCL/datafile/o1_mf_huang_9slqqcwx_.dbf'
CHARACTER SET WE8ISO8859P1

;

默认maxdatafiles为100

3）将MAXDATAFILES 改为8，使用上面的脚本创建控制文件

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/export/home/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/export/home/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/export/home/oracle/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/export/home/oracle/oradata/orcl/system01.dbf',
  '/export/home/oracle/oradata/orcl/undotbs01.dbf',
  '/export/home/oracle/oradata/orcl/sysaux01.dbf',
  '/export/home/oracle/oradata/orcl/users01.dbf',
  '/export/home/oracle/oradata/orcl/example01.dbf',
  '/export/home/oracle/oradata/orcl/ORCL/datafile/o1_mf_huang_9slqqcwx_.dbf'
CHARACTER SET WE8ISO8859P1

;

SQL> ALTER DATABASE RECOVER;


Database altered.

SQL>  ALTER DATABASE OPEN ;


Database altered.

4）再次转储控制文件

SQL> alter database backup controlfile to trace as '/home/oracle/ctl1.ctl';

 发现/ctl1.ctl中    MAXDATAFILES 8

5）新增几个表空间，再次转储控制文件，ctl2.ctl中的  MAXDATAFILES 变成了38.

SQL> create tablespace huang1;


Tablespace created.


SQL> create tablespace huang2;


Tablespace created.


SQL> create tablespace huang3;


Tablespace created.

SQL>  alter database backup controlfile to trace as '/home/oracle/ctl2.ctl';


Database altered.

以上实验说明了maxdatafiles值会自动变大的。

其实从官方文档上可以查到资料，在10.2.0及后的版本中，maxdatafiles，MAXLOGMEMBERS 这些类似的参数能够自动expand，但是在10.2.0之前却不能自动expand