Oracle snapshot standby database's scheduler jobs are not executed

Oracle snapshot standby database's scheduler jobs are not executed

In Oracle 11g, the snapshot backup snapshot standby database feature of data guard is more suitable for rapid deployment of a temporary test database that is the same as the online environment. For the construction method, see :. recently, it was found that the scheduler jobs of the snapshot backup snapshot standby database were not executed, and the information of scheduler job was not found in DBA_SCHEDULER_JOBS.

By referring to the Jobs are not working after running uard Switchover/Failover (Document ID 1292755.1), we found that the problem lies in database_role, and the v $ database of the snapshot standby database. database_role is snapshot standby, but dba_scheduler_job_roles.database_role is consistent with that of the master database. Therefore, you can use the dbms_scheduler.set_attribute method to change database_role of job_name.
The solution is as follows:
Db version: 11.2.0.4
OS: CentOS 6.6 x86_64
-- View database role database_role
Select database_role from v $ database;
/*
DATABASE_ROLE
SNAPSHOT STANDBY
*/
-- View SCHEDULER_JOBS and find that nothing is displayed
Select OWNER, JOB_NAME from DBA_SCHEDULER_JOBS;
/*
OWNER JOB_NAME
*/
-- View dba_scheduler_job_roles
Select job_name, database_role, enabled from dba_scheduler_job_roles;
/*
JOB_NAME DATABASE_ROLE ENABLED
XMLDB_NFS_CLEANUP_JOB PRIMARY FALSE
SM $ CLEAN_AUTO_SPLIT_MERGE PRIMARY TRUE
RSE $ CLEAN_RECOVERABLE_SCRIPT PRIMARY TRUE
FGR $ AUTOPURGE_JOB PRIMARY FALSE
BSLN_MAINTAIN_STATS_JOB PRIMARY TRUE
DRA_REEVALUATE_OPEN_FAILURES PRIMARY TRUE
HM_CREATE_OFFLINE_DICTIONARY PRIMARY FALSE
ORA $ AUTOTASK_CLEAN PRIMARY TRUE
FILE_WATCHER PRIMARY FALSE
PURGE_LOG PRIMARY TRUE
AUTOGATHERACHIEVE PRIMARY TRUE
MGMT_STATS_CONFIG_JOB PRIMARY TRUE
MGMT_CONFIG_JOB PRIMARY TRUE
RLM $ SCHDNEGACTION PRIMARY TRUE
RLM $ EVTCLEANUP PRIMARY TRUE
*/
-- Modify the DATABASE_ROLE of the scheduler job to be run.
Begin
Dbms_scheduler.set_attribute (name => 'autogatherachieve ', attribute => 'database _ role', value => 'snapshot standby ');
End;
-- View the modified dba_scheduler_job_roles. At this time, DATABASE_ROLE has been changed to snapshot standby.
Select job_name, database_role, enabled from dba_scheduler_job_roles where job_name = 'autogatherachieve ';
/*
JOB_NAME DATABASE_ROLE ENABLED
AUTOGATHERACHIEVE SNAPSHOT STANDBY TRUE
*/
-- View SCHEDULER_JOBS. The scheduler job AUTOGATHERACHIEVE is also displayed.
Select owner, job_name from DBA_SCHEDULER_JOBS;
/*
OWNER JOB_NAME
SYSTEM AUTOGATHERACHIEVE
*/
-- Check whether the scheduler job runs normally through dba_scheduler_job_log.

Note:
If you use dbms_scheduler.set_attribute on 11.2.0.2 and 11.2.0.3, an error of RA-16612: string value too long for attribute "database_role" may occur, see Scheduler Job on a Snapshot Standby database does not exist in DBA_SCHEDULER_JOBS while its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS (Document ID 1551817.1). You can try to solve this problem by applying patch 13399711.
Of course, another way to solve this problem is to manually convert the physical standby into a similar snapshot standby Based on the snapshot standby principle. For details, refer to snapshot.

The Document ID 1292755.1 and 1551817.1 are as follows:
Jobs are not working after running uard Switchover/Failover (Document ID 1292755.1)

In this Document
Symptoms
Changes
Cause
Solution
References

Applies:
Oracle Server-Enterprise Edition-Version: 11.1.0.6 to 11.2.0.2-Release: 11.1 to 11.2
Information in this document applies to any platform.
Symptoms
Scenario:

1) login uard 11g with logical standby database

2) Customer implemented DBMS_SCHEDULER as SYS on both primary and standby site.
The job is listed in DBA_SCHEDULER_JOBS on primary/standby before a switchover/failover-action.

3) Customer completed MED into uard switchover.

4) After the switchover the job is gone from view DBA_SCHEDULER_JOBS on NEW primary site,
However the job is visible in DBA_OBJECTS.

Since the job is still available in dba_objects recreating or dropping the job fails:

ORA-27477: "SYS. MON_TMP_UNDO_JOB" already exists.

Changes
After upgrade from 10g to 11g the jobs doesn't work anymore on the new primary in case of a switchover/failover.
This worked fine in 10g.
Cause
A new attribute is introduced in 11g dbms_sched1_called "DATABASE_ROLE ".

