Datafile status
Job details
| Name: |
Datafile status |
| 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: |
|
| Version: |
1.0 |
| Default schedule: |
10m |
| Requires engine install: |
No |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’] |
Parameters
| Name |
Default value |
Description |
| return_status_offline |
1 |
Alarm or warning status if datafile is offline. 0=OK, 1=Warning, 2=Alarm |
| return_status_recovery |
2 |
Alarm or warning status if datafile needs recovery. 0=OK, 1=Warning, 2=Alarm |
Job Summary
- Purpose: The purpose of this job is to check the operational status of Oracle database datafiles, verifying whether they are online, offline, or require recovery.
- Why: This job is important as it ensures data integrity and availability in an Oracle Database System. If datafiles are offline or require recovery, it could potentially disrupt database operations and affect system performance adversely.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT d.File#, d.name, t.name, d.status FROM v$datafile d, v$tablespace t WHERE d.ts#=t.ts#
Job Details
- Job Name: Oracle Datafile status
- Version: 1.0
- Company: dbwatch.com
- Category: Availability
- Description: Performs checks on Oracle datafiles to verify their current operating status and ensure their availability.
Technical Execution
- Monitoring Approach:
- Utilizes SQL queries against Oracle system views `v$datafile` and `v$tablespace`.
- Validates access privileges with hidden properties using specified compatibility criteria.
- Importance:
- Ensures that the Oracle datafiles are operational and flags any that are not, by assigning different statuses for various conditions such as “offline” and “needs recovery”.
- Helps in proactive maintenance and avoiding potential downtime by timely addressing issues related to datafiles.
Compatibility and Installation
- This job is exclusively designed for Oracle database instances and checks for compatibility with versions where datafiles and tablespaces are relevant.
- Automatically installed if the instance type matches Oracle and there are no conditions contradicting its execution (like being a physical standby database or lacking specific engines).
Scheduling and Dependencies
- Default Schedule: Every 10 minutes
- Dependencies: Relies on the database instance running Oracle and possessing necessary privileges to read from `v$datafile` and `v$tablespace`.
Reporting and Output
- Output Analysis:
- Utilizes a mixture of SQL and JavaScript to analyze the status of each datafile.
- Reports the total number of datafiles checked, and lists any with status issues, providing detailed messages about the nature of the problem.
- Sets job status based on the severity of the findings, with specific triggers for warnings or alarms.
| Status Key |
Information |
| Alarm (2) |
Datafile needs recovery or has another critical issue |
| Warning (1) |
Datafile is offline |
| OK (0) |
All datafiles are operational and have no identified issues |
- Report Template:
- Version: 2
- Title: Datafile status
- Description: Provides detailed outputs on the state of each datafile within the database, enhancing the visibility of potential issues that might affect database reliability or performance.