ORA-00059: maximum number of DB_FILES exceeded

一、现象

给数据库扩容表空间的时候，遭遇ORA-00059: maximum number of DB_FILES exceeded错误

SQL> SQL> create tablespace st_hie_data datafile '/data/oracle/aidb/st_hie_data01.d
bf' size 10g autoextend on next 100M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

二、原因

数据库文件数量超过DB_FILES参数设置的阈值，默认该参数值为200，即数据库最大支持创建200个数据文件

• 当添加的数据文件数量超过db_files 参数值限制时，会报ORA-00059错误，通过修改参数来增大db_files 参数值
• db_files超过操作系统允许打开的文件数时，则会RA-01131: DB_FILES system parameter value 80000 exceeds limit of 65534报错，通过ulimit -a查看并修改open files数量
• 当db_files 参数值已经达到控制文件maxdatafiles设置的值大小时，控制文件maxdatafiles值会自动增加，maxdatafiles值无需修改

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.

数据库中真正的最大的datafiles 的个数由DB_FILES 参数决定，虽然控制文件中也有限制，但是这个控制文件中的限制参数会自动的增加，直到到达DB_FILES的值。

三、解决方法

(1)查看db_files参数值及数据文件数量
SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

SQL> select count(*) from dba_data_files;

(2)修改db_files参数
SQL> alter system set db_files=500 scope=spfile;

(3)重启数据库
SQL> shutdown immediate

SQL> startup

(4)确认修改
SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     500

SQL> select * from v$controlfile_record_section;

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE                             316             1            1           0    0           0
CKPT PROGRESS                       8180            11            0           0    0           0
REDO THREAD                          256             8            1           0    0           0
REDO LOG                              72            16            6           0    0           6
DATAFILE                             520           400          205           0    0       18877
FILENAME                             524          2298          220           0    0           0
TABLESPACE                            68           100           21           0    0          23
TEMPORARY FILENAME                    56           100            9           0          0      13823

RECORDS_USED: 表示当前已经存在的数量

(4)验证控制文件中的maxdatafiles

SQL> alter database backup controlfile to trace as '/tmp/testdb.ctl';

Database altered.

$ more /tmp/testdb.ctl

CREATE CONTROLFILE REUSE DATABASE "AIDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 400
    MAXINSTANCES 8
    MAXLOGHISTORY 21212
LOGFILE
  GROUP 1 '/data/oracle/aidb/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/aidb/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/aidb/redo03.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 4 '/data/oracle/aidb/redo04.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 5 '/data/oracle/aidb/redo05.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 6 '/data/oracle/aidb/redo06.log'  SIZE 200M BLOCKSIZE 512
  
  
或

SQL> oradebug setmypid
Statement processed.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/aidb/aidb/trace/testdb_ora_991.trc

$ more /u01/app/oracle/diag/rdbms/aidb/aidb/trace/testdb_ora_991.trc