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

Purpose: The purpose of this job is to monitor and react to any changes in the status of data files and temporary files in an Oracle database environment.
Why: This job is important because it ensures the availability and integrity of the database by monitoring the status of critical files. If these files go offline or need recovery, the system can take appropriate actions based on predefined parameters, thereby preventing potential data loss or downtime.
Manual Checking: For manual verification, you can query the Oracle dynamic performance views directly:

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:

Skipping over files that are temporarily quarantined
Raising alerts based on the severity level assigned to different file statuses

Dependencies and Side Effects

Dependencies: The system requires read access on v$datafile and v$tempfile views.
Side Effects: Any incorrect configuration or missed updates on the defined parameters could lead to unmonitored file statuses, potentially overlooking critical issues.

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.