关于db_files和maxdatafiles的问题

昨天在做生产监控的时候发现有个库的表空间不够了，就发邮件给客户的dba去处理，但是得到的反馈是尝试添加的时候发现已经超过了数据文件的最大数限制。这个错误毫无疑问就是"ORA-00059: Maximum Number Of db_files Exceeded"
一看到这个问题，一下子感觉就头大了。这个参数是在创建数据库的时候就应该考虑到的。竟然没有考虑周全，如果控制文件中的maxdatafiles没有值太小，只能重建控制文件了。如果本身数据文件的数量超过了最大限制，甚至还需要考虑重建数据库，如果碰到这种事就太郁闷了。
查看oracle的文档，赫然可以看到以下的参数是需要在创建数据库(尤其是RAC)中需要考虑的。

• ACTIVE_INSTANCE_COUNT

• ARCHIVE_LAG_TARGET

• CLUSTER_DATABASE

• CLUSTER_DATABASE_INSTANCES

• CONTROL_FILES

• DB_BLOCK_SIZE

• DB_DOMAIN

• DB_FILES

• DB_NAME

• DB_RECOVERY_FILE_DEST

• DB_RECOVERY_FILE_DEST_SIZE

• DB_UNIQUE_NAME

• UNDO_MANAGEMENT
  
  对于参数db_files的解释如下：

当前库是11gR2版本，默认值是200。
在其它版本中的情况关于maxdatafiles的情况如下：

 ---------------- V7      8.0.5    8.1.6    9.2      10.1     10.2
   —————————————————-------- -------- ------ -------- -------- -------- 
     UNIX    Per TS          1022     1022    1022     1022     1022
             Per DB 1022     65536    65536   65534    65334    65334 
     VMS     Per TS          1022       -       -        -      1022
             Per DB 1022     65536      -                       65334
     WINDOWS Per TS          1022     1022       
             Per DB 1022     65536    65536   65534    65334    65334 

11g中对datafiles略有增长，为65533，表空间中最大数据文件数也是1022,根据操作系统可能还要略微低一些。

回到两个参数db_files和maxdatafiles,db_files是一个"soft" limit，而maxdatafiles是一个“hard" limit.

maxdatafiles是在create database语句中包含的，写入了controlfile,如果要修改maxdatafiles，则需要重建控制文件。

db_files则是在实例级别的限制，这个值需要小于maxdatafiles，但是可以在参数文件中根据需要来做变更。

具体可以参考MOS Doc ID 119507.1，里面给出了详细的解决步骤，就不再赘述了。

SOLVING ORA-1118 and ORA-1165 :
------------------------------

The following are possible options to get around these errors :     
   
1.  You should verify that you haven't reached a hard limit for MAXDATAFILES 
   imposed by the Operating System. This limit can be found in the O/S specific
   documentation.
   
   If MAXDATAFILES is already equal to the maximum value, then you must 
   restructure the database by reducing the number of datafiles associated with 
   each individual tablespace:

     a) export the objects from the tablespace
     b) drop the tablespace
     c) recreate the tablespace with less datafiles
     d) import the objects back into the tablespace

    In some cases, if the datafile size is too small, it might be usefull to 
    resize the datafiles. Make sure that you keep the datafile size lower than 
    the Operating System limitation on file sizes :

     > ALTER DATABASE DATAFILE 'D:\Oradata\Orcl\datafile\usr01.dbf' RESIZE 500M;

    Query DBA_SEGMENTS to find out all objects belonging to a particular  
    tablespace.

2.  Increase the MAXDATAFILES parameter.  It is possible for you to increase 
    the MAXDATAFILES limit without recreating the entire database.  This is done
    by recreating the control file only.     
   
3. Especially for Oracle8+ you should make sure that you do not encounter an 
   error against the maximum number of open database files (DB_FILES). It is 
   more likely that the value for DB_FILES is too low since the controlfile in 
   Oracle8 expands automatically as long as the number of the added datafile is
   lower then the value for DB_FILES. Normally the error message should 
   indicate this:

     ORA-00059 : maximum number of DB_FILES exceeded

4.  Recreate the database.  Since the MAXDATAFILES parameter is specified    
    upon database creation, recreating the database allows you to increase   
    this parameter.  
   
    In addition to your operating system documentation, you may want to    
    reference the following bulletins:   
 
    O7 - Creating an Oracle 7.X Database under Unix - Note:10280.1 RECREATING THE CONTROL FILE:   
----------------------------  
 
In Oracle7 or higher, you can create the control file.  In addition, you can get
Oracle to create the script for you.   To do this, perform the following steps:   
   
1.  With the database mounted or open, issue the following commands:    
    
    SQL> alter database backup controlfile to trace;    
    SQL> exit    
   
2.  A trace file will have been generated in your 'user_dump_dest'.   
    User_dump_dest is an init.ora parameter, and can be found by    
    issuing:   
   
    SQL> show parameter user_dump_dest   
   
    The easiest way to locate the correct trace is to look at its date.  A   
    file will exist with the current date and time.  The naming convention    
    for these files is operating system specific.   
   
3.  Once the file is located, search through the file for the word "CONTROL"    
   
    You should find:    
   
    # The following commands will create a new control file and use it    
    # to open the database.    
    # No data other than log history will be lost.  Additional logs may    
    ETC.ETC.    
    
4.  Copy this trace file to some location and rename it to end it ".sql",    
    for this example, it is called "recr_con.sql".     
   
5.  Edit the "recr_con.sql" deleting the trace header information.  Then  
    increase the value that you find next to the word "MAXDATAFILES".    
    
6.  Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).    
   
7.  Take a full database backup at this time.    
   
8.  Remove the current control files.  It is essential to remove all control   
    files, otherwise, you will receive an error.  In addition, you CANNOT   
    REUSE the control file, since the size of the control file will increase   
    when you increase MAXDATAFILES.   
    
9.  Create the controlfile within SQLDBA:   
   
    SQL> connect internal     [if using sqldba or svrmgrl]
         or 
         connect / as sysdba  [if using sqlplus]
    SQL> @recr_con.sql    
    SQL> alter database open noresetlogs;    
    
    If you receive a "Statement processed" message, then your database is    
    now back up and running with a higher datafile limit.  It is recommended   
    to shutdown at this time and take a full backup.    
    
CREATE CONTROLFILE SYNTAX:   
-------------------------
   
The following is information on the create control file syntax, this    
information is fully documented in the Oracle SQL Reference Manual.   
   
The syntax of this command is similar to CREATE DATABASE.    
The defaults for any missing clauses are the same as the    
DATABASE defaults for CREATE DATABASE.    
    
CREATE CONTROLFILE [REUSE]    
   SET DATABASE name    
   [LOGFILE filespec [, filespec] ...]    
    RESETLOGS | NORESETLOGS    
   [MAXLOGFILES integer]    
   [DATAFILE filespec [, filespec] ...]    
   [MAXDATAFILES integer]    
   [MAXINSTANCES integer]    
   [ARCHIVELOG | NOARCHIVELOG]    
   [SHARED | EXCLUSIVE]