实测10.2.0.5/11.2.0.4版本修改DB_FILES参数不需要重建控制文件

最近有客户数据文件达到db_files参数上限，和同事就这个参数的修改沟通时，许多人还认为是以前8i/9i时代，修改此参数后需要重建控制文件。通过测试验证修改10.2.0.5/11.2.0.4版本测试修改DB_FILES参数不需要重建控制文件(RAC需要两个节点都停掉再启动，不能先后重启节点).

如下为11.2.0.4+LINUX版本的测试过程:

1.查看当前的控制文件trace中MAXDATAFILES值为默认的100及db_files值为默认的200

SQL> alter database backup controlfile to trace as '/home/oracle/aa.trc';
 Database altered. 
SQL> show parameter db_files 
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
db_files integer 200 

$ cat aa.trc|grep MAXDATAFILES 
MAXDATAFILES 100 
MAXDATAFILES 100

2.实际创建超过db_files参数的数据文件时报错：ORA-00059: maximum number of DB_FILES exceeded 创建超过200个数据文件：

SQL> create tablespace bystest1 datafile '/u01/app/oracle/oradata/bys1/bystest1-01.dbf' size 1m autoextend on;

Tablespace created.

SQL> alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-02.dbf' size 1m;

Tablespace altered.

SQL> alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-03.dbf' size 1m; 
alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-04.dbf' size 1m; 
………………
到达db_files=200限制时，会报错：
SQL> alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-196.dbf' size 1m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
SQL> alter database backup controlfile to trace as '/home/oracle/aa2.trc';
Database altered.
此时控制文件中的信息;
$ cat aa2.trc|grep MAXDATAFILES
MAXDATAFILES 200
MAXDATAFILES 200

3.修改db_files=250参数并重启数据库：

SQL> alter system set db_files=250 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 276825128 bytes
Database Buffers 339738624 bytes
Redo Buffers 7507968 bytes
Database mounted.
Database opened.

4.继续创建数据文件并验证参数、控制文件信息：db_files=250同时控制文件中MAXDATAFILES会自动增长

SQL> alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-196.dbf' size 1m;
………………
alter tablespace bystest1 add datafile '/u01/app/oracle/oradata/bys1/bystest1-209.dbf' size 1m;

SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 250

SQL> alter database backup controlfile to trace as '/home/oracle/aa3.trc';

Database altered.

SQL> select count(*) from v$dbfile;

COUNT(*)
----------
214

$ cat aa3.trc|grep MAXDATAFILES
MAXDATAFILES 400
MAXDATAFILES 400