oracle maxdatafiles,ORACLE MAXDATAFILES and db_files

_init.ora on VMS),

there is a parameter "DB_FILES". This is the limit on the total number of

files associated with a particular INSTANCE of a database. Since this

parameter can be changed simply by editing the init.ora and shutting down

and restarting the database, it is considered a "soft" limit.

COMMON QUESTIONS:

-----------------

1 - What are the default values of MAXDATAFILES ?

The default and the range of values of maxdatafiles and db_files are

operating system specific. Please refer to your operating system specific

Oracle manuals.

| 7.3.4 | 8.0.5 | 8.1.6 | 9.2 | 10.1 | 10.2

----------------------------------------------------------------

UNIX | 30 | 30 | 30 | 30 | 30 | 30

----------------------------------------------------------------

VMS | 32 | 32 | - | 32 | - | 30

----------------------------------------------------------------

Windows | 32 | 254 | 254 | 32 | 32 | -

----------------------------------------------------------------

2 - Why is there a limit on MAXDATAFILES?

Each platform uses a port-specific number of bits to store the ORACLE

file numbers. Thus, MAXDATAFILES is limited by this number.

Typical values are:

V7 8.0.5 8.1.6 9.2 10.1 10.2

-------- -------- ------ -------- -------- --------

UNIX Per TS 1022 1022 1022 1022 1022

Per DB 1022 65536 65536 65534 65334 65334

VMS Per TS 1022 - - - 1022

Per DB 1022 65536 - 65334

WINDOWS Per TS 1022 1022

Per DB 1022 65536 65536 65534 65334 65334

Notice that each value is 2^n - 2 for some n.

3 - Why would one set MAXDATAFILES to anything less than the port-specific

maximum?

Increasing the value of MAXDATAFILES increases the size of the

CONTROL FILE.

4 - Why would one set DB_FILES to anything less than MAXDATAFILES?

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.

5 - How can I determine my machine's maximum limit on MAXDATAFILES?

Check your ORACLE Installation and User's Guide.

The index should point to a port-specific limit.

6 - How can I determine where my CONTROL FILE(s) are?

In SVRMGR or SQL*PLUS depending on the version, type:

show parameter control_files;

If you have multiple control files, you may find that some of them may

be cut off in the output from show parameter. In this case, you can query

from V$CONTROLFILE;

7 - How To Set Maxdatafiles While Creating Database With Dbca.

You can change the Maxdatafiles value in the DBCA template which DBCA use

for DB creation.

COMMONS ERRORS : ORA-1118 and ORA-1165 :

-----------------------------------------

ORA-01118: cannot add any more database files: limit of 32 exceeded

ORA-01165 : MAXDATAFILES may not exceed when attempting to add

datafiles to the database

These erros means that your database has hit the

MAXDATAFILES limit, it is not a problem with the DB_FILES parameter,

because if it was, an ORA-59: 'maximum number of DB_FILES exceeded'

would arise.

These errors occurs because you have hit the hard limit for the number of data

files.

SOLVING ORA-1118 and ORA-1165 :

------------------------------

The following are possible options to get around these errors :

1. You should verify that you haven't reached a hard limit for MAXDATAFILES

imposed by the Operating System. This limit can be found in the O/S specific

documentation.

If MAXDATAFILES is already equal to the maximum value, then you must

restructure the database by reducing the number of datafiles associated with

each individual tablespace:

a) export the objects from the tablespace

b) drop the tablespace

c) recreate the tablespace with less datafiles

d) import the objects back into the tablespace

In some cases, if the datafile size is too small, it might be usefull to

resize the datafiles. Make sure that you keep the datafile size lower than

the Operating System limitation on file sizes :

> ALTER DATABASE DATAFILE 'D:\Oradata\Orcl\datafile\usr01.dbf' RESIZE 500M;

Query DBA_SEGMENTS to find out all objects belonging to a particular

tablespace.

2. Increase the MAXDATAFILES parameter. It is possible for you to increase

the MAXDATAFILES limit without recreating the entire database. This is done

by recreating the control file only.

3. Especially for Oracle8+ you should make sure that you do not encounter an

error against the maximum number of open database files (DB_FILES). It is

more likely that the value for DB_FILES is too low since the controlfile in

Oracle8 expands automatically as long as the number of the added datafile is

lower then the value for DB_FILES. Normally the error message should

indicate this:

ORA-00059 : maximum number of DB_FILES exceeded

4. Recreate the database. Since the MAXDATAFILES parameter is specified

upon database creation, recreating the database allows you to increase

this parameter.

In addition to your operating system documentation, you may want to

reference the following bulletins:

O7 - Creating an Oracle 7.X Database under Unix -

RECREATING THE CONTROL FILE:

----------------------------

In Oracle7 or higher, you can create the control file. In addition, you can get

Oracle to create the script for you. To do this, perform the following steps:

1. With the database mounted or open, issue the following commands:

SQL> alter database backup controlfile to trace;

SQL> exit

2. A trace file will have been generated in your 'user_dump_dest'.

User_dump_dest is an init.ora parameter, and can be found by

issuing:

SQL> show parameter user_dump_dest

The easiest way to locate the correct trace is to look at its date. A

file will exist with the current date and time. The naming convention

