Categories
Blogroll dba

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?

By Laurent Schneider

Oracle Certified Master

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

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

@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

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.