ServiceNow Oracle Automation Workflows

← Back to Technology  |  View the Jira workflows →

The following are simplified automation workflows coded in Ansible YML for the ServiceNow implementation.

REF - Environment Refresh Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: REF | SOURCE:TARGET  (legacy: SRC_SVC:SRC_SCHEMA=>TGT_SVC:TGT_SCHEMA)
3-play structure: PLAY1/PLAY3 run on the controller, PLAY2 runs directly on the Oracle host. Includes ITIL change classification and documented risk-mitigation controls
Start Process ServiceNow Decision SQL/Execute ITIL Alignment Cost Containment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 PLAY1: fetch changes, parse refresh spec
2 PRE-FLIGHT: remove stale outcomes file from any aborted previous run
3 0-1: ServiceNow intake and shepherd for REF (standard, NO APPROVAL)
4 1: show 'No Tasks Found' and exit cleanly when queue is empty
→ WARN: missing or invalid refresh spec — skipped
5 2: parse refresh spec (legacy vs new format, service aliases)
6 2: show parsed refresh jobs
7 2: write refresh job list to temp file for PLAY 2
8 PLAY2-INIT: read + decode refresh job list
8b ITIL alignment: classify this refresh — link the CMDB Configuration Item to the target service, set risk=Low/impact=Low, and record a planned 4-hour change window
9 PRE-FLIGHT: confirm expdp binary exists — fail if not
10 PRE-FLIGHT: confirm impdp binary exists — fail if not
11 PRE-FLIGHT: confirm DATA_PUMP_DIR exists and is writable
→ Risk mitigation: confirming the tools and destination directory exist before touching any schema avoids a half-completed export with nowhere to land
12 JOB: set per-job file names + full paths
13 JOB: confirm source schema exists — fail if not
14 JOB: export source schema — fail if export failed
15 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
16 JOB: import target schema
17 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
18 JOB: post export/import results as work note
19 JOB: remove dump + log files (success only)
→ Cost containment: removing the export dump file after a successful refresh avoids accumulating storage from repeated refresh cycles
20 JOB: preserve export/import output on controller (failed only)
21 JOB: write outcome to controller temp file
22 PLAY3-INIT: read + decode job outcomes
23 7: show outcome per issue
24 8: move SUCCESSFUL to review, Close SUCCESSFUL
25 8: work-note FAILED changes with failure summary
26 8: close FAILED changes (close_code unsuccessful)
27 8b: create DBA repair follow-up change
28 9: print final summary + clean up temp files
PDB - Create Pluggable Database Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: PDB | host | CDB_NAME | NEW_PDB
MANAGEMENT approval required. Includes ITIL change classification and documented risk-mitigation controls
Start Process ServiceNow Decision Approval SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 0: pre-flight: verify instance awake + credentials
2 0: abort with clear message if unreachable
3 0: query change requests tagged 'PDB |'
4 0: collect tagged change requests
5 0: intake summary
6 0: exit cleanly when there is nothing to do
→ WARN: unusable spec — skipped
7 1: build the PDB creation job list
→ Nothing valid to process — ends here
8 1: jobs queued
8b ITIL alignment: classify each job — link the CMDB Configuration Item to the target CDB, set risk=Medium/impact=Low, and record a planned 1-hour change window
9 2: new change: request approval (state=assess)
10 2: work note: awaiting approval
11 2: advance: Assess → Authorize
12 2: advance: Authorize → Scheduled
13 2: advance: Scheduled → Implement
→ WARN: if a change could not be advanced
14 2: re-query for CRs genuinely at Implement now
15 2: filter pdb_jobs to only genuinely-implemented ones
→ Risk mitigation: re-querying live state rather than trusting a cached approval status prevents creating a PDB against a request that isn't genuinely approved yet
→ Nothing ready this pass (still pending approval)
16 2: work note: PDB creation started
17 3: capture container state, OMF setting, seed datafile path
18 3: fold pre-check results into each job
19 3: show what the pre-check found
20 4: generate a PDB admin password for each job
21 4: create datafile directory when OMF is not in use
22 4: create, open and save the state of the PDB
23 4: show the creation outcome (no credentials)
→ Best practice: the admin password never appears in this outcome log — it's disclosed exactly once, in a dedicated work note, never written to disk logs
24 5: capture v$pdbs after the operation
25 5: determine the outcome for each change
26 5: per-change outcome
27 6: ensure the log directory exists
28 6: set the run timestamp
29 6: write the evidence log for each change
30 6: attach the evidence log to the change request
→ WARN: if evidence log could not be attached
31 7: post the result work note
→ Confirm the work notes were posted
32 8: move changes to review
33 8: close SUCCESSFUL changes (close_code successful)
34 8: close FAILED changes (close_code unsuccessful)
→ WARN: if a change could not be closed
35 9: create DBA repair follow-up change
36 10: final summary
DDP - Drop / Decommission Pluggable Database Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: DDP | host | CDB | PDB_TO_DROP
MANAGEMENT+CAB approval always required (destructive). Includes ITIL change classification and documented risk-mitigation controls
Start Process ServiceNow Decision Approval SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 Drop Pluggable Database (DDP) — ServiceNow-driven decommission
2 Parse DDP spec: 'DDP | host | CDB | PDB'
3 Extract spec fields
→ Malformed spec — skip this CR
→ Unknown host — skip this CR
4 Resolve oracle_home/SID/user from oracle_instance_map
4b ITIL alignment: classify this drop — link the CMDB Configuration Item to host:CDB:PDB, set risk=High/impact=Medium, and record a planned 1-hour change window
5 Set ready-to-execute flag default
6 New change: promote to Assess, hold for approval
7 Pending approval or mid-walk: advance if approved
8 Already at Implement — ready to execute
→ Not ready this pass — stop here
9 Check for required confirmation line
10 Escalate block reason if target is protected
→ Risk mitigation: a literal 'CONFIRM DROP <PDB>' phrase requirement plus a protected-name blocklist together prevent an accidental or automated drop of the wrong or a protected database
→ Handle blocked drop — close unsuccessful with follow-up
11 Ensure base working directory exists, world-traversable
12 Ensure remote working directory exists
13 Write pre-drop snapshot SQL
→ Best practice: capturing a full pre-drop snapshot (DBA_PDBS, tablespaces, datafiles) before an irreversible operation preserves evidence of exactly what existed beforehand
14 Write drop SQL — close then drop including datafiles
15 Write execution wrapper (explicit ORACLE_SID export — sudo strips env)
16 Post work note — drop starting
17 Execute snapshot + drop as oracle user
18 Read drop log
19 Determine outcome (splitlines only)
20 Refine outcome by scanning log lines for ORA- errors
21 Downgrade to failure if any ORA- error line is present
→ 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
22 Attach snapshot + drop logs to the change request
→ WARN: if any log failed to attach
23 Post work note — outcome summary
24 Walk Implement → Review
25 Walk Review → Closed successful
26 Walk Review → Closed unsuccessful
→ WARN: if this CR could not be closed
27 Open DBA follow-up on failed drop
DCR - Run Data Change Script Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: DCR | ENVIRONMENT | title (+ .sql attachment)
MANAGEMENT approval for PROD only. Includes ITIL change classification and documented risk-mitigation controls. Own custom intake (not COMMON_snow_intake.yml)
Start Process ServiceNow Decision Approval SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 0: create timestamped working directory (snow_work_dir)
2 0: pre-flight: verify instance awake + credentials valid
3 0: abort with clear message if instance unreachable (HIBERNATING PDI / 401 check)
4 0: query DCR-tagged CRs in new/assess/authorize/scheduled/implement
5 0: collect tagged CRs, parse target environment
6 0: annotate each change: env + is_prod flag from short_description
7 0: intake summary
7b ITIL alignment: classify each change — link the CMDB Configuration Item by environment, set risk=Medium/impact=Medium, and record a planned 1-hour change window
8 0: promote NON-PROD new → scheduled (step 1 of 2)
9 0: promote NON-PROD scheduled → implement (step 2) — no approval needed
→ WARN: NON-PROD change could not advance to implement
10 0: PROD: request approval (state=assess, no CAB)
→ Risk mitigation: PROD-only approval gating reserves the highest-consequence environment for human review while non-PROD stays fast-moving and low-friction
11 0: note PROD changes awaiting approval
12 0: APPROVED PROD: assess → authorize → scheduled → implement
13 0: re-query executable queue (state=implement)
14 0: build snow_work + snow_cr_map (reject approval in [requested, rejected])
15 0: executable queue summary
16 1: end play when snow_work length == 0
17 3: create per-change subdirectory in snow_work_dir
18 4: list attachments on each CR (GET /api/now/attachment)
19 4: build change → attachment pairs
→ WARN: duplicate .sql filenames on a change (community.general.counter)
20 4: download .sql attachments (case-insensitive regex, mode 0755)
21 4b: rename .sql → .ssh (bash param expansion, case-insensitive)
22 5: build scripts_to_run (issue_key, script_path), only successful downloads
→ WARN: if none downloaded
23 5b: insert connection header at top of each script
24 5c: insert ORACLE_HOME shell heading wrapper at top
25 5d: append 'EOF' footer at bottom of each script
26 6: execute shell scripts (bash script_path, ignore_errors)
27 6a: write combined stdout/stderr/exit-code to .log file
28 6b: build stdout tail lookup (last 10 non-empty lines) + show per-script results
29 6c: read log file per script (slurp) + extract/count error lines
→ Best practice: scanning execution logs for error patterns (ORA-/SP2-/ERROR/WARN) rather than trusting exit codes alone catches partial or silent failures
30 6d: show detailed error report for failed scripts
31 7: mark issue FAILED if any script rc≠0, else DONE
32 7: show final per-issue outcomes
33 7b: post work-note summary per change
34 8: move SUCCESSFUL to Review + Close SUCCESSFUL
35 9: move FAILED to Review (required before closing) + Close FAILED
→ WARN: if FAILED change could not be closed
36 10: upload execution logs as ServiceNow attachments
37 10b: write failure report per FAILED issue
38 10b: COMMON_snow_create_followup.yml (fu_tag=DCR)
39 11: build + print summary (per-issue outcomes, per-script error details)
40 12: rename snow_work_dir to _failed (failed runs only), confirm final path
CAU - Create Application User Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: CAU | Full Name | DB1,DB2,...
MANAGEMENT approval required. Includes ITIL change classification and documented best-practice controls
Start Process ServiceNow Decision Approval SQL/Execute ITIL Alignment Best Practice Close OK Close Failed Follow-up
1 0-1: ServiceNow intake and shepherd for CAU
2 1: show 'No Tasks Found', end play when queue empty
→ WARN: missing user name or databases — skipped
3 2: build change → user + target databases mapping
4 2: show user → Oracle username → databases mapping
4b ITIL alignment: classify each job — link the CMDB Configuration Item to the primary target database, set risk=Low/impact=Low, and record a planned 1-hour change window
5 3: generate random password for each issue
→ Best practice: a single generated 16-character password (upper/lower/digit/special) replaces any hand-typed or reused credential, and never touches Ansible logs (no_log)
6 3: attach generated password to each issue entry
7 4: create Oracle user + grant privileges in each target database
8 4b: show create-user outcome per issue per database
9 4c: set log run timestamp
10 4c: ensure the CAU log directory exists
11 4c: write the execution log for each change
12 4c: upload the execution log to the change request
→ WARN: if execution log could not be attached
13 5: write worklog payload JSON to a temp file
14 5a: write worklog-posting Python script to temp file
15 5b: execute the work-note-posting script against payload
16 5c: show work-note posting outcome (no credentials)
→ WARN: if the work-note script failed
17 5d: clean up temp payload and script files
18 6: mark issues DONE (all databases succeeded)
19 6: mark issues FAILED (any database failed)
20 6: move SUCCESSFUL to review, Close SUCCESSFUL
21 6: work-note FAILED changes with failure summary
22 6: close FAILED changes (close_code unsuccessful)
23 6c: write failure report per FAILED issue
24 6c: create DBA follow-up task for FAILED issues
25 7: build + print final summary
PAT - Apply Oracle Patch Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: PAT | host:SID | patch_number
MANAGEMENT+CAB approval required. Includes orchestration dependency gating, ITIL change classification, and documented risk-mitigation controls
Start Process ServiceNow Decision Approval SQL/Execute Orchestration ITIL Alignment Cost Containment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 PLAY1: fetch ServiceNow PAT changes and build patch job list
2 0: remove stale per-issue and combined outcome files from any prior run
3 0-1: ServiceNow intake and shepherd for PAT (MANAGEMENT+CAB)
4 1: show 'No Tasks Found' and exit cleanly when queue is empty
5 2: WARN and skip changes with a missing or unknown patch target
6 2: build patch job list from change requests
7 3: write patch job list to temp file for PLAY 2 + ServiceNow credential sidecar
8 ORCHESTRATION GATE CHECK per job: any unsatisfied 'pre' dependency registered? (e.g. app server not yet stopped)
→ First encounter: spawn dependent CR (e.g. APPSTOP), insert orchestration_run + step — gate NOT clear this pass
→ Nothing left this pass: end play, retry next cron cycle
8b ITIL alignment: classify each remaining job — link the CMDB Configuration Item, set risk=Medium/impact=Medium, and record a planned 2-hour change window
9 PLAY2-INIT: read + decode patch job list
10 JOB: set Oracle environment for target host
11 JOB: work note: patching started
12 JOB: run opatch lsinventory (BEFORE patching, full output)
13 JOB: flag whether the patch is already applied
→ Best practice: checking whether the patch is already applied avoids a redundant, risk-bearing re-application against a system already in the target state
14 JOB: write + upload BEFORE inventory to the change request
15 JOB: create patch staging directory on target host
16 JOB: download patch from Oracle MOS (on controller) — fail if download failed
17 JOB: copy zip to target host, unzip — fail if unzip failed
18 JOB: check current vs. required OPatch version (from patch README)
19 JOB: download + install new OPatch (6880880) if upgrade needed
20 JOB: stop listener (if running)
21 JOB: shut down Oracle database — fail if not clean
→ Risk mitigation: refusing to proceed on an unclean shutdown prevents applying a binary patch underneath a database that hasn't fully released its files
22 JOB: kill remaining oracle processes using ORACLE_HOME (CheckActiveFilesAndExecutables)
23 JOB: ensure + verify patch directory readable by oracle
24 JOB: apply patch with opatch
25 JOB: start Oracle database, start listener
26 JOB: opatch lsinventory AFTER (confirm patch listed)
→ Risk mitigation: confirming the patch actually appears in the inventory after applying it, rather than trusting a clean exit code alone
27 JOB: write + upload AFTER inventory to the change request
28 JOB: determine overall job outcome
29 JOB: remove patch zip + extracted directory
→ Cost containment: removing the downloaded patch archive and staging files immediately after use avoids accumulating unnecessary disk consumption across repeated patch cycles
30 JOB: write per-issue outcome JSON, merge into combined outcomes file on controller
31 ORCHESTRATION FIRE POST for successful issues: check dependency_registry, open dependent CR (e.g. APPSTART) automatically
32 PLAY3-INIT: read + decode patch outcomes
33 10: post worklog entry per issue
34 11: move SUCCESSFUL to review, Close SUCCESSFUL
35 11: work-note FAILED changes with failure summary
36 11: close FAILED changes (close_code unsuccessful)
37 11b: write failure report per FAILED issue
38 11b: create DBA repair follow-up change
39 12: print final summary + clean up temp files
CDP - Change Database Password Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: CDP | Full Name
NO APPROVAL. Includes ITIL change classification and documented best-practice controls
Start Process ServiceNow Decision SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 0-1: ServiceNow intake and shepherd for CDP
2 1: abort if no work found, end play gracefully
→ WARN: missing user name — skipped
3 2: build change → Oracle user mapping
4 2: show reporter → Oracle user mapping
4b ITIL alignment: classify each reset — link the CMDB Configuration Item to the Oracle user account, set risk=Low/impact=Low, and record a planned 1-hour change window
5 3: generate random password for each Oracle user
6 3: attach generated password to each issue entry
7 4: reset Oracle password for each user in each database
8 4b: show password-reset outcome per issue per database
9 5: post new temporary password as work note (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
→ WARN: if the password work note could not be posted
10 6: mark issues DONE (all databases succeeded)
11 6: mark issues FAILED (any database failed)
12 6: move SUCCESSFUL to review, Close SUCCESSFUL
13 6: post failure work note and close FAILED changes
14 6c: set follow-up run timestamp
15 6c: ensure follow-up log dir exists
16 6c: write failure report (no stdout — may contain credentials)
→ Risk mitigation: the failure report deliberately omits raw stdout, which could contain a partially-applied password, keeping credentials out of any log a wider audience might read
17 6c: create DBA follow-up task for FAILED issues
18 7: build + print final summary
UPG - Oracle 21c → 26ai Upgrade Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: UPG | host | ORACLE_SID
MANAGEMENT+CAB approval required. Includes ITIL change classification, cost containment gate, and documented risk-mitigation controls
Start Process ServiceNow Decision Approval SQL/Execute ITIL Alignment Cost Containment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 Check 26ai Oracle home exists on target host
2 PRE: Assert 26ai home is installed
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 PRE: ServiceNow intake and shepherd for UPG (MANAGEMENT+CAB)
5 PRE: nothing to do? end play when queue is empty
6 PRE: build upgrade job list from change requests
7 JOB: dispatch upgrade job per issue
8 JOB: set per-job facts
9 JOB: work note: upgrade started
10 JOB: write source DB check script
11 JOB: check source DB version and state
12 JOB: detect whether the database is already upgraded
→ If already upgraded: work note + close successful when already upgraded — done, no further action
→ Best practice: idempotency check prevents a duplicate upgrade attempt against an already-upgraded database
13 JOB: determine whether a STARTUP is needed
14 JOB: bring the database to OPEN when it is not
15 JOB: assert source is 21c and OPEN
15b ITIL alignment: classify this change — link the CMDB Configuration Item, set risk=High/impact=High, and record a planned change window (Forward Schedule of Change)
16 Cost containment gate: estimate out-of-place clone storage cost (size_gb × cost_storage_per_gb_onetime)
17 Cost containment gate: check/request cost approval (COMMON_snow_cost_check.yml) — creates a u_cost_approval record on first encounter
→ If budget not yet approved: stop here for this issue, retry next pass (meta: end_host) — no clone or disk work begins until a budget owner approves
→ Cost containment: gating the storage-heavy clone behind explicit budget approval prevents surprise infrastructure charges before they're incurred, not after
18 JOB: create job directories
19 JOB: write autoupgrade.cfg
20 JOB: write AutoUpgrade analyze script
21 JOB: run AutoUpgrade ANALYZE phase (pre-upgrade check)
22 JOB: parse analyze summary
23 JOB: assert analyze passed
→ Risk mitigation: the ANALYZE phase surfaces upgrade-blocking issues before any irreversible change is attempted
24 JOB: read analyze status.log
25 JOB: work note: analyze complete
26 JOB: write AutoUpgrade deploy script
27 JOB: launch AutoUpgrade DEPLOY (async)
28 JOB: wait for deploy to finish (can take hours)
29 JOB: parse deploy summary
30 JOB: assert deploy succeeded
31 JOB: write post-upgrade bounce/verify script
32 JOB: restart database under 26ai home and verify
33 JOB: assert database is on 26ai and OPEN
34 JOB: ensure /etc/oratab points at 26ai home
35 JOB: work note: upgrade complete (Guaranteed Restore Point retained deliberately)
→ Best practice: the Guaranteed Restore Point is deliberately kept after success, not dropped immediately — a real rollback path stays available through the post-upgrade observation window
36 JOB: move change to review
37 JOB: close change (close_code successful)
38 JOB: WARN if the change could not be closed
→ Any of the assertions above failing: JOB: classify failure (special-cases stale recovery data, with resume/clear guidance)
→ Best practice: failure classification distinguishes a genuinely new failure from stale recovery data left behind by an earlier interrupted run, avoiding a misleading diagnosis
39 JOB: work note: upgrade failed, DBA review required
40 JOB: close change (close_code unsuccessful)
41 JOB: write failure report for follow-up
42 JOB: fetch AutoUpgrade status.log from host
43 JOB: create DBA repair follow-up task
44 JOB: clean up temp scripts
PRF - AWR Performance Analysis Workflow (SIMPLIFIED, ITIL-ALIGNED)
Spec: PRF | host | ORACLE_SID | START_DATETIME | END_DATETIME
NO APPROVAL — read-only analysis. Includes ITIL change classification and documented risk-mitigation controls
Start Process ServiceNow Decision SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1 PRE: ServiceNow intake and shepherd for PRF
2 PRE: nothing to do? end play when queue empty
3 PRE: build job list (split short_description: host/SID/start_dt/end_dt)
→ WARN: unusable spec — expects date-times like '2026-02-21 21:02:00'
→ Unknown host — skip this CR
4 JOB: resolve oracle_home/user from oracle_instance_map
5 JOB: set per-job facts
5b ITIL alignment: classify this analysis — link the CMDB Configuration Item to host:SID, set risk=Low/impact=Low, and record a planned 1-hour change window
6 JOB: work note: analysis started
7 JOB: ensure base working directory exists
8 JOB: ensure per-job working directory writable
9 JOB: write snapshot-resolution SQL
10 JOB: write snapshot-resolution wrapper (explicit ORACLE_SID)
11 JOB: resolve snapshot IDs as oracle
12 JOB: parse resolved snapshot IDs
→ Fail cleanly if no bracketing snapshots found
13 JOB: show resolved snapshot range
14 JOB: write AWR report SQL
15 JOB: write top-5 CPU SQL_ID query
16 JOB: write execution wrapper (explicit ORACLE_SID)
17 JOB: run AWR report + top-5 query as oracle
18 JOB: parse top-5 SQL_IDs from output
→ Determine whether top-5 extraction succeeded
19 JOB: show top SQL_IDs found
20 JOB: write tuning advisor script per SQL_ID
21 JOB: execute tuning advisor per SQL_ID (ALTER SESSION SET CONTAINER first)
→ Risk mitigation: the SQL Tuning Advisor only ever produces advice — execute_tuning_task never modifies the database, so this entire analysis carries zero write risk to production data
22 JOB: read each tuning advisor report
23 JOB: build one-line recommendation summary per SQL_ID
24 JOB: attach AWR report to the change request
25 JOB: attach each tuning report to the change request
26 JOB: post analysis summary work note
27 JOB: walk Implement → Review
28 JOB: close successful
29 JOB: close unsuccessful
30 JOB: open follow-up for DBA team to implement fixes
→ Best practice: any recommended fix (new index, SQL Profile) is routed through a separate, approval-gated follow-up change rather than applied automatically — analysis and implementation stay cleanly separated
31 JOB: open DBA repair follow-up on total failure
32 JOB: clean up temp files

Please download this code from https://github.com/r-able/Oracle-SelfService . The code is free.