PDB status
Job details
Name: |
PDB status |
Platform: |
Oracle |
Category: |
Availability |
Description: |
Alerts if PDB is not in correct state |
Long description: |
Alerts if PDB is not in correct state |
Version: |
0.1 |
Default schedule: |
* * * * |
Requires engine install: |
Yes |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & is_containerdb=‘YES’] |
Parameters
Name |
Default value |
Description |
Correct state |
READ WRITE |
Job will send alerts if pluggable databases are not in this state |
Ignored pdbs |
PDB$SEED |
Comma separated list of pdbs that are ignored |
Type of alarm |
2 |
What alarm signal to send if PDB‘s are in wrong state. 2=Alarm, 1=Warning, 0=OK |
History max time |
365 |
How long should we keep data for, in days |
Job Summary
- Purpose: The purpose of this job is to monitor and alert if Oracle Pluggable Databases (PDBs) are not in the specified “Correct state,” typically “READ WRITE.”
- Why: This job is important as it ensures the availability and operational state of the Oracle PDBs. If PDBs are not in the required state, it might affect the overall performance and functionality of the database applications depending on these databases.
- Manual checking: You can check the current state of PDBs manually by issuing the following SQL command in an Oracle environment:
SELECT con_id, name, open_mode FROM sys.v$pdbs;
Description and Functionality
- The job targets instances that are marked as Oracle databases with active engines and configured as container databases.
- Alerts are generated based on the comparison between the actual state of PDBs and the desired state, “READ WRITE.” Other states will trigger alerts with severity depending on the configured alarm type.
- Specific PDBs can be ignored from monitoring based on the “Ignored pdbs” list, which by default includes the seed database (PDB$SEED).
- Historical data of PDB statuses are maintained in a custom table `pdb_status_history`, ensuring that history is kept for a specified duration, defaulting to 365 days.
Implementation Details
- A stored procedure named `pdb_status` is created which checks the state of each PDB.
- Database interactions including insertion of historical data and status updates are committed within this procedure.
- An additional cleanup task is conducted which removes historical entries older than the specified maximum days from the `pdb_status_history` table.
Dependencies and Concerns
- The job depends on two main components:
- “pdb_status” – A direct dependency where the output of the procedure is critical for ongoing status checks.
- There is an exceptional handling block within the procedure to catch and log any errors during the execution, ensuring that all exceptions are recorded and visible for further diagnostics.
Reporting and Alerts
- Reports generated by the `dbWatch Report template` focus on providing historical data when abnormal statuses were detected.
- The default schedule for these reports is once every hour, with the report containing details such as `pdb_name`, `pdb_id`, `state`, `alarm_status`, and `historical date`.
Operational Schedule
- The job is scheduled to run every minute, ensuring near real-time monitoring and alerting on the state of PDBs.
Version and Ownership
- Version: 0.1
- Company: dbwatch.com
This comprehensive monitoring setup ensures that any deviations from expected PDB states are quickly identified, reported, and, ideally, rectified, thus maintaining the integrity and performance of the Oracle database environment.