Difference in DBA_SCHEDULAR_JOBS – Primary/Standby

When the application schemas are compared from Primary and Standby we see a difference in sequence cache value and dba_scheduler_jobs. Here sequence values seems to be due to an Archive gap but dba_schedular_job is the one which is a real concern to us. This article explains why there are no records in standby and records in production .

Explanation

We did testing and found that there are 3 main tables for Schedules:

1. DBA_SCHEDULER_JOB_RUN_DETAILS: Displays log run details for all Scheduler jobs in the database.
2. DBA_SCHEDULER_JOBS: It displays information about all Scheduler jobs in the database.
3. DBA_SCHEDULER_JOB_LOG: It displays log information for all Scheduler jobs in the database.

DBA_SCHEDULER_JOBS only shows information about all Scheduler jobs in the database. Interestingly a scheduled job can exist on either a primary database or a logical standby database, but a scheduled job cannot exist on a physical standby database because it would then be possible for a scheduled task to run on both the primary and its physical standby counterpart. However, for a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes.

You can look at the following tables and compare records in Standby are updated with Primary:

• DBA_SCHEDULER_JOB_RUN_DETAILS
• DBA_SCHEDULER_JOB_LOG

DBMS_SCHEDULER uses the DATABASE_ROLE attribute to determine whether it should run on either:

• (a) The primary database.

or

• (b) A specified logical standby database.

You can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role. During a switchover or failover, the new role of the database is recognized and the appropriate jobs will be run based on the new role.

So Ideally the dba_scheduler_jobs would have no data for Standby Database

Posted from:https://support.dbvisit.com/hc/en-us/articles/216506768-Difference-in-DBA-SCHEDULAR-JOBS-Primary-Standby

dbms_scheduler log history – purging manually

Today, I came across a SYSAUX tablespace that was asking for more space. It was about 12Gb, which seems a bit large to me. If you use the $ORACLE_HOME/rdbms/admin/awrinfo.sql , you will get a report of the occupants.  Occupant JOB_SCHEDULER took over 11Gb space, which makes you think.

The default out-of-the-box maintainance job, uses the global attribute LOG_HISTORY to remove old dbms_scheduler job logging:

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

Now this is default 30 days, and can be set with:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘log_history’,’15’);

But what if your maintainance jobs are failing for some reason (timeout/bugs?). You may need to clean it manually. The easy way to do this, is to use:

exec DBMS_SCHEDULER.PURGE_LOG(<days>, which_log=>’JOB_LOG’);

so for example:

exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>’JOB_LOG’);

But if the number of logs is very large, you have to do this carefully, step by step:

First determine the distribution of logging you have.

Select count(1) from dba_scheduler_job_log where log_date < sysdate – 100;

Make sure that you get an idea like:

older than 300 days	12 rows
older than 250 days	12831 rows
older than 200 days	438121 rows

Posted from: https://webgeest.blogspot.com/2015/06/purge-loghistory.html

Oracle Scheduler SQL Cheat Sheet

 Oracle Scheduler is a built-in job scheduler in Oracle Database that enables you to manage and execute various tasks, such as running database program units, external executables, and scripts. It provides a flexible and sophisticated way to schedule jobs based on time, events, or dependencies, allowing you to automate routine tasks and reduce manual intervention.

The Scheduler offers advanced features, including prioritization of jobs based on business requirements, resource allocation, and monitoring of job execution. It also supports execution of jobs in a clustered environment, such as Oracle Real Application Clusters (Oracle RAC). With Oracle Scheduler, you can streamline your database operations, improve reliability, and reduce operating costs.

You can use following SQLs to manage your Scheduler and the jobs:

Check Scheduler Job Details in CDB

SQL

— View job details in the Container Database (CDB)

SELECT

  CON_ID,

  JOB_NAME,

  JOB_TYPE,

ENABLED,

STATE,

  NEXT_RUN_DATE,

  REPEAT_INTERVAL

FROM

  cdb_scheduler_jobs;

Monitor Currently Running Jobs

SQL

— View currently running jobs

SELECT

  job_name,

  session_id,

  running_instance,

  elapsed_time

FROM

  dba_scheduler_running_jobs;

View Job Run Details

SQL

— View details of job runs

SELECT

*

FROM

  DBA_SCHEDULER_JOB_RUN_DETAILS;

View Job-Related Logs

SQL

— View logs related to job execution

SELECT

*

FROM

  DBA_SCHEDULER_JOB_LOG;

Check All Scheduler Schedules

