Eliminating Re-Creation of Control File when increasing the value of MAXDATAFILES parameter

With the enhancements in Oracle 10g Release 2, we can eliminate control file recreation for changing values of MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters. In earlier versions of Oracle database, you have to recreate the controlfile in order to modify any of these parameters.

These parameters are defined at the time of database creation. MAXDATAFILES parameter indicates the maximum number of datafiles that a database can accommodate. This parameter also affects the size of the of the control file.

DB_FILES initialization parameter specifies the maximum number of database files that can be opened for the database. If you modify this parameter then the instance should be bounced for the new value to take effect.

I have specified MAXDATAFILES to be 20 when creating my test database and started my instance with DB_FILES=19. We can check the current value of MAXDATAFILES for the database by:

1) Creating a trace of the control file, and,

2) Querying the V$CONTROLFILE_RECORD_SECTION view.

Check the current value of DB_FILES initialization parameter of the instance:

SQL> show parameter db_files

NAME TYPE VALUE
---------- ----------- -----------
db_files integer 20

Query the current value of MAXDATAFILES parameter defined for the database:

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

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 20 3

1 row selected.

Alternatively, we can check the value of MAXDATAFILES by creating a trace of the control file.

SQL> alter database backup controlfile to trace as 'c:\testdb\control\control.txt';

Database altered.

Open the file control.txt using any text editor and view the current setting for the MAXDATAFILES parameter.

Let’s, find out how many datafiles exist in the database:

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
3

I will create another 17 tablespaces with 17 datafiles using the below script:

SQL> select 'create tablespace drop_ts' || rownum ||
  2            ' datafile ''c:\testdb\data\drop_ts' || rownum ||
  3            '.dbf'' size 10M;' txt
  4    from all_objects where rownum <18;

TXT
-------------------------------------------------------------------------------
create tablespace drop_ts1 datafile 'c:\testdb\data\drop_ts1.dbf' size 10M;
create tablespace drop_ts2 datafile 'c:\testdb\data\drop_ts2.dbf' size 10M;
create tablespace drop_ts3 datafile 'c:\testdb\data\drop_ts3.dbf' size 10M;
create tablespace drop_ts4 datafile 'c:\testdb\data\drop_ts4.dbf' size 10M;
create tablespace drop_ts5 datafile 'c:\testdb\data\drop_ts5.dbf' size 10M;
create tablespace drop_ts6 datafile 'c:\testdb\data\drop_ts6.dbf' size 10M;
create tablespace drop_ts7 datafile 'c:\testdb\data\drop_ts7.dbf' size 10M;
create tablespace drop_ts8 datafile 'c:\testdb\data\drop_ts8.dbf' size 10M;
create tablespace drop_ts9 datafile 'c:\testdb\data\drop_ts9.dbf' size 10M;
create tablespace drop_ts10 datafile 'c:\testdb\data\drop_ts10.dbf' size 10M;
create tablespace drop_ts11 datafile 'c:\testdb\data\drop_ts11.dbf' size 10M;
create tablespace drop_ts12 datafile 'c:\testdb\data\drop_ts12.dbf' size 10M;
create tablespace drop_ts13 datafile 'c:\testdb\data\drop_ts13.dbf' size 10M;
create tablespace drop_ts14 datafile 'c:\testdb\data\drop_ts14.dbf' size 10M;
create tablespace drop_ts15 datafile 'c:\testdb\data\drop_ts15.dbf' size 10M;
create tablespace drop_ts16 datafile 'c:\testdb\data\drop_ts16.dbf' size 10M;
create tablespace drop_ts17 datafile 'c:\testdb\data\drop_ts17.dbf' size 10M;

17 rows selected.

SQL>

Querying the data dictionary shows that we have 20 datafiles in our database.

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
20

1 row selected.

Let us try to create one more tablespace and see what happens:

SQL> create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M;
create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

The command returns with an error indicating that we have already reached the maximum number of datafiles and it cannot create new datafiles anymore.

At this point, we have to shutdown the database, increase the value of DB_FILES initialization parameter, and start the database. Shut the database down:

SQL> shutdown immediate;

I modify the DB_FILES parameter in my PFILE to 25 (If you are using SPFILE then create a PFILE) and start the database:

SQL> startup pfile=c:\testdb\pfile\initTESTDB.ora

Query the DB_FILES initialization parameter after instance startup:

SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_files integer 25

SQL> select type, record_size, records_total, records_used
2 from v$controlfile_record_section
3 where type = 'DATAFILE';

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 20 20

Yeah, the new value is in effect. But querying the RECORDS_TOTAL column of V$CONTROLFILE_RECORD_SECTION view displays the maximum limit as 20.

Let us try to create a new tablespace with one datafile and see what happens.

SQL> create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M;

Tablespace created.

Wow, the tablespace is created. Lets query again V$CONTROLFILE_RECORD_SECTION view:

SQL> select type, records_total, records_used
2 from v$controlfile_record_section
3 where type = 'DATAFILE';

TYPE RECORDS_TOTAL RECORDS_USED
---------------------------- ------------- ------------
DATAFILE 52 21

The control file has automatically expanded to accommodate the new datafile information. The RECORDS_TOTAL column value has increased to 52 (20 original + 32 the default value) and the RECORDS_USED column is at 21 (meaning we have 21 datafiles in our database).

The message recorded in the alert.log is :

create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M
Tue Jul 03 12:46:15 2007
Expanded controlfile section 4 from 20 to 52 records
Requested to grow by 32 records; added 1 blocks of records
Completed: create tablespace drop_ts18 datafile 'c:\testdb\data\drop_ts18.dbf' size 10M

Note: Temp files associated with temporary tablespace do not account for MAXDATAFILES limit.

Regards