db_files和maxdatafiles

db_files和maxdatafiles

maxdatafiles: max # of datafiles that can be recorded in the control file. the larger value of maxdatafiles, the larger the control file

db_files: max # of datafiles that can be open for a particular instance. usaully db_files is less than maxdatafiles. the larger value of db_files, the larger SGA.

the max number of datafiles an instance can attach is min(db_files, maxdatafiles)

===============================================

Oracle db_files and maxdatafiles parameters

Don Burleson

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:ORAWIN95DATABASELOG2ORCL.ORA' SIZE 200K,
GROUP 2 'D:ORAWIN95DATABASELOG1ORCL.ORA' SIZE 200K DATAFILE 'D:ORAWIN95DATABASESYS1ORCL.ORA', 'D:ORAWIN95DATABASEUSR1ORCL.ORA', 'D:ORAWIN95DATABASERBS1ORCL.ORA', 'D:ORAWIN95DATABASETMP1ORCL.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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-85036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-85036/