Solution

In an Oracle 11g Data Guard environment you have to define the database role ('Primary 'or 'logical standby') in the DBMS_SCHEDULER package.

SQL> select job_name, database_role, enabled from dba_scheduler_job_roles;

Dbms_scheduler.set_attribute (name => 'xxxx ',
Attribute => 'database _ role', value => 'logical standby ');

For more details please see

Oracle? Data Guard, Concepts and Administration, 11g Release 2 (11.2)
Appendix C.8.2: Unsupported PL/SQL Supplied Packages

And

Oracle Database, PL/SQL Packages and Types Reference, 11g Release 2 (11.2)
Chapter 128: DBMS_SCHEDULER

And

Oracle? Database Administrator's Guide, 11g Release 2 (11.2)
Scheduler Support for Oracle Data Guard

######################################## ######################################## #########
Scheduler Job on a Snapshot Standby database does not exist in DBA_SCHEDULER_JOBS while its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS (Document ID 1551817.1)

In this Document
Symptoms
Cause
Solution
References

Applies:
Oracle Database-Enterprise Edition-Version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms

+ Oracle Database 11.2.0.3 (without fix of Bug 13399711 ).

+ On a Snapshot Standby database, a scheduler job record is not shown in DBA_SCHEDULER_JOBS view when its logs exist in DBA_SCHEDULER_JOB_RUN_DETAILS view.

+ Health Check reports zero potential errors on this instance.

+ There is an entry for this concerned job in OBJ $ & schedver $ _ JOB. However, no record is shown up in DBA_SCHEDULER_JOBS view.

+ Recreating this job errors out with ORA-27477 error (claiming that job already exists) as it actually exists in OBJ $ & SCHEDULER $ _ JOB.

+ Dropping this scheduler job or setting its DATABASE_ROLE to logical standby errors out with ORA-27476 error claiming that it does not exist as it does not show up in DBA_SCHEDULER_JOBS view.

+ When trying to set the database_role of the job to snapshot standby, the following error is reported:
SQL> exec DBMS_SCHEDULER.SET_ATTRIBUTE ('& scheduler_job_owner. & scheduler_job_name', 'database _ role', 'snapshot standby ');
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ('& scheduler_job_owner. & scheduler_job_name', 'database _ role', 'snapshot standby'); END;
*
ERROR at line 1:
ORA-16612: string value too long for attribute "database_role"
ORA-06512: at "SYS. DBMS_ISCHED", line 4478
ORA-06512: at "SYS. DBMS_SCHEDULER", line 2862
ORA-06512: at line 1

+ As a result, the scheduler job no longer runs on this standby database.
Cause

Bug 16217211 was created maid for this problem.

The job was not shown in your view because of the fact that it was filtered out due to the fact that DATABASE_ROLE of the scheduler job was set to PRIMARY while DATABASE_ROLE of the instance was set to SNAPSHOT STANDBY. hence, the scheduler job will be executed on this instance if and only if the instance turns to be PRIMARY.
For a record to appear in DBA_SCHEDULER_JOBS view, the DATABASE_ROLE of both the scheduler job and the instance must match.

 
 
Solution

1) Please apply Patch 13399711 on the culprit snapshot standby database. this patch is available on top of 11.2.0.3 for Linux x86-64. this patch fixes the ORA-16612 error that is reported when trying to set the DATABASE_ROLE of the scheduler job to snapshot standby to match that of the instance.

2) Then, kindly execute the following:

# Sqlplus/as sysdba
Set line 150

Select DBID, NAME, DATABASE_ROLE from V $ DATABASE;
Select * from DBA_SCHEDULER_JOBS where OWNER = '& scheduler_job_owner' and JOB_NAME = '& scheduler_job_name ';

Set serveroutput on

Declare
V_database_database_role VARCHAR2 (50): = '';
V_job_database_role VARCHAR2 (50): = '';
Begin
-- Checking database_role of the job
DBMS_SCHEDULER.GET_ATTRIBUTE ('& scheduler_job_owner. & scheduler_job_name', 'database _ role', v_job_database_role );
DBMS_OUTPUT.PUT_LINE ('database Role of the concerned job is: '| NVL (v_job_database_role, 'unknown '));

-- Checking the database_role of the database
Select DATABASE_ROLE into v_database_database_role from V $ DATABASE;
DBMS_OUTPUT.PUT_LINE ('database Role of the Database is: '| NVL (v_database_database_role, 'unknown '));

-- Setting the database_role of the job to that of the database (if not matching)
If (NVL (upper (v_job_database_role), '') <> NVL (upper (v_database_database_role),'') and v_database_database_role is not null) Then
DBMS_SCHEDULER.SET_ATTRIBUTE ('& scheduler_job_owner. & scheduler_job_name', 'database _ role', ''' | v_database_database_role | '''');
End IF;

-- Checking new database_role of the job
DBMS_OUTPUT.PUT_LINE ('current Database Role of the concerned job is: '| NVL (v_database_database_role, 'unknown '));

End;
/
Select * from DBA_SCHEDULER_JOBS where OWNER = '& scheduler_job_owner' and JOB_NAME = '& scheduler_job_name ';