SQL

— Set formatting options for output

SET PAGESIZE 200

SET LINES 299

COL START_DATEFOR A45

COL REPEAT_INTERVAL FOR A45

COL schedule_name FOR A34

— View all scheduler schedules

SELECT

  schedule_name,

  schedule_type,

start_date,

  repeat_interval

FROM

  dba_scheduler_schedules;

History of All Scheduler Job Runs

SQL

— Set formatting options for output

SET PAGESIZE 299

SET LINES 299

COL JOB_NAME FOR A24

COL actual_start_date FOR A56

COL RUN_DURATION FOR A34

— View history of all scheduler job runs

SELECT

  job_name,

status,

  actual_start_date,

  run_duration

FROM

  DBA_SCHEDULER_JOB_RUN_DETAILS

ORDER BY

  ACTUAL_START_DATE DESC;

CheckLog Information for All Scheduler Jobs

SQL

— Set formatting options for output

SET PAGESIZE 299

SET LINES 299

COL job_name FOR A24

COL log_date FOR A40

COL operation FOR A19

COL additional_info A79

— View log information for all scheduler jobs

SELECT

  job_name,

  log_date,

status,

  OPERATION,

  ADDITIONAL_INFO

FROM

  dba_scheduler_job_log

ORDER BY

  log_date DESC;

Check All Scheduler Windows Details

SQL

— Set formatting options for output

SET PAGESIZE 300

SET LINESIZE 200

— View all scheduler windows details

SELECT

*

FROM

  dba_scheduler_windows;

Posted from: https://www.fahdmirza.com/2025/01/oracle-scheduler-sql-cheat-sheet.html

OWNER vs JOB_CREATOR in DBMS_SCHEDULER

A few days ago I received an email from a subscriber. The question looked simple… but it came with a twist:

Good morning Javier, 
I have two database users: 
1. OWNER 
2. DBA 

DBA creates a job where the owner is user OWNER.
The job runs without issues.
The user DBA is dropped.

Tricky question:  What happens with this job?
1) It runs? 
2) It Fails?
Why? 

The “automatic” answer many of us would give (me included) is: if the job “belongs to” OWNER and OWNER still exists, then it should keep running.

But Oracle Scheduler stores more information than we typically look at out of habit… and that’s exactly where the surprises show up.

Only checking the OWNER column: professional reflex

I’m the first one guilty of this.

I always go straight to OWNER to know who owns an object. A table might be owned by one user while its index ends up owned by SYSTEM (if a DBA created it, it can happen), or you might be checking who owns a DB link, a synonym, and so on.

So OWNER becomes the default reference column. But in Scheduler there is another concept that often goes unnoticed: the user who created the job.

When the creator and the owner are the same, there’s usually no big problem. Even when jobs are created by SYS or SYSTEM, you can assume the creator will always exist. But here we care about a very specific scenario: when OWNER and JOB_CREATOR are different.

This happens, for example, when a DBA (or a user with privileges such as CREATE ANY JOB) creates jobs “on behalf of” another schema.

What does the documentation say… and what does it omit?

Oracle’s Scheduler documentation explains the model, privileges, and objects (jobs, programs, schedules, chains), and makes it clear there are multiple ways to manage jobs.

But it usually does so from a “functional” point of view: it talks about creating jobs, enabling/disabling them, monitoring them, etc. What it doesn’t really cover are best-practice recommendations, nor does it warn you about collateral effects of offboarding (when a person’s account is removed due to security policy).

And that’s exactly what I miss in the documentation: a note or warning that says something like:

• “If you run DROP USER on the creator, even if the owner still exists, the job becomes BROKEN.”

AskTom and Oracle Support

Apparently, this didn’t happen only to this subscriber, and it’s not a theoretical paranoia. It has happened to more than one person and you can see it as a real case on AskTom (Chris Saxon): when the creator is removed (DROP USER), some jobs end up broken. And in the job log you can see an explicit reason:

• REASON="Job creator: <usuario> dropped"

Reference (AskTom):
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9537363000346826584

There is also a support note that corroborates this behavior (in the sense of “this can happen and there is an explanation”). In my opinion, though, this is something we should be reading in the public documentation—not discovering through support after jobs suddenly become broken while the OWNER itself has not changed.

In other words, in many teams the problem is discovered when the job fails.

And you need to be careful, because recreating the ‘creator’ user does not automatically fix the problem.

Posted from:https://cafedatabase.com/en/owner-vs-job_creator-in-dbms_scheduler/