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.