PRF - AWR Performance Analysis Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED) 
Spec: Jira issue: host, ORACLE_SID/PDB, and start/end date-time fields
Jira-driven, dispatcher + per-issue job include, matching the ServiceNow project's structure. Includes documented risk-mitigation and best-practice controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
Best Practice
Close OK
Close Failed
Follow-up
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
PRE: shepherd PRF intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate, even though this analysis itself is read-only
|
| 3 |
PRE: search Jira for PRF issues ready to execute
|
| 4 |
PRE: collect issues found
|
| 5 |
PRE: end play when queue is empty
|
| 6 |
PRE: build job list from Jira issues
|
| 7 |
PRE: WARN about issues with an unusable spec
|
| 8 |
PRE: keep only jobs with a usable spec
|
| 9 |
PRE: nothing valid to process
|
| 10 |
PRE: jobs queued
|
| → |
Unknown host — skip this issue
|
| 11 |
JOB: resolve Oracle home/user from oracle_instance_map
|
| 12 |
JOB: set per-job facts
|
| 13 |
JOB: comment: analysis started
|
| 14 |
JOB: ensure base + per-job working directory exists and is writable
|
| 15 |
JOB: write snapshot-resolution SQL + wrapper (explicit ORACLE_SID export)
|
| 16 |
JOB: resolve snapshot IDs as oracle
|
| 17 |
JOB: fail cleanly if no bracketing snapshots were found
|
| → |
Risk mitigation: failing cleanly with a clear reason (outside retention window, no snapshots yet) rather than a confusing downstream error
|
| 18 |
JOB: show resolved snapshot range
|
| 19 |
JOB: write AWR report SQL + top-5 CPU SQL_ID query
|
| 20 |
JOB: write execution wrapper (explicit ORACLE_SID export)
|
| 21 |
JOB: run AWR report + top-5 query as oracle
|
| 22 |
JOB: parse top-5 SQL_IDs from output
|
| 23 |
JOB: determine whether top-5 extraction succeeded
|
| 24 |
JOB: show top SQL_IDs found
|
| 25 |
JOB: write and run SQL Tuning Advisor for each SQL_ID (ALTER SESSION SET CONTAINER first)
|
| → |
Risk mitigation: execute_tuning_task only produces advice — this entire analysis carries zero write risk to production data
|
| 26 |
JOB: attach AWR report + each tuning report to the issue
|
| 27 |
JOB: post analysis summary comment
|
| 28 |
JOB: get available transitions for DONE
|
| 29 |
JOB: transition to 'Done'
|
| 30 |
JOB: get available transitions for FAILED
|
| 31 |
JOB: transition to 'Failed'
|
| 32 |
JOB: open follow-up task for the DBA team to implement fixes
|
| → |
Best practice: any recommended fix is routed through a separate follow-up task, never applied automatically — analysis and implementation stay cleanly separated
|
| 33 |
JOB: open DBA repair follow-up on total analysis failure
|
| 34 |
JOB: clean up temp files
|
PDB - Create Pluggable Database Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: target host, container DB, and new PDB name fields
Jira-driven, 3-play structure matching the ServiceNow project's shape. Includes documented risk-mitigation and best-practice controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
Best Practice
Close OK
Close Failed
Follow-up
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
0: shepherd PDB intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's To Do → Pending Approval → In Progress walk is itself a real ITIL change-model gate — genuinely cannot proceed unapproved
|
| 3 |
1: search Jira for PDB issues ready to execute
|
| 4 |
1: collect issues found
|
| 5 |
1: intake summary
|
| 6 |
1: exit cleanly when there is nothing to do
|
| 7 |
1: WARN and skip issues with an unusable spec
|
| → |
Risk mitigation: validating host, container, and PDB-name fields before any database work begins prevents a doomed run from ever reaching the Oracle host
|
| 8 |
1: build the PDB creation job list
|
| 9 |
1: nothing valid to process
|
| 10 |
1: jobs queued
|
| 11 |
1: write job list for PLAY 2
|
| 12 |
1: remove stale outcomes file from any previous run
|
| 13 |
2: slurp + decode the job list from the controller
|
| 14 |
2: capture container state, OMF setting and seed datafile path
|
| 15 |
2: fold the pre-check results into each job
|
| 16 |
2: show what the pre-check found
|
| 17 |
3: generate a PDB admin password for each job
|
| → |
Best practice: a fresh generated admin password per job, never touching Ansible logs (no_log)
|
| 18 |
3: create the datafile directory when OMF is not in use
|
| 19 |
3: create, open and save the state of the pluggable database
|
| 20 |
4: capture v$pdbs after the operation
|
| 21 |
4: determine the outcome for each issue
|
| 22 |
4: per-issue outcome
|
| 23 |
5: ensure the log directory exists on controller
|
| 24 |
5: write the evidence log for each issue (admin password intentionally omitted)
|
| → |
Risk mitigation: the evidence log deliberately excludes the admin password — it's disclosed exactly once, in a dedicated Jira comment, never written to a log file
|
| 25 |
5: write outcomes + job data for PLAY 3
|
| 26 |
6: slurp + decode outcomes from PLAY 2
|
| 27 |
6: attach the evidence log to each issue
|
| 28 |
7: post the result comment (admin password goes ONLY here)
|
| 29 |
8: get available transitions for DONE issues
|
| 30 |
8: transition DONE issues to 'Done'
|
| 31 |
8: get available transitions for FAILED issues
|
| 32 |
8: transition FAILED issues to 'Failed'
|
| 33 |
9: create DBA follow-up task for FAILED issues
|
| 34 |
10: final summary
|
CAU - Create Application User Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: reporter + target databases field
Jira-driven, single-play. Uses CAU_jira_create_user.yml (the confirmed-correct version). Includes documented risk-mitigation and best-practice controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
Best Practice
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
0: shepherd CAU intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate — the request genuinely cannot proceed until approved, not just a label
|
| 3 |
1: search for open Jira issues matching JQL
|
| → |
End play gracefully when no issues are found
|
| 4 |
2: fetch full details for each issue
|
| → |
Risk mitigation: WARN on issues missing the user field rather than silently skipping — a form field ID change becomes visible, not a silent no-op
|
| 5 |
2: build issue → user + target databases mapping
|
| → |
Warn on issues with no recognised values in the databases field
|
| 6 |
2: show user → Oracle username → target databases mapping
|
| 7 |
3: generate random password for each issue
|
| → |
Best practice: a single generated 16-character password replaces any hand-typed credential, and never touches Ansible logs (no_log)
|
| 8 |
3: attach generated password to each issue entry
|
| 9 |
4: create Oracle user and grant privileges in each target database
|
| 10 |
4b: show create-user outcome per issue per database
|
| 11 |
5: write worklog payload JSON to a temp file
|
| 12 |
5a: write worklog-posting Python script to a temp file
|
| 13 |
5b: execute the worklog-posting script against the payload file
|
CDP - Change Database Password Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: reporter's own account, target databases from project config
Jira-driven, single-play. Shepherd advances issue toward In Progress before this runs. Includes documented risk-mitigation and best-practice controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
Best Practice
Close OK
Close Failed
Follow-up
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
0: shepherd CDP intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's To Do → Pending Approval → In Progress walk is itself a real ITIL change-model gate — the issue genuinely cannot proceed until every required approval is granted
|
| 3 |
1: search for open Jira issues matching JQL
|
| → |
Abort if no issues found
|
| 4 |
2: fetch full details for each issue
|
| 5 |
2: build issue → reporter mapping
|
| 6 |
3: generate random password for each Oracle user
|
| 7 |
3: attach generated password to each issue entry
|
| 8 |
4: reset Oracle password for each user in each database
|
| 9 |
4b: show password-reset outcome per issue per database
|
| 10 |
5: post new temporary password as Jira comment (all databases succeeded)
|
| → |
Best practice: the new password is disclosed only once ALL environments confirm success — a partial failure never leaves the requester with a password that only works in some databases
|
| 11 |
6: mark issues DONE (all databases succeeded)
|
| 12 |
6: mark issues FAILED (one or more databases failed)
|
| 13 |
6: get available transitions for SUCCESSFUL issues
|
| 14 |
6: transition SUCCESSFUL issues to 'Done'
|
| → |
WARN when no 'Done' transition is available
|
| 15 |
6: get available transitions for FAILED issues
|
| 16 |
6: transition FAILED issues to 'Failed'
|
| → |
Risk mitigation: case-insensitive, uri-based transition lookup avoids a silent no-op if the workflow's exact status label ever changes
|
| 17 |
6: create DBA follow-up task for FAILED issues
|
DCR - Run Data Change Script Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue + .sql attachment(s)
Jira-driven, single-play. Includes documented risk-mitigation and best-practice controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
0: create timestamped working directory
|
| 3 |
0: shepherd DCR intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate before any script ever executes
|
| 4 |
1: search for open Jira issues matching JQL
|
| → |
Abort if no issues found
|
| 5 |
2: fetch full details for each issue
|
| 6 |
3: create per-issue subdirectory in work_dir
|
| 7 |
4: download .sql attachments from Jira (case-insensitive)
|
| 8 |
4b: rename downloaded .sql attachments to .ssh
|
| 9 |
5: build list of successfully downloaded scripts
|
| → |
Warn if no scripts were downloaded
|
| 10 |
5b: insert connection header at top of each script
|
| 11 |
5c: insert the shell heading wrapper on the SQL scripts loop
|
| 12 |
5d: append EOF footer at bottom of each script
|
| 13 |
6: execute shell scripts
|
| 14 |
6a: write execution output to .log file
|
| 15 |
6b: build stdout tail lookup (last 10 non-empty lines per script)
|
| 16 |
6c: read log file for each executed script
|
| 17 |
6c: extract and count error lines from each log
|
| → |
Risk mitigation: scanning execution logs for error patterns rather than trusting exit codes alone catches partial or silent failures
|
| 18 |
6d: show detailed error report for failed scripts
|
| 19 |
7: mark issue as FAILED if any script returned non-zero
|
| 20 |
7: mark issue as DONE if not already marked failed
|
| 21 |
7: show final per-issue outcomes
|
| 22 |
7b: post worklog summary per issue
|
| 23 |
8: get available transitions for SUCCESSFUL issues
|
DDP - Drop / Decommission Pluggable Database Workflow (Jira, SIMPLIFIED)
Spec: Jira issue: target PDB field + confirmation field
⚠ Known issue: the shepherd call below uses the wrong variable name (shepherd_project_key instead of shepherd_project) and an older search API pattern than the other 6 Jira projects — documented here as it actually behaves today. Because of this bug, the ITIL change-model gate claimed on the other 6 Jira workflows is NOT reliably true here and is deliberately not claimed below. Includes documented risk-mitigation and best-practice controls for the parts of the workflow that ARE working as intended
|
|
Jira
Process
Decision
SQL/Execute
Risk Mitigation
Best Practice
Close OK
Close Failed
Follow-up
|
| 1 |
Pre-flight: verify Jira API token is valid
|
| 2 |
Shepherd DDP intake queue before searching for work — uses shepherd_project_key (inconsistent with the other 6 projects' shepherd_project)
|
| 3 |
1: search Jira for DDP issues ready to execute
|
| 4 |
Build list of drop requests
|
| → |
No DDP work found — end run
|
| 5 |
2: extract target PDB name from issue field
|
| → |
WARNING: target field empty or unrecognized, dump populated fields
|
| 6 |
Normalize PROD alias to PRO key
|
| 7 |
Block drop of a protected PDB unless explicitly overridden
|
| 8 |
Block drop if confirmation string is missing or mismatched
|
| → |
Risk mitigation: a literal confirmation phrase plus a protected-name blocklist together prevent an accidental or automated drop of the wrong or a protected database
|
| → |
Handle blocked issue — comment, transition to Failed, open DBA follow-up
|
| 9 |
Ensure working directory exists
|
| 10 |
Write pre-drop snapshot SQL — captured and attached before anything destructive
|
| → |
Best practice: capturing a full pre-drop snapshot (DBA_PDBS, tablespaces, datafiles) before an irreversible operation preserves evidence of exactly what existed beforehand
|
| 11 |
Write drop SQL — close then drop including datafiles
|
| 12 |
Write execution wrapper script (explicit ORACLE_SID export — sudo strips env)
|
| 13 |
Execute snapshot + drop as oracle user
|
| 14 |
Read drop log for success/failure parsing
|
| 15 |
Determine outcome (rc==0 and no 'ORA-' in drop log)
|
| → |
Risk mitigation: scanning the actual log content for error patterns, not just the shell exit code, catches a drop that partially failed but still returned success
|
| 16 |
Attach snapshot and drop logs to Jira issue
|
| 17 |
Post worklog summary
|
| 18 |
Transition to Done (case-insensitive, uri-based)
|
| 19 |
Transition to Failed (case-insensitive, uri-based)
|
| 20 |
Open DBA follow-up task on failed drop
|
PAT - Apply Oracle Patch Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: host:SID + patch number
Jira-driven, dispatcher + per-issue job include, real Oracle MOS download logic matching the SNOW project. Includes documented risk-mitigation, best-practice and cost-containment controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Cost Containment
Risk Mitigation
Best Practice
|
| 1 |
PRE-FLIGHT: validate Jira API token
|
| 2 |
0: shepherd PAT intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate before any patch work ever begins
|
| 3 |
1: search for open PAT issues
|
| → |
End play gracefully when queue is empty
|
| 4 |
2: fetch full details for each issue
|
| → |
WARN and skip issues with a missing or unknown patch target
|
| 5 |
2: build patch job list from issue details
|
| 6 |
3: write patch job list to temp file for PLAY 2
|
| 7 |
3: write Jira credential sidecar for worklog script
|
| 8 |
JOB: run opatch lsinventory (pre-patch)
|
| 9 |
JOB: flag if patch is already applied (skip patching)
|
| → |
Best practice: checking whether the patch is already applied avoids a redundant, risk-bearing re-application against a system already in the target state
|
| 10 |
JOB: check current OPatch version vs. minimum required (from patch README)
|
| 11 |
JOB: download OPatch (patch 6880880) from MOS if upgrade needed
|
| 12 |
JOB: copy and install new OPatch into ORACLE_HOME
|
| 13 |
JOB: stop listener (if running)
|
| 14 |
JOB: shut down Oracle database
|
| → |
Risk mitigation: refusing to proceed on an unclean shutdown prevents applying a binary patch underneath a database that hasn't fully released its files
|
| 15 |
JOB: kill any remaining oracle processes using ORACLE_HOME (CheckActiveFilesAndExecutables)
|
| 16 |
JOB: ensure + verify patch directory is readable by oracle
|
| 17 |
JOB: apply patch with opatch
|
| 18 |
JOB: start Oracle database, start listener
|
| 19 |
JOB: run opatch lsinventory (post-patch, confirm patch listed)
|
| → |
Risk mitigation: confirming the patch actually appears in the inventory after applying it, rather than trusting a clean exit code alone
|
| 20 |
JOB: determine overall job outcome
|
| 21 |
JOB: remove patch zip and extracted directory
|
| → |
Cost containment: removing the downloaded patch archive and staging files immediately after use avoids accumulating unnecessary disk consumption across repeated patch cycles
|
| 22 |
JOB: write + merge per-issue outcome into combined outcomes file
|
| 23 |
JOB: write worklog-posting script to controller
|
REF - Environment Refresh Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: refresh spec field (source/target service and schema)
Jira-driven, dispatcher + per-issue job include, matching the SNOW project's structure. Includes documented risk-mitigation, best-practice and cost-containment controls
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Cost Containment
Risk Mitigation
Best Practice
|
| 1 |
PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
|
| 2 |
PRE-FLIGHT: remove stale outcomes file from any aborted previous run
|
| 3 |
0: shepherd REF intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate before any schema operation ever runs
|
| 4 |
1: search for open Jira refresh issues matching JQL
|
| → |
End play gracefully when no issues are found
|
| 5 |
2: fetch full details for each issue
|
| → |
Warn and skip issues with a missing or invalid refresh spec
|
| 6 |
2: parse refresh spec from the configured Jira field
|
| 7 |
JOB: set per-job file names + full paths
|
| 8 |
JOB: pre-flight confirm source schema exists — fail if not
|
| → |
Risk mitigation: confirming the source schema exists before touching anything avoids a half-completed export with nowhere to land
|
| 9 |
JOB: export source schema — fail if export failed
|
| 10 |
JOB: drop target schema (idempotent)
|
| → |
Best practice: the drop is written to be idempotent, skipping cleanly if the target schema doesn't already exist, rather than erroring on a fresh target
|
| 11 |
JOB: import target schema
|
| 12 |
JOB: count objects in target schema
|
| → |
Risk mitigation: counting imported objects verifies the refresh actually landed data, rather than trusting a clean import exit code alone
|
| 13 |
JOB: post export/import results to Jira worklog
|
| 14 |
JOB: remove dump and log files (success only)
|
| → |
Cost containment: removing the export dump file after a successful refresh avoids accumulating storage from repeated refresh cycles
|
| 15 |
JOB: ensure follow-up log dir exists on controller (failed only)
|
| 16 |
JOB: preserve export/import output on controller (failed only)
|
| 17 |
JOB: write outcome to controller temp file
|
UPG - Oracle 21c → 26ai Upgrade Workflow (Jira, SIMPLIFIED, ITIL-ALIGNED)
Spec: Jira issue: host + ORACLE_SID
Jira-driven, real AutoUpgrade analyze/deploy/verify/rescue logic matching the SNOW project. Includes documented risk-mitigation and best-practice controls. No formal cost-approval gate in this version — that mechanism is ServiceNow-only
|
|
Jira
Process
Decision
SQL/Execute
ITIL Alignment
Risk Mitigation
Best Practice
Follow-up
|
| 1 |
PRE: verify Jira API token is valid
|
| 2 |
PRE: check 26ai Oracle home exists on target host
|
| 3 |
PRE: check autoupgrade.jar exists in 26ai home
|
| → |
Risk mitigation: validating the target software exists before any change work begins prevents a doomed run from ever reaching production data
|
| 4 |
0: shepherd UPG intake queue toward 'In Progress'
|
| → |
ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate — a genuinely high-risk operation like this cannot proceed unapproved
|
| 5 |
PRE: search for open UPG issues
|
| 6 |
PRE: build upgrade job list from issues
|
| → |
Nothing to do? ends here
|
| 7 |
PRE: upgrade jobs queued
|
| 8 |
JOB: get available transitions, transition to 'In Progress' (if available)
|
| 9 |
JOB: worklog: upgrade started
|
| 10 |
JOB: check source DB version and state
|
| 11 |
JOB: assert source is 21c and OPEN
|
| 12 |
JOB: create job directories, write autoupgrade.cfg
|
| 13 |
JOB: run AutoUpgrade analyze phase (pre-upgrade check)
|
| 14 |
JOB: assert analyze passed
|
| → |
Risk mitigation: the ANALYZE phase surfaces upgrade-blocking issues before any irreversible change is attempted
|
| 15 |
JOB: worklog: analyze complete
|
| 16 |
JOB: launch AutoUpgrade deploy (async)
|
| 17 |
JOB: wait for deploy to finish
|
| 18 |
JOB: assert deploy succeeded
|
| 19 |
JOB: restart database under 26ai home and verify
|
| 20 |
JOB: assert database is on 26ai and OPEN
|
| 21 |
JOB: ensure /etc/oratab points at 26ai home
|
| 22 |
JOB: worklog: upgrade complete
|
| → |
Best practice: the Guaranteed Restore Point created earlier is deliberately retained after success, not dropped immediately — a real rollback path stays available
|
| 23 |
JOB: get transitions for Done, transition to 'Done'
|
| → |
Any assertion failing: JOB: classify failure
|
| → |
Best practice: failure classification distinguishes a genuinely new failure from stale recovery data left behind by an earlier interrupted run, avoiding a misleading diagnosis
|
| 24 |
JOB: worklog: upgrade failed, DBA review required
|
| 25 |
JOB: transition to 'Failed'
|
| 26 |
JOB: write failure report for follow-up
|
| 27 |
JOB: fetch AutoUpgrade status.log from host
|
| 28 |
JOB: create DBA repair follow-up task
|
| 29 |
JOB: clean up temp scripts
|
|
