change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically”

Demo


SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

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

datafiles exceeded, but no error!


SQL> alter database add logfile 
  ('/dbms/oracle/LSC75/redo/f1.sql',
  '/dbms/oracle/LSC75/redo/f2.sql')  size 16M;

Database altered.

SQL> select group#, members from v$log;
    GROUP#    MEMBERS
---------- ----------
         1          1
         2          1
         3          2

logfiles and logmembers exceeded, but no error!


SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
  COUNT(*)
----------
         2

instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?

3 thoughts on “change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime”

  1. It is always useful to go to Metalink!!

    Doc ID: Note:331067.1
    Subject: Maxdatafiles v/s Db_files. Add more data files than value of Maxdatafiles ?
    Type: HOWTO
    Creation Date: 12-AUG-2005
    Last Revision Date: 29-AUG-2005

    Applies to:
    Oracle Server – Enterprise Edition – Version: 8.1.7.0 to 10.2.0.0
    Information in this document applies to any platform.

    Goal
    This document discuss the behaviour of control file setting MAXDATAFILES and the database parameter DB_FILES in Oracle versions 8i and above.

    Prior to 8i, MAXDATAFILES was considered as the hard limit and DB_FILES was considered as the soft limit
    and DB_FILES parameter value is always recommended to be below the MAXDATAFILES setting.

    Whether we can add more datafiles to the database than the value of “MAXDATAFILES” ?
    Do we need to recreate the control file to have a higher value for “MAXDATAFILES” ?

    Solution
    In Oracle8i and higher, if the MAXDATAFILES limit is reached the controlfile will expand automatically.

    In Oracle 8i and higher, when you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to the value specified by the DB_FILES initialization parameter, the control file automatically expands to allow the datafile portion to accommodate more files.

    We can have DB_FILES set to more than the value of MAXDATAFILES.

    However note that these DB_FILES parameter value should be within the OS kernel limits.
    Refer to Note 144638.1 – Relationship Between Common Init.ora Parameters and Unix Kernel Parameters

    If you are adding datafiles to the database and is within the DB_FILES limit, you will get an error only if control file is unable to allocate more space.

    Recreating the control file is not required to increase the MAXDATAFILES parameter.

    It is not good to have a high DB_FILES parameter value ( much higher than required ). Increasing the value of DB_FILES increases the size of the PGA, or Program Global Area, which is allocated for every user process connected to ORACLE.

    References
    Note 144638.1 – Relationship Between Common Init.ora Parameters and Unix Kernel Parameters

  2. @Anonymous
    SQL> select * from v$version;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
    PL/SQL Release 10.2.0.4.0 – Production
    CORE 10.2.0.4.0 Production
    TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Productio
    NLSRTL Version 10.2.0.4.0 – Production

    SQL> show parameter db_files

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_files integer 6
    SQL> select records_total from v$controlfile_record_section where type = ‘DATAFILE';

    RECORDS_TOTAL
    ————-
    6

    1 row selected.

    SQL> alter tablespace users add datafile ‘/u04/oradata/ATAWX/RTEST/users02.dbf’ size 10M;

    Tablespace altered.

    SQL> alter tablespace users add datafile ‘/u04/oradata/ATAWX/RTEST/users03.dbf’ size 10M;

    Tablespace altered.

    SQL> select count(*) from dba_data_files;

    COUNT(*)
    ———-
    6

    1 row selected.

    SQL> alter tablespace users add datafile ‘/u04/oradata/ATAWX/RTEST/users04.dbf’ size 10M;
    alter tablespace users add datafile ‘/u04/oradata/ATAWX/RTEST/users04.dbf’ size 10M
    *
    ERROR at line 1:
    ORA-00059: maximum number of DB_FILES exceeded

  3. Indeed, DB_FILES still exists, but you do not need to recreate the controlfile.

    Just alter system set db_files=200 scope=spfile

    but you intended to answer a 6 years old anonymous comment?

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>