Oracle db_files 和 maxdatafiles 说明

Oracle db_files 和 maxdatafiles 说明

一.  DB_FILES 

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
Real Application Clusters	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. 

 

二.  Maxdatafiles 参数

 

       这个参数是保存在控制文件里的，在DBCA创建实例的时候可以指定该值的大小。 官网对这个参数的说明如下：

 

MAXDATAFILES

       The MAXDATAFILES option of CREATE DATABASE determines the number of data files a database can have. With Oracle Real Application Clusters, databases tend to have more data files and log files than an exclusive mounted database.

 

       一般db_files <= maxdatafiles值。 当select count(*) from dba_data_files; 的值达到db_files时，就需要修改db_files，把这个值调大。

 

       对这个参数的默认值，从我dump 出的trace 文件看，是30. 这个值明显过小。 稍大一点的系统也不止30个datafile。 不过dbca来看，该值是100.

 

SQL>alter database backup controlfile to trace

 

 

CREATE CONTROLFILE REUSE DATABASE "DAVE" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 30

MAXINSTANCES 1

MAXLOGHISTORY 292

 

 

在DBCA创建Instance的时候，我们可以指定该参数。 如下图：

 

 

 

       如果说是已经建好了，就只能重建控制文件来修改该参数值。先将控制文件dump 出来，然后修改改制，在重建控制文件。 在重建之前，记得备份控制文件和DB。 具体操作步骤参考：

       Oracle 控制文件      

 

三. 网上的资料

google到一篇资料，是对这2个参数的说明。  链接如下：

       Oracle db_files and maxdatafiles parameters

       http://www.dba-oracle.com/t_db_files_maxdatafiles.htm

 

说明，需要翻墙，不然打不开。

 

       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.

       In practice, many Oracle DBA with large databases will segregate important tables and indexes into isolated tablespaces and datafiles to give them more control and detailed statistrics.

 

 

---

Fixing a maxdatafiles limit problem

       In practice, the ORA-1118 occurs when your database has hit the MAXDATAFILES limit, usually during database maintenance. Here are instructions from "Rhubarb" Stewart McGlaughlin, one of the best Oracle DBA's in North Carolina:

1. Shutdown database; Backup database
2. Start up database
3. From sqlplus as sysdba, type: alter database backup controlfile to trace;
4. Type: shutdown immediate:
5. Go to the operating system and go to the USER_DUMP_DEST directory
6. Find the newest trace file
7. Edit the trace file and change MAXDATAFILES to the new value. You will also need to delete all of the lines prior to the line that begins: STARTUP NOMOUNT. See sample text below (the example is for a database using ARCHIVELOG):

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG

MAXLOGFILES 32 MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1600
LOGFILE
GROUP 1 'D:/ORAWIN95/DATABASE/LOG2ORCL.ORA' SIZE 200K,
GROUP 2 'D:/ORAWIN95/DATABASE/LOG1ORCL.ORA' SIZE 200K DATAFILE 'D:/ORAWIN95/DATABASE/SYS1ORCL.ORA', 'D:/ORAWIN95/DATABASE/USR1ORCL.ORA', 'D:/ORAWIN95/DATABASE/RBS1ORCL.ORA', 'D:/ORAWIN95/DATABASE/TMP1ORCL.ORA' ;

 

# Database can now be opened normally. ALTER DATABASE OPEN RESETLOGS;

8. From sqlplus as sysdba, run the edited trace file from step 7.
9. Shutdown database and backup database