# Oracle Dba Job Scheduling Automation

> USE THIS SKILL WHENEVER the user mentions DBMS_SCHEDULER, the Oracle Scheduler, scheduler jobs / programs / schedules / job classes / windows, job CHAINS, external (OS-executable) jobs, scheduler CREDENTIALS, calendaring repeat_interval strings, cron jobs or Windows Task Scheduler that drive Oracle work, BROKEN / FAILED / stuck scheduler jobs, rescheduling a job, "run a job now" / STOP_JOB / RUN_JOB, job-log retention/purge, or migrating a legacy DBMS_JOB — EVEN IF they do not name the specific task. Covers scheduler + OS-cron job MONITORING (T1), DBMS_SCHEDULER and external job CREATION, RESCHEDULING, job-chain configuration, and job-failure diagnose+remediate (T2, dry-run default), plus the plan-only Tier-3 runbooks for drops / forced runs / complex chains / credential creation / log purge / DBMS_JOB migration. Follows the oracle-dba-common contracts: SQLcl MCP saved connection dba_ai_conn, Oracle Wallet secrets (never plaintext, never SYS/SYSTEM by default), --dry-run defaults on T2, and tier gating (max s

- Skill: `shreyas70773/oracle-dba-job-scheduling-automation` (Agent Skill, multi-file: 27 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-job-scheduling-automation`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-job-scheduling-automation/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-job-scheduling-automation

---

# Oracle DBA - Job Scheduling & Automation

In-DB job orchestration (DBMS_SCHEDULER jobs, programs, chains, external/OS jobs,
credentials) and the host OS schedulers (cron / Windows Task Scheduler) that drive
the wrapper scripts, for Oracle 19c and 23ai (EE; single_instance and Data Guard;
on-prem). Everything self-executing is **T1 (observe)** or **T2 (reversible,
dry-run-default)**. Every **drop / forced run / complex-chain design / credential
creation / log purge / DBMS_JOB migration is T3** and lives only in
`references/runbooks.md` — it prints commands for a human and never self-executes.

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Scheduler job monitoring | L1 | T1 | `scripts/checks/check_scheduler_jobs.sh` / `.ps1` + `sql/scheduler_job_status.sql` | yes — read-only (emits KEYVAL metrics + threshold findings, never mutates) |
| Cron / OS scheduled-job monitoring | L1 | T1 | `scripts/checks/check_cron_jobs.sh` / `.ps1` | yes — read-only; scans host scheduler + oracle-dba logs; no DB needed |
| Job rescheduling (change repeat_interval) | L1 | T2 | `scripts/reschedule_job.sh` / `.ps1` + `sql/reschedule_job.sql` | yes — noop when interval already equals target (`state=already_set`); logs OLD interval for rollback |
| DBMS_SCHEDULER job creation (PL/SQL block) | L2 | T2 | `scripts/create_scheduler_job.sh` / `.ps1` + `sql/create_scheduler_job.sql` | yes — existing owner.name -> noop (`state=exists`); never drops/recreates |
| External / OS script job scheduling | L2 | T2 | `scripts/create_external_job.sh` / `.ps1` + `sql/create_external_job.sql` | yes — existing owner.name -> noop; requires a pre-existing CREDENTIAL; no OS password embedded |
| Job chain configuration (2-step linear template) | L2 | T2 | `scripts/configure_chain.sh configure` / `.ps1 configure` + `sql/configure_chain.sql` | yes — existing chain -> noop (`state=exists`); step PROGRAMS must pre-exist; never drops |
| Job chain inventory / status | L2 | T1 | `scripts/configure_chain.sh status` / `.ps1 status` + `sql/chain_status.sql` | yes — read-only |
| Job failure diagnosis | L2 | T1 | `scripts/job_failure.sh diagnose` / `.ps1 diagnose` + `sql/job_failure_diagnose.sql` | yes — read-only error-stack / job-log deep dive |
| Job failure remediation (clear BROKEN/FAILED + re-enable) | L2 | T2 | `scripts/job_failure.sh remediate` / `.ps1 remediate` + `sql/job_failure_remediate.sql` | yes — noop if already ENABLED/RUNNING & not broken; reversible via DISABLE |
| Drop a scheduler job | structural | **T3** | `references/runbooks.md#job-drop` | no — recreate from saved DDL only |
| Drop a job chain | structural | **T3** | `references/runbooks.md#chain-drop` | no — recreate from saved DDL only |
| Drop a scheduler program | structural | **T3** | `references/runbooks.md#program-drop` | no — recreate from saved DDL only |
| Force / stop an immediate job run (`RUN_JOB`/`STOP_JOB`) | structural | **T3** | `references/runbooks.md#force-run` | no — an extra run / forced stop is not undoable |
| Build a complex (multi-step / DAG) chain | design | **T3** | `references/runbooks.md#chain-build` | partial — re-runnable design; wrong rules can hang the chain |
| Create a scheduler credential (external jobs) | security | **T3** | `references/runbooks.md#credential-create` | partial — involves an OS password; wallet/secured prompt only |
| Purge scheduler job logs beyond policy | structural | **T3** | `references/runbooks.md#log-purge` | no — deletes audit history; prefer class `log_history` |
| Migrate a legacy DBMS_JOB to DBMS_SCHEDULER | structural | **T3** | `references/runbooks.md#dbmsjob-migrate` | partial — recreate-then-remove; 23ai desupports DBMS_JOB |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB
  prechecks, postchecks, idempotency probes, and `sql/*.sql` run through it via
  `connect_db` / `Connect-Db` (routing label `sqlcl-mcp`). Never SYS/SYSTEM — except
  inside a T3 runbook that explicitly states a privileged need, supplied
  interactively by the operator. The connected user needs the scheduler privileges
  for the action (`CREATE JOB` for own-schema jobs; `CREATE ANY JOB` / `MANAGE
  SCHEDULER` for cross-schema or class/log operations), granted out of band per site policy.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere.** PL/SQL job actions run as the connected schema user — no credentials
  embedded. **External (OS) jobs run behind a pre-existing scheduler CREDENTIAL**
  (`DBMS_CREDENTIAL`, created from the wallet via runbook `#credential-create`); the
  T2 external-job wrapper REFUSES a blank/placeholder credential (`ERR_SECRET` 8) and
  never embeds an OS password.
- **Host work routes to scripts, not SQLcl MCP** (per the routing policy): the OS
  cron / Task Scheduler monitor (`check_cron_jobs.*`) inspects the host scheduler and
  oracle-dba logs directly and needs NO DB connection.
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/jobs/jobs.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/jobs/jobs.log` (Windows); skill segment `jobs`.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. Runs on 19c and 23ai; DBMS_SCHEDULER syntax is version-neutral (see
  `references/version-notes.md`). On Data Guard, scheduler DDL is made on the PRIMARY
  and shipped via redo — never run scheduler DDL against a read-only standby.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2
scripts default to `--dry-run`; pass `--execute` to act. See each script's `--help`.
Every action: **trigger -> precheck (read-only guard) -> action (links below) ->
postcheck (verify) -> rollback note**.

### Scheduler job monitoring (T1)
- **Trigger**: scheduled (every few hours) or when investigating job failures.
- **Precheck**: `dba_ai_conn` reachable (read-only probe).
- **Action**: `scripts/checks/check_scheduler_jobs.sh [--max-broken N --max-failed-runs N]` / `.ps1` + `sql/scheduler_job_status.sql`.
- **Postcheck**: n/a — emits KEYVAL metrics (`broken_jobs`, `failed_runs_24h`, …) + non-fatal findings.
- **Rollback note**: none — read-only.

### Cron / OS scheduled-job monitoring (T1)
- **Trigger**: daily; verify scheduled wrappers are firing and not failing; works when the DB is down.
- **Precheck**: none (read-only; no DB needed).
- **Action**: `scripts/checks/check_cron_jobs.sh [--lookback-hours N --max-failures N --expect-pattern RE]` / `.ps1`.
- **Postcheck**: n/a — emits metrics (`scheduled_failures`, `cron_oracle_dba_lines`, …) + findings.
- **Rollback note**: none — read-only.

### Job rescheduling (T2)
- **Trigger**: change a job's run cadence (drift-correct or new requirement).
- **Precheck**: job exists (read-only probe).
- **Action**: `scripts/reschedule_job.sh --owner O --name J --interval 'FREQ=DAILY;BYHOUR=2' --execute` / `.ps1 -Owner -Name -Interval`.
- **Postcheck**: SQL reports `reschedule_state=changed|already_set`; next_run_date recomputed.
- **Rollback note**: re-run with the OLD interval (logged as `rollback_interval=` before the change).

### DBMS_SCHEDULER job creation (T2)
- **Trigger**: schedule a new in-DB PL/SQL job.
- **Precheck**: owner schema exists.
- **Action**: `scripts/create_scheduler_job.sh --owner O --name J --action 'BEGIN ...; END;' --interval 'CAL' [--enabled TRUE] --execute` / `.ps1`.
- **Postcheck**: job present in `DBA_SCHEDULER_JOBS` (created, or pre-existing -> noop).
- **Rollback note**: `DISABLE` then `DROP_JOB` — `references/runbooks.md#job-drop` (T3).

### External / OS script job scheduling (T2)
- **Trigger**: schedule an OS executable to run from the database under a credential.
- **Precheck**: the named scheduler CREDENTIAL exists (fail-closed if not — create it via `#credential-create`).
- **Action**: `scripts/create_external_job.sh --owner O --name J --executable /path --credential CRED --interval 'CAL' [--arguments a,b] --execute` / `.ps1`.
- **Postcheck**: external job present in `DBA_SCHEDULER_JOBS`.
- **Rollback note**: `DISABLE` then `DROP_JOB` — `#job-drop` (T3). No OS password is ever stored.

### Job chain configuration / status (T2 / T1)
- **Trigger**: build a "run B after A succeeds" 2-step chain (configure); audit chains (status).
- **Precheck**: both step PROGRAMS already exist (configure); `dba_ai_conn` reachable (status).
- **Action**: `scripts/configure_chain.sh configure --owner O --name C --step1 S1 --step1-prog P1 --step2 S2 --step2-prog P2 --execute` / `.ps1 configure`; `... status [--owner --name]`.
- **Postcheck**: chain present+enabled in `DBA_SCHEDULER_CHAINS` (configure); inventory printed (status).
- **Rollback note**: `DISABLE` then `DROP_CHAIN` — `#chain-drop` (T3). Complex DAGs -> `#chain-build` (T3).

### Job failure diagnose / remediate (T1 / T2)
- **Trigger**: a job is BROKEN/FAILED. ALWAYS `diagnose` first; `remediate` only AFTER the root cause is fixed.
- **Precheck**: `dba_ai_conn` reachable (diagnose); job exists (remediate).
- **Action**: `scripts/job_failure.sh diagnose --owner O --name J` / `.ps1 diagnose`; `... remediate --owner O --name J --execute` / `.ps1 remediate`.
- **Postcheck**: remediate reports `re_enabled|already_enabled|running_skip`.
- **Rollback note**: `DBMS_SCHEDULER.DISABLE` re-disables a job you just re-enabled.

## Tier-3 runbooks

All **drops, forced/stopped runs, complex-chain design, credential creation, log
purge, and DBMS_JOB migration** are Tier 3 and live in **`references/runbooks.md`**:
`#job-drop`, `#chain-drop`, `#program-drop`, `#force-run`, `#chain-build`,
`#credential-create`, `#log-purge`, `#dbmsjob-migrate`.

These runbooks **PRINT exact DBMS_SCHEDULER / DBMS_CREDENTIAL / DBMS_JOB commands for a
human operator and NEVER self-execute.** They are gated by `require_approval_token` /
`Require-ApprovalToken`: an automated agent's only sanctioned action is to print the
relevant section and stop with `ERR_APPROVAL` (6) until a human supplies a real change
ticket/token (`--token <TICKET>` or `ODB_APPROVAL_TOKEN`; placeholders are rejected).
The T2 scripts deliberately refuse the destructive escape hatches (no `DROP_JOB`,
`DROP_CHAIN`, `RUN_JOB`/`STOP_JOB`, credential creation) and point to the runbook
instead. When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas (DBMS_SCHEDULER is
version-neutral for this skill; DBMS_JOB is desupported in 23ai — see `#dbmsjob-migrate`).