db_files和maxdatafiles的联系

环境：hp-ux 11iv1    oracle 9204
先建立一个有4个datafile的测试库，然后修改参数db_files=4，重建控制文件：
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
    MAXDATAFILES 4
LOGFILE
  GROUP 1 '/u02/orcl/oradata/TEST/redo01.log'  SIZE 50M,
  GROUP 2 '/u02/orcl/oradata/TEST/redo02.log'  SIZE 50M
DATAFILE
  '/u02/orcl/oradata/TEST/system01.dbf',
  '/u02/orcl/oradata/TEST/undotbs01.dbf',
  '/u02/orcl/oradata/TEST/user01.dbf',
  '/u02/orcl/oradata/TEST/test01.dbf'
CHARACTER SET ZHS16GBK;
然后打开数据库：alter database open;这时报
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
实例也crash掉，我只有4个数据文件啊，为什么还报错？
开始以为是没算上临时文件，想想也不对啊，重建控制文件后临时文件还没添加呢，后面的实验也证实了tempfile数量不受db_files参数的限制。
查看alert日志报：
ORA-01667: cannot add any more tablespaces: limit of 4 exceeded
Error 1667 happened during db open, shutting down database
USER: terminating instance due to error 1667
Instance terminated by USER, pid = 11016
ORA-1092 signalled during: alter database open...
原来是表空间数超过了限制，查看相关视图也证实了

SQL> select * from v$controlfile_record_section;

TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE                     192             1            1           0          0          0
CKPT PROGRESS               4084             4            0           0          0          0
REDO THREAD                  104             1            1           0          0          0
REDO LOG                      72            16            3           0          0          0
DATAFILE                     180             4            4           0          0          0
FILENAME                     524            36            6           0          0          0
TABLESPACE                    68             4           4           0          0          0
TEMPORARY FILENAME            56             4            0           0          0          0
RMAN CONFIGURATION          1108            50            0           0          0          0
LOG HISTORY                   36           226            0           0          0          0

SQL> select * from v$tablespace;
 
       TS# NAME                           INC
---------- ------------------------------ ---
         0 SYSTEM                         YES
         1 UNDOTBS1                       YES
         4 TEST                           YES
         3 USERS                          YES

原来重建控制文件后，最大tablespace数和最大datafile数都变为了重建控制文件时使用的MAXDATAFILES 4，数据库open后会把TEMP表空间算上，所以open的过程就出错了，如果我把db_files设为5会不会超过这个限制呢？

SQL> alter system set db_files=5 scope=spfile;
SQL> startup force;

同样是ORA-01092: ORACLE instance terminated. Disconnection forced，alert日志：ORA-01667: cannot add any more tablespaces: limit of 4 exceeded
看来还得重建控制文件了，把MAXDATAFILES改为5
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
    MAXDATAFILES 5
LOGFILE
  GROUP 1 '/u02/orcl/oradata/TEST/redo01.log'  SIZE 50M,
  GROUP 2 '/u02/orcl/oradata/TEST/redo02.log'  SIZE 50M
DATAFILE
  '/u02/orcl/oradata/TEST/system01.dbf',
  '/u02/orcl/oradata/TEST/undotbs01.dbf',
  '/u02/orcl/oradata/TEST/user01.dbf',
  '/u02/orcl/oradata/TEST/test01.dbf'
CHARACTER SET ZHS16GBK;

SQL> select * from v$controlfile_record_section;

TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE                     192             1            1           0          0          0
CKPT PROGRESS               4084             4            0           0          0          0
REDO THREAD                  104             1            1           0          0          0
REDO LOG                      72            16            3           0          0          0
DATAFILE                     180             5            4           0          0          0
FILENAME                     524            38            7           0          0          0
TABLESPACE                    68             5            4           0          0          0
TEMPORARY FILENAME            56             5            0           0          0          0
RMAN CONFIGURATION          1108            50            0           0          0          0
LOG HISTORY                   36           226            0           0          0          0

SQL> alter database open;
 
Database altered.

SQL> select * from v$controlfile_record_section;
 
TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE                     192             1            1           0          0          0
CKPT PROGRESS               4084             4            0           0          0          0
REDO THREAD                  104             1            1           0          0          0
REDO LOG                      72            16            3           0          0          0
DATAFILE                     180             5            4           0          0          0
FILENAME                     524            38            7           0          0          0
TABLESPACE                    68             5            5           0          0          1
TEMPORARY FILENAME            56             5            0           0          0          0
RMAN CONFIGURATION          1108            50            0           0          0          0
LOG HISTORY                   36           226            1           1          1          1

tablespace项的RECORDS_USED也变为5了，这时先加临时文件看看：

SQL>alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp01.dbf' size 2m  reuse autoextend off;

SQL>select * from v$controlfile_record_section where type in ('DATAFILE', 'TEMPORARY FILENAME');
 
TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                     180             5            4           0          0          0
TEMPORARY FILENAME            56             5            1           0          0          1

