Managing Data Files (Oracle)

Use

To avoid exceeding the limit for the maximum number of files, you must manage the data files in an Oracle database.

Prerequisites

The following constraints limit the maximum number of data files in an Oracle database:

·        The db_files parameter in the init<SID>.ora file is usually set to the value of the maxdatafiles option of the create databasecommand. Currently, the SAP installation process sets maxdatafiles and db_files to 254. If you reach this limit, you can no longer add files to the database.

·        There is a UNIX kernel limit  for the maximum number of open files.

·        There is an absolute Oracle maximum of 65533 files in a database and usually 1022 files in a tablespace. However, certain hardware platforms have a limit lower than this absolute maximum.

Procedure

...

       1.      Monitor your system regularly for the number of data files using BRSPACE or the CCMS.

       2.      Create sufficiently large data files.

When creating data files, make sure that they are large enough to accommodate foreseeable growth in the tablespace. Otherwise, you have to repeat the procedure soon, and you might also eventually reach the limit.

       3.      If necessary to avoid reaching the limit, use BRSPACE to reorganize tablespaces.

If you are about to reach the lowest of the above limits and you decide to solve the problem by reorganizing a tablespace, you should use BRSPACE to identify tablespaces that consist of more than one data file. Then you can reorganize these so that the tablespaces are recreated with fewer data files, if possible with just one data file.

       4.      If you reach the limits specified in "Prerequisites" above, then do the following:

Ў        If you reach the limit for maxdatafiles and db_files, then do one of the following:

§         Increase the value of the db_files parameter, then shut down and restart the database.

§         Reorganize a tablespace consisting of several data files such that the number of files in use is reduced.

Ў        If you reach the UNIX kernel limit for the maximum number of open files, you have to change the relevant UNIX kernel parameter, then make sure that the change takes effect (such as shutting down and restarting the database, logging on again at UNIX level, restarting the UNIX system, and so on).

Ў        In the very unlikely event that you reach the hardware-dependent limit of files in the database (65533 files or less), then you have to perform a reorganization.

Result

You avoid exceeding the file limits for your Oracle database, so reducing downtime for the database and the SAP system.