Difference in DBA_SCHEDULAR_JOBS - Primary/Standby

Follow

Description

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.

Ankit Arora September 23, 2014 13:48

Have more questions? Submit a request

Comments