ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db_files和maxdatafiles的联系

db_files和maxdatafiles的联系

原创 Linux操作系统 作者:xfxyxh 时间:2009-02-19 14:13:42 0 删除 编辑

环境: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    21
  • 访问量
    85142