File status check


Job details

Name: File status check
Platform: Oracle
Category: Availability
Description: Reacts on any changes in status of data files and/or temporary files.
Long description: Task reacts on any changes on the status of data files and/or temporary files.
Version: 3.2
Default schedule: * * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_v_datafile = ‘1′ & _priv_read_v_tempfile = ‘1′]

Parameters

Name Default value Description
ignore files 0 file ID excluded from being checked (comma separated). To avoid file ID duplicates, the file ID for temporary files must be specified with a minus sign in front of the file ID.
return status when file is OFFLINE 1 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when file(s) have status “OFFLINE“ (and are not in “ignore files” list).
return status when file needs RECOVERY 2 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when file(s) have status “RECOVER“ (and are not in “ignore files” list).
quarantine date 2023.01.01 12:00
quarantine files 0 File IDs list (file#) to be ignored if having status OFFLINE or RECOVERY and having checkpoint time within “quarantine time” value.
quarantine date format yyyy.mm.dd hh24:mi Date format used by “quarantine date” parameter.

Job Summary

SELECT FILE#, STATUS FROM v$datafile UNION SELECT FILE#, STATUS FROM v$tempfile;

Detailed Processing Logic

The job definition includes a procedure that checks the status of each data file and temporary file. It considers various parameters like “ignore files,” “return status when file is offline,” “return status when file needs recovery,” “quarantine files,” and associated quarantine dates, using these to determine the overall health status of the files. If a file’s status changes and does not meet the ignore or quarantine criteria, it gets flagged, affecting the database’s health status.

Implementation

This job is implemented through a stored procedure named file_status_check within the database. This procedure assesses each file’s status by scanning through all listed data and temporary files in Oracle’s dynamic performance views (v$datafile and v$tempfile). Specific file IDs can be ignored or quarantined based on the configuration parameters.

Parameters and Configuration

The procedure utilizes several configuration parameters to allow flexibility in how file statuses affect the overall database health check. These parameters help determine how to handle different scenarios when a file is offline or needs to be recovered, including:

Dependencies and Side Effects

Reports and Dashboard

After each execution, the results are made available through a report detailing the status of each file, including additional attributes like file size and type which are retrieved and compiled for quick reference. The report serves as a summary dashboard for the database administrator to monitor file health across both data and temporary Oracle files.

File ID File Type Status Size (MB) Table Space Name Autoextensible Max Size (GB)
file# DATA or TEMP ONLINE, OFFLINE, RECOVER etc. File size in MB Tied table space Yes/No Max possible size

Conclusion

This monitoring job is crucial for maintaining the health and performance of the Oracle database by proactive monitoring and addressing potential issues with data and temporary files, ensuring high availability and minimal service disruption.