Monday, May 04, 2009

DB_FILES... think??? determine???

DB_FILES initialization parameter... why i should to think about it?

When starting Oracle database instance, DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and maximum number of datafiles that can be created for the instance.
Anyway we can change... (but we have to shutdown/startup)

So, if value of DB_FILES is too low, we cannot add datafiles beyond the DB_FILES limit, so we have to shutdown+startup database for changing DB_FILES value.
if value of DB_FILES is too high, memory is unnecessarily consumed.


SQL> create tablespace a01 datafile '+DATA1' size 20M;
create tablespace a01 datafile '+DATA1' size 20M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

We have to change DB_FILES (if use spfile):

SQL> show parameter db_files

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

SQL> alter system set set db_files=500 scope=spfile;

SQL> shutdown immediate

SQL> startup
RAC... How?

We have to shutdown all nodes and startup them.

Because, we may find Error: ORA 1174
Text: DB_FILES is aaa but needs to be bbb to be compatible

Just think and determine about number of datafile... if don't want to maintenance to change it.

By the way, Determine DB_FILES initialization parameter... so don't need to interest MAXDATAFILES parameter specifies on control file.

If we attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

2 comments:

Laurent Schneider said...

according to note 331067.1, maxdatafiles can grow since Oracle 8i!

But others like maxinstances have been introduced in 10gR2 I believe :
http://laurentschneider.com/wordpress/2006/01/change-maxdatafiles-malogfiles-maxinstances-maxlogmembers-without-downtime.html

Surachart Opun said...

thank you... excellent ;)