【前言】Oracle数据库创建是的默认参数一般是够大部分的数据库使用了，但是在一些情况下有写参数是需要进行变更的。
【问题背景】公司有个Oracle的数据库仓库，整个数据库大小5TB+，但是整个空间也快满了。在进行添加表空间的时候就报了ORA-59的错误。
【问题分析】
1、错误分析
[oracle@ekpjdbtest trace]$ oerr ora 59
00059, 00000,
"maximum number of DB_FILES exceeded"
// *Cause: The value of the DB_FILES initialization parameter was exceeded.
// *Action: Increase the value of the DB_FILES parameter and warm start.
|
以上报错显示DB_FILES参数值已经达到最大。
SQL>
select
count(1)
from
dba_data_files;
COUNT(1)
----------
200
SQL> show parameter db_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
|
2、db_files的oracle文档说明
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value |
200
|
| Modifiable | No |
| Range of values |
Minimum: the largest among the absolute file numbers of the datafiles in the database
Maximum: operating system-dependent |
| Basic | No |
| Oracle RAC | Multiple instances must have the same value. |
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.
【解决方法】
知道了整个问题的来龙去脉，解决方法也就很简单了。
SQL> alter system
set
db_files=500 scope=spfile;
Shutdown the database with immediate option and restart so that the
new
value
for
db_files can be read.
SQL> shutdown immediate
SQL> startup
Check the
new
value
for
db_files parameter
SQL> show parameter db_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 500
|
【其他】
1、
DB_FILES
参数涉及到要打开操作系统的数据文件的个数，所以如果还有问题也要查看下操作系统的一些参数情况
[oracle@test trace]$ ulimit -a
open files (-n) 65536
|
2、db_files和maxdatafiles参数的关系
The
db_files parameter is a "soft limit " parameter that controls the
maximum number of physical OS files that can map to an Oracle instance.
Historically (before Oracle8i) you need to be careful not to set
db_files too high, else you would have DBWR (database writer) issues.
The
maxdatafiles parameter is a different "hard limit" parameter. When you
issue a"create database" command, the value you specify for
maxdatafiles is stored in your Oracle control files. The default value
of 32 is usually sufficient, but after Oracle8i there is no downside to
using a larger value.
在Oracle11G maxdatafiles这个参数是会自动扩展的，所以我们没有必要对该文件进行设置（发现有网友因为db_files的文件问题重新创建了控制文件，经过本人测试确实没有必要）。
我在测试环境中进行了操作，maxdatafiles的大小是8，然后增加数据文件的方式来进行表空间，发现数据库的alert log里面就记录了整个maxdatafiles扩展的操作记录。
1、alert log显示控制文件进行了Expanded的操作
Expanded controlfile section 4
from
10 to 40 records
Requested to grow
by
30 records; added 1 blocks of records
2、alter database backup controlfile to trace resetlogs; 查看控制文件的参数情况
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE
"WZQ"
RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 40
MAXINSTANCES 8
MAXLOGHISTORY 292
显示MAXDATAFILES文件已经修改成40了。
|
【总结】一个简单的表空间问题，经过测试和验证把问题都弄清楚了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12679300/viewspace-2157899/ ，如需转载，请注明出处，否则将追究法律责任。
