Workflow- DB Tablespace Full
The DB Tablespace Full workflow is a triage and remediation workflow that is triggered by a "database tablespace full" event from BMC ProactiveNet. This event information is contained in PATROL, Abnormality, and Alarm event types.
This workflow applies to the following databases:
- Oracle 9i
- Oracle 10g
- Oracle 11g
When the BMC Atrium Orchestrator receives the "database tablespace full" event, the workflow runs a database select command to extract underlying data file names for the tablespace specified in the event slot. It then automatically selects the smallest data file to which the workflow must be adding space. The amount of space to be added is the increment size that you have defined in the DB_Tablespace_Full module configuration under the Triage and Remediation runbook (Datafile_Increment_Size_MB).
Remediation is performed only if enough space is available on the file system to extend the data file.
Configuration guidelines
To enable this workflow, you must configure the AutoPilot Credentials Store and the DB_Tablespace_Full configuration module under the Triage and Remediation runbook module on the BMC Atrium Orchestrator server side.
Ensure that the following credentials are added to the AutoPilot Credentials Store:
- Host systems where the database or databases reside
- The database credentials and the database instance that you want to connect to
The DB_Tablespace_Full configuration module contains the following definitions:
Group/Item | Description |
|---|---|
AO_Host | BMC AO host where the Configuration Distribution Peer (CDP) server resides. The ping attempts are launched from this server. |
WF_Detailed_Logging_Flag | Enables detailed logging of this specific workflow in the BMC ProactiveNet Performance Manager Operations Console. The valid values are use default, true, and false. The use defaultvalue applies the true or false value specified in the Detailed Logging File item under the Runbook_Defaults configuration folder that applies to all workflows. You can override this value by specifying its opposite value in the WF_Detailed_Logging_Flag item. |
Datafile_Increment_Size_MB | Size in megabytes by which the remediation process increases the tablespace size to correct the tablespace full condition The default value is 1 MB. |
You can find the action definitions for the DB Tablespace Full workflow in the installationDirectory\pw\server\etccellName\kb\bin\ ao_actions.mrl file.
An extract from the ao_actions.mrl file depicts the action definitions of this workflow for both the manual, on-demand launch and the automatic launch via a remote action policy.
action 'Atrium Orchestrator Actions'.'Triage and Remediate DB
Tablespace Full':
{ ['Administrator', 'Full Access', 'Data Collection Administrator',
'Event Administrator', 'Event Operator', 'Data Collection
Operator', 'Event Operator Supervisor', 'Data Collection
Supervisor'] }
[
'Create Change Request':MC_TRUEFALSE($CREATECHANGERQUEST),
'Change Request Type':MC_CHANGEREQUESTTYPE($CHANGEREQUESTTYPE),
'Create/Update Incident' : MC_TRUEFALSE($CREATEINCIDENT),
'Remediate' : MC_TRUEFALSE($REMEDIATE)
]
:EVENT($EV) where [ $EV.status != 'CLOSED' AND $EV.status
!= 'BLACKOUT']
#If you want this action to be context sensitive to PATROL Events monitoring Disk usage.
#Comment above line and uncomment below line.
#:PATROL_EV($EV)
{
action_requestor($UID,$PWD);
opadd($EV, "Triage and Remediate DB Tablespace Full manual", $UID);
admin_execute(BEMGW,$EV,"Atrium_Orchestrator_DB_Tablespace_Full_
Workflow",[$CREATECHANGERQUEST, $CHANGEREQUESTTYPE,
$CREATEINCIDENT, $REMEDIATE,$UID],YES);
}
END
END
#DB Tablespace Full Workflow - Automatic.
action 'Atrium Orchestrator Actions-Automatic'.'Triage and Remediate DB Tablespace Full':
{
['Administrator', 'Full Access', 'Data Collection Administrator', 'Event Administrator']
}
:EVENT($EV) where [ $EV.status != 'CLOSED' AND $EV.status != 'BLACKOUT']
{
opadd($EV, "Triage and Remediate DB Tablespace Full Auto", "BMC Impact Manager");
admin_execute(BEMGW,$EV,"Atrium_Orchestrator_DB_Tablespace_Full_Workflow",["true","normal","true","true","BMC Impact Manager"],YES);
}
END
Refine rules to validate DB Tablespace Full events
To help process the incoming events that alert users of a tablespace full condition, new refine rules have been added to the BMC ProactiveNet cell. These rules apply to PATROL, Alarm, and Abnormality events. Each refine rule is especially defined to accommodate the requirements of each event type.
PATROL_EV
where [$EV.status != 'CLOSED' AND
$EV.mc_object_class == 'ORACLE_TBSP_INSTANCE']
{
strmatch($EV.mc_object, '%s_%s',[$FLD1,$FLD2]);
$EV.mc_object = $FLD1;
$EV.mc_parameter = $FLD2;
}
END
Abnormality
where [$EV.status != 'CLOSED' AND
$EV.mc_object_class == 'Oracle Table-Space']
{
strmatch($EV.mc_object, '%s = %s, %s = %s, %s',[$FLD1,$FLD2,$FLD3,$FLD4, $FLD5]);
$EV.mc_object = $FLD2;
$EV.mc_parameter = $FLD4;
}
END
Alarm
where [$EV.status != 'CLOSED' AND
$EV.mc_object_class == 'ORACLE_TBSP_INSTANCE']
{ strmatch($EV.mc_object, '%s|%s|%s|%s',[$FLD1,$FLD2,$FLD3,$FLD4]);
$EV.mc_object = $FLD2;
strmatch($FLD4, $FLD2 || '_%s',[$FLD5]);
$EV.mc_parameter = $FLD5;
}
END
Before these new refine rules can take effect, you must recompile the Knowledge Base (KB) of the cell through the mccomp command and then restart the cell.
To enable the refine rules (if not already done), refer to step 5 under Configuring BMC ProactiveNet for the BMC ProactiveNet Performance Management Triage and Remediation Solution.
For more information about refine rules, see Refine-rules.
Launching the workflow
Launch the DB Tablespace Full workflow by selecting an appropriate tablespace full event in the Events Console of the BMC ProactiveNet Operations Console. You can also define a remote action policy for the tablespace full event to launch the DB Tablespace Full workflow automatically.
From the Events Console of the BMC ProactiveNet Operations Console, select the event, and choose the Tools > Remote Actions > Atrium Orchestrator Actions > Triage and Remediate DB Tablespace Full workflow entry. Then fill in the Execute Actions dialog box. See the following table to determine which input value to select for the DB Tablespace Full workflow.
Input parameter | Description |
|---|---|
Create Change Request | Boolean. True/false indicator that shows whether you want to create a change request in BMC Remedy Change Management System. If you choose false, the Change Request Type parameter is ignored. |
Change Request Type | String. Specifies the type of change request (normal/preapproved) |
Create/Update Incident | Boolean. True/false indicator that shows whether you want to create an incident in the incident management system. If an incident already exists, then workflow updates the existing incident information. |
Remediate | Boolean. True/false indicator that shows whether you want to proceed with the remediation action |
From the BMC ProactiveNet Administration Console, define a remote action policy under the Event Management Policies tab, following the procedure in Creating-remote-actions. Complete the policy definition by choosing the appropriate automatic workflow action for DB Tablespace Full.
Common framework: event processing
The BMC ProactiveNet event that triggers the DB Tablespace Full workflow contains the following parameters:
- Target host system where the database resides
- Database name
- Tablespace name
The following table describes the mapping of the main parameters to event definitions.
Event mapping: DB Tablespace Full
Parameter | Event slot |
|---|---|
Target host | mc_host |
Database name | mc_object |
Tablespace name | mc_parameter |
After extracting the configuration data from the event, the common framework determines the logging level for the workflow, sets the level at normal or detailed, and updates the event information in the Notes dialog box of the BMC ProactiveNet Operations Console accordingly.
Next, the AO server host tries to ping the target host system. If it receives a reply, the workflow continues. Otherwise, the workflow terminates.
As the workflow continues, it gathers PATROL annotation data if the event class is PATROL_EV. It adds the annotation details to the event notes. It then proceeds to start the triage process.
Triage processing
During triage processing, the workflow extracts the database credentials from Credential Store module and then establishes the connection to the database. Then it next finds the smallest data file in the tablespace. The workflow will extend the data file by the amount specified in the DB_Tablespace_Full module of the Triage and Remediation runbook.
Before proceeding to the remediation phase, the workflow verifies whether sufficient free space is available on the file system to extend the data file by the amount specified in the module configuration.
If the disk space is insufficient, the workflow generates a message indicating that there is not enough disk space to increase the table space size. Remediation is not performed. The event notes are updated accordingly.
Remediation processing
The workflow continues with remediation if sufficient disk space is available on the filesystem to extend the data file. During the remediation process, the workflow extracts the database credentials from the Credential Store module and launches an alter database SQL statement. The statement adds space to the data file that was specified during the triage phase. The workflow then updates the event log with the results.
In a BMC Remedy IT Service Management scenario, after the remediation has been performed and after the monitoring knowledge module detects that the tablespace is available again, it closes out the BMC ProactiveNet event. The workflow continues to check the event status for the configured amount of time. After the event is closed, BMC Atrium Orchestrator permits the associated task to close. The task closure results in the closure of the change ticket. After the change ticket is closed, BMC Atrium Orchestrator receives an alert from BMC Remedy ITSM, which triggers the closing of the incident.