控制文件中的DATAFILE项的RECORDS_USED值没有变化，而TEMPORARY FILENAME项的RECORDS_USED值变化了，继续增加：

SQL>alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp02.dbf' size 2m  reuse autoextend off;

SQL>select * from v$controlfile_record_section where type in ('DATAFILE', 'TEMPORARY FILENAME');
 
TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                     180             5            4           0          0          0
TEMPORARY FILENAME            56             5            2           0          0          1

临时文件会不会只允许加5个呢？继续加，当增加到第6个临时文件时：

SQL> alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp06.dbf' size 2m  reuse autoextend off;
alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp06.dbf' size 2m  reuse autoextend off
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3637], [11], [10], [], [], [], [], []

竟然报了个ora-600错误，alert日志里有如下信息：
alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp06.dbf' size 2m  reuse autoextend off
Thu Feb 19 16:41:34 2009
kccrsz: expanded controlfile section 7 from 5 to 15 records
  number of logical blocks in section remains at 1
Thu Feb 19 16:41:34 2009
Errors in file /u01/home/ora92/product/9.2.0/rdbms/log/test_ora_12414.trc:
ORA-00600: internal error code, arguments: [3637], [11], [10], [], [], [], [], []
ORA-600 signalled during: alter tablespace temp add tempfile '/u02/orcl/orad...
说是扩展到了15 records，查看v$controlfile_record_section视图也是如此：

SQL> select * from v$controlfile_record_section where type in ('DATAFILE', 'TEMPORARY FILENAME');
 
TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                     180             5            4           0          0          0
TEMPORARY FILENAME            56            15            6           0          0          6

那临时文件有没有添加进去呢？

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------------------
/u02/orcl/oradata/TEST/temp01.dbf
/u02/orcl/oradata/TEST/temp02.dbf
/u02/orcl/oradata/TEST/temp03.dbf
/u02/orcl/oradata/TEST/temp04.dbf
/u02/orcl/oradata/TEST/temp05.dbf

SQL> select name from v$tempfile;
 
NAME
----------------------------------------------------------
/u02/orcl/oradata/TEST/temp01.dbf
/u02/orcl/oradata/TEST/temp02.dbf
/u02/orcl/oradata/TEST/temp03.dbf
/u02/orcl/oradata/TEST/temp04.dbf
/u02/orcl/oradata/TEST/temp05.dbf
/u02/orcl/oradata/TEST/temp06.dbf

两者的查询还不一样，控制文件中已经记录了，再加的时候也报错：
SQL> alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp06.dbf' size 2m  reuse autoextend off;
alter tablespace temp add tempfile '/u02/orcl/oradata/TEST/temp06.dbf' size 2m  reuse autoextend off
*
ERROR at line 1:
ORA-01537: cannot add data file '/u02/orcl/oradata/TEST/temp06.dbf' - file already part of database
最后又重启一下库才好了。

下面来看看db_files和maxdatafiles对datafile数量是怎么限制的吧，目前有4个数据文件，db_files=5，那是可以再增加一个的吧：

SQL> alter tablespace test add datafile '/u02/orcl/oradata/TEST/test02.dbf' size 2M autoextend off;

Tablespace altered.

SQL> alter tablespace test add datafile '/u02/orcl/oradata/TEST/test03.dbf' size 2M autoextend off;
alter tablespace test add datafile '/u02/orcl/oradata/TEST/test03.dbf' size 2M autoextend off
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

已经达到db_files的设定值了，然后修改db_files=6，maxdatafiles还是5，重启数据库

SQL> alter tablespace test add datafile '/u02/orcl/oradata/TEST/test03.dbf' size 2M autoextend off;
 
Tablespace altered.
 
SQL> select * from v$controlfile_record_section;
 
TYPE                 RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE                     192             1            1           0          0          0
CKPT PROGRESS               4084             4            0           0          0          0
REDO THREAD                  104             1            1           0          0          0
REDO LOG                      72            16            3           0          0          0
DATAFILE                     180             6            6           0          0          4
FILENAME                     524            38           15           0          0          0
TABLESPACE                    68             5            5           0          0          1
TEMPORARY FILENAME            56            15            6           0          0          6
RMAN CONFIGURATION          1108            50            0           0          0          0
LOG HISTORY                   36           226            2           1          2          2

看来oracle会自动超过maxdatafiles的限制，只有db_files参数限定了最大能加的数据文件数
而临时文件则不受db_files参数的限制，虽说会自动扩展，但和maxdatafiles有一定关系，maxdatafiles过小可能会触发ORA-600错误，造成不可预料的后果，不过建库时默认的maxdatafiles参数是够用了
最后发现TABLESPACE项的值会随着db_files参数的值变化而变化，所以不可能出现表空间数不够用的情况，只可能提示ORA-00059: maximum number of DB_FILES exceeded

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/339291/viewspace-555698/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/339291/viewspace-555698/