for these files is operating system specific.

3. Once the file is located, search through the file for the word "CONTROL"

You should find:

# The following commands will create a new control file and use it

# to open the database.

# No data other than log history will be lost. Additional logs may

ETC.ETC.

4. Copy this trace file to some location and rename it to end it ".sql",

for this example, it is called "recr_con.sql".

5. Edit the "recr_con.sql" deleting the trace header information. Then

increase the value that you find next to the word "MAXDATAFILES".

6. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).

7. Take a full database backup at this time.

8. Remove the current control files. It is essential to remove all control

files, otherwise, you will receive an error. In addition, you CANNOT

REUSE the control file, since the size of the control file will increase

when you increase MAXDATAFILES.

9. Create the controlfile within SQLDBA:

SQL> connect internal [if using sqldba or svrmgrl]

or

connect / as sysdba [if using sqlplus]

SQL> @recr_con.sql

SQL> alter database open noresetlogs;

If you receive a "Statement processed" message, then your database is

now back up and running with a higher datafile limit. It is recommended

to shutdown at this time and take a full backup.

CREATE CONTROLFILE SYNTAX:

-------------------------

The following is information on the create control file syntax, this

information is fully documented in the Oracle SQL Reference Manual.

The syntax of this command is similar to CREATE DATABASE.

The defaults for any missing clauses are the same as the

DATABASE defaults for CREATE DATABASE.

CREATE CONTROLFILE [REUSE]

SET DATABASE name

[LOGFILE filespec [, filespec] ...]

RESETLOGS | NORESETLOGS

[MAXLOGFILES integer]

[DATAFILE filespec [, filespec] ...]

[MAXDATAFILES integer]

[MAXINSTANCES integer]

[ARCHIVELOG | NOARCHIVELOG]

[SHARED | EXCLUSIVE]

Parameters:

REUSE: If present the control files may already exist. The

new control files will overwrite the existing files. If

this option is missing, the new control files must not

yet exist. As in CREATE DATABASE, the names of the

control files are determined by the init.ora parameter

control_files.

DATABASE: Must match the database names in the data and log

files.

LOGFILE: This clause lists all the online logs that will be

used for this database. If not specified the port

dependant defaults will be assumed. The interpretation

of the filespecs depends on the next parameter.

RESETLOGS: If this flag is present the current contents of

the online logs are ignored. The new control files will

contain flags requiring ALTER DATABASE OPEN RESETLOGS,

which initializes the logs. Media recovery may be

applied as needed before the open. Note that either

RESETLOGS or NORESETLOGS must be specified. It is safest

to choose RESETLOGS and follow it with normal media

recovery.

NORESETLOGS: If specified, the log files must be the current

online logs. They must not be restored backups, and all

log files must be listed. Their headers are read to

construct the control file entries. They are used for

recovery. If archiving is enabled all the online logs

must be archived, even if they were already archived.

The SIZE option in the filespecs, if present, will be

used to validate the size of the file named.

MAXLOGFILES: Same as for CREATE DATABASE. May be different

than the value in the original control file, but it may

not be smaller the maximum number of log files the

database ever contained - including ones that have been

dropped. Set it greater than or equal to the value used

at CREATE DATABASE time.

DATAFILE: To ensure proper behavior, all datafiles for the

database must be listed. It is possible to omit a non

system tablespace file only if media recovery is enabled

and you will not be doing an open reset logs on the first

open after the create controlfile. If the omitted

file(s) contain(s) active rollback segments, the open

will most likely fail, in which case the missing

datafile(s) must be found, and the controlfile recreated.

All datafiles listed must be accessible since they are

assumed to be online. They MAY be backup copies needing

recovery. Their headers are read to construct the

control file records. The SIZE option in the filespecs,

if present, is used to validate the size of the file

named. The reuse option is ignored. The next database

open validates that all the files are specified and that

the sizes match.

MAXDATAFILES: Same as for CREATE DATABASE. May be different

than the value in the original control file, but it may

not be smaller the maximum number of data files the

database ever contained - including ones that have been

dropped.

MAXINSTANCES: Same as for CREATE DATABASE. May be different

than the value in the original control file.

[NO]ARCHIVELOG: Same as for CREATE DATABASE. May be

different than the value in the original control file.

If you wish to archive logs, it is recommended that the

ARCHIVELOG option be used with CREATE CONTROLFILE even

though the option can later be enabled with an ALTER

DATABASE command. NOARCHIVELOG is the default.

SHARED: Same as for CREATE DATABASE.

EXCLUSIVE: Same as for CREATE DATABASE.

EXAMPLE:

-------

CREATE CONTROLFILE

SET DATABASE ORACLE

LOGFILE '/releases1/6036p/dbs/log2ORACLE.dbf',

'/releases1/6036p/dbs/log3ORACLE.dbf'

DATAFILE '/releases1/oracle/dbs/data_space.dbf',

'/releases1/6036p/dbs/usrORACLE.dbf'

MAXDATAFILES 121

NORESETLOGS;

References:

-----------

Oracle® Database Administrator's Reference

10g Release 2 (10.2) for hp OpenVMS

Oracle® Database Administrator's Reference

10g Release 2 (10.2) for UNIX-Based Operating Systems