环境: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/