Max datafiles check

Job details

Name:	Max datafiles check
Platform:	Oracle
Category:	Availability
Description:	Checks the ‘soft limit’ and the ‘hard limit’ of the maximum number of physical OS files, that can be mapped to an Oracle instance.
Long description:	From Oracle 9i, an attempt to add a file whose number is greater than MAXDATAFILES (‘hard limit’), but less than or equal to DB_FILES (‘soft limit’), causes the Oracle control file to expand automatically. Within the ora parameter ‘db_files’ applies to the ‘soft limit’. The MAXDATAFILES value stored in the Oracle control files applies to the ‘hard limit’.
Version:	2.7
Default schedule:	15 5,17 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_v_controlfile_record_section = ‘1′ & _priv_read_v_parameter = ‘1′]

Name	Default value	Description
warning threshold “soft limit”	5	Warning threshold – how many data files can be allocated to the Oracle instance before the init ora parameter “db_files” value is reached (“db_file” is a static parameter).
warning threshold “hard limit”	5	Warning threshed – how many data files can be allocated to the Oracle instance before the MAXDATAFILES limit, stored in the Oracle control files, is reached. Your will have to recreate control files to change this limit.
alarm threshold “soft limit”	2	Alarm threshed – how many data files can be allocated to the Oracle instance before the init ora parameter “db_files” value is reached (“db_file” is a static parameter).
alarm threshold “hard limit”	2	Alarm threshed – how many data files can be allocated to the Oracle instance before the MAXDATAFILES limit, stored in the Oracle control files, is reached. Your will have to recreate control files to change this limit.

Purpose: The purpose of this job is to monitor the allocation of data files against predefined limits within an Oracle instance.
Why: This job is important as it ensures that the number of data files does not exceed the limits set at the database level, which are critical for managing database storage and avoiding potential outages or performance issues. Exceeding these limits can require significant effort to rectify, including reconstructing database control files.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT count(*) FROM v$datafile;

SELECT records_total FROM v$controlfile_record_section WHERE type = 'DATAFILE';

SELECT value FROM v$parameter WHERE name = 'db_files';

The monitoring job involves a procedure called “max_files_check” that verifies the number of data files currently allocated against the maximum thresholds (‘soft limit’ derived from the ‘db_files’ parameter and ‘hard limit’ from MAXDATAFILES) configured in the Oracle control files.
The procedure dynamically gathers data file counts and compares them against warning and alarm thresholds set for both the ‘soft’ and ‘hard’ limits.
If file counts near or reach these thresholds, an appropriate status is reported back through dbWatch Control Center mechanisms, potentially triggering alert conditions.

The job raises warnings or alarms when the difference between the maximum configured data files and the current count approaches the defined thresholds:
- A warning is issued if the count is within five files from the soft or hard limit.
- An alarm is triggered if the count is within two files from these limits.

This job relies on access privileges to dynamic performance views like v$datafile, v$controlfile_record_section, and v$parameter.
The monitoring job runs twice daily at 5 and 17 hours by default, which is set in the job’s scheduling configurations.

A dbWatch report template is included with the job definition that provides outputs like:
- Current configuration values for ‘db_files’ (soft limit) and MAXDATAFILES (hard limit).
- Count of different file types, like data files and temporary files.

Configuration	Value
db_files (init ora parameter)	200
maxdatafiles (stored in control files)	250

Type	File Count
Data files	160
Temporary files	20

This job is crucial for maintaining the integrity and performance of Oracle databases by ensuring that they operate within the file count limits without needing manual intervention, provided all conditions for automatic monitoring and checks are met.