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
datafiles exceeded, but no error!
logfiles and logmembers exceeded, but no error!
instances exceeded, but no error!
so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?
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?
Posted by Laurent Schneider at 1/19/2006 03:37:00 PM
Permalink : change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
1 Comments:
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
Post a Comment
<< Home