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

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).