My Experiments with Oracle

By Sriram Sanka -The AUAA

Archive for May, 2011

ORA-00059: maximum number of DB_FILES exceeded

Posted by sriram on May 27, 2011


This is all about the Relation B/W  MAXDATAFILES and DB_FILES In Oralce Database.

–This will Give Us db_files value. max no of data files that we can add.

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

This will give the MAXDATAFILES specified at Control file level while creating Database.

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

Lets Start with a small case. I have Created My database with DB_FILES   as 10.

C:\Windows\System32>sqlplus system/tejajun20

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

Let us have a look at the no of data files I have.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>

Let me add some data files to users tables

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
         8

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        10

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

As I have already reached the max limit 10,Adding one more file will raise an Error.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number 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.

Lets Try to increase the value to a small number.

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

System altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
20

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;

Tablespace altered.

SQL>

See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).

Lets do that And verify what `ll happen.


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

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        14
--- In another window I am adding datafiles upto the limit "100"
SQL> /

  COUNT(*)
----------
        48

SQL> /

  COUNT(*)
----------
        55

SQL> /

  COUNT(*)
----------
        82

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;

Tablespace altered.

SQL>
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;

Tablespace altered.

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          200

----Observe it Automatically changed..

SQL>

As Per Oracle Documents :

Consider Possible Limitations When Adding Datafiles to a Tablespace:

  1. You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
  2. Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
  3. Operating systems impose limits on the number and size of datafiles.
  4. The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.

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.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.

For More Information Please Read:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

Advertisements

Posted in Oracle Server Administration | 98 Comments »

 
%d bloggers like this: