Saturday, December 10, 2011

ORA-00059: maximum no. of DB_FILES exceeded


ORA-00059:
maximum number of DB_FILES exceeded
Cause:
The value of the DB_FILES initialization parameter was exceeded.
Action:
Increase the value of the DB_FILES parameter and warm start.

Today I came across ORA00059: maximum number of DB_FILES exceeded, while adding a tablespace to my database then I checked db_files parameter

This will give the MAXDATAFILES specified at control file level while creating the database

Select   value  from  v$parameter   where  name = ‘db_files’ ;

value
-------
500

Also I checked  v$control_record_section to see MAXDATAFILES 

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

records_total
----------------
700

Then I increased the db_files parameter

SQL> alter system set db_files=700 scope=spfile;
System altered.

And bounce the database to see the new value

select   value  from  v$parameter   where  name = ‘db_files’;
value
-------
700

You can still increase db_files to morethan control_record_section parameter

EX:  db_files= 800
      Control_record_section=700

But when you reach datafiles to near 700 then control_record_section automatically increases its value

Ex: select count(*) from dba_data_files;
Count(*)
------------
698

And still if you add one more to it

Ex: select count(*) from dba_data_files;

Count(*)
------------
699

Then you can check the control_record_section  parameter automatically increases its value

select records_total from v$controlfile_record_section where type = ‘DATAFILE’ ;

records_total
------------------
800

NOTE:
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you 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.
DB_FILES specifies the maximum number of database files that can be opened for this database.


0 comments:

Post a Comment

Auto Scroll Stop Scroll